Cloning a Pluggable Database within same Container

Himanshu , , No comments
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 :

Post a Comment

Changing the ORACLE BASE Location

Himanshu , , 4 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" ?>
<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<ORACLEHOME_INFO>
   <GUID>252246286.7539233482</GUID>
   <HOME/>
   <ARU_PLATFORM_INFO>
      <ARU_ID>123</ARU_ID>
      <ARU_ID_DESCRIPTION>Linux</ARU_ID_DESCRIPTION>
   </ARU_PLATFORM_INFO>
   <PROPERTY_LIST>
      <PROPERTY NAME="ARCHITECTURE" VAL="64"/>
      <PROPERTY NAME="ORACLE_BASE" VAL="/oracle/apps"/>
   </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"?>
<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<ORACLEHOME_INFO>
    <GUID>252246286.7539233482</GUID>
    <HOME/>
    <ARU_PLATFORM_INFO>
        <ARU_ID>123</ARU_ID>
        <ARU_ID_DESCRIPTION>Linux</ARU_ID_DESCRIPTION>
    </ARU_PLATFORM_INFO>
    <PROPERTY_LIST>
        <PROPERTY NAME="ARCHITECTURE" VAL="64"/>
        <PROPERTY NAME="ORACLE_BASE" VAL="/u01/TEST"/>
    </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

4 comments :

Post a Comment

Subscribe to: Posts ( Atom )

Consult US

Name

Email *

Message *

Follow by Email