Clone Pluggable Database to Remote Container Database

In this demo I show how to create a pluggable database to a remote container.

We want to quickly clone the the existing TSTDB pluggable database to our QA server to QAT1DB so the QA team can begin to QA the new code.

In a non-CDB database this would take several commands and steps to complete an RMAN clone. I will show how in a multitenant environment how easy it is to clone an existing pluggable database to a remote container.

Environment

Source Container- TESTDBS

Source Pluggable- TSTDB

Destination Container- QATDBS

Destination Pluggable- QAT1DB

Note: When we are going to clone a pluggable database it requires the source pluggable database to be in read-only mode. I hope Oracle will correct this in version 12.1.0.2. In a later post I will test using a physical standby to achieve not needing to close the source pluggable database.

Steps

I will use a common user I created on one of my previous post you can read here.

1. First thing we need to do is grant the "Create Pluggable Database" privilege to my common role in the source CDB. 

$sqlplus / as sysdba

SQL>grant create pluggable database to C##MIDLEVELDBA container=all;

2. Here you can see that my common user has the common role granted.

SQL>select grantee,granted_role,default_role,common
from dba_role_privs
where grantee='C##ORADBADMIN';

3. Connect to the destination CDB.

$sqlplus / as sysdba

SQL>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

You can see below I have no current PDB other then the seed.

4. Connected to the destination CDB create the dblink pointing to the source PDB in the source CDB with the common user. 

SQL>create database link tstdb
connect to c##oradbadmin identified by temp1234
using 'alpddbs002:1521/tstdb';

5. Test the dblink.

SQL>select instance_name,host_name from v$instance@tstdb;

SQL>select con_id,name,open_mode,open_time from c$pdbs@tstdb; 

6. Connect to source CDB verify open mode for the TSTDB PDB we will be cloning.

$sqlplus / as sysdba

SQL>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

7. Close the TSTDB PDB.

SQL>alter pluggable databse tstd close immediate; 

8. Open the TSTDB PDB for read only.

SQL>alter pluggable database tstdb open read only; 

9.  Check the TSTDB PDB status after the open mode change.

SQL>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

We can see that the open mode is now read only.

10. Issue the create pluggable database from the destination CDB using the dblink.

SQL>create pluggable database qat1pdb from tstdb@tstdb;

I get the following error message

Review the alert log on the source CDB and I see the following error. 

Trace file on the source system does not show much. 

I issue the following but still same error which in my case I did not need to pass the file_name_convert since I am using ASM and the diskgroup are name the same in both servers.

create pluggable database qat1pdb from tstdb@tstdb
file_name_convert = ('+DG01','+DG01');

I test using a TNS file and recreate the dblink but same error. I also tested create the dblink using the source CDB but the same issue.

Quick scan of Oracle support does not show and articles or any bugs reported.

Quick scan of the internet using Google I find some one has already created a blog post on the same issue which you can see here. The user states the same issue as I am seeing and has reported the issue to Oracle support. The user states a bug has been created but as of this writing there is no fix yet.

More to come................................

Follow me on Twitter

Clone Pluggable Database to Local Container Database

In this demo I show how to create a pluggable database from an existing pluggable to a local container.

We want to quickly clone the the existing TSTDB pluggable database to DEV4DB so the development team can review a possible bug in there application code.

In a non-CDB database this would take several commands and steps to complete an RMAN clone. I will show how in a multitenant environment how easy it is to clone an existing pluggable database.

Environment

Container- TESTDBS

Pluggable- TSTDB

New Pluggable- DEV4DB

Note: When we are going to clone a pluggable database it requires the source pluggable database to be in read-only mode. I hope Oracle will correct this in version 12.1.0.2. In a later post I will test using a physical standby to achieve not needing to close the source pluggable database.

Steps

1. Connect to root container

$sqlplus / as sysdba

2. Show current pluggable databases.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

3. When cloning a pluggable database the source pluggable database must be in read-only mode.
SQL>alter pluggable database tstdb close immediate;

4. Now open the source pluggable database in read-only.
SQL>alter pluggable database tstdb open read only;

5. Execute the create pluggable database command to clone the tstdb to dev4db.
SQL> create pluggable database dev4db
from tstdb
file_name_convert = ('+DG01','+DG01')
storage (maxsize 6G max_shared_temp_size 1000M);

Observation of the alert log

We can see that the DEV4DB pluggable database is create with pdb id 7. One thing is it says datafile # 7-9 are being deleted from file$. I do not show that the files were deleted so seems the wording in the alert log is misleading. The buffer cache is flushed only for the new pluggable database and then it marks the pluggable database new. The pluggable database is mark new until it is successfully open for the first time.

6. Show status for all pluggable databases after clone is complete.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

We can see that our new DEV4DB has been created and is in a mounted state.

7. We need to open the new pluggable database DEV4DB for read write.
SQL>alter pluggable database DEV4DB open read write;

Observation of the alert log

We see that when the new pluggable database is open it does a check of the dictionary. There is also a warning message to review the view PDB_PLUG_IN_VIOLATIONS for more details.

Then the pluggable database DEV4DB opens successful.

8. Show status for all pluggable databases after new pluggable is open.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

We can see that our new DEV4DB has been created and is in a open read write state. We can also see that the source pluggable database is still in read only mode.

Review of the  PDB_PLUG_IN_VIOLATIONS shows warning message for different database components.
SQL>select name,cause,type,message,action from pdb_plug_in_violations
where name='DEV4DB';

9.  Review new datafiles for pluggable database DEV4DB.
SQL>select b.con_id,b.name,a.name as db_file_name from v$datafile a, v$pdbs b
where a.con_id= b.con_id;

We can see three new datafiles for DEV4DB.

10. We need to close the source database and new open for read write.
SQL>alter pluggable database tstdb close immediate;

11. We need top open the source database for read write.
SQL>alter pluggable database tstdb open read write;

12.  Show status for all pluggable databases.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

We can see that the source TSTDB is now open for read write.

13. The create pluggable command also create an active service for the pluggable database DEV4DB. We the two SQL statements below we can see that the size of the DEV4DB pluggable database matches to the TSTDB. We also see that the DEV4DB pluggable database as an active service called dev4db.

SQL>select con_id,name,open_mode,open_time,ceil(total_size)/1024/1024 total_size_in_mb from v$pdbs
order by con_id asc;

SQL>select con_id,con_name,name,network_name from v$active_services
order by con_id asc;

Check out my next blog post that will show how to create a pluggable database to a remote container from existing pluggable database.

Follow me on Twitter

Drop Pluggable Database

In this demo I show how to drop a pluggable database using DBCA or command line.

Environment

CDB- TESTDBS
PDB- TSTDB1 and TSTDB3

Drop Pluggable Database using Command Line

We will drop pluggable database TSTDB1 using command line.

1. Connect to container root and check current pluggable databases.
$sqlplus / as sysdba
SQL> show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;

2. Using RMAN do a report schema to see datafile location. You of course can also do this by selecting from the dictionary tables v$datafile and v$tempfile.
$rman target /
RMAN>report schema

4. Close the pluggable database you want to drop.
SQL>alter pluggable database tstdb1 close immediate;

5. Connected to the container drop the pluggable database.
$sqlplus / as sysdba
SQL>drop pluggable database tstdb1 including datafiles;

Since the option "including datafiles" was used we can see the the drop pluggable database command automatically removed all datafiles and tempfiles.

NOTE: It is not recommended to drop a pluggable database with out including the datafiles since it will require manually cleaning the datafiles using OS utilities which could lead to dropping the wrong datafiles.

You may have a requirement to move the pluggable database so that could be a reason to issue the drop pluggable database with out including the "including datafile" option.

Drop Pluggable Database using DBCA

We will drop pluggable database TSTDB3 using DBCA.

1. Connect to container root and check current pluggable databases.
$sqlplus / as sysdba
SQL> show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;

2. Execute DBCA
$ORACLE_HOME/bin/dbca

Select Manager Pluggable Databases click Next.

3. Select Delete a Pluggable Database click Next.

4. Select the container database from where you will drop the pluggable database and click Next.

5. Select the pluggable database you want to drop in the case I will select TSTDB3 and click Next.

6. Review summary and click Finish.

7. The drop process will begin you can click the activity or alert log to review details of the drop process.

8. Once the pluggable database has been dropped click OK to close the DBCA session.

Follow me on Twitter

Create Pluggable Database

In this demo I show how to use DBCA or command line to create new pluggable database into an existing container database.

Environment

CDB- TESTDBS
PDB- DEV3DB and DEV4DB

Create Pluggable Database Command Line

1. Connect to container root and check current pluggable databases.
$sqlplus / as sysdba
SQL> show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;

2. Create new Pluggable database
SQL>CREATE PLUGGABLE DATABASE dev4db
ADMIN USER pdbadm IDENTIFIED BY temp1234
ROLES = (connect)
DEFAULT TABLESPACE users
DATAFILE '+DG01' SIZE 250M AUTOEXTEND ON
STORAGE (MAXSIZE 5G)
PATH_PREFIX = '+DG01';

3.Check newly created pluggable database.
SQL>show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;

4.Open new pluggable database
SQL>alter pluggable database dev4db open read write;

5. Check pluggable database open mode.
SQL>show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;

6. Using RMAN do a report schema to see datafile location. You of course can also do this by selecting from the dictionary tables v$datafile and v$tempfile.
$rman target /
RMAN>report schema

Here is a select statement that can be used to see the datafiles if you don't want to use RMAN report schema.

SQL>set linesize 500
SQL>set pagesize 100
SQL>col name format a20
SQL>col db_file_name format a80
SQL>select b.con_id,b.name,a.name as db_file_name from v$datafile a, v$pdbs b
where a.con_id= b.con_id;

Create Pluggable Database DBCA

I will show how you can also create pluggable database using DBCA.

1. Execute DBCA
$ORACLE_HOME/bin/dbca

2. Select Manage Pluggable Databases click Next.

3. Select Create a Pluggable Database click Next.

4. Select your container database and click Next.

5. Select Create a New Pluggable Database click Next.

6. Set pluggable database name, specify storage type, database location, default tablespace and administrator account information. This is the similar information  I set during the pluggable database command line step. In my case I will ignore the warning message about the password. Click Next

7. Review the summary and click Finish.

8. The pluggable database create will begin. You can use the activity and alert log button to monitor the details during the creation process.

9. Once the pluggable database has been created click the OK button to close out the DBCA session.

10. Check the newly created pluggable database. You can see that when creating a pluggable database from DBCA the pluggable database is automatically in open read write mode.
$sqlplus / as sysdba
SQL>show con_id
SQL>select con_id,name,open_mode,open_time from v$pdbs;

11. Using RMAN do a report schema to see datafile location. You of course can also do this by selecting from the dictionary tables v$datafile and v$tempfile.
$rman target /
RMAN>report schema

Here is a select statement that can be used to see the datafiles if you don't want to use RMAN report schema.

SQL>set linesize 500
SQL>set pagesize 100
SQL>col name format a20
SQL>col db_file_name format a80
SQL>select b.con_id,b.name,a.name as db_file_name from v$datafile a, v$pdbs b
where a.con_id= b.con_id;

Create Pluggable Database Using Enterprise Manager Cloud Control 12c

If you would like to see how to create more then one pluggable database in one provision operation see my blog post here.

Follow me on Twitter