Continuing with my configured Standby database ....
how to restore a lost datafile in the Standby [when there is no backup on the Standby server and no backup recently taken or planned on the Primary server]
I have two missing files and the "well-known" RMAN command "LIST FAILURE" cannot be used. (So, I cannot also use "ADVISE FAILURE" and "REPAIR FAILURE"). That is why it is also important to know how to query the V$RECOVER_FILE view
Note that I did not issue a CANCEL RECOVERY command and the DATAFILE OFFLINE cannot be used.
And database recovery on the Standby database has resumed (as is evident from the Sequence numbers for Online Redo Logs shown above)
Note that if you use V$RECOVER_FILE, you must remember that the PDBSEED files are listed !! -- although they do not need Recovery.
So, it is a good idea to be aware of the V$RECOVER_FILE view.
UPDATE : See this post for a follow-up.
how to restore a lost datafile in the Standby [when there is no backup on the Standby server and no backup recently taken or planned on the Primary server]
STDBYDB>sqlplus '/ as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:19:56 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/STDBYDB/system01.dbf
/opt/oracle/oradata/STDBYDB/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/undotbs01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/users01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
11 rows selected.
SQL> !rm /opt/oracle/oradata/STDBYDB/users01.dbf
SQL> !rm /opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>
STDBYDB>sqlplus '/ as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:22:00 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>
STDBYDB>sqlplus '/ as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:27:38 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select file#, error
2 from v$recover_file
3 where error is not null
4 order by file#
5 /
FILE# ERROR
---------- -----------------------------------------------------------------
7 FILE NOT FOUND
12 FILE NOT FOUND
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 8 22:30:41 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2778483057, not open)
RMAN> list failure;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 05/08/2020 22:30:48
RMAN-05533: Command LIST FAILURE is not supported on STANDBY database
RMAN>
I have two missing files and the "well-known" RMAN command "LIST FAILURE" cannot be used. (So, I cannot also use "ADVISE FAILURE" and "REPAIR FAILURE"). That is why it is also important to know how to query the V$RECOVER_FILE view
RMAN> quit Recovery Manager complete. STDBYDB>rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 8 22:33:20 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2778483057, not open) RMAN> sql 'alter database datafile 7 offline'; using target database control file instead of recovery catalog sql statement: alter database datafile 7 offline RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 05/08/2020 22:33:30 RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 7 offline ORA-01668: standby database requires DROP option for offline of data file RMAN> RMAN> restore datafile 7 from service ORCLCDB; Starting restore at 08-MAY-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=27 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service ORCLCDB channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/STDBYDB/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 08-MAY-20 RMAN> restore datafile 12 from service ORCLCDB; Starting restore at 08-MAY-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service ORCLCDB channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 08-MAY-20 RMAN> RMAN> exit Recovery Manager complete. STDBYDB>sqlplus SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:39:06 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 STDBYDB>tail -10 alert_STDBYDB.log rfs (PID:4274): Re-archiving LNO:4 T-1.S-9 2020-05-08T22:39:42.085513+08:00 PR00 (PID:4249): Media Recovery Waiting for T-1.S-10 (in transit) 2020-05-08T22:39:42.098700+08:00 ARC0 (PID:3261): Archived Log entry 30 added for T-1.S-9 ID 0xa7521ccd LAD:1 2020-05-08T22:39:42.122808+08:00 rfs (PID:4274): Selected LNO:4 for T-1.S-10 dbid 2778483057 branch 1036108814 2020-05-08T22:39:43.171982+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log STDBYDB>tail -10 alert_STDBYDB.log Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log 2020-05-08T22:40:28.038857+08:00 rfs (PID:4274): No SRLs available for T-1 2020-05-08T22:40:28.040754+08:00 rfs (PID:4274): Opened log for T-1.S-11 dbid 2778483057 branch 1036108814 2020-05-08T22:40:28.043930+08:00 ARC3 (PID:3268): Archived Log entry 31 added for T-1.S-10 ID 0xa7521ccd LAD:1 2020-05-08T22:40:28.107110+08:00 PR00 (PID:4249): Media Recovery Waiting for T-1.S-11 (in transit) STDBYDB>tail -10 alert_STDBYDB.log 2020-05-08T22:40:46.240128+08:00 rfs (PID:4274): Archived Log entry 32 added for B-1036108814.T-1.S-11 ID 0xa7521ccd LAD:2 2020-05-08T22:40:46.282764+08:00 rfs (PID:4274): Selected LNO:4 for T-1.S-12 dbid 2778483057 branch 1036108814 2020-05-08T22:40:46.492569+08:00 PR00 (PID:4249): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_11_1036108814.dbf PR00 (PID:4249): Media Recovery Waiting for T-1.S-12 (in transit) 2020-05-08T22:40:46.646475+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log STDBYDB>
Note that I did not issue a CANCEL RECOVERY command and the DATAFILE OFFLINE cannot be used.
And database recovery on the Standby database has resumed (as is evident from the Sequence numbers for Online Redo Logs shown above)
Note that if you use V$RECOVER_FILE, you must remember that the PDBSEED files are listed !! -- although they do not need Recovery.
SQL> select name from v$datafile where file# in (select file# from v$recover_file); NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf SQL> SQL> l 1 select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time 2 from v$pdbs p, v$recover_file r, v$datafile f 3 where p.con_id=r.con_id 4 and r.con_id=f.con_id 5 and r.file#=f.file# 6* order by 1,2 SQL> / PDBNAME FILE# FILENAME ONLINE_ ERROR TIME -------- ---------- ------------------------------------------------------ ------- ---------------- --------- PDB$SEED 5 /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf ONLINE 04-MAY-19 PDB$SEED 6 /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf ONLINE 04-MAY-19 PDB$SEED 8 /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf ONLINE 04-MAY-19 SQL>
So, it is a good idea to be aware of the V$RECOVER_FILE view.
UPDATE : See this post for a follow-up.
No comments:
Post a Comment