Sunday, 30 July 2017

Restore and Recovery of Pluggable Database (PDB) after dropping Pluggable Database (PDB)

Restore and Recovery of Pluggable Database (PDB) after dropping Pluggable Database (PDB) ...

SQL> startup ;

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 8801008 bytes

Variable Size 624952592 bytes

Database Buffers 436207616 bytes

Redo Buffers 3780608 bytes

Database mounted.

Database opened.

--- Create new pluggable database

create pluggable database anuj1 admin user anuj identified by vihaan123;

set linesize 200

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.create_scn,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status

from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

PDB_ID CREATE_SCN NAME OPEN_MODE RESTRICTED STATUS

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

2 1409189 PDB$SEED READ ONLY NO NORMAL

3 1524245 ANUJ MOUNTED n/a NORMAL

4 1674153 ANUJT MOUNTED n/a NORMAL

5 1701092 ANUJ1 MOUNTED n/a NEW

SQL> alter pluggable database anuj1 open ;

Pluggable database altered.

PDB_ID CREATE_SCN NAME OPEN_MODE RESTRICTED STATUS

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

2 1409189 PDB$SEED READ ONLY NO NORMAL

3 1524245 ANUJ MOUNTED n/a NORMAL

4 1674153 ANUJT MOUNTED n/a NORMAL

5 1701092 ANUJ1 READ WRITE NO NORMAL

set head off verify off echo off pages 1500 linesize 120 feedback off

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select

'PDB_ID..................................................: '||PDB_ID ,

'PDB_NAME................................................: '||PDB_NAME ,

'DBID....................................................: '||DBID ,

'CON_UID.................................................: '||CON_UID,

'GUID....................................................: '||GUID ,

'STATUS..................................................: '||STATUS,

'CREATION_SCN............................................: '||CREATION_SCN,

'VSN.....................................................: '||VSN,

'LOGGING ................................................: '||LOGGING ,

'FORCE_LOGGING ..........................................: '||FORCE_LOGGING ,

'FORCE_NOLOGGING ........................................: '||FORCE_NOLOGGING,

'APPLICATION_ROOT........................................: '||APPLICATION_ROOT,

'APPLICATION_PDB.........................................: '||APPLICATION_PDB,

'APPLICATION_SEED........................................: '||APPLICATION_SEED,

'APPLICATION_ROOT_CON_ID.................................: '||APPLICATION_ROOT_CON_ID,

'IS_PROXY_PDB............................................: '||IS_PROXY_PDB,

'CON_ID..................................................: '||CON_ID,

'UPGRADE_PRIORITY........................................: '||UPGRADE_PRIORITY,

'APPLICATION_CLONE.......................................: '||APPLICATION_CLONE,

'FOREIGN_CDB_DBID........................................: '||FOREIGN_CDB_DBID,

'UNPLUG_SCN..............................................: '||UNPLUG_SCN,

'FOREIGN_PDB_ID..........................................: '||FOREIGN_PDB_ID,

'CREATION_TIME...........................................: '||CREATION_TIME,

'REFRESH_MODE............................................: '||REFRESH_MODE,

'REFRESH_INTERVAL........................................: '||REFRESH_INTERVAL

from CDB_PDBS

where 1=1

and PDB_ID=5

;

PDB_ID..................................................: 5

PDB_NAME................................................: ANUJ1

DBID....................................................: 3927710339

CON_UID.................................................: 3927710339

GUID....................................................: 557155DB36F60EC6E055000000000001

STATUS..................................................: NORMAL

CREATION_SCN............................................: 1701092

VSN.....................................................: 203424000

LOGGING ................................................: LOGGING

FORCE_LOGGING ..........................................: NO

FORCE_NOLOGGING ........................................: NO

APPLICATION_ROOT........................................: NO

APPLICATION_PDB.........................................: NO

APPLICATION_SEED........................................: NO

APPLICATION_ROOT_CON_ID.................................:

IS_PROXY_PDB............................................: NO

CON_ID..................................................: 5

UPGRADE_PRIORITY........................................:

APPLICATION_CLONE.......................................: NO

FOREIGN_CDB_DBID........................................: 1477822556

UNPLUG_SCN..............................................: 1699940

FOREIGN_PDB_ID..........................................: 2

CREATION_TIME...........................................: 29-07-2017 09:10:48

REFRESH_MODE............................................: NONE

REFRESH_INTERVAL........................................:

Creating a test table !!!

sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

connect system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

-bash-4.2$ sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:18:22 2017

Last Successful login time: Thu Jul 27 2017 20:12:59 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> connect system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

Connected.

-bash-4.2$ sqlplus anuj/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:20:03 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user

USER is "ANUJ"

SQL> def

DEFINE _DATE = "29-JUL-17" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "anuj1" (CHAR)

DEFINE _USER = "ANUJ" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)

DEFINE _EDITOR = "vi" (CHAR)

DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

DEFINE _O_RELEASE = "1202000100" (CHAR)

-bash-4.2$ sqlplus anuj/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:28:05 2017

Last Successful login time: Sat Jul 29 2017 09:20:03 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table anuj( tdate date);

Table created.

SQL> insert into anuj as select sysdate from dual;

insert into anuj as select sysdate from dual

ERROR at line 1:

ORA-00926: missing VALUES keyword

SQL> insert into anuj select sysdate from dual ;

insert into anuj select sysdate from dual

ERROR at line 1:

ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:36:23 2017

Last Successful login time: Sat Jul 29 2017 09:27:17 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> GRANT UNLIMITED TABLESPACE TO anuj;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ sqlplus anuj/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:37:08 2017

Last Successful login time: Sat Jul 29 2017 09:28:05 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> insert into anuj select sysdate from dual ;

1 row created.

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select * from anuj;

TDATE

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

29-07-2017 09:37:13

SQL>

-bash-4.2$ sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:56:41 2017

Last Successful login time: Sat Jul 29 2017 09:36:24 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set linesize 200 pagesize 200

col FILE_NAME for a70

SQL> SQL> col PDB_NAME for a20

select a.CON_ID,PDB_NAME,b.status pdbstatus,TABLESPACE_NAME,FILE_NAME ,trunc(BYTES/1024/1024/1024,2) Gb, trunc(MAXBYTES/1024/1024/1024,2) Max from cdb_data_files a,dba_pdbs b

where b.PDB_ID=a.CON_ID

SQL> 2 3 order by 1;

CON_ID PDB_NAME PDBSTATUS TABLESPACE_NAME FILE_NAME GB MAX

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

5 ANUJ1 NORMAL UNDOTBS1 /u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/dat .09 31.99

afile/o1_mf_undotbs1_dqrjrhhw_.dbf

5 ANUJ1 NORMAL SYSAUX /u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/dat .27 31.99

afile/o1_mf_sysaux_dqrjrhhv_.dbf

5 ANUJ1 NORMAL SYSTEM /u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/dat .24 31.99

afile/o1_mf_system_dqrjrhhl_.dbf

=========================================

Taking a full backup before dropping ( This is a must )

RMAN> RUN

2> {

3> configure controlfile autobackup on;

4> set command id to 'ORCLBackupFull';

5> ALLOCATE CHANNEL c1 DEVICE TYPE disk;

6> backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/app/o

7> sql 'alter system archive log current';

8> backup tag ORCL_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIV

backup tag ORCL_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%

9> 10> release channel c1;

11> }

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

allocated channel: c1

channel c1: SID=60 device type=DISK

Starting backup at 29-JUL-17

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_114_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:11:11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_sysaux_dq898gpw_.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_system_dq898gom_.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_undotbs1_dq898gpx_.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_115_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:03:42

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00022 name=/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujto1_mf_sysaux_dq898gpw_.dbf

input datafile file number=00021 name=/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujto1_mf_system_dq898gom_.dbf

input datafile file number=00023 name=/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujto1_mf_undotbs1_dq898gpx_.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_116_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:07:04

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf

channel c1: starting piece 1 at 29-JUL-17

Message from syslogd@localhost at Jul 29 10:24:23 ...

kernel:BUG: soft lockup - CPU#0 stuck for 21s! [kswapd0:25]

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_117_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:04:43

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00025 name=/u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrjrhhv_.dbf

input datafile file number=00024 name=/u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrjrhhl_.dbf

input datafile file number=00026 name=/u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrjrhhw_.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_118_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:04:54

Finished backup at 29-JUL-17

Starting Control File and SPFILE Autobackup at 29-JUL-17

piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1477822556-20170729-01 comment=NONE

Finished Control File and SPFILE Autobackup at 29-JUL-17

sql statement: alter system archive log current

Starting backup at 29-JUL-17

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=5 STAMP=950474250

input archived log thread=1 sequence=6 RECID=4 STAMP=950474214

input archived log thread=1 sequence=7 RECID=3 STAMP=950474204

input archived log thread=1 sequence=8 RECID=6 STAMP=950610821

input archived log thread=1 sequence=9 RECID=7 STAMP=950610823

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_120_1_ARCHIVE tag=ORCL_ARCHIVE comment=NONE

channel c1: backup set complete, elapsed time: 00:02:31

channel c1: deleting archived log(s)

archived log file name=/u01/app/oracle/Archive/1_5_950041842.dbf RECID=5 STAMP=950474250

archived log file name=/u01/app/oracle/Archive/1_6_950041842.dbf RECID=4 STAMP=950474214

archived log file name=/u01/app/oracle/Archive/1_7_950041842.dbf RECID=3 STAMP=950474204

archived log file name=/u01/app/oracle/Archive/1_8_950041842.dbf RECID=6 STAMP=950610821

archived log file name=/u01/app/oracle/Archive/1_9_950041842.dbf RECID=7 STAMP=950610823

Finished backup at 29-JUL-17

Starting backup at 29-JUL-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_121_1_CONTROL tag=ORCL_CONTROL comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 29-JUL-17

Starting Control File and SPFILE Autobackup at 29-JUL-17

piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1477822556-20170729-02 comment=NONE

Finished Control File and SPFILE Autobackup at 29-JUL-17

released channel: c1

========================================================================

-bash-4.2$

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 10:40:11 2017

SQL> alter pluggable database anuj1 close immediate ;

Pluggable database altered.

SQL> drop pluggable database anuj1 INCLUDING DATAFILES ;

Pluggable database dropped.

RMAN>

RMAN> restore pluggable database anuj1 ;

Starting restore at 29-JUL-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/29/2017 10:45:51

RMAN-06813: could not translate pluggable database ANUJ1

-bash-4.2$ export ORACLE_SID=dbcdbaux

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 10:50:28 2017

Connected to an idle instance.

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Duplicate database to 'dbcdbaux' noopen backup location '/u01/app/oracle/RmanBackup/'; DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

RMAN> exit

Recovery Manager complete.

-bash-4.2$ export ORACLE_SID=dbcdbaux

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 10:50:28 2017

Connected to an idle instance.

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ rman auxiliary sys/vihaanssss1!

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 29 10:53:41 2017

connected to auxiliary database: DBCDBAUX (not mounted)

RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

Starting Duplicate Db at 29-JUL-17

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''DBCDBAUX'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from '/u01/app/oracle/RmanBackup/ORCL_20170729_121_1_CONTROL';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DBCDBAUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

Starting restore at 29-JUL-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=35 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08

output file name=/u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

Finished restore at 29-JUL-17

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''DBCDBAUX'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

}

executing Memory Script

sql statement: alter system set db_name = ''DBCDBAUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/29/2017 10:57:45

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 12.1.0.2.0

ORA-00202: control file: '/u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL'

RMAN> exit

Recovery Manager complete.

-bash-4.2$ ls -ltr /u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

-rw-r----- 1 oracle oinstall 18726912 Jul 29 10:56 /u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

SQL> startup nomount pfile=initdbcdbaux.ora ;

ORACLE instance started.

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ rman auxiliary sys/vihaanssss1!

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 29 11:12:21 2017

connected to auxiliary database: DBCDBAUX (not mounted)

RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

Starting Duplicate Db at 29-JUL-17

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''DBCDBAUX'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from '/u01/app/oracle/RmanBackup/ORCL_20170729_121_1_CONTROL';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DBCDBAUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

Starting restore at 29-JUL-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

Finished restore at 29-JUL-17

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

Skipping pluggable database ANUJ

Skipping pluggable database ANUJT

Automatically adding tablespace SYSTEM

Automatically adding tablespace SYSAUX

Automatically adding tablespace PDB$SEED:SYSTEM

Automatically adding tablespace PDB$SEED:SYSAUX

Automatically adding tablespace UNDOTBS1

Skipping tablespace USERS

Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects

contents of Memory Script:

{

set until scn 1707599;

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 6 to new;

set newname for clone datafile 8 to new;

set newname for clone datafile 24 to new;

set newname for clone datafile 25 to new;

set newname for clone datafile 26 to new;

restore

clone database

skip forever tablespace "USERS",

"ANUJ":"UNDOTBS1",

"ANUJ":"SYSTEM",

"ANUJ":"SYSAUX",

"ANUJT":"UNDOTBS1",

"ANUJT":"SYSTEM",

"ANUJT":"SYSAUX" ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 29-JUL-17

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170729_114_1_FULL

Message from syslogd@localhost at Jul 29 11:20:02 ...

kernel:BUG: soft lockup - CPU#0 stuck for 25s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:21:17 ...

kernel:BUG: soft lockup - CPU#0 stuck for 27s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:21:18 ...

kernel:BUG: soft lockup - CPU#0 stuck for 26s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:21:19 ...

kernel:BUG: soft lockup - CPU#0 stuck for 22s! [gnome-shell:2743]

Message from syslogd@localhost at Jul 29 11:36:00 ...

kernel:BUG: soft lockup - CPU#0 stuck for 24s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:37:21 ...

kernel:BUG: soft lockup - CPU#0 stuck for 22s! [oracle:24765]

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_11

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:46:38

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/dbcd

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/dbcd

channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/dbcd

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORC

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_117_1_FULL tag=ORCL_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:17:42

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00025 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170729_118_1_FULL

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_118_1_FULL tag=ORCL_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:16

Finished restore at 29-JUL-17

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=950617525 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_dqrr1838_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=11 STAMP=950617525 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=950617526 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=13 STAMP=950617526 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_system_dqrtv31c_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=14 STAMP=950617526 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=15 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf

datafile 24 switched to datafile copy

input datafile copy RECID=16 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf

datafile 25 switched to datafile copy

input datafile copy RECID=17 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf

datafile 26 switched to datafile copy

input datafile copy RECID=18 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf

contents of Memory Script:

{

set until scn 1707599;

recover

clone database

skip forever tablespace "USERS",

"ANUJ":"UNDOTBS1",

"ANUJ":"SYSTEM",

"ANUJ":"SYSAUX",

"ANUJT":"UNDOTBS1",

"ANUJT":"SYSTEM",

"ANUJT":"SYSAUX" delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 29-JUL-17

using channel ORA_AUX_DISK_1

Executing: alter database datafile 11 offline drop

Executing: alter database datafile 9 offline drop

Executing: alter database datafile 10 offline drop

Executing: alter database datafile 23 offline drop

Executing: alter database datafile 21 offline drop

Executing: alter database datafile 22 offline drop

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=8

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=9

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170729_120_1_ARCHIVE

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_120_1_ARCHIVE tag=ORCL_ARCHIVE

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_950041842.dbf thread=1 sequence=8

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_950041842.dbf RECID=2 STAMP=950617658

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_950041842.dbf thread=1 sequence=9

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 7 needs more recovery to be consistent

ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/users01.dbf'

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_950041842.dbf RECID=1 STAMP=950617585

media recovery complete, elapsed time: 00:00:52

Finished recover at 29-JUL-17

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''DBCDBAUX'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

}

executing Memory Script

sql statement: alter system set db_name = ''DBCDBAUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBCDBAUX" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 SIZE 200 M ,

GROUP 2 SIZE 200 M ,

GROUP 3 SIZE 200 M

DATAFILE

'/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_dqrr1838_.dbf',

'/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_system_dqrtv31c_.dbf',

'/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf'

CHARACTER SET US7ASCII

contents of Memory Script:

{

set newname for clone tempfile 1 to new;

set newname for clone tempfile 2 to new;

set newname for clone tempfile 5 to new;

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 2 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 5 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf RECID=1 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf RECID=2 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf RECID=3 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf RECID=4 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf RECID=5 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf RECID=6 STAMP=950618295

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=3 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=4 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf

datafile 25 switched to datafile copy

input datafile copy RECID=5 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf

datafile 26 switched to datafile copy

input datafile copy RECID=6 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf

Leaving database unopened, as requested

Cannot remove created server parameter file

Finished Duplicate Db at 29-JUL-17

RMAN>

RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name DBCDBAUX

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

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

1 810 SYSTEM *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_dqrr1838_.dbf

3 470 SYSAUX *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf

4 50 UNDOTBS1 *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf

5 250 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_system_dqrtv31c_.dbf

6 280 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf

8 100 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf

24 250 _###_UNKNOWN_PDB_#_5:SYSTEM *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf

25 290 _###_UNKNOWN_PDB_#_5:SYSAUX *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf

26 100 _###_UNKNOWN_PDB_#_5:UNDOTBS1 *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 20 TEMP 32767 /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_temp_%u_.tmp

2 64 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_temp_%u_.tmp

5 64 _###_UNKNOWN_PDB_#_5:TEMP 32767 /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_temp_%u_.tmp

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 ANUJ MOUNTED

4 ANUJT MOUNTED

5 ANUJ1 MOUNTED

SQL> ALTER PLUGGABLE DATABASE anuj1 OPEN RESETLOGS ;

ALTER PLUGGABLE DATABASE anuj1 OPEN RESETLOGS

ERROR at line 1:

ORA-39862: RESETLOGS option only valid after a Pluggable Database incomplete

recovery

SQL> alter pluggable database anuj1 unplug into '/tmp/anuj1.xml';

Pluggable database altered.

SQL> SQL> def

DEFINE _DATE = "29-JUL-17" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "dbcdbaux" (CHAR)

DEFINE _USER = "SYS" (CHAR)

DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)

DEFINE _EDITOR = "vi" (CHAR)

DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

DEFINE _O_RELEASE = "1202000100" (CHAR)

SQL>

SQL> SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/tmp/anuj1.xml',

pdb_name => 'anuj1');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible ....');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible :( ');

END IF;

END;

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14

15 /

DECLARE

ERROR at line 1:

ORA-65012: Pluggable database ANUJ1 already exists.

ORA-06512: at "SYS.DBMS_PDB", line 24

ORA-06512: at line 4

SQL> SQL> SQL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cat /tmp/anuj1.xml

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

<PDB>

<vsns>

</vsns>

<name>SYSTEM</name>

<file>

<path>/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf</path>

-bash-4.2$ . oraenv

ORACLE_SID = [dbcdbaux] ? orcl

The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.2$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 14:18:11 2017

Connected to an idle instance.

SQL> startup ;

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 8801008 bytes

Variable Size 624952592 bytes

Database Buffers 436207616 bytes

Redo Buffers 3780608 bytes

Database mounted.

Database opened.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL> SQL> SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/tmp/anuj1.xml',

pdb_name => 'anuj1');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible ....');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible :( ');

END IF;

END;

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14

compatible ....

PL/SQL procedure successfully completed.

drwxr-x--- 5 oracle oinstall 94 Jul 29 09:10 ORCL/

drwxr-x--- 2 oracle oinstall 6 Jul 22 18:46 orclpdb/

drwxr-x--- 2 oracle oinstall 150 Jul 22 20:55 pdbseed/

-bash-4.2$ cd orclpdb/

-bash-4.2$ ls -ld */

ls: cannot access */: No such file or directory

-bash-4.2$ ls -ld */

ls: cannot access */: No such file or directory

-bash-4.2$ ls -ltr

total 0

-bash-4.2$ pwd

/u01/app/oracle/oradata/orcl/orclpdb

-bash-4.2$ mkdir anuj1

-bash-4.2$

-bash-4.2$ cd anuj1/

-bash-4.2$ pwd

/u01/app/oracle/oradata/orcl/orclpdb/anuj1

-bash-4.2$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 14:48:53 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE PLUGGABLE DATABASE anuj1 USING '/tmp/anuj1.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/orclpdb/anuj1/');

CREATE PLUGGABLE DATABASE anuj1 USING '/tmp/anuj1.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/orclpdb/anuj1/')

ERROR at line 1:

ORA-01276: Cannot add file

/u01/app/oracle/oradata/orcl/orclpdb/anuj1/o1_mf_system_dqrvw0pb_.dbf. File

has an Oracle Managed Files file name.

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE anuj1 OPEN READ WRITE;

set linesize 200

col NAME for a30

col "RESTRICTED" FORMAT A10

select d.PDB_ID,v.name,v.open_mode,nvl(v.restricted, 'n/a') "RESTRICTED",d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

SQL> SQL> SQL> SQL> 2 3 4

PDB_ID NAME OPEN_MODE RESTRICTED STATUS

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

2 PDB$SEED READ ONLY NO NORMAL

3 ANUJ MOUNTED n/a NORMAL

4 ANUJT MOUNTED n/a NORMAL

6 ANUJ1 READ WRITE NO NORMAL