pdb 在同版本下 unplug plug 操作

鄢雅畅

2023-12-01

os: centos 7.4
db: oracle 12.1.0.2

oracle 12c 开始 ,最具优势之一就是 pdb 的 unplug 和 plug .
本文介绍下同版本的 unplug plug 操作.

版本

# lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.4.1708 (Core) 
Release:	7.4.1708
Codename:	Core

# su - oracle
$ sqlplus / as sydba;
SQL> set lines 500;
SQL> set pages 500;
SQL> 
SQL> 
SQL> select ins."INSTANCE_NUMBER",ins."INSTANCE_NAME",ins."HOST_NAME",ins."VERSION"
from v$instance ins
;   

INSTANCE_NUMBER INSTANCE_NAME	 HOST_NAME							  VERSION
--------------- ---------------- ---------------------------------------------------------------- -----------------
	      1 orcl		 oradb								  12.1.0.2.0

SQL> select *
from v$version v
where 1=1
;

BANNER										     CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production		  0
PL/SQL Release 12.1.0.2.0 - Production							  0
CORE	12.1.0.2.0	Production								  0
TNS for Linux: Version 12.1.0.2.0 - Production						  0
NLSRTL Version 12.1.0.2.0 - Production							  0

数据准备

$ sqlplus / as sysdba;

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 22 17:59:58 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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> show con_id;  

CON_ID
------------------------------
1
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  MOUNTED
	 
SQL> alter pluggable database orclpdb open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO

SQL> alter session set container=orclpdb;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
ORCLPDB
SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 ORCLPDB			  READ WRITE NO

SQL> 
SQL> create table tmp_t1(c0 varchar(100));

Table created.

SQL> insert into tmp_t1 select level from dual connect by level < 5;

9 rows created.

SQL> select * from tmp_t1;

C0
--------------------------------------------------------------------------------
1
2
3
4

4 rows selected.

SQL> commit;

Commit complete.

pdbs unplug

APEX 默认安装,有时候需要卸载 APEX

$ sqlplus / as sysdba;

SQL> select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';

COMP_ID 		       STATUS
------------------------------ --------------------------------------------
APEX			       VALID

SQL> @?/apex/apxremov_con.sql

SQL> alter pluggable database orclpdb close;

Pluggable database altered.

SQL> alter pluggable database orclpdb unplug into '/tmp/orclpdb.xml';

Pluggable database altered.

SQL> select pdb_id, pdb_name, status from dba_pdbs;

    PDB_ID PDB_NAME														  STATUS
---------- -------------------------------------------------------------- ---------
	 3 ORCLPDB															    UNPLUGGED
	 2 PDB$SEED															    NORMAL

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  MOUNTED

SQL> drop pluggable database orclpdb keep datafiles;

SQL> select pdb_id, pdb_name, status from dba_pdbs;

    PDB_ID PDB_NAME														  STATUS
---------- -------------------------------------------------------------- ---------
	 2 PDB$SEED															    NORMAL

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO

ok,unplug 操作成功.

$ lsnrctl status

Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", 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...
Service "orclp" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclp_DGMGRL" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

pdbs plug

现在操作下 plug, 文件 /tmp/orclpdb.xml 非常重要.

SQL> create pluggable database orclpdb using '/tmp/orclpdb.xml' nocopy tempfile reuse;

SQL> select pdb_id, pdb_name, status from dba_pdbs;

    PDB_ID PDB_NAME														  STATUS
---------- -------------------------------------------------------------- ---------
	 3 ORCLPDB															    NEW
	 2 PDB$SEED															    NORMAL

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  MOUNTED

SQL> alter pluggable database orclpdb open;

Pluggable database altered.

SQL> alter session set container=orclpdb;

Session altered.

SQL> select * from tmp_t1;

C0
--------------------------------------------------------------------------------
1
2
3
4

4 rows selected.

plug 操作成功.

上述create pluggable database语句中，因为数据文件都在xml文件指定的位置，且仍使用原来的位置作为新的pdb的数据文件的存储位置，因此没有包含其他子句。

另外一条plug PDB的语句也可能如下：

CREATE PLUGGABLE DATABASE orclpdb
USING '/tmp/orclpdb.xml' 
SOURCE_FILE_NAME_CONVERT=('/location1/','/location2/')
MOVE 
FILE_NAME_CONVERT=('/location2/','/location3/')
PATH_PREFIX='/location3/'
STORAGE ( MAXSIZE              UNLIMITED 
          MAX_SHARED_TEMP_SIZE UNLIMITED
         );

这里xml文件中明确数据文件在/location1/中，而实际上数据文件在/location2/中，且最终我们要将数据文件
放在/location3/中。

参考:
https://docs.oracle.com/database/121/SQLRF/statements_2008.htm#SQLRF55667
https://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

$ tail -n 500 /u01/app/oracle/diag/rdbms/orclp/orcl/trace/alert_orcl.log

Mon Apr 22 21:46:21 2019
Archived Log entry 540 added for thread 1 sequence 204 ID 0x5b00f5a1 dest 1:
drop pluggable database orclpdb keep datafiles
Mon Apr 22 21:48:11 2019
Deleted file /u01/app/oracle/oradata/orcl/orclpdb/orclpdb_temp012019-01-22_03-25-27-PM.dbf
Completed: drop pluggable database orclpdb keep datafiles
Mon Apr 22 22:00:11 2019
create pluggable database orclpdb using '/tmp/orclpdb.xml' nocopy
Mon Apr 22 22:00:12 2019
****************************************************************
Pluggable Database ORCLPDB with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for ORCLPDB is AL32UTF8
Deleting old file#8 from file$ 
Deleting old file#9 from file$ 
Deleting old file#10 from file$ 
Deleting old file#11 from file$ 
Adding new file#12 to file$(old file#8) 
Adding new file#13 to file$(old file#9) 
Adding new file#14 to file$(old file#10) 
Adding new file#15 to file$(old file#11) 
Successfully created internal service orclpdb at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database ORCLPDB with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database orclpdb using '/tmp/orclpdb.xml' nocopy
Mon Apr 22 22:00:28 2019
TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P508 (43576) VALUES LESS THAN (TO_DATE(' 2019-04-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY: ADDED INTERVAL PARTITION SYS_P511 (43576) VALUES LESS THAN (TO_DATE(' 2019-04-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
alter pluggable database orclpdb open
Mon Apr 22 22:01:40 2019
Pluggable database ORCLPDB dictionary check beginning
Pluggable Database ORCLPDB Dictionary check complete
Database Characterset for ORCLPDB is AL32UTF8
Accessing dba_registry_sqlpatch hit error: 942
Opening pdb ORCLPDB (3) with no Resource Manager plan active
Mon Apr 22 22:01:43 2019
Logminer Bld: Build started
Resize operation completed for file# 12, old size 276480K, new size 286720K
Mon Apr 22 22:01:46 2019
Logminer Bld: Done
Pluggable database ORCLPDB opened read write
Completed: alter pluggable database orclpdb open
Mon Apr 22 22:01:47 2019
Thread 1 advanced to log sequence 207 (LGWR switch)
  Current log# 3 seq# 207 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Mon Apr 22 22:01:47 2019
Archived Log entry 542 added for thread 1 sequence 206 ID 0x5b00f5a1 dest 1:
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

# cat /tmp/orclpdb.xml 

<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <xmlversion>1</xmlversion>
  <pdbname>ORCLPDB</pdbname>
  <cid>3</cid>
  <byteorder>1</byteorder>
  <vsn>202375680</vsn>
  <vsns>
    <vsnnum>12.1.0.2.0</vsnnum>
    <cdbcompt>12.1.0.2.0</cdbcompt>
    <pdbcompt>12.1.0.2.0</pdbcompt>
    <vsnlibnum>0.0.0.0.22</vsnlibnum>
    <vsnsql>22</vsnsql>
    <vsnbsv>8.0.0.0.0</vsnbsv>
  </vsns>
  <dbid>3080346874</dbid>
  <ncdb2pdb>0</ncdb2pdb>
  <cdbid>1526014759</cdbid>
  <guid>8007E1AF97E22C23E053C938A8C0BA00</guid>
  <uscnbas>3663234</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/oradata/orcl/orclpdb/system01.dbf</path>
      <afn>8</afn>
      <rfn>1</rfn>
      <createscnbas>1738816</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>34560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3080346874</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3663228</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2962611</frlsb>
      <frlt>998931252</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>SYSAUX</name>
    <type>0</type>
    <tsn>1</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf</path>
      <afn>9</afn>
      <rfn>4</rfn>
      <createscnbas>1738819</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>76800</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3080346874</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3663228</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2962611</frlsb>
      <frlt>998931252</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>TEMP</name>
    <type>1</type>
    <tsn>2</tsn>
    <status>1</status>
    <issft>0</issft>
    <bmunitsize>128</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/orcl/orclpdb/orclpdb_temp012019-01-22_03-25-27-PM.dbf</path>
      <afn>3</afn>
      <rfn>1</rfn>
      <createscnbas>1738817</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>0</status>
      <fileblocks>2560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>80</incsize>
    </file>
  </tablespace>
  <tablespace>
    <name>USERS</name>
    <type>0</type>
    <tsn>3</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/oradata/orcl/orclpdb/SAMPLE_SCHEMA_users01.dbf</path>
      <afn>10</afn>
      <rfn>9</rfn>
      <createscnbas>1738822</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>640</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3080346874</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3663228</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2962611</frlsb>
      <frlt>998931252</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>EXAMPLE</name>
    <type>0</type>
    <tsn>4</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/oradata/orcl/orclpdb/example01.dbf</path>
      <afn>11</afn>
      <rfn>10</rfn>
      <createscnbas>1738824</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>159200</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3080346874</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3663228</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>2962611</frlsb>
      <frlt>998931252</frlt>
    </file>
  </tablespace>
  <optional>
    <ncdb2pdb>0</ncdb2pdb>
    <csid>873</csid>
    <ncsid>2000</ncsid>
    <options>
      <option>APS=12.1.0.2.0</option>
      <option>CATALOG=12.1.0.2.0</option>
      <option>CATJAVA=12.1.0.2.0</option>
      <option>CATPROC=12.1.0.2.0</option>
      <option>CONTEXT=12.1.0.2.0</option>
      <option>DV=12.1.0.2.0</option>
      <option>JAVAVM=12.1.0.2.0</option>
      <option>OLS=12.1.0.2.0</option>
      <option>ORDIM=12.1.0.2.0</option>
      <option>OWM=12.1.0.2.0</option>
      <option>SDO=12.1.0.2.0</option>
      <option>XDB=12.1.0.2.0</option>
      <option>XML=12.1.0.2.0</option>
      <option>XOQ=12.1.0.2.0</option>
    </options>
    <olsoid>0</olsoid>
    <dv>0</dv>
    <APEX>4.2.5.00.08:1</APEX>
    <parameters>
      <parameter>processes=300</parameter>
      <parameter>memory_target=1644167168</parameter>
      <parameter>db_block_checksum='FULL'</parameter>
      <parameter>db_ultra_safe='DATA_AND_INDEX'</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible='12.1.0.2.0'</parameter>
      <parameter>archive_lag_target=600</parameter>
      <parameter>_catalog_foreign_restore=FALSE</parameter>
      <parameter>undo_retention=1440</parameter>
      <parameter>db_block_checking='FULL'</parameter>
      <parameter>utl_file_dir='/tmp'</parameter>
      <parameter>open_cursors=300</parameter>
      <parameter>enable_pluggable_database=TRUE</parameter>
    </parameters>
    <tzvers>
      <tzver>primary version:18</tzver>
      <tzver>secondary version:0</tzver>
    </tzvers>
    <walletkey>0</walletkey>
    <hasclob>1</hasclob>
    <awr>
      <loadprofile>CPU Usage Per Sec=0.000000</loadprofile>
      <loadprofile>DB Block Changes Per Sec=0.000000</loadprofile>
      <loadprofile>Database Time Per Sec=0.000000</loadprofile>
      <loadprofile>Executions Per Sec=0.000000</loadprofile>
      <loadprofile>Hard Parse Count Per Sec=0.000000</loadprofile>
      <loadprofile>Logical Reads Per Sec=0.000000</loadprofile>
      <loadprofile>Logons Per Sec=0.000000</loadprofile>
      <loadprofile>Physical Reads Per Sec=0.000000</loadprofile>
      <loadprofile>Physical Writes Per Sec=0.000000</loadprofile>
      <loadprofile>Redo Generated Per Sec=0.000000</loadprofile>
      <loadprofile>Total Parse Count Per Sec=0.000000</loadprofile>
      <loadprofile>User Calls Per Sec=0.000000</loadprofile>
      <loadprofile>User Rollbacks Per Sec=0.000000</loadprofile>
      <loadprofile>User Transaction Per Sec=0.000000</loadprofile>
    </awr>
    <hardvsnchk>0</hardvsnchk>
  </optional>
</PDB>

pdb 在同版本下 unplug plug 操作

版本

数据准备

pdbs unplug

pdbs plug

相关阅读

相关文章

相关问答

相关文档