Search This Blog

Total Pageviews

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

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

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

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


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

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

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

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

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

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

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

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

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

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






SQL> alter pluggable database anuj1 close immediate ;

Pluggable database altered.

SQL> drop  pluggable database anuj1 INCLUDING DATAFILES ;

Pluggable database dropped.


RMAN>
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

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

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

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

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

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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

executing command: SET NEWNAME

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>
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>
  <xmlversion>1</xmlversion>
  <pdbname>ANUJ1</pdbname>
  <cid>5</cid>
  <byteorder>1</byteorder>
  <vsn>203424000</vsn>
  <vsns>
    <vsnnum>12.2.0.1.0</vsnnum>
    <cdbcompt>12.2.0.0.0</cdbcompt>
    <pdbcompt>12.2.0.0.0</pdbcompt>
    <vsnlibnum>0.0.0.0.24</vsnlibnum>
    <vsnsql>24</vsnsql>
    <vsnbsv>8.0.0.0.0</vsnbsv>
  </vsns>
  <dbid>3927710339</dbid>
  <ncdb2pdb>0</ncdb2pdb>
  <cdbid>1329327402</cdbid>
  <guid>557155DB36F60EC6E055000000000001</guid>
  <uscnbas>1710141</uscnbas>
  <uscnwrp>0</uscnwrp>
  <undoscn>225</undoscn>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <isnft>0</isnft>
    <encts>0</encts>
    <flags>0</flags>
    <bmunitsize>8</bmunitsize>
    <file>
      <path>/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf</path>
      <afn>24</afn>
      <rfn>1</rfn>
      <createscnbas>1701092</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>32000</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>203423744</vsn>
      <fdbid>3927710339</fdbid>
      <fcpsb>1710133</fcpsb>
      <fcpsw>0</fcpsw>
      <frlsb>1707600</frlsb>
      <frlsw>0</frlsw>
      <frlt>950622206</frlt>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>1280</incsize>
      <plugscn>0</plugscn>
      <plugafn>0</plugafn>
      <plugdbid>0</plugdbid>




-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

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

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$
-bash-4.2$
-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

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


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.


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/new');



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/new');

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



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........................................:



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=6
;



Oracle DBA

anuj blog Archive