1. Backup database
RMAN> list backup;
List of Backup Sets
===================
....
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 303.08M DISK 00:01:31 21-MAR-12
BP Key: 36 Status: AVAILABLE Compressed: YES Tag: TAG20120321T123122
Piece Name: +FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1960721 21-MAR-12 +DATA/oratest/datafile/system.256.773672945
2 Full 1960721 21-MAR-12 +DATA/oratest/datafile/sysaux.257.773672955
3 Full 1960721 21-MAR-12 +DATA/oratest/datafile/undotbs1.258.773672957
4 Full 1960721 21-MAR-12 +DATA/oratest/datafile/users.259.773672963
5 Full 1960721 21-MAR-12 +DATA/oratest/datafile/example.265.773673233
6 Full 1960721 21-MAR-12 +DATA/oratest/datafile/tab.267.774023547
7 Full 1960721 21-MAR-12 +DATA/oratest/datafile/drop_tbs.269.778509333
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 9.36M DISK 00:00:02 21-MAR-12
BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20120321T123258
Piece Name: +FRA/oratest/autobackup/2012_03_21/s_778509178.266.778509181
SPFILE Included: Modification time: 21-MAR-12
SPFILE db_unique_name: ORATEST
Control File Included: Ckp SCN: 1961455 Ckp time: 21-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 9.36M DISK 00:00:02 21-MAR-12
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: TAG20120321T124426
Piece Name: +FRA/oratest/autobackup/2012_03_21/s_778509866.294.778509869
SPFILE Included: Modification time: 21-MAR-12
SPFILE db_unique_name: ORATEST
Control File Included: Ckp SCN: 1962073 Ckp time: 21-MAR-12
2. View data, time and drop one table
SQL> select * from drop_user.drop_table;
ID
----------
2
1
1
1
1
1
1
1
1
1
1
ID
----------
1
1
1
1
15 rows selected.
SQL>
SQL>
SQL> host
bash-3.2$ date
Wed Mar 21 13:03:38 EET 2012
bash-3.2$ exit
exit
SQL> drop table drop_user.drop_table;
Table dropped.
SQL> exit
3. Recover database until time before drop
-bash-3.2$ export NLS_LANG=american_america.us7ascii
-bash-3.2$ export NLS_DATE_FORMAT="yyyymmdd:hh24:mi:ss"
-bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 21 13:05:20 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORATEST (DBID=3309052188)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 313159680 bytes
Fixed Size 2225576 bytes
Variable Size 272632408 bytes
Database Buffers 33554432 bytes
Redo Buffers 4747264 bytes
RMAN> run
2> {
3> set until time '20120321:13:00:00';
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 20120321:13:08:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=2 STAMP=778509788 file name=+FRA/oratest/datafile/syst em.284.778509743
destination for restore of datafile 00001: +DATA/oratest/datafile/system.256.773 672945
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=+DATA/oratest/datafile/system.256.773672945 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=3 STAMP=778509832 file name=+FRA/oratest/datafile/sysa ux.282.778509799
destination for restore of datafile 00002: +DATA/oratest/datafile/sysaux.257.773 672955
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=+DATA/oratest/datafile/sysaux.257.773672955 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=5 STAMP=778509852 file name=+FRA/oratest/datafile/undo tbs1.263.778509849
destination for restore of datafile 00003: +DATA/oratest/datafile/undotbs1.258.7 73672957
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=+DATA/oratest/datafile/undotbs1.258.773672957 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=7 STAMP=778509863 file name=+FRA/oratest/datafile/users.292.778509863
destination for restore of datafile 00004: +DATA/oratest/datafile/users.259.773672963
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=+DATA/oratest/datafile/users.259.773672963 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=4 STAMP=778509847 file name=+FRA/oratest/datafile/example.300.778509833
destination for restore of datafile 00005: +DATA/oratest/datafile/example.265.773673233
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=+DATA/oratest/datafile/example.265.773673233 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=6 STAMP=778509859 file name=+FRA/oratest/datafile/tab.291.778509855
destination for restore of datafile 00006: +DATA/oratest/datafile/tab.267.774023547
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=+DATA/oratest/datafile/tab.267.774023547 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00007
input datafile copy RECID=10 STAMP=778510722 file name=+FRA/oratest/datafile/drop_tbs.293.778509865
destination for restore of datafile 00007: +DATA/oratest/datafile/drop_tbs.269.778510537
channel ORA_DISK_1: copied datafile copy of datafile 00007
output file name=+DATA/oratest/datafile/drop_tbs.269.778510537 RECID=0 STAMP=0
Finished restore at 20120321:13:10:46
Starting recover at 20120321:13:10:46
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 20120321:13:10:48
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/21/2012 13:11:08
RMAN-11003: failure during parse/execution of SQL statement: alter database open read only
ORA-16005: database requires recovery
RMAN>
NOTE: In DBA2 course for 11gr2 is RMAN> sql 'alter database open read only'; and this should be working.
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 21 13:11:35 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from drop_user.drop_table;
ID
----------
2
1
1
1
1
1
1
1
1
1
1
ID
----------
1
1
1
1
15 rows selected.
SQL>
No comments:
Post a Comment