How To Open The PDB Automatically When CDB Restarts
1. Restart the container database:
SQL> show con_name
SQL> shutdown immediate;
SQL> startup
2. Check the status of PDBS:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB MOUNTED
3. Open the PDBS:
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
Now use save state command to save the states, so that next time ,when container db restarts, PDBs will in READ WRITE MODE automatically.
4. Save the PDB state:
SQL> alter pluggable database ORCLPDB save state;
Pluggable database altered.
5. check the saved state in dba_pdb_saved_states
SQL> select con_name, state from dba_pdb_saved_states;
CON_NAME STATE
-------------------- --------------
ORCLPDB OPEN
6.Bounce the container database, to check the PDB state:
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5117050656 bytes
Fixed Size 9277216 bytes
Variable Size 922746880 bytes
Database Buffers 4177526784 bytes
Redo Buffers 7499776 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
We can observed that PDBs are in READ WRITE mode automatically.
We can discard the saved state, so that next time CDB restarts, PDBs will start with MOUNT STATE only.
SQL> select con_name, state from dba_pdb_saved_states
CON_NAME STATE
-------------------- --------------
ORCLPDB OPEN
SQL> alter pluggable database orclpdb discard state;
Pluggable database altered.
SQL> select con_name, state from dba_pdb_saved_states;
no rows selected
1. Bounce the CDB to check the PDB State:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5117050656 bytes
Fixed Size 9277216 bytes
Variable Size 922746880 bytes
Database Buffers 4177526784 bytes
Redo Buffers 7499776 bytes
Database mounted.
Database opened.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB MOUNTED
We can observe that, only orclpdb started in MOUNTED stage, because we have discarded the saved state for orclpdb.
As this feature is not available in 12.1.0.1, we can create a trigger to open the PDBs automatically, with container database startup.
CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END;
Convert Non CDB Database To PDB Database In Oracle 12c
NON-CDB DB -NAME -> NONCDB
CDB DB NAME -> DBATEST
Make sure One container database already exists.
1. Open the non-cdb database in read only mode:
Make sure One container database already exists.
1. Open the non-cdb database in read only mode:
SQL> select name from v$database;
NAME
---------
NONCDB
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only
ORACLE instance started.
Total System Global Area 1.8119E+10 bytes
Fixed Size 7641528 bytes
Variable Size 1.0133E+10 bytes
Database Buffers 7851737088 bytes
Redo Buffers 126574592 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NONCDB READ ONLY
2. Check the compatibility of PDB on ( NONCDB)
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/export/home/oracle/NonCDB.xml');
END;
/
PL/SQL procedure successfully completed.
3. shutdown the NON-CDB database ( NONCDB)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Now connect to the container database, where it need to be plugged.
SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/export/home/oracle/NonCDB.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
5. Check the violations:
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB';
NAME CAUSE TYPE
-------------------- -------------------- ---------
MESSAGE STATUS
----------------------------------- ---------
NONCDB Non-CDB to PDB WARNING
PDB plugged in is a non-CDB, PENDING
requires noncdb_to_pdb.sql be run.
NONCDB Parameter WARNING
CDB parameter memory_target PENDING
mismatch: Previous 17280M Current
13856M
6. Create pluggable database ( DBATEST)
SQL> create pluggable database NONCDB using '/export/home/oracle/NonCDB.xml' NOCOPY;
create pluggable database NONCDB using '/export/home/oracle/NonCDB.xml' NOCOPY
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file '/archive/NONCDB/temp01.dbf'
--- As tempfile is already there, so mention tempfile reuse tag, to avoid this error.
SQL> create pluggable database NONCDB using '/export/home/oracle/NonCDB.xml' NOCOPY tempfile reuse;
Pluggable database created.
7 . Run the noncdb_to_pdb.sql script
ALTER SESSION SET CONTAINER=NONCDB;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
8. Open the PDB:
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
-------------------- ----------
NONCDB READ WRITE
1 row selected.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
-------------------- ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
NONCDB READ WRITE
No comments:
Post a Comment