Anyway, I read Oracle Database 12c: Oracle Multitenant Option blog today (Read more - Adopting a Non-CDB as a Pluggable Database in a Multitenant Container Database). I found how to plug non-CDB in CDB. Interesting! So, I thought I should learn and tested it a bit. So, I decided to play it - plug non-CDB in CDB (the same version 12c). (Example File)
Non-CDB name: opun
CDB name: orcl (After "opun" database was plugged in. PDB name is "pdb_opun").
First, Connected non-CDB and create XML file.
[oracle@test12c trace]$ . oraenvNote: I wanted move database files to another path, because I wanted database file of all PDBs in CDB path (u01/app/oracle/oradata/ORCL/).
ORACLE_SID = [orcl] ? opun
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@test12c trace]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 21:30:00 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
SQL> select cdb from v$database;
CDB
---
NO
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 452986632 bytes
Database Buffers 788529152 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL>
SQL> set serverout on
SQL> !mkdir /u01/app/oracle/opun_db
SQL> exec dbms_pdb.describe('/u01/app/oracle/opun_db/opun.xml')
PL/SQL procedure successfully completed.
SQL> !ls /u01/app/oracle/opun_db/
opun.xml
SQL> !less /u01/app/oracle/opun_db/opun.xml
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;
[oracle@test12c trace]$ cp /u01/app/oracle/opun_db/opun.xml /u01/app/oracle/opun_db/opun.xml.bakOr use this, when creating Pluggable database.
[oracle@test12c trace]$ grep -i opun /u01/app/oracle/opun_db/opun.xml
<pdbname>opun</pdbname>
<path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_system_8xb6bgc1_.dbf</path>
<path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_sysaux_8xb640k7_.dbf</path>
<path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp</path>
<path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_users_8xb6lr4t_.dbf</path>
[oracle@test12c trace]$
[oracle@test12c trace]$ mkdir /u01/app/oracle/oradata/ORCL/OPUN
[oracle@test12c trace]$ mv /u01/app/oracle/oradata/OPUN/datafile /u01/app/oracle/oradata/ORCL/OPUN/
[oracle@test12c trace]$ vi /u01/app/oracle/opun_db/opun.xml
[oracle@test12c trace]$ diff /u01/app/oracle/opun_db/opun.xml.bak /u01/app/oracle/opun_db/opun.xml
20c20
< <path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_system_8xb6bgc1_.dbf</path>
---
> <path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_system_8xb6bgc1_.dbf</path>
44c44
< <path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_sysaux_8xb640k7_.dbf</path>
---
> <path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_sysaux_8xb640k7_.dbf</path>
69c69
< <path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp</path>
---
> <path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp</path>
90c90
< <path>/u01/app/oracle/oradata/OPUN/datafile/o1_mf_users_8xb6lr4t_.dbf</path>
---
> <path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_users_8xb6lr4t_.dbf</path>
[oracle@test12c trace]$ grep -i opun /u01/app/oracle/opun_db/opun.xml
<pdbname>opun</pdbname>
<path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_system_8xb6bgc1_.dbf</path>
<path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_sysaux_8xb640k7_.dbf</path>
<path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp</path>
<path>/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_users_8xb6lr4t_.dbf</path>
[oracle@test12c trace]$ ll /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_system_8xb6bgc1_.dbf /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_sysaux_8xb640k7_.dbf /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_users_8xb6lr4t_.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jul 4 21:19 /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_sysaux_8xb640k7_.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 4 21:19 /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_system_8xb6bgc1_.dbf
-rw-r-----. 1 oracle oinstall 91234304 Jul 4 21:14 /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp
-rw-r-----. 1 oracle oinstall 5251072 Jul 4 21:19 /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_users_8xb6lr4t_.dbf
[oracle@test12c trace]$
Create pluggable database pdb_opun using '/u01/app/oracle/opun_db/opun.xml'
COPY
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/OPUN/', '/u01/app/oracle/oradata/ORCL/OPUN/');
Then, Connected CDB and checked by using "dbms_pdb.check_plug_compatibility".
[oracle@test12c ~]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@test12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 21:41:10 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set lines 150
SQL> set pages 1000
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
SQL> select cdb from v$database;
CDB
---
YES
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
ORCLPDB MOUNTED
SQL>
SQL> set serverout on
SQL> declare
compat boolean := FALSE;
begin
compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => '/u01/app/oracle/opun_db/opun.xml');
if compat
then
dbms_output.put_line('Yes');
else
dbms_output.put_line('No');
end if;
end;
/
No
PL/SQL procedure successfully completed.
SQL> select * from PDB_PLUG_IN_VIOLATIONS;
TIME NAME CAUSE TYPE
--------------------------------------------------------------------------- ------------------------------ ---------------------- ---------
ERROR_NUMBER LINE
------------ ----------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS
---------
ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
04-JUL-13 09.51.58.096065 PM OPUN Non-CDB to PDB WARNING
0 1
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
PENDING
Run noncdb_to_pdb.sql.
04-JUL-13 09.51.58.096681 PM OPUN Parameter WARNING
0 1
CDB parameter sga_target mismatch: Previous 1258291200 Current 0
PENDING
Please check the parameter in the current CDB
04-JUL-13 09.51.58.097387 PM OPUN Parameter WARNING
0 2
CDB parameter pga_aggregate_target mismatch: Previous 415236096 Current 0
PENDING
Please check the parameter in the current CDB
SQL>
After that, Created pluggable database.
SQL> create pluggable database pdb_opun using '/u01/app/oracle/opun_db/opun.xml' nocopy;From PDB_PLUG_IN_VIOLATIONS result - PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. So! ran noncdb_to_pdb.sql script.
create pluggable database pdb_opun using '/u01/app/oracle/opun_db/opun.xml' nocopy
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file '/u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp'
SQL> !rm /u01/app/oracle/oradata/ORCL/OPUN/datafile/o1_mf_temp_8xb6nlc7_.tmp
SQL> create pluggable database pdb_opun using '/u01/app/oracle/opun_db/opun.xml' nocopy;
Pluggable database created.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
ORCLPDB MOUNTED
PDB_OPUN MOUNTED
SQL>
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 04-JUL-2013 21:59:40
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 04-JUL-2013 21:59:05
Uptime 0 days 0 hr. 0 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, 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 "orclpdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb_opun" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB_OPUN
2 ;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB_OPUN
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> show con_nameFinally, checked tablespaces and etc.
CON_NAME
------------------------------
PDB_OPUN
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01403: no data found"
DOC> error if we're not in a PDB.
DOC> This script is intended to be run right after plugin of a PDB,
DOC> while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> VARIABLE cdbname VARCHAR2(128)
SQL> VARIABLE pdbname VARCHAR2(128)
SQL> BEGIN
2 SELECT sys_context('USERENV', 'CDB_NAME')
3 INTO :cdbname
4 FROM dual
5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
6 SELECT sys_context('USERENV', 'CON_NAME')
7 INTO :pdbname
8 FROM dual
9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> COLUMN pdbname NEW_VALUE pdbname
SQL> COLUMN pdbid NEW_VALUE pdbid
SQL>
SQL> select :pdbname pdbname from dual;
PDBNAME
--------------------------------------------------------------------------------
PDB_OPUN
1 row selected.
.
.
.
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL>
SQL> show con_name
CON_NAME
------------------------------
PDB_OPUN
SQL> select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
SQL> alter pluggable database PDB_OPUN open;
Pluggable database altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
4 rows selected.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
ORCLPDB MOUNTED
PDB_OPUN READ WRITE
3 rows selected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> connect system/***@127.0.0.1:1521/PDB_OPUN
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB_OPUN
SQL>
Posts
No comments:
Post a Comment