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.
