AWR Snapshot 및 Report 는 CDB level 또는 PDB level 에서 생성가능하며 AWR Snapshot 은 기본으로 CDB level 에서만 생성된다.
1. PDB AWR snapshot 수동 생성하는 경우.
SQL> connect <username>/<password> as sysdba SQL> alter session set container=PDB1; SQL> exec dbms_workload_repository.create_snapshot();
2. PDB AWR snapshots 의 자동생성 환경 구성.
SQL> alter session set container = CDB$ROOT; SQL> alter system set AWR_PDB_AUTOFLUSH_ENABLED = TRUE; SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000;
3. cdb_hist_snapshot 을 조회해서 CDB, PDB 의 AWR snapshot 확인가능
SQL> alter session set container=CDB$ROOT; SQL> select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;
SQL> alter session set container=CDB$ROOT; SQL> @?/rdbms/admin/awrrpt
5. PDB에서 PDB AWR reportSQL> alter session set container=PDB1; SQL> @?/rdbms/admin/awrrpt
Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'.
'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report
Enter value for report_type:
Specify the location of AWR Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ AWR_ROOT - Use AWR data from root (default) AWR_PDB - Use AWR data from PDB <<------- PDB Snapshot이 별도로 생성된 경우 선택가능
Enter value for awr_location:
AWR Snapshots and Reports from Oracle Multitentant Database(CDB, PDB) (Doc ID 2295998.1)
SQL> create tablespace TEST datafile '/oracle/app/oracle/oradata/ORCL/test01.dbf' size 100M;
RMAN> DUPLICATE DATABASE TO ORCL2 skip tablespace 'TEST' 2> FROM ACTIVE DATABASE 3> SPFILE 4> parameter_value_convert ('ORCL','ORCL2') 5> set db_file_name_convert='/oracle/app/oracle/oradata/ORCL/','/oracle/app/oracle/oradata/ORCL2/' 6> set log_file_name_convert='/oracle/app/oracle/oradata/ORCL/','/oracle/app/oracle/oradata/ORCL2/' 7> set control_files='/oracle/app/oracle/oradata/ORCL2/control01.ctl','/oracle/app/oracle/oradata/ORCL2/control02.ctl' 8> set log_archive_dest_1='location=/oracle/archive2' 9> ; 10> Starting Duplicate Db at 2024-09-23:22:05:56 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=38 device type=DISK current log archived
contents of Memory Script: { restore clone from service 'ORCL' spfile to '/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora'; sql clone "alter system set spfile= ''/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora''"; } executing Memory Script
Starting restore at 2024-09-23:22:05:58 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring SPFILE output file name=/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2024-09-23:22:06:02
sql statement: alter system set spfile= ''/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora''
contents of Memory Script: { sql clone "alter system set db_name = ''ORCL2'' comment= ''duplicate'' scope=spfile"; sql clone "alter system set audit_file_dest = ''/oracle/app/oracle/admin/ORCL2/adump'' comment= '''' scope=spfile"; sql clone "alter system set db_name = ''ORCL2'' comment= '''' scope=spfile"; sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'' comment= '''' scope=spfile"; sql clone "alter system set local_listener = ''LISTENER_ORCL2'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/oracle/app/oracle/oradata/ORCL2/control01.ctl'', ''/oracle/app/oracle/oradata/ORCL2/control02.ctl'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_1 = ''location=/oracle/archive2'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script
sql statement: alter system set db_name = ''ORCL2'' comment= ''duplicate'' scope=spfile sql statement: alter system set audit_file_dest = ''/oracle/app/oracle/admin/ORCL2/adump'' comment= '''' scope=spfile sql statement: alter system set db_name = ''ORCL2'' comment= '''' scope=spfile sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'' comment= '''' scope=spfile sql statement: alter system set local_listener = ''LISTENER_ORCL2'' comment= '''' scope=spfile sql statement: alter system set db_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile sql statement: alter system set log_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile sql statement: alter system set control_files = ''/oracle/app/oracle/oradata/ORCL2/control01.ctl'', ''/oracle/app/oracle/oradata/ORCL2/control02.ctl'' comment= '''' scope=spfile sql statement: alter system set log_archive_dest_1 = ''location=/oracle/archive2'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''ORCL2'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'ORCL' primary controlfile; alter clone database mount; } executing Memory Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''ORCL2'' comment= ''Modified by RMAN duplicate'' scope=spfile
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oracle/app/oracle/oradata/ORCL2/control01.ctl output file name=/oracle/app/oracle/oradata/ORCL2/control02.ctl Finished restore at 2024-09-23:22:06:59
database mounted Checking that duplicated tablespaces are self-contained Datafile 5 skipped by request
contents of Memory Script: { set newname for datafile 1 to "/oracle/app/oracle/oradata/ORCL2/system01.dbf"; set newname for datafile 2 to "/oracle/app/oracle/oradata/ORCL2/sysaux01.dbf"; set newname for datafile 3 to "/oracle/app/oracle/oradata/ORCL2/undotbs01.dbf"; set newname for datafile 4 to "/oracle/app/oracle/oradata/ORCL2/users01.dbf"; restore from nonsparse from service 'ORCL' clone database skip forever tablespace "TEST" ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME
Starting restore at 2024-09-23:22:07:09 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ORCL2/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORCL2/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORCL2/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORCL2/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2024-09-23:22:07:18
sql statement: alter system archive log current current log archived
contents of Memory Script: { restore clone force from service 'ORCL' archivelog from scn 1346295; switch clone datafile all; } executing Memory Script
Starting restore at 2024-09-23:22:07:19 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=71 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=72 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2024-09-23:22:07:21
contents of Memory Script: { set until scn 1346449; recover clone database skip forever tablespace "TEST" delete archivelog ; } executing Memory Script
executing command: SET until clause
Starting recover at 2024-09-23:22:07:21 using channel ORA_AUX_DISK_1
Executing: alter database datafile 5 offline drop starting media recovery
archived log for thread 1 with sequence 71 is already on disk as file /oracle/archive2/arch_1_1151767787_71.arc archived log for thread 1 with sequence 72 is already on disk as file /oracle/archive2/arch_1_1151767787_72.arc archived log file name=/oracle/archive2/arch_1_1151767787_71.arc thread=1 sequence=71 archived log file name=/oracle/archive2/arch_1_1151767787_72.arc thread=1 sequence=72 media recovery complete, elapsed time: 00:00:00 Finished recover at 2024-09-23:22:07:22
contents of Memory Script: { delete clone force archivelog all; } executing Memory Script
contents of Memory Script: { sql clone "alter system set db_name = ''ORCL2'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; } executing Memory Script
sql statement: alter system set db_name = ''ORCL2'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile Oracle instance started
Total System Global Area 3690983864 bytes
Fixed Size 8946104 bytes Variable Size 721420288 bytes Database Buffers 2952790016 bytes Redo Buffers 7827456 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/oracle/app/oracle/oradata/ORCL2/redo01.log' ) SIZE 200 M REUSE, GROUP 2 ( '/oracle/app/oracle/oradata/ORCL2/redo02.log' ) SIZE 200 M REUSE, GROUP 3 ( '/oracle/app/oracle/oradata/ORCL2/redo03.log' ) SIZE 200 M REUSE DATAFILE '/oracle/app/oracle/oradata/ORCL2/system01.dbf' CHARACTER SET AL32UTF8
contents of Memory Script: { set newname for tempfile 1 to "/oracle/app/oracle/oradata/ORCL2/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/oracle/app/oracle/oradata/ORCL2/sysaux01.dbf", "/oracle/app/oracle/oradata/ORCL2/undotbs01.dbf", "/oracle/app/oracle/oradata/ORCL2/users01.dbf"; switch clone datafile all; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/ORCL2/temp01.dbf in control file
contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script
database opened Dropping offline and skipped tablespaces Executing: drop tablespace "TEST" including contents cascade constraints Finished Duplicate Db at 2024-09-23:22:08:14
12c Alert Log Appears To Show Unexpected Instance Restart (Doc ID 2049516.1) Dumping Current Patch Information Shows Up In Alert Log At Random Times (Doc ID 2377750.1)
증상 : log.xml 파일이 10MB 에 도달하여 새로 생성되는 시점에 alert log 에 "Creating new log segment:" 정보 발생하거나 또는 재기동하지 않았는데 뜬금없이 db parameter 정보와 patch 정보를 alert 에 발생시킴
Solution : -> ls -al $ORACLE_BASE/diag/rdbms/sid/SID/alert 에서 xml 파일 확인 -> log.xml 은 10M 단위로 rotate 됨
SQL> alter system set "_log_segment_dump_parameter"=FALSE <---- db parameter SQL> alter system set "_log_segment_dump_patch"=FALSE <---- db patch
두 파라미터 모두 시스템 즉시 적용 되며 , 운영에 지장을 주지 않음.
XML alert log 인 log.xml file 이 새로 생성될 때, db 의 parameter 정보와 patch 정보를 남기는 기능을 off 하는 기능임.
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-AUG-2024 16:02:21
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.140)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 20-AUG-2024 14:52:10 Uptime 0 days 1 hr. 10 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/19.0.0/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.140)(PORT=1522))) Services Summary... Service "ORCL" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Service "ORCL2" has 1 instance(s). Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service... Service "ORCLXDB" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... The command completed successfully
RMAN> DUPLICATE DATABASE TO ORCL2 FROM ACTIVE DATABASE SPFILE parameter_value_convert ('ORCL','ORCL2') set db_file_name_convert='/oracle/app/oracle/oradata/ORCL/','/oracle/app/oracle/oradata/ORCL2/' set log_file_name_convert='/oracle/app/oracle/oradata/ORCL/','/oracle/app/oracle/oradata/ORCL2/' set control_files='/oracle/app/oracle/oradata/ORCL2/control01.ctl','/oracle/app/oracle/oradata/ORCL2/control02.ctl' set log_archive_dest_1='location=/oracle/archive2' ;
- 여기서부터는 진행 로그 Starting Duplicate Db at 20-AUG-24 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=38 device type=DISK current log archived
contents of Memory Script: { restore clone from service 'ORCL' spfile to '/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora'; sql clone "alter system set spfile= ''/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora''"; } executing Memory Script
Starting restore at 20-AUG-24 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring SPFILE output file name=/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-AUG-24
sql statement: alter system set spfile= ''/oracle/app/oracle/product/19.0.0/dbs/spfileORCL2.ora''
contents of Memory Script: { sql clone "alter system set db_name = ''ORCL2'' comment= ''duplicate'' scope=spfile"; sql clone "alter system set audit_file_dest = ''/oracle/app/oracle/admin/ORCL2/adump'' comment= '''' scope=spfile"; sql clone "alter system set db_name = ''ORCL2'' comment= '''' scope=spfile"; sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'' comment= '''' scope=spfile"; sql clone "alter system set local_listener = ''LISTENER_ORCL2'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/oracle/app/oracle/oradata/ORCL2/control01.ctl'', ''/oracle/app/oracle/oradata/ORCL2/control02.ctl'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_1 = ''location=/oracle/archive2'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script
sql statement: alter system set db_name = ''ORCL2'' comment= ''duplicate'' scope=spfile
sql statement: alter system set audit_file_dest = ''/oracle/app/oracle/admin/ORCL2/adump'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''ORCL2'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'' comment= '''' scope=spfile
sql statement: alter system set local_listener = ''LISTENER_ORCL2'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/oracle/app/oracle/oradata/ORCL/'', ''/oracle/app/oracle/oradata/ORCL2/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/oracle/app/oracle/oradata/ORCL2/control01.ctl'', ''/oracle/app/oracle/oradata/ORCL2/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=/oracle/archive2'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''ORCL2'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'ORCL' primary controlfile; alter clone database mount; } executing Memory Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL2'' comment= ''Modified by RMAN duplicate'' scope=spfile
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oracle/app/oracle/oradata/ORCL2/control01.ctl output file name=/oracle/app/oracle/oradata/ORCL2/control02.ctl Finished restore at 20-AUG-24
database mounted
contents of Memory Script: { set newname for datafile 1 to "/oracle/app/oracle/oradata/ORCL2/system01.dbf"; set newname for datafile 2 to "/oracle/app/oracle/oradata/ORCL2/sysaux01.dbf"; set newname for datafile 3 to "/oracle/app/oracle/oradata/ORCL2/undotbs01.dbf"; set newname for datafile 4 to "/oracle/app/oracle/oradata/ORCL2/users01.dbf"; restore from nonsparse from service 'ORCL' clone database ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-AUG-24 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ORCL2/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORCL2/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORCL2/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORCL2/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-AUG-24
sql statement: alter system archive log current current log archived
contents of Memory Script: { restore clone force from service 'ORCL' archivelog from scn 1080410; switch clone datafile all; } executing Memory Script
Starting restore at 20-AUG-24 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=36 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service ORCL channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=37 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-AUG-24
contents of Memory Script: { set until scn 1080548; recover clone database delete archivelog ; } executing Memory Script
executing command: SET until clause
Starting recover at 20-AUG-24 using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 36 is already on disk as file /oracle/archive2/arch_1_1151767787_36.arc archived log for thread 1 with sequence 37 is already on disk as file /oracle/archive2/arch_1_1151767787_37.arc archived log file name=/oracle/archive2/arch_1_1151767787_36.arc thread=1 sequence=36 archived log file name=/oracle/archive2/arch_1_1151767787_37.arc thread=1 sequence=37 media recovery complete, elapsed time: 00:00:00 Finished recover at 20-AUG-24
contents of Memory Script: { delete clone force archivelog all; } executing Memory Script
contents of Memory Script: { sql clone "alter system set db_name = ''ORCL2'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; } executing Memory Script
sql statement: alter system set db_name = ''ORCL2'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile Oracle instance started
Total System Global Area 3690983864 bytes
Fixed Size 8946104 bytes Variable Size 721420288 bytes Database Buffers 2952790016 bytes Redo Buffers 7827456 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/oracle/app/oracle/oradata/ORCL2/redo01.log' ) SIZE 200 M REUSE, GROUP 2 ( '/oracle/app/oracle/oradata/ORCL2/redo02.log' ) SIZE 200 M REUSE, GROUP 3 ( '/oracle/app/oracle/oradata/ORCL2/redo03.log' ) SIZE 200 M REUSE DATAFILE '/oracle/app/oracle/oradata/ORCL2/system01.dbf' CHARACTER SET AL32UTF8
contents of Memory Script: { set newname for tempfile 1 to "/oracle/app/oracle/oradata/ORCL2/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/oracle/app/oracle/oradata/ORCL2/sysaux01.dbf", "/oracle/app/oracle/oradata/ORCL2/undotbs01.dbf", "/oracle/app/oracle/oradata/ORCL2/users01.dbf"; switch clone datafile all; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/ORCL2/temp01.dbf in control file
contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script
database opened Finished Duplicate Db at 20-AUG-24
RMAN>
복제 완료 !!
- 테스트후 ORCL2 drop
export ORACLE_SID=ORCL2 sqlplus "/as sysdba"
shutdown immediate startup mount exclusive restrict; drop database;
- 만일 이짓거리를 반복적으로 수행하고자 한다면..
1. vi dup.sql
DUPLICATE DATABASE TO ORCL2 FROM ACTIVE DATABASE SPFILE parameter_value_convert ('ORCL','ORCL2') set db_file_name_convert='/oracle/app/oracle/oradata/ORCL/','/oracle/app/oracle/oradata/ORCL2/' set log_file_name_convert='/oracle/app/oracle/oradata/ORCL/','/oracle/app/oracle/oradata/ORCL2/' set control_files='/oracle/app/oracle/oradata/ORCL2/control01.ctl','/oracle/app/oracle/oradata/ORCL2/control02.ctl' set log_archive_dest_1='location=/oracle/archive2' ;
2. vi dup.sh export ORACLE_SID=ORCL2 sqlplus / as sysdba <<EOF shutdown immediate; startup mount exclusive restrict; drop database; EOF export ORACLE_SID=ORCL2 sqlplus / as sysdba <<EOF startup nomount pfile='/oracle/app/oracle/product/19.0.0/dbs/initORCL2.ora'; ! rman target sys/oracle@ORCL auxiliary sys/oracle@ORCL2 cmdfile=/home/oracle/dup.sql log=/home/oracle/dup.log append exit EOF
3. crontab 등록해서 원하는 주기로 반복 사용 가능 00 23 * * * /home/oracle/dup.sh
1번노드에서 수행 root@rose1:/root# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to dba. The execution of the script is complete.
2번노드에서 수행 root@rose2:/root# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to dba. The execution of the script is complete. 1번노드에서 수행 root@rose1:/root# /u01/app/23.0.0.0/grid/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME=/u01/app/23.0.0.0/grid Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The contents of "oraenv" have not changed. No need to overwrite. The contents of "coraenv" have not changed. No need to overwrite. 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. RAC option enabled on: Linux Executing command '/u01/app/23.0.0.0/grid/perl/bin/perl -I/u01/app/23.0.0.0/grid/perl/lib -I/u01/app/23.0.0.0/grid/crs/install /u01/app/23.0.0.0/grid/crs/install/rootcrs.pl ' Using configuration parameter file: /u01/app/23.0.0.0/grid/crs/install/crsconfig_params The log of current session can be found at: /u01/app/oracle/crsdata/rose1/crsconfig/rootcrs_rose1_2024-08-07_03-50-49PM.log 2024/08/07 15:50:56 CLSRSC-594: Executing installation step 1 of 19: 'ValidateEnv'. 2024/08/07 15:50:56 CLSRSC-594: Executing installation step 2 of 19: 'CheckRootCert'. 2024/08/07 15:50:57 CLSRSC-594: Executing installation step 3 of 19: 'GenSiteGUIDs'. 2024/08/07 15:50:59 CLSRSC-594: Executing installation step 4 of 19: 'SetupOSD'. 2024/08/07 15:50:59 CLSRSC-594: Executing installation step 5 of 19: 'CheckCRSConfig'. 2024/08/07 15:51:00 CLSRSC-594: Executing installation step 6 of 19: 'SetupLocalGPNP'. 2024/08/07 15:51:01 CLSRSC-594: Executing installation step 7 of 19: 'CreateRootCert'. 2024/08/07 15:51:02 CLSRSC-594: Executing installation step 8 of 19: 'ConfigOLR'. 2024/08/07 15:51:02 CLSRSC-594: Executing installation step 9 of 19: 'ConfigCHMOS'. 2024/08/07 15:51:02 CLSRSC-594: Executing installation step 10 of 19: 'CreateOHASD'. 2024/08/07 15:51:03 CLSRSC-594: Executing installation step 11 of 19: 'ConfigOHASD'. 2024/08/07 15:51:13 CLSRSC-594: Executing installation step 12 of 19: 'SetupTFA'. 2024/08/07 15:51:13 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'. 2024/08/07 15:51:13 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'. 2024/08/07 15:51:16 CLSRSC-594: Executing installation step 15 of 19: 'CheckFirstNode'. 2024/08/07 15:51:17 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'. 2024/08/07 15:51:21 CLSRSC-4002: Successfully installed Oracle Autonomous Health Framework (AHF). CRS-4256: Updating the profile Successful addition of voting disk 70302270ae374f52bfdb52dce11423c6. Successful addition of voting disk 853b00134e034fcebf88f25576fa2a80. Successful addition of voting disk 7578baed74b14fd6bfcdf937816cc086. Successful addition of voting disk def894b31fa94f55bf82a38e52a1eedf. Successful addition of voting disk 31929cd8f29d4f0ebf1b91a21ee27fa6. Successfully replaced voting disk group with +VOTE. CRS-4256: Updating the profile CRS-4266: Voting file(s) successfully replaced ##STATEFile Universal IdFile Name Disk group --------------------------------- --------- 1. ONLINE70302270ae374f52bfdb52dce11423c6 (/dev/VOTE1) [VOTE] 2. ONLINE853b00134e034fcebf88f25576fa2a80 (/dev/VOTE2) [VOTE] 3. ONLINE7578baed74b14fd6bfcdf937816cc086 (/dev/VOTE3) [VOTE] 4. ONLINEdef894b31fa94f55bf82a38e52a1eedf (/dev/VOTE4) [VOTE] 5. ONLINE31929cd8f29d4f0ebf1b91a21ee27fa6 (/dev/VOTE5) [VOTE] Located 5 voting disk(s). 2024/08/07 15:52:29 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'. 2024/08/07 15:52:57 CLSRSC-343: Successfully started Oracle Clusterware stack 2024/08/07 15:53:03 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'. clscfg: EXISTING configuration version 23 detected. Successfully accumulated necessary OCR keys. Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. 2024/08/07 15:53:41 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'. 2024/08/07 15:54:13 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded 2번노드에서 수행 root@rose2:/root# /u01/app/23.0.0.0/grid/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME=/u01/app/23.0.0.0/grid Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The contents of "oraenv" have not changed. No need to overwrite. The contents of "coraenv" have not changed. No need to overwrite. 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. RAC option enabled on: Linux Executing command '/u01/app/23.0.0.0/grid/perl/bin/perl -I/u01/app/23.0.0.0/grid/perl/lib -I/u01/app/23.0.0.0/grid/crs/install /u01/app/23.0.0.0/grid/crs/install/rootcrs.pl ' Using configuration parameter file: /u01/app/23.0.0.0/grid/crs/install/crsconfig_params The log of current session can be found at: /u01/app/oracle/crsdata/rose2/crsconfig/rootcrs_rose2_2024-08-07_04-54-49PM.log 2024/08/07 16:54:56 CLSRSC-594: Executing installation step 1 of 19: 'ValidateEnv'. 2024/08/07 16:54:56 CLSRSC-594: Executing installation step 2 of 19: 'CheckRootCert'. 2024/08/07 16:54:57 CLSRSC-594: Executing installation step 3 of 19: 'GenSiteGUIDs'. 2024/08/07 16:54:57 CLSRSC-594: Executing installation step 4 of 19: 'SetupOSD'. 2024/08/07 16:54:57 CLSRSC-594: Executing installation step 5 of 19: 'CheckCRSConfig'. 2024/08/07 16:54:58 CLSRSC-594: Executing installation step 6 of 19: 'SetupLocalGPNP'. 2024/08/07 16:54:58 CLSRSC-594: Executing installation step 7 of 19: 'CreateRootCert'. 2024/08/07 16:54:58 CLSRSC-594: Executing installation step 8 of 19: 'ConfigOLR'. 2024/08/07 16:54:59 CLSRSC-594: Executing installation step 9 of 19: 'ConfigCHMOS'. 2024/08/07 16:54:59 CLSRSC-594: Executing installation step 10 of 19: 'CreateOHASD'. 2024/08/07 16:55:00 CLSRSC-594: Executing installation step 11 of 19: 'ConfigOHASD'. 2024/08/07 16:55:06 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service' 2024/08/07 16:55:15 CLSRSC-594: Executing installation step 12 of 19: 'SetupTFA'. 2024/08/07 16:55:15 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'. 2024/08/07 16:55:15 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'. 2024/08/07 16:55:19 CLSRSC-594: Executing installation step 15 of 19: 'CheckFirstNode'. 2024/08/07 16:55:19 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'. 2024/08/07 16:55:21 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'. 2024/08/07 16:56:15 CLSRSC-4002: Successfully installed Oracle Autonomous Health Framework (AHF). 2024/08/07 16:57:25 CLSRSC-343: Successfully started Oracle Clusterware stack 2024/08/07 16:57:25 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'. 2024/08/07 16:57:25 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'. 2024/08/07 16:57:43 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded 양 노드에서root.sh가 완료되어OK하면 후속작업이 진행되고 완료된다.
-Diskgroup생성전asm_diskstring추가작업 root@rose1:/root# su - oracle [ROSE1]oracle@rose1:/home/oracle# . oraenv ORACLE_SID = [ROSE1] ? +ASM1 ORACLE_HOME = [/home/oracle] ? /u01/app/23.0.0.0/grid The Oracle base remains unchanged with value /u01/app/oracle [+ASM1]oracle@rose1:/home/oracle# sa SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Thu Aug 8 09:03:08 2024 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle.All rights reserved. Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.5.0.24.07 SQL> alter system set asm_diskstring='/dev/VOTE*','/dev/DATA*','/dev/RECO*'; System altered. export DISPLAY=작업PC IP:0.0 ./asmca
- Directory사전 생성 mkdir -p /u01/app/oracle/product/23.0.0.0/dbhome_1 chown -R oracle:dba /u01 chmod -R 755 /u01 1.설치파일을FTP로 해당서버에upload LINUX.X64_235000_forEngineeredSystems_db_home.zip (Size 2.1G) 2.설치이미지 압축해제(반드시$ORACLE_HOME밑에 압축해제 해야 함) LINUX.X64_235000_forEngineeredSystems_db_home.zip -d $ORACLE_HOME 3.runInstaller실행 xhost + su – oracle export DISPLAY=작업PC IP:0.0 cd $ORACLE_HOME ./runInstaller실행
[Set Up RAC Software Only지정 후, Next]
[Oralcle Real Application Clusters선택후Next]
[노드 지정후, Next]
[Enterprise Edition지정 후, Next]
[Oracle base지정 후, Next]
[그룹 지정 후, Next]
[Automatically run configuration scripts선택하지 않는다, Next]
[Ignore all체크후, Next]
[Summary확인후, Install]
[설치 진행이 완료되면 화면의script를 순서대로 실행]
[root@rose1]# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to dba. The execution of the script is complete. [root@rose1]# /u01/app/oracle/product/23.0.0.0/dbhome_1/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME=/u01/app/oracle/product/23.0.0.0/dbhome_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... 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. 2번도 동일하게 수행후OK
3. 설치이미지 압축해제 (반드시 $ORACLE_HOME 밑에 압축해제 해야 함) unzip LINUX.X64_235000_forEngineeredSystems_db_home .zip -d $ORACLE_HOME
4. runInstaller실행 xhost + su – oracle export DISPLAY=작업PC IP:0.0 cd $ORACLE_HOME ./runInstaller 실행
[Set Up Software Only 지정 후, Next]
[Single instance database installation지정 후, Next]
[Enterprise Edition 지정 후, Next]
[Oracle base 지정 후, Next]
[Inventory 지정 후, Next]
[그룹 지정 후, Next]
[Automatically run configuration scripts 선택하지 않는다, Next]
[Ignore all 체크후, Next]
[설치 진행이 완료되면 화면의 script 를 순서대로 실행]
[root@23ai ~]# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to dba. The execution of the script is complete.
[root@23ai ~]# /u01/app/oracle/product/23.0.0.0/dbhome_1/root.sh Performing root user operation.
The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/23.0.0.0/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ...
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.
[Database S/W 설치완료]
5. Netca 이용 리스너 생성 cd $ORACLE_HOME/bin ./netca
[Listener configuration 지정 후, Next]
[Add 지정 후, Next]
[Listener name지정 후, Next]
[Protocol 지정 후, Next]
[Port 지정 후, Next]
[No 지정 후, Next]
[Next]
[Finish]
6. DBCA 이용 Database 생성 <--- 단 여기서 Exadata 용 Binary 이므로 마지막에 Error 가 난다. 해결방법도 기술한다. export DISPLAY=작업PC IP:0.0 cd $ORACLE_HOME/bin ./dbca
[Create Database 선택 후, Next]
[Advanced configuration선택 후, Next]
[Custom Database 선택 후, Next]
[db name/SID 지정, PDB 이름 지정 (252개까지 가능) 후, Next]
[Datafile 저장경로 설정 후, Next]
[Fast Recovery Area / 아카이브 경로 설정, Next]
[리스너 지정 후, Next]
[Component 확인 후, Next]
[Character sets 확인 후, Next]
[EM Uncheck 후, Next]
[SYS, SYSTEM password 설정 후, Next]
[Generate database creation scripts 체크후 , Next]
[Summary 확인후, Finish]
[Database 생성 진행]
On-premise 정식버전이면 에러 안 뿜겠지만 이 단계 이후 진행하면 DB생성 진행중
ORA-27350: This version of the Oracle Database software cannot be run on this platform
메세지와 함께 더 이상 설치를 진행할수 없다.
- 해결방법-
GUI 로는 설치를 마무리 할수 없고 Silent mode 를 써서 아름답게 마무리 하자.
DB생성 12 step 에서 생성된 scripts 의 directory 로 간다.
cd /u01/app/oracle/admin/CDB1/scripts
vi init.ora
_exadata_feature_on=true <--- 마지막 줄에 해당 hidden 추가후 저장
[CDB1]oracle@23ai:/u01/app/oracle/admin/CDB1/scripts# ls -al
CDB1.sh 실행하면 됨. <---- 당연하게도 스크립트는 설정된 이름에 따라 다를수 있다.
text 모드로 쭈욱 진행되고 완료가 되면...
[CDB1]oracle@23ai:/home/oracle# ss
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Thu Aug 1 22:48:48 2024 Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.5.0.24.07
CDB$ROOT@CDB1> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED
- 설치 끝!!--
23ai 는 설치작업 보다는 새로이 적용된 기능 및 Architecture 자체를 이해하고 접근하는 것이 굉장히 중요한 버전이 되었다.
- 설정확인 Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.5.0.24.07
SQL> show parameter time_at_dbtimezone NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ time_at_dbtimezone string off
SQL> select name,ISPDB_MODIFIABLE from v$parameter where name='time_at_dbtimezone'; NAME ISPDB ---------------------------------------- ----- time_at_dbtimezone TRUE
- 2개의 PDB가 있다고 가정 - 각각의 PDB에 접속하여 아래와 같이 변경
미국 LA SQL>alter system set time_at_dbtimezone = 'DATABASE' scope=spfile; SQL>alter database set time_zone='America/Los_Angeles';
한국 Seoul SQL>alter system set time_at_dbtimezone = 'DATABASE' scope=spfile; SQL>alter database set time_zone='Asia/Seoul';
- CDB에서 모든 PDB 재기동 SQL>alter pluggable database all close; SQL>alter pluggable database all open;
- 각각의 PDB에 접속하여 아래와 같이 확인 show parameter time_at_dbtimezone SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "NOW" FROM DUAL;
OFF- 모든 시간 종속 작업의 경우 데이터베이스는 데이터베이스가 시작될 때 적용되었던 데이터베이스 호스트 시스템의 표준 시간대를 사용합니다. 이는 Oracle Database 23ai 이전 릴리스의 동작입니다.
USER_SQL- SQL 함수는 PDB 설정 에 따라 날짜와 시간을 반환합니다 SYSDATE. 다른 모든 시간 종속 작업의 경우 데이터베이스는 데이터베이스가 시작되었을 때 적용되었던 데이터베이스 호스트 시스템의 표준 시간대를 사용합니다. SYSTIMESTAMPDBTIMEZONE
DATABASE - 모든 시간 종속 작업에 대해 데이터베이스는 PDB 설정 에서 지정된 시간대를 사용합니다.
Run post script (root로) export GI_HOME=/u01/app/19.0.0.0/grid $GI_HOME/rdbms/install/rootadd_rdbms.sh $GI_HOME/crs/install/rootcrs.sh -postpatch -rollback
이런식으로 접속하곤 하는데 이것 마저 귀찮다면 환경변수 설정하고 바로 SYS나 SYSTEM 계정으로 PDB로 접속할수 있다. = bequeath connections
공식적인 방법은 아니다.
1. 우선 CDB 에 접속하여 아래와 같은 trigger 를 만들어 준다.
SQL>CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DBMS_SET_PDB" after logon on database
WHEN (user = 'SYS' or user = 'SYSTEM') declare pdb_name varchar2(64); begin DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name); if(pdb_name is not null) then EXECUTE IMMEDIATE 'alter session set container = ' || pdb_name; end if; exception when others then NULL; end dbms_set_pdb; / Trigger created.
2. 오라클 계정 .bash_profile 설정
CDB 이름은 CDB
PDB 이름은 PDB 라고 가정
export ORACLE_SID=CDB export ORACLE_PDB_SID=PDB
:wq!
저장후..
다시 접속
sqlplus "/as sysdba"
SQL> show user USER is "SYS"
SQL> show con_name
CON_NAME ------------------------------ PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB READ WRITE NO
