Contents

  1. Restore Oracle database to different client
    1. Envirenment
    2. Record the source host date and time of today's backup
      1. On the source host
      2. On the Source Or Destination host
    3. Destination host
      1. pfile
      2. restore controlfile
      3. restore database
      4. recover database
      5. open database
    4. datafile recover
    5. undo recover

Restore Oracle database to different client

Envirenment

Record the source host date and time of today's backup

On the source host

rman target /nocatalog <<"EOF"
LIST BACKUP OF DATABASE COMPLETED AFTER '(SYSDATE-2)';
EOF

On the Source Or Destination host

$ mminfo -v -s emc-networker.carpoly.com -c txld11244 | grep ctrl | sort -k 4 -n | tail 

emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/23/2020 12:11:24 AM 16 MB 2608303698 cb full RMAN:ncv6db_db_ctrl_2121_1_20200323
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/23/2020 12:21:10 PM 16 MB 880294239 cb full RMAN:ncv6db_db_ctrl_2333_1_20200323
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/24/2020 03:30:53 AM 16 MB 4000911003 cb full RMAN:ncv6db_db_ctrl_2556_1_20200324
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/24/2020 06:13:32 AM 16 MB 746140858 cb full RMAN:ncv6db_db_ctrl_2566_1_20200324
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/24/2020 06:25:18 PM 16 MB 4168736832 cb full RMAN:ncv6db_db_ctrl_2651_1_20200324
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/24/2020 12:10:21 AM 16 MB 4068007834 cb full RMAN:ncv6db_db_ctrl_2396_1_20200324
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/24/2020 12:20:56 PM 16 MB 24742616 cb full RMAN:ncv6db_db_ctrl_2605_1_20200324
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/25/2020 03:35:27 AM 16 MB 511336756 cb full RMAN:ncv6db_db_ctrl_2829_1_20200325
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/25/2020 06:09:44 AM 16 MB 1870300510 cb full RMAN:ncv6db_db_ctrl_2838_1_20200325
emc_networker.carpoly.com.007 adv_file txld11244.group.com 03/25/2020 12:11:49 AM 16 MB 1081749881 cb full RMAN:ncv6db_db_ctrl_2669_1_20200325

oracle-recover<2020-03-25 10:27:05> /home/oracle

$nsrinfo -s emc-networker.carpoly.com txld11244 -n oracle | grep ctrl | head 

ncv6db_db_ctrl_2838_1_20200325, date=1585087784 Wed 25 Mar 2020 06:09:44 AM CST
ncv6db_db_ctrl_2829_1_20200325, date=1585078527 Wed 25 Mar 2020 03:35:27 AM CST
ncv6db_db_ctrl_2669_1_20200325, date=1585066309 Wed 25 Mar 2020 12:11:49 AM CST
ncv6db_db_ctrl_2651_1_20200324, date=1585045518 Tue 24 Mar 2020 06:25:18 PM CST
ncv6db_db_ctrl_2605_1_20200324, date=1585023656 Tue 24 Mar 2020 12:20:56 PM CST
ncv6db_db_ctrl_2566_1_20200324, date=1585001612 Tue 24 Mar 2020 06:13:32 AM CST
ncv6db_db_ctrl_2556_1_20200324, date=1584991853 Tue 24 Mar 2020 03:30:53 AM CST
ncv6db_db_ctrl_2396_1_20200324, date=1584979821 Tue 24 Mar 2020 12:10:21 AM CST
ncv6db_db_ctrl_2381_1_20200323, date=1584959042 Mon 23 Mar 2020 06:24:02 PM CST
ncv6db_db_ctrl_2333_1_20200323, date=1584937270 Mon 23 Mar 2020 12:21:10 PM CST

Destination host

mkdir -pv /data/ncv6db/
mkdir -pv /fra/ncv6db/archivelog
mkdir -pv /{redo1,redo2}/ncv6db
mkdir -pv /oracle/app/oracle/admin/ncv6db/{adump,bdump,cdump,ddump,udump,pfile}

chown -Rv oracle:oinstall /data/ncv6db /fra/ncv6db
chown -Rv oracle:oinstall /oracle/app/oracle/admin/ncv6db/
chown -Rv oracle:oinstall /redo1 /redo2

pfile

rman target / nocatalog <<"EOF"
spool log to Restore-spfile.log
SET DBID=2690271439

RUN {
startup force nomount;
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';

restore spfile to pfile '/home/oracle/initncv6db.ora' from 'ncv6db_db_spfile_2284_1_20200323';

RELEASE CHANNEL CH1;
}
spool log off

EOF

ncv6db.__db_cache_size=18844169011
ncv6db.__java_pool_size=375809638
ncv6db.__large_pool_size=375809638
ncv6db.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
ncv6db.__pga_aggregate_target=5583457484
ncv6db.__sga_target=23890755584
ncv6db.__shared_io_pool_size=0
ncv6db.__shared_pool_size=2093796556
ncv6db.__streams_pool_size=1073741824
*._ash_size=25165824
*._b_tree_bitmap_plans=FALSE
*._db_block_prefetch_limit=0
*._db_block_prefetch_quota=10
*._db_file_noncontig_mblock_read_count=11
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_cartesian_enabled=FALSE
*._optimizer_group_by_placement=FALSE
*._optimizer_skip_scan_enabled=FALSE
*._PX_use_large_pool=TRUE
*._serial_direct_read='NEVER'
*._smu_debug_mode=134217728
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.archive_lag_target=1800
*.log_archive_dest_1='location=/fra/ncv6db/archivelog'
*.audit_file_dest='/oracle/app/oracle/admin/ncv6db/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/data/ncv6db/control01.ctl','/fra/ncv6db/control02.ctl'
*.db_block_size=8192
*.db_cache_size=14872687411
*.db_domain=''
*.db_name='ncv6db'
*.db_recovery_file_dest='/fra'
*.db_recovery_file_dest_size=42949672600
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.disk_asynch_io=FALSE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ncv6dbXDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1','10513 trace name context forever,level 2'
*.filesystemio_options='ASYNCH'
*.java_pool_size=10971520
*.large_pool_size=614400
*.lock_sga=FALSE
#*.log_archive_start=TRUE
*.log_buffer=20971520
*.open_cursors=2000
*.optimizer_dynamic_sampling=4
*.optimizer_index_cost_adj=40
*.pga_aggregate_target=5583457484
*.pre_page_sga=FALSE
*.processes=7035
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_max_size=23890755584
*.sga_target=23890755584
*.shared_pool_size=8589934592
*.undo_management='auto'
*.undo_tablespace='UNDOTBS2'
*.use_indirect_data_buffers=FALSE
*.workarea_size_policy='AUTO'

restore controlfile

startup nomount pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initncv6db.ora'; 

rman target / nocatalog <<"EOF"
spool log to Restore-controlfile.log
SET DBID=2690271439
startup nomount 

RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';

restore controlfile from 'ncv6db_db_ctrl_2556_1_20200324';
alter database mount;

RELEASE CHANNEL CH1;
}
spool log off

EOF

restore database

rman target / nocatalog <<"EOF"
spool log to Restore-database.log

RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';

RESTORE DATABASE UNTIL TIME = "TO_DATE('2020-03-24-03:07:28', 'YYYY-MM-DD-HH24:MI:SS')" preview;
#RESTORE DATABASE UNTIL TIME = "TO_DATE('2020-03-24-03:07:28', 'YYYY-MM-DD-HH24:MI:SS')" validate;
RESTORE DATABASE UNTIL TIME = "TO_DATE('2020-03-24-03:07:28', 'YYYY-MM-DD-HH24:MI:SS')";

RELEASE CHANNEL CH1;
}
spool log off

EOF

recover database

rman target / nocatalog <<"EOF"
spool log to Recover-database.log

RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';
ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';

RECOVER DATABASE UNTIL TIME = "TO_DATE('2020-03-24-03:07:28', 'YYYY-MM-DD-HH24:MI:SS')";

RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
}
spool log off

EOF

RMAN-03002: failure of recover command at 12/06/2018 19:05:44
RMAN-06054: media recovery requesting unknown archived log for thread 1 
with sequence 1 and starting SCN of 1823550

open database

alter database open resetlogs;

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/25/2020 11:08:59
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '/redo1/ncv6db/redo2.log'


SQL> select group#,status,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1         /redo1/ncv6db/redo1.log
        11         /redo1/ncv6db/redo11.log
        12         /redo1/ncv6db/redo12.log
        13         /redo1/ncv6db/redo13.log
        14         /redo1/ncv6db/redo14.log
        15         /redo1/ncv6db/redo15.log
         2         /redo1/ncv6db/redo2.log
         3         /redo1/ncv6db/redo3.log
         4         /redo1/ncv6db/redo4.log
         5         /redo1/ncv6db/redo5.log
         6         /redo2/ncv6db/redo6.log

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         7         /redo2/ncv6db/redo7.log
         8         /redo2/ncv6db/redo8.log
         9         /redo2/ncv6db/redo9.log
        10         /redo2/ncv6db/redo10.log

15 rows selected.

SQL> alter database clear logfile group 2;

Database altered.

oracle>$ rman target / nocatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 27 15:14:13 2020

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

connected to target database: NCV6DB (DBID=2690271439, not open)
using target database control file instead of recovery catalog

RUN {
2> ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';
3> sql "alter database datafile 2 offline";
4> restore datafile 2;
5> recover datafile 2;
6> RELEASE CHANNEL CH1;
7> }

allocated channel: CH1
channel CH1: SID=9258 device type=SBT_TAPE
channel CH1: NMDA Oracle v19.2.0.1

sql statement: alter database datafile 2 offline
Fri Mar 27 15:16:36 2020
alter database datafile 2 offline
Completed: alter database datafile 2 offline

Starting restore at 2020-03-27 15:16:36

channel CH1: starting datafile backup set restore
channel CH1: specifying datafile(s) to restore from backup set
channel CH1: restoring datafile 00002 to /data/ncv6db/sysaux01.dbf
channel CH1: reading from backup piece ncv6db_db_LEV0_2052_1_20200322
Fri Mar 27 15:17:16 2020
Full restore complete of datafile 2 /data/ncv6db/sysaux01.dbf.  Elapsed time: 0:00:37
  checkpoint is 78198070101
  last deallocation scn is 925426
channel CH1: piece handle=ncv6db_db_LEV0_2052_1_20200322 tag=TAG20200322T034541
channel CH1: restored backup piece 1
channel CH1: restore complete, elapsed time: 00:00:45
Finished restore at 2020-03-27 15:17:23

Starting recover at 2020-03-27 15:17:23
channel CH1: starting incremental datafile backup set restore
channel CH1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /data/ncv6db/sysaux01.dbf
channel CH1: reading from backup piece ncv6db_db_lev1_2229_1_20200323
Fri Mar 27 15:17:34 2020
Incremental restore complete of datafile 2 /data/ncv6db/sysaux01.dbf
...
..
.
  Mem# 0: /redo2/ncv6db/redo8.log
Recovery of Online Redo Log: Thread 1 Group 9 Seq 84 Reading mem 0
  Mem# 0: /redo2/ncv6db/redo9.log
Media Recovery Complete (ncv6db)
Completed: alter database recover logfile '/fra/ncv6db/archivelog/1_69_1036144566.dbf'
media recovery complete, elapsed time: 00:01:42
Finished recover at 2020-03-27 15:19:36

released channel: CH1

Recovery of Online Redo Log: Thread 1 Group 8 Seq 83 Reading mem 0
  Mem# 0: /redo2/ncv6db/redo8.log
Recovery of Online Redo Log: Thread 1 Group 9 Seq 84 Reading mem 0
  Mem# 0: /redo2/ncv6db/redo9.log
Media Recovery Complete (ncv6db)
Completed: alter database recover logfile '/fra/ncv6db/archivelog/1_69_1036144566.dbf'
Fri Mar 27 15:22:45 2020
alter database datafile 2 online
Completed: alter database datafile 2 online

{{{

datafile recover

column NAME format a50;
select FILE#,NAME from v$datafile;

RMAN> report schema

RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';

sql "alter database datafile 2 offline";
restore datafile 2;
recover datafile 2;
sql "alter database datafile 2 online";

RELEASE CHANNEL CH1;
}

undo recover

show parameter undo;
alter system set undo_management=manual scope = spfile;

  *  same thing as above. If your database was shutdown clean AND THEN the logs were lost - no problem.

  * If your database was shutdown crashed - we needed those redo logs to perform instance crash recovery and you have just lost all of your data - your archives are useless. 

oracle-recover<2020-03-27 15:32:14> /

root># dd if=/dev/zero of=/data/ncv6db/undotbs02.dbf bs=4M count=1
1+0 records in
1+0 records out
4194304 bytes (4.2 MB) copied, 0.00600314 s, 699 MB/s

oracle-recover<2020-03-27 15:34:42> /
root># Fri Mar 27 15:34:50 2020
Hex dump of (file 108, block 1) in trace file /oracle/app/oracle/diag/rdbms/ncv6db/ncv6db/trace/ncv6db_ora_22516.trc
Corrupt block relative dba: 0x1b000001 (file 108, block 1)
Completely zero block found during kcvxfh v8
Reading datafile '/data/ncv6db/undotbs02.dbf' for corruption at rdba: 0x1b000001 (file 108, block 1)
Reread (file 108, block 1) found different corrupt data (no logical check)
Hex dump of (file 108, block 1) in trace file /oracle/app/oracle/diag/rdbms/ncv6db/ncv6db/trace/ncv6db_ora_22516.trc
Corrupt block relative dba: 0x1b000001 (file 108, block 1)
Completely zero block found during reread

rman target / nocatalog <<"EOF"
spool log to Recover-UNDO.log

RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(NSR_SERVER=emc-networker.carpoly.com,NSR_CLIENT=txld11244.group.com,NSR_RECOVER_POOL=Default)';

sql "alter database datafile 108 offline";
restore datafile 108;
recover datafile 108;
sql "alter database datafile 108 online";

RELEASE CHANNEL CH1;
}
EOF

désert/EMC/Networker/redirected-restore (last edited 2020-03-27 07:40:17 by merlin)