January 2017

Cloning a Pluggable Database within same Container

Cloning a Pluggable database

The container database can have number for PDB’s so we can either create new PDB’s or clone an existing PDB.

Now let’s try to clone our Pluggable database into a new pluggable database.

We had few custom objects in our Pluggable database PDB121. So we will validate if after cloning we are able to get the same objects in cloned pluggable database.

Source Pluggable database details:

Pluggable Database Name: PDB121

Schema Name: Himanshu

Object name: Test_reccovery2

Global user: C##Himanshu

Let’s give the new pluggable database name as : PDBCLONE

Note:

******************************************************************************

The disadvantage in cloning a current pluggable database is that it must be in Read Only Mode.

******************************************************************************

Steps for performing cloning:

1. Connect to our container database and check the current PDBs.

SQL> select name from v$database;

NAME

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

CDB122

SQL> select con_id,name,open_mode,restricted from v$pdbs;

CON_ID NAME OPEN_MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB122 READ WRITE NO

4 PDB121 READ WRITE NO

2. Bring PDB121 in read only mode.

SQL> alter pluggable database PDB121 close;

Pluggable database altered.

SQL> select CON_ID, NAME, OPEN_MODE,restricted from V$PDBS;

CON_ID NAME OPEN_MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB122 READ WRITE NO

4 PDB121 MOUNTED

SQL> alter pluggable database PDB121 open read only;

Pluggable database altered.

SQL> select CON_ID, NAME, OPEN_MODE,restricted from V$PDBS;

CON_ID NAME OPEN_MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB122 READ WRITE NO

4 PDB121 READ ONLY NO

3. Create new pluggable database using PDB121.

20:11:19 SQL> create pluggable database PDBCLONE from PDB121 FILE_NAME_CONVERT= ('/ub817/oradataCDB122/CDB122/PDB121', '/ub817/oradata/ CDB122/CDB122/PDBCLONE');

Pluggable database created.

Elapsed: 00:00:27.36

4. Verify new pluggable database. It should be in mounted state and we need to open it.

20:11:58 SQL> select CON_ID, NAME, OPEN_MODE, restricted from V$PDBS;

CON_ID NAME OPEN_MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB122 READ WRITE NO

4 PDB121 READ ONLY NO

5 PDBCLONE MOUNTED

20:12:39 SQL> alter pluggable database PDBCLONE OPEN;

Pluggable database altered.

Elapsed: 00:00:20.04

20:14:24 SQL> select CON_ID, NAME, OPEN_MODE, restricted from V$PDBS;

CON_ID NAME OPEN_MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB122 READ WRITE NO

4 PDB121 READ ONLY NO

5 PDBCLONE READ WRITE NO

Elapsed: 00:00:00.05

5. Our clone pluggable database is ready. Lets check for the custom objects.

20:17:13 SQL> select username,CREATED from dba_users where upper(username) like '%HIMANSHU%';

USERNAME CREATED

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

HIMANSHU 14-SEP-16

c##Himanshu 19-SEP-16

Elapsed: 00:00:00.18

20:17:25 SQL> select count(*) from himanshu.test_reccovery2;

COUNT(*)

----------

91843

Elapsed: 00:00:01.94

Wooow that was easy, Looks good.

But when cloning using this method it actually copies the files with same name as in source.

So we should move/rename as a good practice and of course it’s simple in 12c.

CON_NAME

-----------

PDBCLONE

20:22:06 SQL> select file_name from dba_data_files;

FILE_NAME

-----------------------------------------------------------------------------/ub817/oradata/CDB122/CDB122/PDBCLONE/system01.dbf

/ub817/oradata/CDB122/CDB122/PDBCLONE/sysaux01.dbf

/ub817/oradata/CDB122/CDB122/PDBCLONE/PDB121_users01.dbf

Elapsed: 00:00:00.31

Rename Datafile:

20:22:08 SQL> alter database move datafile '/ub817/oradata/CDB122/CDB122/PDBCLONE/PDB121_users01.dbf' to '/ub817/oradata/CDB122/CDB122/PDBCLONE/PDBCLONE_users01.dbf';

Database altered.

Elapsed: 00:00:13.08

20:24:11 SQL> select file_name from dba_data_files;

FILE_NAME

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

/ub817/oradata/CDB122/CDB122/PDBCLONE/system01.dbf

/ub817/oradata/CDB122/CDB122/PDBCLONE/sysaux01.dbf

/ub817/oradata/CDB122/CDB122/PDBCLONE/PDBCLONE_users01.dbf

No comments :

Changing the ORACLE BASE Location

Himanshu funoracleapps , Oracle Database , ORACLE_BASE 6 comments

Recently I faced an issue in my new environment where ORACLE_BASE was pointing to wrong location in a particular environment.

To correct this we have two options:

1. Either change the environment variable ORACLE_BASE on server to the new location.

export ORACLE_BASE=/newlocation

2. We can also change the ORACLE_BASE variable using the ‘orabase’ utility. It can be done in 11g and above version.

Steps to change using orabase

a) Check the current value of the ORACLE_BASE using below command:

$ORACLE_HOME/bin/orabase

/oracle/apps

The value can be verified from oraclehomeproperties.xml file. File is located at $ORACLE_HOME/inventory/ContentsXML/

oracle@test :/oracle/app/TEST/product/11.2.0.3/inventory/ContentsXML]

$ cat oraclehomeproperties.xml

<?xml version="1.0" standalone="yes" ?>

<ORACLEHOME_INFO>

<HOME/>

<ARU_PLATFORM_INFO>

<ARU_ID>123</ARU_ID>

<ARU_ID_DESCRIPTION>Linux</ARU_ID_DESCRIPTION>

</ARU_PLATFORM_INFO>

<PROPERTY_LIST>

</PROPERTY_LIST>

</ORACLEHOME_INFO>

b) Backup the oraclehomeproperties.xml

oracle@test :/oracle/app/TEST/product/11.2.0.3/inventory/ContentsXML]

$ cp -ip oraclehomeproperties.xml oraclehomeproperties.xml_him

c) Change ORACLE_BASE to new location using orabase utility

oracle@test :/oracle/app/TEST/product/11.2.0.3/inventory/ContentsXML]

$ $ORACLE_HOME/bin/orabase -s /u01/TEST

Once the command is executed successfully oraclehomeproperties.xml value is updated.

We can verify the new value in oraclehomeproperties.xml file as well.

oracle@test :/oracle/app/TEST/product/11.2.0.3/inventory/ContentsXML]

$ cat oraclehomeproperties.xml

<?xml version="1.0" encoding="UTF-8"?>

<ORACLEHOME_INFO>

<HOME/>

<ARU_PLATFORM_INFO>

<ARU_ID>123</ARU_ID>

<ARU_ID_DESCRIPTION>Linux</ARU_ID_DESCRIPTION>

</ARU_PLATFORM_INFO>

<PROPERTY_LIST>

</PROPERTY_LIST>

</ORACLEHOME_INFO>

This is only applicable for 11g version. For version 12.1 and later it will not work.

For any incorrect path of Oracle Base , Please also check below file:

/oracle/app/product/12.1.0.2/install/orabasetab