Oracle 12c Database RMAN Full Backup using SQL Developer

Oracle 12c Database: Cloning PDB using SQL Developer

How to clone a PDB1 database using of SQL Developer

Modify the State to Read Only (If Open then Shutdown and Open in Read Only mode)


Use the Clone Pluggable Database Option

First you have to shutdown the PDB1 database.

 

See the status of PDB1.

 Start database read only mode.

 Right click on PDB1 database and select the Clone Pluggable database option.

 Change the Database Name: NEW_PDB & Select the File Name Conversion option.

Goto to SQL tab and change the Clone database files path.

Clone database created.

You can start the clone NEW_PDB database.

Create the new connection for "test" user which ready created in PDB1.

Verify the data in NEW_PDB database.

 

Now you start the PDB1 database for normal operation.

Oracle 12c: Opening a PDB Using SQL Developer

You can open Pluggable database using SQL Deverloper

Make a connection to the CDB and using the DBA view expand the container database.

Select the PDB1 database

 Right click on PDB1 & Select the Modify State.

Select the READ ONLY.

You can see the Status of PDB1 database. (Read Only)

Create user for testing....You are getting error because PDB1 database read only mode.

Now you start the PDB1 database.

 

Oracle 12c Database Enable Archive Log Mode for CDB using SQL Developer

Enable Archive Log Mode using SQL Developer

Enable the Archive mode

Edit the Listener.ora file.

\

Verifying if Archiver is started in CDB1

Verifying if Archiver is started in all pluggable databases like  PDB1 

Oracle 12c Database Recovering Pluggable Database

Oracle 12c Database Online Move Datafile

Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take

SQL> ALTER TABLESPACE test OFFLINE;

SQL> !mv  /ora/test1/test01.dbf    /ora/test2/test01.dbf

SQL> ALTER DATABASE RENAME FILE ‘/ora/test1/test01' TO ‘/ora/test2/test01.dbf’;

SQL>ALTER TABLESPACE test ONLINE;

The good news is that Oracle 12cR1 now offers the ability to move entire datafiles between different storage locations without ever having to take the datafiles offline. The datafiles being moved remain completely accessible to applications in almost all situations, including querying against or performing DML and DDL operations against existing objects, creating new objects, and even rebuilding indexes online. Online Move Datafile (OMD) also makes it possible to migrate a datafile between non-ASM and ASM storage (or vice-versa) while maintaining transparent application access to that datafile’s underlying database objects. OMD is completely compatible with online block media recovery, the automatic extension of a datafile, the modification of a tablespace between READ WRITE and READ ONLY mode, and it even permits backup operations to continue against any datafiles that are being moved via this feature.




[oracle@oracle12c bin]$ export ORACLE_SID=cdb1

[oracle@oracle12c bin]$ pwd

/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin

[oracle@oracle12c bin]$ ./sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Fri 6 Sept 22 01:38:02 2013

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

SQL> conn / as sysdba
Connected.


SQL> show con_name

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

SQL> alter session set container=pdb1;

Session altered.

 SQL> show pdbs;

    CON_ID CON_NAME  OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

3 PDB1  READ WRITE NO

SQL> select file_name from dba_data_Files;

FILE_NAME

--------------------------------------------------------------------------------

/ora/oracle/app/oracle/oradata/cdb1/pdb1/system01.dbf

/ora/oracle/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf

/ora/oracle/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

SQL> show con_name

CON_NAME

------------------------------

PDB1

SQL> select tablespace_name from dba_data_Files;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

USERS

Creating Tablespace user_data

SQL> create tablespace user_dat 

  2  datafile '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf'

  3  size 1g;

Tablespace created.

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

USERS

USER_DAT

Inserting Rows in test table

SQL> create table system.test(id number) tablespace user_dat;

Table created.

SQL> insert into system.test values(05);

1 row created.

SQL> commit;

Commit complete.

Move Tablespace datafile to another location

SQL> alter database move datafile

  2  '/ora/oracle/app/oracle/oradata/cdb1/pdb1/userdat.dbf' to

  3  '/ora/oracle/app/oracle/oradata/cdb1/movefile/userdat.dbf';

Database altered.

SQL> insert into system.test values(20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from system.test;

ID

----------

5

20

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@oracle12c bin]$ pwd

/ora/oracle/app/oracle/product/12.1.0/dbhome_1/bin

oracle@oracle12c bin]$ cd /

oracle@oracle12c cdb1]$ ls

control01.ctl  pdb1.xml  pdbseed     redo03.log    temp01.dbf

movefile       pdb2      redo01.log  sysaux01.dbf  undotbs01.dbf

pdb1           pdb3      redo02.log  system01.dbf  users01.dbf

oracle@oracle12c cdb1]$ cd movefile

oracle@oracle12c movefile]$ pwd

/ora/oracle/app/oracle/oradata/cdb1/movefile

oracle@oracle12c movefile]$ ls

userdat.dbf

[oracle@oracle12c movefile]$ cd ..

[oracle@oracle12c cdb1]$ cd pdb1

[oracle@oracle12c pdb1]$ ls

pdb1_users01.dbf  

sysaux01.dbf  

system01.dbf  

temp01.dbf

Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c

Migrate a Non-Container DB (CDB) to PDB

Container DB: CDB2

Non-PDB:       PDB7

 Shutdown the non-CDB and start it in read-only mode.

$ export ORACLE_SID=pdb7

$ sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup open read only;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size    2293880 bytes
Variable Size  595595144 bytes
Database Buffers  234881024 bytes
Redo Buffers    2334720 bytes
Database mounted.
Database opened.


This procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> 
SQL> begin
  2  DBMS_PDB.DESCRIBE(
  3  pdb_descr_file => '/tmp/db12c.xml');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Shutdown the non-CDB database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

$ oracle@oracle12c bin]$ export ORACLE_SID=cdb2


$ oracle@oracle12c bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on SEPT 21 01:15:21 2013

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

Connect to an existing CDB and create a new PDB using the file describing the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.


SQL> create pluggable database pdb7 using '/tmp/db12c.xml'
  2  copy    
  3  file_name_convert = ('/ora/oracle/app/oracle/oradata/pdb7/','/ora/oracle/app/oracle/oradata/cdb2/pdb7/');

Pluggable database created.
System altered.


Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean up the new PDB, removing any items that should not be present in a PDB. You can see an example of the output produced by this script here.

SQL> alter session set container = pdb7;

Session altered.

SQL>@ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

..........

...........
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;

Startup the PDB and check the open mode.

SQL> alter session set container=pdb7;

Session altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ------------------------
PDB7                           READ WRITE

1 row selected.