Search This Blog

Total Pageviews

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.


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

Oracle DBA

anuj blog Archive