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
;



Posted by at 1 comment:

Friday, 28 July 2017

Oracle 12c Recover pluggable database ...

Recover pluggable database ... 


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> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANUJ                           MOUNTED

SQL> alter pluggable database anuj open ;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANUJ                           READ WRITE NO
SQL>
col FILE_NAME for a150
set linesize 200
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES
where 1=1
and CON_ID> 2;

    CON_ID TABLESPACE_NAME                FILE_NAME
---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
         3 SYSTEM                         /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_system_dq898gom_.dbf
         3 SYSAUX                         /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_sysaux_dq898gpw_.dbf
         3 UNDOTBS1                       /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_undotbs1_dq898gpx_.dbf


Creating new pluggable database anujt

SQL>
CREATE PLUGGABLE DATABASE anujt FROM anuj FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujt')SQL>
  2  /

Pluggable database created.


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                           READ WRITE NO         NORMAL
         4    1674153 ANUJT                          MOUNTED    n/a        NEW


SQL> alter pluggable database anujt open ;

Pluggable database altered.




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


PDB_ID..................................................: 4
PDB_NAME................................................: ANUJT
DBID....................................................: 1804906803
CON_UID.................................................: 1804906803
GUID....................................................: 55518EB33D0B0D6CE055000000000001
STATUS..................................................: NORMAL
CREATION_SCN............................................: 1674153
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..................................................: 4
UPGRADE_PRIORITY........................................:
APPLICATION_CLONE.......................................: NO
FOREIGN_CDB_DBID........................................: 1477822556
UNPLUG_SCN..............................................: 1674105
FOREIGN_PDB_ID..........................................: 3
CREATION_TIME...........................................: 27-07-2017 19:16:03
REFRESH_MODE............................................: NONE
REFRESH_INTERVAL........................................:


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                           READ WRITE NO         NORMAL
         4    1674153 ANUJT                          READ WRITE NO         NORMAL



Take a backup for CDB

RUN
 {
configure controlfile autobackup on;
set command id to 'ORCLBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ORCL_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCL_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}


RMAN> RUN
2>  {
configure controlfile autobackup on;
3> 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/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
7> sql 'alter system archive log current';
8> backup tag ORCL_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
9> backup tag ORCL_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
10> release channel c1;
11> }

using target database control file instead of recovery catalog
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=79 device type=DISK

Starting backup at 27-07-2017 19:27: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 27-07-2017 19:27:18

channel c1: finished piece 1 at 27-07-2017 19:43:08
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_104_1_FULL tag=ORCL_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:15:55
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 27-07-2017 19:43:21
channel c1: finished piece 1 at 27-07-2017 19:46:38
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_105_1_FULL tag=ORCL_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:03:17
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 27-07-2017 19:46:39
channel c1: finished piece 1 at 27-07-2017 19:50:58
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_106_1_FULL tag=ORCL_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:04:19
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 27-07-2017 19:50:59

channel c1: finished piece 1 at 27-07-2017 19:59:05
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_107_1_FULL tag=ORCL_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:08:10
Finished backup at 27-07-2017 19:59:10

Starting Control File and SPFILE Autobackup at 27-07-2017 19:59:32
piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1477822556-20170727-01 comment=NONE
Finished Control File and SPFILE Autobackup at 27-07-2017 20:00:42

sql statement: alter system archive log current

Starting backup at 27-07-2017 20:04:15
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=1 STAMP=950472254
input archived log thread=1 sequence=6 RECID=2 STAMP=950472266
channel c1: starting piece 1 at 27-07-2017 20:04:59



channel c1: finished piece 1 at 27-07-2017 20:07:06
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_109_1_ARCHIVE tag=ORCL_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:02:07
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/Archive/1_5_950041842.dbf RECID=1 STAMP=950472254
archived log file name=/u01/app/oracle/Archive/1_6_950041842.dbf RECID=2 STAMP=950472266
Finished backup at 27-07-2017 20:07:14

Starting backup at 27-07-2017 20:07:15
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 27-07-2017 20:07:31
channel c1: finished piece 1 at 27-07-2017 20:07:32
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_110_1_CONTROL tag=ORCL_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-07-2017 20:07:32

Starting Control File and SPFILE Autobackup at 27-07-2017 20:07:33
piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1477822556-20170727-02 comment=NONE
Finished Control File and SPFILE Autobackup at 27-07-2017 20:07:38

released channel: c1



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



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


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

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 20:12:42 2017

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

Last Successful login time: Sun Jul 23 2017 09:43:14 +01:00

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


Create new user in pluggable database ..

SQL> grant dba to anuju identified by anuj ;

Grant succeeded.


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

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 20:16:55 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>  alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL>  select sysdate from dual;

SYSDATE
-------------------
27-07-2017 20:19:10


-bash-4.2$ export NLS_TERRITORY=UNITED KINGDOM;
-bash-4.2$ export  NLS_NCHAR=UTF8;
-bash-4.2$ export  NLS_LANG=ENGLISH;
-bash-4.2$ sqlplus anuju/anuj@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anujt)))

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 20:23:46 2017

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

Last Successful login time: Thu Jul 27 2017 20:17:58 +01:00

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

SQL>  select sysdate from dual;

SYSDATE
-------------------
27-07-2017 20:23:50


SQL> create table anuj_drop_test as select * from dual;

Table created.


We need to recover database up to this point !!!!!!!!

SQL> select sysdate from dual;

SYSDATE
-------------------
27-07-2017 20:27:14

SQL> select * from anuj_drop_test ;

D
-
X

SQL> drop table anuj_drop_test ;

Table dropped.


-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 20:28:56 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>
alter pluggable database anujt close immediate;SQL>

Pluggable database altered.

run
  {
   SET UNTIL TIME "TO_DATE('27-JUL-2017 20:27:14','DD-MON-YYYY HH24:MI:SS')";
   RESTORE pluggable database anujt;
   RECOVER pluggable database anujt auxiliary destination='/home/oracle/anujt';
   ALTER PLUGGABLE DATABASE anujt OPEN RESETLOGS;
  }


-bash-4.2$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 27 20:31:53 2017

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

connected to target database: ORCL (DBID=1477822556)

RMAN> run
2>   {
3>    SET UNTIL TIME "TO_DATE('27-JUL-2017 20:27:14','DD-MON-YYYY HH24:MI:SS')";
   RESTORE pluggable database anujt;
   RECOVER pluggable database anujt auxiliary destination='/home/oracle/anujt';
   ALTER PLUGGABLE DATABASE anujt OPEN RESETLOGS;
  }4> 5> 6> 7>

executing command: SET until clause

Starting restore at 27-07-2017 20:32:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=74 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/orcl/ORC
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/orcl/ORC
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/orcl/ORC
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_106_1_FULL tag=ORCL_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:54
Finished restore at 27-07-2017 20:36:30

Starting recover at 27-07-2017 20:36:30
current log archived
using channel ORA_DISK_1


starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/Archive/1_7_950041842.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170727_109_1_ARCHIVE
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170727_109_1_ARCHIVE tag=ORCL_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
media recovery complete, elapsed time: 00:00:02
Finished recover at 27-07-2017 20:37:40

Statement processed



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

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 27 20:41:11 2017

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

Last Successful login time: Thu Jul 27 2017 20:23:46 +01:00

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

SQL> select * from anuj_drop_test ;

D
-
X

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


col end_resetlogs_time for a20
select con_id,db_incarnation#,pdb_incarnation#,incarnation_time,end_resetlogs_time,flashback_database_allowed,status from v$pdb_incarnation
order by end_resetlogs_time ;

    CON_ID DB_INCARNATION# PDB_INCARNATION# INCARNATION_TIME END_RESETLOGS_TIME   FLA STATUS
---------- --------------- ---------------- ---------------- -------------------- --- -------
         2               1                0 26-01-2017 13:52 26-01-2017 13:52     YES PARENT
         3               1                0 26-01-2017 13:52 26-01-2017 13:52     YES PARENT
         1               1                0 26-01-2017 13:52 26-01-2017 13:52     YES PARENT
         4               2                0 22-07-2017 20:30 22-07-2017 20:30     YES PARENT
         3               2                0 22-07-2017 20:30 22-07-2017 20:30     YES CURRENT
         2               2                0 22-07-2017 20:30 22-07-2017 20:30     YES CURRENT
         1               2                0 22-07-2017 20:30 22-07-2017 20:30     YES CURRENT
         4               2                1 27-07-2017 20:27 27-07-2017 20:37     YES CURRENT

8 rows selected.


Posted by at No comments:

Oracle 12c pluggable database table list ?

Oracle 12c pluggable database table  list ?

List of tables for pluggable database 


SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANUJ                           MOUNTED
         4 ANUJT                          READ WRITE NO


SQL> alter session set container=ANUJT ;
Session altered.


alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set linesize 200 pagesize 200 
col con_name   for a20
col owner      for a20
col table_name for a25
select a.con_id,b.con_name,owner,a.table_name table_name,num_rows,last_analyzed from cdb_tables a,v$active_services b
WHERE 1=1 
-- and a.table_name=upper('anuj_drop_test')
and a.con_id=b.con_id
and owner not in ('DBSFWUSER','MDSYS','OUTLN','CTXSYS','OLAPSYS','SYSTEM','DVSYS','AUDSYS','DBSNMP','GSMADMIN_INTERNAL','OJVMSYS','ORDSYS','APPQOSSYS','XDB','ORDDATA','SYS','WMSYS','LBACSYS')
-- and a.CON_ID=4
order by 1
/


    CON_ID CON_NAME             OWNER                TABLE_NAME                  NUM_ROWS LAST_ANALYZED
---------- -------------------- -------------------- ------------------------- ---------- ----------------
         4 ANUJT                ANUJ                 TEST
         4 ANUJT                ANUJU                ANUJ_DROP_TEST                     1 27-07-2017 20:27
Posted by at No comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive