How to restore oracle database to point in time using rman from NetBackup (從NBU還原Oracle database至指定時間)

11/10/2022 02:35:00 下午

1. Check backup controlfile, this case need restore to 2022-11-04 08:00:00, choose backup controlfile Tag TAG20221104T113557 is best, the backup time is 2022-11-04 11:35:57, and remember dbid number

[oracle@cbs-db3 script]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Nov 10 13:48:31 2022
Version 19.13.0.0.0

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

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in TARGET database is not current
connected to target database: ORCL (DBID=1636935633)

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
469     Full    11.25M     SBT_TAPE    00:00:12     30-OCT-22      
        BP Key: 469   Status: AVAILABLE  Compressed: NO  Tag: TAG20221030T044530
        Handle: c-1636935633-20221030-01   Media: @aaaad
  Control File Included: Ckp SCN: 14873924     Ckp time: 30-OCT-22

...ingore...

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
581     Full    11.25M     SBT_TAPE    00:00:07     04-NOV-22      
        BP Key: 581   Status: AVAILABLE  Compressed: NO  Tag: TAG20221104T073557
        Handle: c-1636935633-20221104-04   Media: @aaaad
  Control File Included: Ckp SCN: 15488574     Ckp time: 04-NOV-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
583     Full    11.25M     SBT_TAPE    00:00:08     04-NOV-22      
        BP Key: 583   Status: AVAILABLE  Compressed: NO  Tag: TAG20221104T113557
        Handle: c-1636935633-20221104-05   Media: @aaaad
  Control File Included: Ckp SCN: 15504644     Ckp time: 04-NOV-22
  
...ingore...

RMAN> exit

2. shutdown database and start to nomount state

[oracle@cbs-db3 script]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 10 13:52:13 2022
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size		    8910928 bytes
Variable Size		 3103784960 bytes
Database Buffers	 3321888768 bytes
Redo Buffers		    7864320 bytes
SQL> exit

3. edit and exec restore controlfile file from handle is c-1636935633-20221104-05

[oracle@cbs-db3 script]$ vim restore_controlfile.sh
connect target '/';
SET DBID=1636935633;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
SEND 'NB_ORA_SERV=vcdm01-mas01,NB_ORA_CLIENT=cbs-db3,NB_ORA_POLICY=DB-ORALOG-D-01';
RESTORE controlfile from 'c-1636935633-20221104-05';
RELEASE CHANNEL ch00;
}

[oracle@cbs-db3 script]$ rman cmdfile=restore_controlfile.sh log=restore_controlfile.out
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

4. check restore controlfile status

[oracle@cbs-db3 script]$ cat restore_controlfile.out 

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Nov 10 13:56:16 2022
Version 19.13.0.0.0

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

RMAN> connect target *;
2> SET DBID=1636935633;
3> RUN {
4> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
5> SEND 'NB_ORA_SERV=vcdm01-mas01,NB_ORA_CLIENT=cbs-db3,NB_ORA_POLICY=DB-ORALOG-D-01';
6> RESTORE controlfile from 'c-1636935633-20221104-05';
7> RELEASE CHANNEL ch00;
8> }
9> 
connected to target database: ORCL (not mounted)

executing command: SET DBID

using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=771 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 9.1 (2021052518)

sent command to channel: ch00

Starting restore at 10-NOV-22

channel ch00: restoring control file
channel ch00: restore complete, elapsed time: 00:00:07
output file name=/u01/app/oradata/ORCL/controlfile/o1_mf_kd4ngk18_.ctl
Finished restore at 10-NOV-22

released channel: ch00

Recovery Manager complete.

5. edit and exec restore database to specific point in time

[oracle@cbs-db3 script]$ vim restore_db_time.sh 
connect target '/';
SET DBID=1636935633;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
SEND 'NB_ORA_SERV=vcdm01-mas01,NB_ORA_CLIENT=cbs-db3,NB_ORA_POLICY=DB-ORALOG-D-01';
ALTER DATABASE MOUNT;
set until time "to_date('20221104 08:00:00','yyyymmdd hh24:mi:ss')";
RESTORE DATABASE;
recover database;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

[oracle@cbs-db3 script]$ rman cmdfile=restore_db_time.sh log=restore_db_time.out
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>

6. check restore and recover database status

[oracle@cbs-db3 script]$ cat restore_db_time.out 

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Nov 10 13:59:19 2022
Version 19.13.0.0.0

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

RMAN> connect target *;
2> SET DBID=1636935633;
3> RUN {
4> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
5> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
6> SEND 'NB_ORA_SERV=vcdm01-mas01,NB_ORA_CLIENT=cbs-db3,NB_ORA_POLICY=DB-ORALOG-D-01';
7> ALTER DATABASE MOUNT;
8> set until time "to_date('20221104 08:00:00','yyyymmdd hh24:mi:ss')";
9> RESTORE DATABASE;
10> recover database;
11> RELEASE CHANNEL ch00;
12> RELEASE CHANNEL ch01;
13> }
14> 
connected to target database: ORCL (not mounted)

executing command: SET DBID

using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=390 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 9.1 (2021052518)

allocated channel: ch01
channel ch01: SID=771 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 9.1 (2021052518)

sent command to channel: ch00
sent command to channel: ch01

Statement processed

executing command: SET until clause

Starting restore at 10-NOV-22

channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00003 to /u01/app/oradata/ORCL/datafile/o1_mf_sysaux_kd4ndlg7_.dbf
channel ch00: restoring datafile 00005 to /u01/app/oradata/ORCL/datafile/o1_mf_db_kl2mpcr2_.dbf
channel ch00: restoring datafile 00007 to /u01/app/oradata/ORCL/datafile/o1_mf_users_kd4nfdo3_.dbf
channel ch00: reading from backup piece bk_573_1_1119843604
channel ch01: starting datafile backup set restore
channel ch01: specifying datafile(s) to restore from backup set
channel ch01: restoring datafile 00001 to /u01/app/oradata/ORCL/datafile/o1_mf_system_kd4nc59t_.dbf
channel ch01: restoring datafile 00002 to /u01/app/oradata/ORCL/datafile/o1_mf_db_kl2mpgt6_.dbf
channel ch01: restoring datafile 00004 to /u01/app/oradata/ORCL/datafile/o1_mf_undotbs1_kd4nfclc_.dbf
channel ch01: reading from backup piece bk_572_1_1119843604
channel ch00: piece handle=bk_573_1_1119843604 tag=HOT_DB_BK_INC_LVL0
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:08:35
channel ch01: piece handle=bk_572_1_1119843604 tag=HOT_DB_BK_INC_LVL0
channel ch01: restored backup piece 1
channel ch01: restore complete, elapsed time: 00:08:35
Finished restore at 10-NOV-22

Starting recover at 10-NOV-22

starting media recovery

channel ch00: starting archived log restore to default destination
channel ch00: restoring archived log
archived log thread=1 sequence=672
channel ch00: reading from backup piece al_575_1_1119847677
channel ch01: starting archived log restore to default destination
channel ch01: restoring archived log
archived log thread=1 sequence=673
channel ch01: reading from backup piece al_576_1_1119847677
channel ch00: piece handle=al_575_1_1119847677 tag=TAG20221104T044757
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:07
archived log file name=/u01/archivelog/1_672_1109154900.dbf thread=1 sequence=672
channel ch00: starting archived log restore to default destination
channel ch00: restoring archived log
archived log thread=1 sequence=674
channel ch00: reading from backup piece al_580_1_1119857742
channel ch01: piece handle=al_576_1_1119847677 tag=TAG20221104T044757
channel ch01: restored backup piece 1
channel ch01: restore complete, elapsed time: 00:00:08
archived log file name=/u01/archivelog/1_673_1109154900.dbf thread=1 sequence=673
channel ch01: starting archived log restore to default destination
channel ch01: restoring archived log
archived log thread=1 sequence=675
channel ch01: reading from backup piece al_582_1_1119872142
channel ch00: piece handle=al_580_1_1119857742 tag=TAG20221104T073542
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:07
archived log file name=/u01/archivelog/1_674_1109154900.dbf thread=1 sequence=674
channel ch01: piece handle=al_582_1_1119872142 tag=TAG20221104T113542
channel ch01: restored backup piece 1
channel ch01: restore complete, elapsed time: 00:00:08
archived log file name=/u01/archivelog/1_675_1109154900.dbf thread=1 sequence=675
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-NOV-22

released channel: ch00

released channel: ch01

Recovery Manager complete.

7. open database and resetlogs then finish done

[oracle@cbs-db3 script]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 10 14:09:13 2022
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> alter database open resetlogs;

Database altered.

SQL> exit
JohnsonJ

張貼者： JohnsonJ

張貼留言

0 留言