12c实验记录4：现在 很多生产环境多个业务模块集成到一个数据库中，但是系统共用模块一旦出问题将会影响所有业务的运行，这是最大的一个弊端，对于这种情况，12c 推出了 pluggable 完美的解决了这个问题，而且简化了 data migration 的操作。下面是我CRT保存的实验记录。
SQL> SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl12c
db_unique_name string orcl12c
global_names boolean FALSE
instance_name string orcl12c
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string orcl12c
SQL>
SQL> show con_id con_name
CON_ID
------------------------------
1
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST MOUNTED
SQL>
SQL> select con_id,name,open_mode,dbid from v$pdbs;
CON_ID NAME OPEN_MODE DBID
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY 315034528
3 TEST MOUNTED 2925957051
SQL> alter pluggable database test open;
Pluggable database altered.
SQL> alter session set container=test;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
TEST
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> col file_name for a20
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------- ---------- ------------------------------
/oradata/orcl12c/sys 1 SYSTEM
tem01.dbf
/oradata/orcl12c/sys 3 SYSAUX
aux01.dbf
/oradata/orcl12c/use 6 USERS
rs01.dbf
/oradata/orcl12c/und 4 UNDOTBS1
otbs01.dbf
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------- ---------- ------------------------------
SQL> create pluggable database pdb1 admin user pdb1 identified by oracle; ------OMF 模式才能 这么用
create pluggable database pdb1 admin user pdb1 identified by oracle
*
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
SQL> conn / as sysDBA
/Connected.
SQL>
create pluggable database pdb1 admin user pdb1 identified by oracle
*
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
SQL> create pluggable database pdb1 admin user pdb identified by oracle storage (maxsize 2G max_shared_temp_size 100m) default tablespace testdb datafile '/oradata/orcl12c/test/testdb.dbf' size 25m autoextend on path_prefix = '/oradata/orcl12c/test/' file_name_convert=('/oradata/orcl12c/pdbseed','/oradata/orcl12c/test/');
create pluggable database pdb1 admin user pdb identified by oracle storage (maxsize 2G max_shared_temp_size 100m) default tablespace testdb datafile '/oradata/orcl12c/test/testdb.dbf' size 25m autoextend on path_prefix = '/oradata/orcl12c/test/' file_name_convert=('/oradata/orcl12c/pdbseed','/oradata/orcl12c/test/')
*
ERROR at line 1:
ORA-01537: cannot add file '/oradata/orcl12c/test//system01.dbf' - file already
part of database
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST MOUNTED
SQL> alter pluggable database test open;
Pluggable database altered.
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST READ WRITE NO
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> alter pluggable database test close immediate;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST MOUNTED
SQL> alter pluggable database test unplug into '/tmp/test.xml';
Pluggable database altered.
SQL> drop pluggable database test including datafiles;
Pluggable database dropped.
SQL> show user
USER is "SYS"
SQL> show parameter pdb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string
pdb_lockdown string
pdb_os_credential string
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
SQL>
SQL> create pluggable database pdb1 admin user pdb1 identified by oracle storage (maxsize 2G max_shared_temp_size 100M) default tablespace testdb datafile '/oradata/orcl12c/test/testdb.dbf' size 25m autoextend on path_prefix='/oradata/orcl12/test' file_name_convert=('/oradata/orcl12c/pdbseed/','/oradata/orcl12c/test');
Pluggable database created.
SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;
PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
DBID STATUS CREATION_SCN
---------- --------- ------------
2
PDB$SEED
315034528 NORMAL 1594471
3
PDB1
2270947164 NEW 1746873
PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
DBID STATUS CREATION_SCN
---------- --------- ------------
SQL> col pdb_name for a30
SQL> /
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
---------- ------------------------------ ---------- --------- ------------
2 PDB$SEED 315034528 NORMAL 1594471
3 PDB1 2270947164 NEW 1746873
SQL> l
1* select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs
SQL>
SQL> select con_id,dbid,open_mode from v$pdbs;
CON_ID DBID OPEN_MODE
---------- ---------- ----------
2 315034528 READ ONLY
3 2270947164 MOUNTED
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> select con_id,dbid,open_mode from v$pdbs;
CON_ID DBID OPEN_MODE
---------- ---------- ----------
2 315034528 READ ONLY
3 2270947164 READ WRITE
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';
Pluggable database altered.
】SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.
SQL> create pluggable database pdb1 admin user pdb1 identified by oracle storage (maxsize 2G max_shared_temp_size 100M) default tablespace testdb datafile '/oradata/orcl12c/test/testdb.dbf' size 25m autoextend on path_prefix='/oradata/orcl12c/test' file_name_convert=('/oradata/orcl12c/pdbseed/','/oradata/orcl12c/test');
Pluggable database created.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> select file_name,file_id,tablespace_name from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------- ---------- ------------------------------
/oradata/orcl12c/tes 15 SYSTEM
tsystem01.dbf
/oradata/orcl12c/tes 16 SYSAUX
tsysaux01.dbf
/oradata/orcl12c/tes 17 TESTDB
t/testdb.dbf
SQL> select open_mode,name from v$database;
OPEN_MODE NAME
-------------------- ---------
READ WRITE ORCL12C
SQL> show user;
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select open_mode,name,con_id from v$pdbs;
OPEN_MODE NAME CON_ID
---------- ------------------------------ ----------
READ ONLY PDB$SEED 2
READ WRITE PDB1 3
SQL> conn / as sysdba
Connected.
SQL> startup force;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 562039536 bytes
Database Buffers 268435456 bytes
Redo Buffers 5455872 bytes
show Database mounted.
con_name con_id
Database opened.
SQL>
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
SQL> alter session set container=pdb1
2 ;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 MOUNTED
SQL> show pdb
SP2-0158: unknown SHOW option "pdb"
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> alter database open;
select file_name from dba_data_files;
select name from v$datafiles;
Database altered.
SQL>
FILE_NAME
--------------------
/oradata/orcl12c/tes
tsystem01.dbf
/oradata/orcl12c/tes
tsysaux01.dbf
/oradata/orcl12c/tes
t/testdb.dbf
SQL> col file_name for a50
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME
--------------------------------------------------
TABLESPACE_NAME
------------------------------
/oradata/orcl12c/testsystem01.dbf
SYSTEM
/oradata/orcl12c/testsysaux01.dbf
SYSAUX
/oradata/orcl12c/test/testdb.dbf
TESTDB
SQL> shutdown immediate
SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.
SQL> create pluggable database pdb1 admin user pdb1 identified by oracle storage (maxsize 2G max_shared_temp_size 100M) default tablespace testdb datafile '/oradata/orcl12c/test/testdb.dbf' size 25m autoextend on path_prefix='/oradata/orcl12c/test/' file_name_convert=('/oradata/orcl12c/pdbseed/','/oradata/orcl12c/test/');
Pluggable database created.
SQL> alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';
alter pluggable database pdb1 unplug into '/tmp/pdb1.xml'
*
ERROR at line 1:
ORA-65170: XML file /tmp/pdb1.xml already exists
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
ORCL12C YES
SQL> create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/)';
create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/)'
*ERROR at line 1:ORA-02000: missing ) keyword
SQL> reate pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=
SQL> create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/');
create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/')
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file
/oradata/orcl12c/test/system01.dbf for value of guid
(2EA15526B43222B5E0530100007F4249 in the plug XML file,
2EB115F0AE9C19BEE0530100007FAF6D in the data file)
1* create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/')
SQL> create pluggable database pdb2 using '/tmp/pdb1.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/')
2 ;
create pluggable database pdb2 using '/tmp/pdb1.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/')
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing
container.
SQL> drop pluggable database pdb1;
Pluggable database dropped.
SQL> create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/');
create pluggable database pdb2 using '/tmp/test.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/')
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file
/oradata/orcl12c/test/system01.dbf for value of guid
(2EA15526B43222B5E0530100007F4249 in the plug XML file,
2EB115F0AE9C19BEE0530100007FAF6D in the data file)
SQL> create pluggable database pdb2 using '/tmp/pdb1.xml' copy file_name_convert=('/oradata/orcl12c/test/','/oradata/orcl12c/pdb2/');
Pluggable database created.
SQL> SQL> SQL>
SQL> exec dbms_pdb.sync_pdb();
PL/SQL procedure successfully completed.
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 315034528 PDB$SEED READ ONLY
3 3710433175 PDB2 MOUNTED
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database pdb2 UNPLUG into '/tmp/pdb2.xml';
alter pluggable database pdb2 UNPLUG into '/tmp/pdb2.xml'
*
ERROR at line 1:
ORA-65170: XML file /tmp/pdb2.xml already exists
Pluggable database altered.
SQL> alter pluggable database pdb2 open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database
SQL> drop pluggable database pdb2;
Pluggable database dropped.
SQL> show con_id con_name
CON_ID
------------------------------
1
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
SQL> create pluggable database pdb3 using '/tmp/pdb2.xml' copy file_name_convert=('/oradata/orcl12c/pdb2/','/oradata/orcl12c/pdb3/');
Pluggable database created.
SQL> select con_id,open_mode,name from v$pdbs;
CON_ID OPEN_MODE NAME
---------- ---------- ------------------------------
2 READ ONLY PDB$SEED
3 MOUNTED PDB3
SQL> alter session set container=pdb3;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB3
SQL> alter database open;
select con_id,open_mode,name from v$pdbs;
Database altered.
SQL>
CON_ID OPEN_MODE NAME
---------- ---------- ------------------------------
3 READ WRITE PDB3
SQL> shutdown immediate;
Pluggable Database closed.
SQL> show con_name
CON_NAME
------------------------------
PDB3
SQL>
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
来源： http://blog.sina.com.cn/s/blog_1442877660102vzng.html |
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖