Oracle DBA Tips and Techniques

By Arjun Raja

DROP AND RECREATE PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

DROP AND RECREATE PLUGGABLE DATABASE

I created a pluggable database using this syntax.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1′);

Forgot to put a slash after pdb1d1 – hence the datafiles were created in the same directory as container db.

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1system01.dbf
/u02/oradata/cdbd1/pdb1d1sysaux01.dbf

To rectify this :

First drop the pluggable database and remove all datafiles .

Connect to CBD

sqlplus / as sysdba

SQL> drop pluggable database pdb1d1 including datafiles;
drop pluggable database pdb1d1 including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB1D1 is not closed on all instances.

Therefore close the pluggable database first –

SQL> alter pluggable database pdb1d1 close;

Pluggable database altered.

SQL>drop pluggable database pdb1d1 including datafiles;

Pluggable database dropped.

SQL>

Now try logging in to the pluggable database.

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 09:51:23 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
Create the pluggable database again.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1/’);

sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> select file_name from dba_Data_files;
select file_name from dba_Data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

OPEN THE PDB

Login as sysdba into the CDB.

SQL> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

Pluggable database altered.

SQL>

Now login to the PDB

SQL> select file_name from dba_Data_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1/system01.dbf
/u02/oradata/cdbd1/pdb1d1/sysaux01.dbf

SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1/temp01.dbf

SQL>

The alert.log file as a CDB shows new DDL statements such as:

• CREATE PLUGGABLE DATABASE
• ALTER PLUGGABLE DATABASE
• DROP PLUGGABLE DATABASE

This alert log

drop pluggable database pdb1d1 including datafiles
ORA-65025 signalled during: drop pluggable database pdb1d1 including datafiles…
alter pluggable database pdb1d1 close
Fri May 09 09:50:08 2014
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1D1 closed
Completed: alter pluggable database pdb1d1 close
drop pluggable database pdb1d1 including datafiles
Fri May 09 09:50:43 2014
Deleted file /u02/oradata/cdbd1/pdb1d1temp01.dbf
Deleted file /u02/oradata/cdbd1/pdb1d1sysaux01.dbf
Deleted file /u02/oradata/cdbd1/pdb1d1system01.dbf
Completed: drop pluggable database pdb1d1 including datafiles
Fri May 09 09:54:54 2014
CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1_admin IDENTIFIED BY *FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1/’)
Fri May 09 09:55:07 2014
****************************************************************
Pluggable Database PDB1D1 with pdb id – 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$
Deleting old file#4 from file$
Adding new file#10 to file$(old file#2)
Adding new file#11 to file$(old file#4)
Successfully created internal service pdb1d1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB1D1 with pdb id – 3 is now marked as NEW.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: