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
Changing the ORACLE BASE Location
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
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
Subscribe to:
Posts
(
Atom
)
No comments :
Post a Comment