Create the Database from RMAN backup of the TEST database until time

Restore database DEMO from Tape full backup of the TEST database files

• Copy the FULL  BACKUP files from Tape to disk /u02/backupsets/TEST_RESTORE
• Drop the database DEMO from HOST if it already present
• Start the database in no mount
• Identify the SCN or date from TEST list backup
• Recover the database until  SCN  using Duplicate command

Identify the Archive log sequence numbers generated at end of the backup
And find the LOW  SCN 42612597056  and restore until LOW SCN
Example (Red to consider)
   List of Archived Logs in backup set 9078
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6802    42612597056 Jun 01 2012 20:17:10 42612597205 Jun 01 2012 20:17:32
  1    6803    42612597205 Jun 01 2012 20:17:32 42623802321 Jun 02 2012 00:16:00
  1    6804    42623802321 Jun 02 2012 00:16:00 42634272350 Jun 02 2012 06:05:24
  1    6805    42634272350 Jun 02 2012 06:05:24 42645392744 Jun 02 2012 18:00:0

Or you can put any SCN number below 42612597056 numbers ( see  the output)

  List of Archived Logs in backup set 9071
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6801    42607563494 Jun 01 2012 20:00:20 42612597056 Jun 01 2012 20:17:10
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9072    3.42M      DISK        00:00:02     Jun 01 2012 20:17:19
        BP Key: 10128   Status: AVAILABLE  Compressed: NO  Tag: TAG20120601T201716
        Piece Name: +FRA/TEST/backupset/2012_06_01/annnf0_tag20120601t201716_0.4826.784844239
 
List of Archived Logs in backup set 9072
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  2    5541    42607563498 Jun 01 2012 20:00:22 42612597059 Jun 01 2012 20:17:11
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9074    94.50K     DISK        00:00:07     Jun 01 2012 20:17:42
        BP Key: 10130   Status: AVAILABLE  Compressed: NO  Tag: TAG20120601T201734
        Piece Name: +FRA/TEST/backupset/2012_06_01/annnf0_tag20120601t201734_0.4992.784844263
  List of Archived Logs in backup set 9074
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  2    5542    42612597059 Jun 01 2012 20:17:11 42612597207 Jun 01 2012 20:17:33
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
9075    16.00K     DISK        00:00:07     Jun 01 2012 20:17:42
        BP Key: 10131   Status: AVAILABLE  Compressed: NO  Tag: TAG20120601T201734
        Piece Name: +FRA/TEST/backupset/2012_06_01/annnf0_tag20120601t201734_0.3649.784844263
  List of Archived Logs in backup set 9075
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6802    42612597056 Jun 01 2012 20:17:10 42612597205 Jun 01 2012 20:17:32

Restore works if put Archived Logs  Thrd seq start from 5541 to 5542 
and  6802

   List of Archived Logs in backup set 9078
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    6802    42612597056 Jun 01 2012 20:17:10 42612597205 Jun 01 2012 20:17:32
  1    6803    42612597205 Jun 01 2012 20:17:32 42623802321 Jun 02 2012 00:16:00
  1    6804    42623802321 Jun 02 2012 00:16:00 42634272350 Jun 02 2012 06:05:24
  1    6805    42634272350 Jun 02 2012 06:05:24 42645392744 Jun 02 2012 18:00:0
Or   we can run the query below and identify the SCN number

 alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col next_change# format 999999999999999;
col first_change# format 999999999999999;
  select sequence#, to_date(first_time,'DD-MON-YYYY HH24:MI:SS') first_time,
  first_change#,    to_date(next_time,'DD-MON-YYYY HH24:MI:SS') next_time,
 next_change# from v$archived_log  where completion_time between to_date('JUN-01-2012','MON-DD-YYYY') and Sysdate

Duplicate script
--------------------
#!/usr/bin/ksh
export ORAENV_ASK='NO'
export ORACLE_SID=TEST1
/usr/local/bin/oraenv
export NLS_LANG=American_america.us7ascii;
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";
$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_DEMO.log <<EOF
connect auxiliary sys/password@DEMO;
run
{
allocate auxiliary channel aux1 device type disk;
set until SCN 42612597059;
duplicate target database to "DEMO" pfile='/u01/app/xxxxx/product/10.2.0/db_1/dbs/initDEMO.ora' NOFILENAMECHECK;
}
EOF
mailx -s "TEST_Tape to DEMO duplication result" "email id" < /tmp/duplicate_tape_DEMO.log

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more