2017. 2. 19. 20:57 오라클
Oracle 12cR2 vesion 이 Exadata Version 용으로 1주일전에 조용하게 Release 되었다.
현재 Oracle Database Enterprise Edition 12.2.0.1 For Linux x86-64 만 가능한듯 하다. (Standard 도 별도 다운 가능한듯)
Non Exadata 환경에서도 테스트는 가능할듯 싶은데 이래저래 시간이 없네..
2017.2.10 Release 되었고 Oracle 계정만 있다면 edelivery.oracle.com 에서 다운로드 사용가능하다.
- Non Exadata 환경에서 실행시켜 본 모습
- edelivery.oracle.com 에 올라온 모습
2017. 1. 25. 16:59 오라클
linux 7.x 에서 11.2.0.4 RAC 설치시 root.sh 실행후 에러 발생 할때...
------------------------------------------------------------------
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oragrid/product/11.2.0.4/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization - successful
root wallet
root wallet cert
root cert export
peer wallet
profile reader wallet
pa wallet
peer wallet keys
pa wallet keys
peer cert request
pa cert request
peer cert
pa cert
peer root cert TP
profile reader root cert TP
pa root cert TP
peer pa cert TP
pa peer cert TP
profile reader pa cert TP
profile reader peer cert TP
peer user cert
pa user cert
Adding Clusterware entries to inittab
ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2016-06-23 12:45:29.782:
> CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
>End Command output
2016-06-23 16:25:13: Starting CSS in exclusive mode
2016-06-23 16:25:13: Executing cmd: /oragrid/product/11.2.0.4/bin/crsctl start resource ora.cssd -init -env CSSD_MODE=-X
2016-06-23 16:25:34: Command output:
해결책 : root.sh 실행전 patch 18370031 먼저 적용 !!
압축풀고 opatch napply -oh $GRID_HOME -local ./18370031
적용되면 root.sh 실행 !! 끝
2017. 1. 4. 15:21 오라클
ora_nsa2 Process 에 대해서 찾아보다가..Online redo log를 standby server 로 전송하는 역할이고..
오라클의 X$ 뷰를 통해서 Background 프로세스의 이름 및 용도를 확인하는 방법까지..
11.2.0.4 기준 (297개 확인가능, 내가 만든 TEST instance 포함)
--------------------------------------------------------------------------------
set lines 120
column EXTERNAL_NAME format a13
column INTERNAL_NAME format a13
column DESCRIPTION format a50
SELECT x$ksbdd.ksbddidn AS external_name,
x$ksmfsv.ksmfsnam AS internal_name, x$ksbdd.ksbdddsc AS description
FROM x$ksbdd, x$ksbdp, x$ksmfsv
WHERE x$ksbdd.indx = x$ksbdp.indx AND x$ksbdp.addr = x$ksmfsv.ksmfsadr
ORDER BY 1;
EXTERNAL_NAME INTERNAL_NAME DESCRIPTION
------------- ------------- --------------------------------------------------
ABMR krbabrBgProc_ Auto BMR Background Process
ACFS kfofsbg_ ACFS CSS
ACMS ksacms_ Atomic Controlfile to Memory Server
ARB0 kfgbr0_ ASM Rebalance 0
ARB1 kfgbr1_ ASM Rebalance 1
ARB2 kfgbr2_ ASM Rebalance 2
ARB3 kfgbr3_ ASM Rebalance 3
ARB4 kfgbr4_ ASM Rebalance 4
ARB5 kfgbr5_ ASM Rebalance 5
ARB6 kfgbr6_ ASM Rebalance 6
ARB7 kfgbr7_ ASM Rebalance 7
ARB8 kfgbr8_ ASM Rebalance 8
ARB9 kfgbr9_ ASM Rebalance 9
ARBA kfgbrA_ ASM Rebalance 10
ARC0 kcrrd0_ Archival Process 0
ARC1 kcrrd1_ Archival Process 1
ARC2 kcrrd2_ Archival Process 2
ARC3 kcrrd3_ Archival Process 3
ARC4 kcrrd4_ Archival Process 4
ARC5 kcrrd5_ Archival Process 5
ARC6 kcrrd6_ Archival Process 6
ARC7 kcrrd7_ Archival Process 7
ARC8 kcrrd8_ Archival Process 8
ARC9 kcrrd9_ Archival Process 9
ARCa kcrrdxa_ Archival Process 10
ARCb kcrrdxb_ Archival Process 11
ARCc kcrrdxc_ Archival Process 12
ARCd kcrrdxd_ Archival Process 13
ARCe kcrrdxe_ Archival Process 14
ARCf kcrrdxf_ Archival Process 15
ARCg kcrrdxg_ Archival Process 16
ARCh kcrrdxh_ Archival Process 17
ARCi kcrrdxi_ Archival Process 18
ARCj kcrrdxj_ Archival Process 19
ARCk kcrrdxk_ Archival Process 20
ARCl kcrrdxl_ Archival Process 21
ARCm kcrrdxm_ Archival Process 22
ARCn kcrrdxn_ Archival Process 23
ARCo kcrrdxo_ Archival Process 24
ARCp kcrrdxp_ Archival Process 25
ARCq kcrrdxq_ Archival Process 26
ARCr kcrrdxr_ Archival Process 27
ARCs kcrrdxs_ Archival Process 28
ARCt kcrrdxt_ Archival Process 29
ASMB kfnbgd_ ASM Background
CJQ0 kkjcjq0_ Job Queue Coordinator
CKPT kcvcdp_ checkpoint
CTWR krcbgp_ Change Tracking Writer
DBRM kskdbrm_ DataBase Resource Manager
DBW0 kcbdw0_ db writer process 0
DBW1 kcbdw1_ db writer process 1
DBW2 kcbdw2_ db writer process 2
DBW3 kcbdw3_ db writer process 3
DBW4 kcbdw4_ db writer process 4
DBW5 kcbdw5_ db writer process 5
DBW6 kcbdw6_ db writer process 6
DBW7 kcbdw7_ db writer process 7
DBW8 kcbdw8_ db writer process 8
DBW9 kcbdw9_ db writer process 9
DBWa kcbdw10_ db writer process 10 (a)
DBWb kcbdw11_ db writer process 11 (b)
DBWc kcbdw12_ db writer process 12 (c)
DBWd kcbdw13_ db writer process 13 (d)
DBWe kcbdw14_ db writer process 14 (e)
DBWf kcbdw15_ db writer process 15 (f)
DBWg kcbdw16_ db writer process 16 (g)
DBWh kcbdw17_ db writer process 17 (h)
DBWi kcbdw18_ db writer process 18 (i)
DBWj kcbdw19_ db writer process 19 (j)
DBWk kcbdw20_ db writer process 20 (k)
DBWl kcbdw21_ db writer process 21 (l)
DBWm kcbdw22_ db writer process 22 (m)
DBWn kcbdw23_ db writer process 23 (n)
DBWo kcbdw24_ db writer process 24 (o)
DBWp kcbdw25_ db writer process 25 (p)
DBWq kcbdw26_ db writer process 26 (q)
DBWr kcbdw27_ db writer process 27 (r)
DBWs kcbdw28_ db writer process 28 (s)
DBWt kcbdw29_ db writer process 29 (t)
DBWu kcbdw30_ db writer process 30 (u)
DBWv kcbdw31_ db writer process 31 (v)
DBWw kcbdw32_ db writer process 32 (w)
DBWx kcbdw33_ db writer process 33 (x)
DBWy kcbdw34_ db writer process 34 (y)
DBWz kcbdw35_ db writer process 35 (z)
DIA0 kjzdtp0_ diagnosibility process 0
DIA1 kjzdtp1_ diagnosibility process 1
DIA2 kjzdtp2_ diagnosibility process 2
DIA3 kjzdtp3_ diagnosibility process 3
DIA4 kjzdtp4_ diagnosibility process 4
DIA5 kjzdtp5_ diagnosibility process 5
DIA6 kjzdtp6_ diagnosibility process 6
DIA7 kjzdtp7_ diagnosibility process 7
DIA8 kjzdtp8_ diagnosibility process 8
DIA9 kjzdtp9_ diagnosibility process 9
DIAG kstdtp_ diagnosibility process
DMON rfm_dmon_proc DG Broker Monitor Process
_
DSKM kszdskmp_ slave DiSKMon process
EMNC kponc_ EMON Coordinator
FBDA ktfaarch_ Flashback Data Archiver Process
FMON ksfmfmon_ File Mapping Monitor Process
FSFP rfi_fsfp_ Data Guard Broker FSFO Pinger
GEN0 ksbgen0_ generic0
GMON kfdp_gmon_ diskgroup monitor
GTX0 k2qgtx0_ Global Txn process 0
GTX1 k2qgtx1_ Global Txn process 1
GTX2 k2qgtx2_ Global Txn process 2
GTX3 k2qgtx3_ Global Txn process 3
GTX4 k2qgtx4_ Global Txn process 4
GTX5 k2qgtx5_ Global Txn process 5
GTX6 k2qgtx6_ Global Txn process 6
GTX7 k2qgtx7_ Global Txn process 7
GTX8 k2qgtx8_ Global Txn process 8
GTX9 k2qgtx9_ Global Txn process 9
GTXa k2qgtx10_ Global Txn process 10
GTXb k2qgtx11_ Global Txn process 11
GTXc k2qgtx12_ Global Txn process 12
GTXd k2qgtx13_ Global Txn process 13
GTXe k2qgtx14_ Global Txn process 14
GTXf k2qgtx15_ Global Txn process 15
GTXg k2qgtx16_ Global Txn process 16
GTXh k2qgtx17_ Global Txn process 17
GTXi k2qgtx18_ Global Txn process 18
GTXj k2qgtx19_ Global Txn process 19
INSV rfi_insv_ Data Guard Broker INstance SlaVe Process
LCK0 kclck0_ Lock Process 0
LGWR kcrprc_ Redo etc.
LMD0 kjid0_ global enqueue service daemon 0
LMHB kjfmhdm_ lm heartbeat monitor
LMON kjidm_ global enqueue service monitor
LMS0 kjmlms0_ global cache service process 0
LMS1 kjmlms1_ global cache service process 1
LMS2 kjmlms2_ global cache service process 2
LMS3 kjmlms3_ global cache service process 3
LMS4 kjmlms4_ global cache service process 4
LMS5 kjmlms5_ global cache service process 5
LMS6 kjmlms6_ global cache service process 6
LMS7 kjmlms7_ global cache service process 7
LMS8 kjmlms8_ global cache service process 8
LMS9 kjmlms9_ global cache service process 9
LMSa kjmlmsa_ global cache service process 10
LMSb kjmlmsb_ global cache service process 11
LMSc kjmlmsc_ global cache service process 12
LMSd kjmlmsd_ global cache service process 13
LMSe kjmlmse_ global cache service process 14
LMSf kjmlmsf_ global cache service process 15
LMSg kjmlmsg_ global cache service process 16
LMSh kjmlmsh_ global cache service process 17
LMSi kjmlmsi_ global cache service process 18
LMSj kjmlmsj_ global cache service process 19
LMSk kjmlmsk_ global cache service process 20
LMSl kjmlmsl_ global cache service process 21
LMSm kjmlmsm_ global cache service process 22
LMSn kjmlmsn_ global cache service process 23
LMSo kjmlmso_ global cache service process 24
LMSp kjmlmsp_ global cache service process 25
LMSq kjmlmsq_ global cache service process 26
LMSr kjmlmsr_ global cache service process 27
LMSs kjmlmss_ global cache service process 28
LMSt kjmlmst_ global cache service process 29
LMSu kjmlmsu_ global cache service process 30
LMSv kjmlmsv_ global cache service process 31
LMSw kjmlmsw_ global cache service process 32
LMSx kjmlmsx_ global cache service process 33
LMSy kjmlmsy_ global cache service process 34
LSP0 dgllsp_ Logical Standby
LSP1 dgldbp_ Dictionary build process for Logical Standby
LSP2 dglsgsp_ Set Guard Standby Information for Logical Standby
MARK kfioSr_markp_ mark AU for resync koordinator
MMAN kmgsdp_ Memory Manager
MMNL kebmmnl_ Manageability Monitor Process 2
MMON kebmmon_ Manageability Monitor Process
MRP0 krsmmrp_ Managed Standby Recovery
NSA1 krswnsa1_ Redo transport NSA1
NSA2 krswnsa2_ Redo transport NSA2
NSA3 krswnsa3_ Redo transport NSA3
NSA4 krswnsa4_ Redo transport NSA4
NSA5 krswnsa5_ Redo transport NSA5
NSA6 krswnsa6_ Redo transport NSA6
NSA7 krswnsa7_ Redo transport NSA7
NSA8 krswnsa8_ Redo transport NSA8
NSA9 krswnsa9_ Redo transport NSA9
NSAA krswnsa10_ Redo transport NSAA
NSAB krswnsa11_ Redo transport NSAB
NSAC krswnsa12_ Redo transport NSAC
NSAD krswnsa13_ Redo transport NSAD
NSAE krswnsa14_ Redo transport NSAE
NSAF krswnsa15_ Redo transport NSAF
NSAG krswnsa16_ Redo transport NSAG
NSAH krswnsa17_ Redo transport NSAH
NSAI krswnsa18_ Redo transport NSAI
NSAJ krswnsa19_ Redo transport NSAJ
NSAK krswnsa20_ Redo transport NSAK
NSAL krswnsa21_ Redo transport NSAL
NSAM krswnsa22_ Redo transport NSAM
NSAN krswnsa23_ Redo transport NSAN
NSAO krswnsa24_ Redo transport NSAO
NSAP krswnsa25_ Redo transport NSAP
NSAQ krswnsa26_ Redo transport NSAQ
NSAR krswnsa27_ Redo transport NSAR
NSAS krswnsa28_ Redo transport NSAS
NSAT krswnsa29_ Redo transport NSAT
NSAU krswnsa30_ Redo transport NSAU
NSAV krswnsa31_ Redo transport NSAV
NSS1 krswnss1_ Redo transport NSS1
NSS2 krswnss2_ Redo transport NSS2
NSS3 krswnss3_ Redo transport NSS3
NSS4 krswnss4_ Redo transport NSS4
NSS5 krswnss5_ Redo transport NSS5
NSS6 krswnss6_ Redo transport NSS6
NSS7 krswnss7_ Redo transport NSS7
NSS8 krswnss8_ Redo transport NSS8
NSS9 krswnss9_ Redo transport NSS9
NSSA krswnss10_ Redo transport NSSA
NSSB krswnss11_ Redo transport NSSB
NSSC krswnss12_ Redo transport NSSC
NSSD krswnss13_ Redo transport NSSD
NSSE krswnss14_ Redo transport NSSE
NSSF krswnss15_ Redo transport NSSF
NSSG krswnss16_ Redo transport NSSG
NSSH krswnss17_ Redo transport NSSH
NSSI krswnss18_ Redo transport NSSI
NSSJ krswnss19_ Redo transport NSSJ
NSSK krswnss20_ Redo transport NSSK
NSSL krswnss21_ Redo transport NSSL
NSSM krswnss22_ Redo transport NSSM
NSSN krswnss23_ Redo transport NSSN
NSSO krswnss24_ Redo transport NSSO
NSSP krswnss25_ Redo transport NSSP
NSSQ krswnss26_ Redo transport NSSQ
NSSR krswnss27_ Redo transport NSSR
NSSS krswnss28_ Redo transport NSSS
NSST krswnss29_ Redo transport NSST
NSSU krswnss30_ Redo transport NSSU
NSSV krswnss31_ Redo transport NSSV
NSV0 rfins0_ Data Guard Broker NetSlave Process 0
NSV1 rfins1_ Data Guard Broker NetSlave Process 1
NSV2 rfins2_ Data Guard Broker NetSlave Process 2
NSV3 rfins3_ Data Guard Broker NetSlave Process 3
NSV4 rfins4_ Data Guard Broker NetSlave Process 4
NSV5 rfins5_ Data Guard Broker NetSlave Process 5
NSV6 rfins6_ Data Guard Broker NetSlave Process 6
NSV7 rfins7_ Data Guard Broker NetSlave Process 7
NSV8 rfins8_ Data Guard Broker NetSlave Process 8
NSV9 rfins9_ Data Guard Broker NetSlave Process 9
NSVA rfinsa_ Data Guard Broker NetSlave Process A
NSVB rfinsb_ Data Guard Broker NetSlave Process B
NSVC rfinsc_ Data Guard Broker NetSlave Process C
NSVD rfinsd_ Data Guard Broker NetSlave Process D
NSVE rfinse_ Data Guard Broker NetSlave Process E
NSVF rfinsf_ Data Guard Broker NetSlave Process F
NSVG rfinsg_ Data Guard Broker NetSlave Process G
NSVH rfinsh_ Data Guard Broker NetSlave Process H
NSVI rfinsi_ Data Guard Broker NetSlave Process I
NSVJ rfinsj_ Data Guard Broker NetSlave Process J
NSVK rfinsk_ Data Guard Broker NetSlave Process K
NSVL rfinsl_ Data Guard Broker NetSlave Process L
NSVM rfinsm_ Data Guard Broker NetSlave Process M
NSVN rfinsn_ Data Guard Broker NetSlave Process N
NSVO rfinso_ Data Guard Broker NetSlave Process O
NSVP rfinsp_ Data Guard Broker NetSlave Process P
NSVQ rfinsq_ Data Guard Broker NetSlave Process Q
NSVR rfinsr_ Data Guard Broker NetSlave Process R
NSVS rfinss_ Data Guard Broker NetSlave Process S
NSVT rfinst_ Data Guard Broker NetSlave Process T
NSVU rfinsu_ Data Guard Broker NetSlave Process U
PING ksxp_ping_ interconnect latency measurement
PMON ksuclp_ process cleanup
PSP0 kso_spawner0_ process spawner 0
QMNC kwqmnc_ AQ Coordinator
RBAL kfgbrm_ ASM Rebalance master
RCBG qesrcbg_ Result Cache: Background
RECO k2vbkg_ distributed recovery
RMS0 kjmrms0_ rac management server
RPOP ksfdss_repop_ instant restore repopulation daemon
RSM0 rfrmp0_ Data Guard Broker Resource Guard Process 0
RSMN ksvrrp_ Remote Slave Monitor
RVWR krfwrp_ Recovery Writer
SMCO ktsjsmco_ Space Manager Process
SMON ktmprc_ System Monitor Process
TEST ksbtstp_ test background process
VBG0 kfvbg0_ Volume BG 0
VBG1 kfvbg1_ Volume BG 1
VBG2 kfvbg2_ Volume BG 2
VBG3 kfvbg3_ Volume BG 3
VBG4 kfvbg4_ Volume BG 4
VBG5 kfvbg5_ Volume BG 5
VBG6 kfvbg6_ Volume BG 6
VBG7 kfvbg7_ Volume BG 7
VBG8 kfvbg8_ Volume BG 8
VBG9 kfvbg9_ Volume BG 9
VDBG kfvdbg_ Volume Driver BG
VKRM kskvkrm_ Virtual sKeduler for Resource Manager
VKTM kstmvktm_ Virtual Keeper of TiMe process
VMB0 kfvmb0_ Volume Membership 0
XDMG kxdam_mgr_ cell automation manager
XDWK kxdam_slvbg_ cell automation worker actions
297 rows selected.
2016. 12. 5. 11:46 오라클
2016. 10. 28. 11:15 오라클
가끔씩 Listener 를 셋팅할때 아무생각없이 이렇게 셋팅할 때가 있는데..Fail-over 환경시 문제가 될수 있다.
- 수정 전-
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
Public Network 장애로 VIP 가 Fail-over 될때 listener stop 을 시도하게 된다. (PROTOCAL=IPC)를 첫 address 위치로
변경하면 lsnrctl 이 IPC address 를 통하여 접속하므로 그 다음 설정되어 있는 IP를 찾는 작업에 시간을 소모하지 않고 (TCP timeout 동안 기다리지 않고) 바로 listener stop 을 할 수가 있다.
- 수정 후 -
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521)(IP = FIRST))
)
)
2016. 10. 20. 17:00 오라클
어떠한 이유로 인해 Recylebin 으로 drop 된 (실제로는 rename만 된것이지만) 객체들을 일정한 기간 만큼만 지우고자 하는 경우가 있을 것이다.
해당 경우 아래와 같이 하면 된다.
보기 예 1 )
- 7일이 경과한 Table 을 삭제하고자 할때
select 'purge table '||owner||'."'||OBJECT_NAME||'";'
from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-7;
'PURGETABLE'||OWNER||'."'||OBJECT_NAME||'";'
------------------------------------------------------------------------------------------------------------------------
purge table WMSYS."BIN$PgNXeTuLWoPgU24AEKyH3g==$0";
purge table WMSYS."BIN$PgRouDktJGngU24AEKx4sg==$0";
purge table WMSYS."BIN$PgTrksKrSP3gU24AEKzvyw==$0";
Elapsed: 00:00:00.02
보기 예 2 )
- 5분이 경과한 Table 을 삭제하고자 할때
select 'purge table '||owner||'."'||OBJECT_NAME||'";'
from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(5/(24*60));
끝.
2016. 9. 7. 11:21 오라클
Oracle option enable/disable 방법
라이센스에 관련된 부분이니 민감한 부분임...
원래 DB 내리고 하라고 하는데 Single 이라 그런지 기동상태에서 해도 잘 되네??
But!!! alert log 살펴보면 해당 메세지 지속적으로 발생함.
Wed Sep 07 10:58:44 2016
WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages
현재 인스상태 상태와 일치하지 않으므로 메세지 발생하게 됨.
내렸다 올리면 없어짐..
[oracle:/home/oracle]#chopt disable partitioning
Writing to /oracle/product/11.2.0.4/install/disable_partitioning.log...
/usr/bin/make -f /oracle/product/11.2.0.4/rdbms/lib/ins_rdbms.mk part_off ORACLE_HOME=/oracle/product/11.2.0.4
/usr/bin/make -f /oracle/product/11.2.0.4/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/oracle/product/11.2.0.4
sys@TEST> set line 120;
sys@TEST> col parameter for A50
sys@TEST> col value for A20
sys@TEST> select parameter,value from v$option where parameter='Partitioning';
PARAMETER VALUE
-------------------------------------------------- --------------------
Partitioning FALSE
- 11.2
chopt enable dm (Oracle Data Mining)
chopt disable dm
chopt enable dv (Database Vault)
chopt disable dv
chopt enable olap (Oracle OLAP)
chopt disable olap
chopt enable lbac (Oracle Label Security)
chopt disable lbac
chopt enable partitioning (Oracle Partitioning)
chopt disable partitioning
chopt enable rat (Real Application Testing)
chopt disable rat
- 12.1
chopt enable dm (Oracle Data Mining)
chopt disable dm
chopt enable olap (Oracle OLAP)
chopt disable olap
chopt enable partitioning (Oracle Partitioning)
chopt disable partitioning
chopt enable rat (Real Application Testing)
chopt disable rat
- 해당 옵션 적용 여부 확인
set line 120;
col parameter for A50
col value for A20
select parameter,value from v$option where parameter='Partitioning';
select parameter,value from v$option where parameter='Active Data Guard';
- 해당 옵션 사용유무 확인
col name for A60
col version for A15
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%PARTITION%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%OLAP%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%TEXT%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%XDB%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%VAULT%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%REAL%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%LABEL%';
select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like '%ACTIVE%';
2016. 9. 6. 17:35 오라클
잘쓰던 시스템이 local 접속인데도 sqlplus 가 hang 걸리며 안될 때가 있다. 보통 보안관련 설정이나 권한변경 등이 원인이 되곤 하는데..
정확하게 파악하기 위하여 system call 을 추정해서 확인해 볼수 있다.
보통 흔한 증상으로
idle> conn / as sysdba
여기까진 접속이 잘 된다. 근데..
SQL> conn / as sysdba 하면 먹통이 된다.
==================================
이러면 막막한데 strace 를 써서 확인해 볼수 있겠다.
1. 만약 linux 경우
strace -f -o sqlplus.trc sqlplus /nolog
connect / as sysdba
sqlplus.trc 화일일 열어서 의심이 가는 부분을 확인해 본다. 100%는 아니지만 어느정도 유추가능한 경우가 있음.
2. AIX나 Solaris 인 경우
truss -aefo sqlplus.trc sqlplus /nolog
connect / as sysdba
(AIX, Solaris)
2016. 9. 2. 11:08 오라클
계정 변경 및 기타 문제로 인해 Oracle Inventory 정보유실로 corrupt 되었을 때..
==================================================
[oracle:/oracle]#opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/product/11.2.0.4
Central Inventory : /oracle/oraInventory
from : /oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2016-09-02_10-58-33AM_1.log
OPatch failed to locate Central Inventory.
Possible causes are:
The Central Inventory is corrupted
The oraInst.loc file specified is not valid.
LsInventorySession failed: OPatch failed to locate Central Inventory.
Possible causes are:
The Central Inventory is corrupted
The oraInst.loc file specified is not valid.
OPatch failed with error code 73
==================================================
Oracle Home 정보를 다시 attach 하여 복구 완료..
$ORACLE_HOME/oui/bin> ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/oracle/product/11.2.0.4" ORACLE_HOME_NAME="OraDb11g_home1"
확인 잘됨 !
[oracle:/home/oracle]#opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/product/11.2.0.4
Central Inventory : /oracle/oraInventory
from : /oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2016-09-02_11-02-35AM_1.log
Lsinventory Output file location : /oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2016-09-02_11-02-35AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: single
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 22502456 : applied on Thu Apr 21 16:29:11 KST 2016
Unique Patch ID: 19856194
Patch description: "Database Patch Set Update : 11.2.0.4.160419 (22502456)"
Created on 21 Mar 2016, 11:49:22 hrs
Sub-patch 21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Sub-patch 21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch 20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch 20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17288409, 21051852, 17811429, 18607546, 17205719, 20506699, 17816865
17922254, 17754782, 16934803, 13364795, 17311728, 17441661, 17284817
16992075, 17446237, 14015842, 19972569, 21756677, 21538558, 20925795
17449815, 17375354, 19463897, 13866822, 17982555, 17235750, 17478514
18317531, 14338435, 18235390, 20803583, 13944971, 20142975, 17811789
16929165, 18704244, 20506706, 17546973, 20334344, 14054676, 17088068
17346091, 18264060, 17343514, 21538567, 19680952, 18471685, 19211724
13951456, 21847223, 16315398, 18744139, 16850630, 19049453, 18673304
17883081, 19915271, 18641419, 18262334, 17006183, 16065166, 18277454
16833527, 10136473, 18051556, 17865671, 17852463, 18554871, 17853498
18334586, 17551709, 17588480, 19827973, 17344412, 17842825, 18828868
17025461, 11883252, 13609098, 17239687, 17602269, 19197175, 22195457
18316692, 17313525, 12611721, 19544839, 18964939, 17600719, 18191164
19393542, 17571306, 18482502, 20777150, 19466309, 17040527, 17165204
18098207, 16785708, 17465741, 17174582, 16180763, 16777840, 12982566
19463893, 22195465, 16875449, 12816846, 17237521, 19358317, 17811438
17811447, 21983325, 17945983, 18762750, 16912439, 17184721, 18061914
17282229, 18331850, 18202441, 17082359, 18723434, 21972320, 19554106
14034426, 18339044, 19458377, 17752995, 20448824, 17891943, 17258090
17767676, 16668584, 18384391, 17040764, 17381384, 15913355, 18356166
14084247, 20596234, 20506715, 21756661, 13853126, 18203837, 14245531
21756699, 16043574, 22195441, 17848897, 17877323, 21453153, 17468141
20861693, 17786518, 17912217, 17037130, 18155762, 16956380, 17478145
17394950, 18641461, 18189036, 18619917, 17027426, 21352646, 16268425
22195492, 19584068, 18436307, 17265217, 17634921, 13498382, 21526048
19258504, 20004087, 17443671, 22195485, 18000422, 20004021, 22321756
17571039, 21067387, 16344544, 18009564, 14354737, 21286665, 18135678
18614015, 20441797, 18362222, 17835048, 16472716, 17936109, 17050888
17325413, 14010183, 18747196, 17761775, 16721594, 17082983, 20067212
21179898, 17302277, 18084625, 15990359, 18203835, 17297939, 17811456
16731148, 21168487, 13829543, 17215560, 14133975, 17694209, 17385178
18091059, 8322815, 17586955, 17201159, 17655634, 18331812, 19730508
18868646, 17648596, 16220077, 16069901, 17348614, 17393915, 17274537
17957017, 18096714, 17308789, 18436647, 14285317, 19289642, 14764829
18328509, 17622427, 22195477, 16943711, 22502493, 14368995, 17346671
18996843, 17783588, 21343838, 16618694, 17672719, 18856999, 18783224
17851160, 17546761, 17798953, 18273830, 22092979, 16596890, 19972566
16384983, 17726838, 17360606, 22321741, 13645875, 18199537, 16542886
21787056, 17889549, 14565184, 17071721, 17610798, 20299015, 21343897
22893153, 20657441, 17397545, 18230522, 16360112, 19769489, 12905058
18641451, 12747740, 18430495, 17016369, 17042658, 14602788, 17551063
19972568, 21517440, 18508861, 19788842, 14657740, 17332800, 13837378
19972564, 17186905, 18315328, 19699191, 17437634, 22353199, 18093615
19006849, 19013183, 17296856, 18674024, 17232014, 16855292, 17762296
14692762, 21051840, 17705023, 19121551, 21330264, 19854503, 21868720
19309466, 18681862, 18554763, 20558005, 17390160, 18456514, 16306373
13955826, 18139690, 17501491, 17752121, 21668627, 17299889, 17889583
18673325, 19721304, 18293054, 17242746, 17951233, 17649265, 18094246
19615136, 17011832, 16870214, 17477958, 18522509, 20631274, 16091637
17323222, 16595641, 16524926, 18228645, 18282562, 17596908, 17156148
18031668, 16494615, 22683225, 17545847, 17655240, 17614134, 13558557
17341326, 17891946, 17716305, 16392068, 19271443, 21351877, 18092127
18440047, 17614227, 14106803, 16903536, 18973907, 18673342, 19032867
17389192, 17612828, 16194160, 17006570, 17721717, 17390431, 17570240
16863422, 18325460, 19727057, 16422541, 19972570, 17267114, 18244962
21538485, 18765602, 18203838, 16198143, 17246576, 14829250, 17835627
18247991, 14458214, 21051862, 16692232, 17786278, 17227277, 16042673
16314254, 16228604, 16837842, 17393683, 17787259, 20331945, 20074391
15861775, 16399083, 18018515, 22683212, 18260550, 21051858, 17036973
16613964, 17080436, 16579084, 18384537, 18280813, 20296213, 16901385
15979965, 18441944, 16450169, 9756271, 17892268, 11733603, 16285691
17587063, 21343775, 16538760, 18180390, 18193833, 21387964, 21051833
17238511, 17824637, 16571443, 18306996, 14852021, 18674047, 17853456
12364061, 22195448
--------------------------------------------------------------------------------
OPatch succeeded.
2016. 8. 31. 16:01 오라클
음..12cR2 Beta3에서는 샘플 스크립트가 빠져있네... 샘플이 왜 누락되어 있는지?.. 보고 확인해야 될 때가 있는데..
더 엄격한 ora12c_strong_verify_function 도 추가되어 있군..12cR1 부터 있었는진 모르겠다..
12cR2 Beta3 꺼
$ORACLE_HOEn/rdbms/admin/utlpwdmg.sql
Rem
Rem $Header: rdbms/admin/utlpwdmg.sql /main/13 2016/01/04 21:20:04 sumkumar Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) 2006, 2015, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem sumkumar 12/15/15 - Bug 22369990: Make all PVFs as common objects
Rem so as to make them available inside PDBs
Rem yanlili 09/18/15 - Fix bug 20603202: Handle quoted usernames if
Rem called directly
Rem hmohanku 02/17/15 - bug 20460696: add long identifier support
Rem sumkumar 12/26/14 - Proj 46885: set inactive account time to
Rem UNLIMITED for DEFAULT profile
Rem jkati 10/16/13 - bug#17543726 : remove complexity_check,
Rem string_distance, ora12c_strong_verify_function
Rem since we now provide them by default with new db
Rem creation
Rem skayoor 10/26/12 - Bug 14671375: Execute privilege on pwd verify
Rem func
Rem jmadduku 07/30/12 - Bug 13536142: Re-organize the code
Rem jmadduku 12/02/11 - Bug 12839255: Compliant Password Verify functions
Rem jmadduku 01/21/11 - Proj 32507: Add a new password verify function
Rem STIG_verify_function and enhance functionality of
Rem code that checks distance between old and new
Rem password
Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity check
Rem nireland 08/31/00 - Improve check for username=password. #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
Rem
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is provided.
Rem *************************************************************************
Rem BEGIN Password Management Parameters
Rem *************************************************************************
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
/**
The below set of password profile parameters would take into consideration
recommendations from Center for Internet Security[CIS Oracle 11g].
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 20
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
*/
/**
The below set of password profile parameters would take into
consideration recommendations from Department of Defense Database
Security Technical Implementation Guide[STIG v8R1].
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;
*/
Rem *************************************************************************
Rem END Password Management Parameters
Rem *************************************************************************
[oracle:/oracle/product/12.2.0.1/rdbms/admin]#vi utlpwdmg.sql
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 20
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
*/
/**
The below set of password profile parameters would take into
consideration recommendations from Department of Defense Database
Security Technical Implementation Guide[STIG v8R1].
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;
*/
Rem *************************************************************************
Rem END Password Management Parameters
Rem *************************************************************************
샘플없으니 불편해서 일단 11g꺼 참고로 보기로 하고..
11gR2 꺼
$ORACLE_HOEn/rdbms/admin/utlpwdmg.sql
Rem
Rem $Header: utlpwdmg.sql 02-aug-2006.08:18:05 asurpur Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity check
Rem nireland 08/31/00 - Improve check for username=password. #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
Rem
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
CREATE OR REPLACE FUNCTION verify_function_11G
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
-- Check for the minimum length of the password
IF length(password) < 10 THEN
raise_application_error(-20001, 'Password length less than 10');
END IF;
-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;
-- Check if the password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;
-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;
-- Check if the password contains at least one letter, one digit
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20008, 'Password must contain at least one digit, one character');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, and one character');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
-- Below is the older version of the script
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
-- Enable this if you want older version of the Password Profile parameters
-- ALTER PROFILE DEFAULT LIMIT
-- PASSWORD_LIFE_TIME 60
-- PASSWORD_GRACE_TIME 10
-- PASSWORD_REUSE_TIME 1800
-- PASSWORD_REUSE_MAX UNLIMITED
-- FAILED_LOGIN_ATTEMPTS 3
-- PASSWORD_LOCK_TIME 1/1440
-- PASSWORD_VERIFY_FUNCTION verify_function;
