클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
PDB 데이터 딕셔너리
- 각 PDB 내의 데이터 딕셔너리는 해당 PDB만 볼 수 있다.
- 물리적으로 PDB의 데이터 딕셔너리 데이터는 각각의 PDB의 SYSTEM 테이블 스페이스에 저장된다.
- CDB 뷰에서 PDB의 데이터 딕셔너리에 액세스 할 수 있도록 PDB가 생성되거나 플러그될 때 CDB와 PDB의 데이터 딕셔너리 사이에 포인터 셋이 만들어진다.
- PDB 생성과 삭제
- 오라클에서 제공하는 Hand-On-Lab: http://www.oracle.com/technetwork/community/developer-vm/index.html#hol
Hand-On-Lab 제공 시나리오
| 1 | seed 데이터베이스로부터 PDB 생성 | 복제 방식 |
| 2 | PDB를 언플러그해서 CDB로 플러그 | 플로그인 방식 |
| 3 | non-CDB를 PDB로 플러그 | 플로그인 방식 |
seed 데이터베이스로부터 PDB 생성
SQL> create pluggable database PDB3 admin user adm identified by adm file_name_convert=('/oradata/CDB1/pdbseed', '/oradata/CDB1/pdb3');
Pluggable database created.
SQL> alter session set container=pdb3;
Session altered.
SQL>
SQL> startup
Pluggable Database opened.
PDB를 언플러그해서 CDB로 플러그
SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in PDB3...
***************************************************************************
************************************************************
====>> ERRORS FOUND for PDB3 <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for PDB3 <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in PDB3 Completed.
***************************************************************************
***************************************************************************
***************************************************************************
oracle@localhost:/oradata/CDB1/pdb3$ cat /u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 11-25-2016 11:28:08
Script Version: 12.1.0.2.0 Build: 010
**********************************************************************
Database Name: CDB1
Container Name: PDB3
Container ID: 3
Version: 12.1.0.1.0
Compatible: 12.1.0.0.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V18
**********************************************************************
[Update parameters]
[No parameters to update]
**********************************************************************
**********************************************************************
[Renamed Parameters]
[No Renamed Parameters in use]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
[No Obsolete or Desupported Parameters in use]
**********************************************************************
[Component List]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] OPTION OFF
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
**********************************************************************
[ Unsupported Upgrade: Tablespace Data Suppressed ]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
0 ERRORS exist in your database.
0 WARNINGS exist in your database.
0 INFORMATIONAL messages messages have been reported.
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 1509653.1
***********************************
SQL> execute dbms_stats.gather_dictionary_stats;
SQL> @/u01/app/oracle/cfgtoollogs/CDB1/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2016-11-25 11:28:05 Version: 12.1.0.2 Build: 010
Beginning Pre-Upgrade Fixups...
Executing in container PDB3
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
No fixup routines were executed.
**************************************************
**************** Pre-Upgrade Fixup Script Complete *********************
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database PDB3 close;
SQL> alter pluggable database PDB3 unplug into '/tmp/pdb3.xml';
SQL> drop pluggable database PDB3 keep datafiles;
SQL> select name, cdb from v$database;
NAME CDB
--------------------------- ---------
CDB2 YES
SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE
DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/tmp/pdb3.xml',
pdb_name => 'PDB3') WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
NO
SQL> select name,cause,type, status, message
from PDB_PLUG_IN_VIOLATIONS
where name='PDB3'; 2 3
NAME CAUSE TYPE STATUS MESSAGE
---------- ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
PDB3 VSN not ma ERROR PENDING PDB's version does not match CDB's version: PDB's version 12.1.0.0.0. CDB's version 12.1.0.2.0.
tch
PDB3 APEX WARNING PENDING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08
PDB3 Parameter WARNING PENDING CDB parameter processes mismatch: Previous 300 Current 200
PDB3 Parameter WARNING PENDING CDB parameter sga_target mismatch: Previous 800M Current 1000M
PDB3 Parameter WARNING PENDING CDB parameter compatible mismatch: Previous 12.1.0.0.0 Current 12.1.0.2.0
PDB3 Parameter WARNING PENDING CDB parameter pga_aggregate_target mismatch: Previous 100M Current 120M
6 rows selected.
SQL> create pluggable database pdb3 using '/tmp/pdb3.xml' file_name_convert=('/oradata/CDB1/pdb3', '/oradata/CDB2/pdb3');
Pluggable database created.
SQL> alter pluggable database PDB3 open upgrade;
Warning: PDB altered with errors.
alter pluggable database PDB3 open upgrade
Fri Nov 25 13:09:18 2016
Pluggable database PDB3 dictionary check beginning
Pluggable Database PDB3 Dictionary check complete
Database Characterset for PDB3 is WE8ISO8859P15
***************************************************************
WARNING: Pluggable Database PDB3 with pdb id - 4 is
altered with errors or warnings. Please look into
PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Opening pdb PDB3 (4) with no Resource Manager plan active
Fri Nov 25 13:09:19 2016
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Fri Nov 25 13:09:19 2016
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
Fri Nov 25 13:09:19 2016
ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY;
Fri Nov 25 13:09:19 2016
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Pluggable database PDB3 opened read write
Completed: alter pluggable database PDB3 open upgrade
$ /u01/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c 'PDB3' catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = PDB3
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_6537.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
Parallel PDB Upgrades = 2
SQL PDB Process Count = 2
SQL Process Count = 0
New SQL Process Count = 2
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1
PDB3
PDB Inclusion:[PDB3] Exclusion:[]
Start processing of PDB3
[/u01/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c 'PDB3' -I -i pdb3 -n 2 catupgrd.sql]
Argument list for [catctl.pl]
SQL Process Count n = 2
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = pdb3
Run in c = PDB3
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 1
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0.2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrdpdb3_catcon_6778.lst
catcon: See catupgrdpdb3*.log files for output generated by scripts
catcon: See catupgrdpdb3_*.lst files for spool files, if any
Number of Cpus = 2
SQL PDB Process Count = 2
SQL Process Count = 2
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1
PDB3
PDB Inclusion:[PDB3] Exclusion:[]
------------------------------------------------------
Phases [0-73] Start Time:[2016_11_25 13:15:24]
Container Lists Inclusion:[PDB3] Exclusion:[NONE]
------------------------------------------------------
Serial Phase #: 0 PDB3 Files: 1 Time: 8s
Serial Phase #: 1 PDB3 Files: 5 Time: 30s
Restart Phase #: 2 PDB3 Files: 1 Time: 0s
Parallel Phase #: 3 PDB3 Files: 18 Time: 10s
Restart Phase #: 4 PDB3 Files: 1 Time: 0s
Serial Phase #: 5 PDB3 Files: 5 Time: 12s
Serial Phase #: 6 PDB3 Files: 1 Time: 7s
Serial Phase #: 7 PDB3 Files: 4 Time: 4s
Restart Phase #: 8 PDB3 Files: 1 Time: 0s
Parallel Phase #: 9 PDB3 Files: 62 Time: 37s
Restart Phase #:10 PDB3 Files: 1 Time: 0s
Serial Phase #:11 PDB3 Files: 1 Time: 9s
Restart Phase #:12 PDB3 Files: 1 Time: 0s
Parallel Phase #:13 PDB3 Files: 91 Time: 4s
Restart Phase #:14 PDB3 Files: 1 Time: 0s
Parallel Phase #:15 PDB3 Files: 111 Time: 8s
Restart Phase #:16 PDB3 Files: 1 Time: 0s
Serial Phase #:17 PDB3 Files: 3 Time: 0s
Restart Phase #:18 PDB3 Files: 1 Time: 0s
Parallel Phase #:19 PDB3 Files: 32 Time: 18s
Restart Phase #:20 PDB3 Files: 1 Time: 0s
Serial Phase #:21 PDB3 Files: 3 Time: 4s
Restart Phase #:22 PDB3 Files: 1 Time: 0s
Parallel Phase #:23 PDB3 Files: 23 Time: 91s
Restart Phase #:24 PDB3 Files: 1 Time: 0s
Parallel Phase #:25 PDB3 Files: 11 Time: 38s
Restart Phase #:26 PDB3 Files: 1 Time: 0s
Serial Phase #:27 PDB3 Files: 1 Time: 1s
Restart Phase #:28 PDB3 Files: 1 Time: 0s
Serial Phase #:30 PDB3 Files: 1 Time: 0s
Serial Phase #:31 PDB3 Files: 257 Time: 14s
Serial Phase #:32 PDB3 Files: 1 Time: 0s
Restart Phase #:33 PDB3 Files: 1 Time: 0s
Serial Phase #:34 PDB3 Files: 1 Time: 1s
Restart Phase #:35 PDB3 Files: 1 Time: 0s
Restart Phase #:36 PDB3 Files: 1 Time: 0s
Serial Phase #:37 PDB3 Files: 4 Time: 34s
Restart Phase #:38 PDB3 Files: 1 Time: 0s
Parallel Phase #:39 PDB3 Files: 13 Time: 61s
Restart Phase #:40 PDB3 Files: 1 Time: 0s
Parallel Phase #:41 PDB3 Files: 10 Time: 5s
Restart Phase #:42 PDB3 Files: 1 Time: 0s
Serial Phase #:43 PDB3 Files: 1 Time: 4s
Restart Phase #:44 PDB3 Files: 1 Time: 0s
Serial Phase #:45 PDB3 Files: 1 Time: 1s
Serial Phase #:46 PDB3 Files: 1 Time: 0s
Restart Phase #:47 PDB3 Files: 1 Time: 0s
Serial Phase #:48 PDB3 Files: 1 Time: 56s
Restart Phase #:49 PDB3 Files: 1 Time: 0s
Serial Phase #:50 PDB3 Files: 1 Time: 24s
Restart Phase #:51 PDB3 Files: 1 Time: 0s
Serial Phase #:52 PDB3 Files: 1 Time: 0s
Restart Phase #:53 PDB3 Files: 1 Time: 0s
Serial Phase #:54 PDB3 Files: 1 Time: 29s
Restart Phase #:55 PDB3 Files: 1 Time: 0s
Serial Phase #:56 PDB3 Files: 1 Time: 8s
Restart Phase #:57 PDB3 Files: 1 Time: 1s
Serial Phase #:58 PDB3 Files: 1 Time: 0s
Restart Phase #:59 PDB3 Files: 1 Time: 0s
Serial Phase #:60 PDB3 Files: 1 Time: 0s
Restart Phase #:61 PDB3 Files: 1 Time: 0s
Serial Phase #:62 PDB3 Files: 1 Time: 0s
Restart Phase #:63 PDB3 Files: 1 Time: 0s
Serial Phase #:64 PDB3 Files: 1 Time: 1s
Serial Phase #:65 PDB3 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH; LIBPATH=/u01/app/oracle/product/12.1.0.2/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u01/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/lib; export DYLD_LIBRARY_PATH; /u01/app/oracle/product/12.1.0.2/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only -pdbs 'PDB3' > catupgrdpdb3_datapatch_upgrade.log 2> catupgrdpdb3_datapatch_upgrade.err
returned from sqlpatch
Time: 37s
Serial Phase #:66 PDB3 Files: 1 Time: 2s
Serial Phase #:68 PDB3 Files: 1 Time: 1s
Serial Phase #:69 PDB3 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH; LIBPATH=/u01/app/oracle/product/12.1.0.2/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u01/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/lib; export DYLD_LIBRARY_PATH; /u01/app/oracle/product/12.1.0.2/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -pdbs 'PDB3' > catupgrdpdb3_datapatch_normal.log 2> catupgrdpdb3_datapatch_normal.err
returned from sqlpatch
Time: 47s
Serial Phase #:70 PDB3 Files: 1 Time: 72s
Serial Phase #:71 PDB3 Files: 1 Time: 5s
Serial Phase #:72 PDB3 Files: 1 Time: 4s
Serial Phase #:73 PDB3 Files: 1 Time: 0s
------------------------------------------------------
Phases [0-73] End Time:[2016_11_25 13:26:52]
Container Lists Inclusion:[PDB3] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 689s PDB3
LOG FILES: (catupgrdpdb3*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0.2/cfgtoollogs/CDB2/upgrade/upg_summary.log
Total Upgrade Time: [0d:0h:11m:29s]
Time: 691s For PDB(s)
Grand Total Time: 691s
LOG FILES: (catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:11m:31s]
SQL> alter session set container=PDB3;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-11-25 13:32:34
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-11-25 13:33:40
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB3 READ WRITE NO
"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4285
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.