Database Point-in-Time Recovery
Il database target è "primary" mentre il catalogo è installato su un pdb del database "rmancat".
Colleghiamoci al db Primary ed effettauiamo uno switch dei redolog file e poi uno shutdown.
SQL> alter system switch logfile;
SQL> SHUTDOWN IMMEDIATE;
Riavviamo il db in stato mount ed efftuiamo il backup del database.
[oracle@primary ~]$ rman target / catalog rcatown/rcatown@rcatpdb
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 22 11:51:53 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1590558615, not open)
connected to recovery catalog database
RMAN> backup database plus archivelog;
Per verificare se è possibile efffettuare il restore di uno specifico file o set di file da un backup.
RMAN> Restore Database validate;
RMAN> Restore Database validate;
Se terminata con successo risaliamo ai file usati per il restore del db.
RMAN> Restore Database preview Summary;
Starting restore at 22-MAR-21
using channel ORA_DISK_1
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
9090 B F A DISK 22-MAR-21 1 1 NO TAG20210322T115305
9092 B F A DISK 22-MAR-21 1 1 NO TAG20210322T115305
9091 B F A DISK 22-MAR-21 1 1 NO TAG20210322T115305
archived logs generated after SCN 2858298 not found in repository
recovery will be done up to SCN 2858231
Media recovery start SCN is 2858298
Recovery must be done beyond SCN 2858340 to clear datafile fuzziness
Finished restore at 22-MAR-21
Dopodichè spegniamo il db e riavviamolo in modalità nomount in restricted mode (cioè il db è accessibile solo agli amministratori).
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount restrict;
ORACLE instance started.
Total System Global Area 2835349504 bytes
Fixed Size 8796816 bytes
Variable Size 721421680 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7979008 bytes
SQL> alter database mount;
SQL> drop database;
[oracle@primary ~]$ rman target / catalog rcatown/rcatown@rcatpdb
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> set dbid=1590558615
executing command: SET DBID
database name is "ORCL" and DBID is 1590558615
RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/db_1/dbs/initorcl.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 281019704 bytes
Database Buffers 775946240 bytes
Redo Buffers 8146944 bytes
RMAN> restore spfile from autobackup;
Starting restore at 22-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210322
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210321
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210320
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210319
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210318
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210317
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210316
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/22/2021 12:15:33
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
RMAN> list backup summary;
....................
.........................
Seleziare l'ultima riga
9131 B F A DISK 22-MAR-21 1 1 NO TAG20210322T115316
RMAN> list backup tag TAG20210322T115316;
List of Backup Sets
==================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9131 Full 18.00M DISK 00:00:00 22-MAR-21
BP Key: 9135 Status: AVAILABLE Compressed: NO Tag: TAG20210322T115316
Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_03_22/o1_mf_s_1067858880_j5jxrwly_.bkp
SPFILE Included: Modification time: 22-MAR-21
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 2858340 Ckp time: 22-MAR-21
Piece Name: contiene il riferimento al backup del control file e spfile.
Effetttuaimo il restore del spfile
RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_03_22/o1_mf_s_1067858880_j5jxrwly_.bkp';
Starting restore at 22-MAR-21
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_03_22/o1_mf_s_1067858880_j5jxrwly_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 22-MAR-21
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2021_03_22/o1_mf_s_1067858880_j5jxrwly_.bkp';
Starting restore at 22-MAR-21
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlorcl.dbf
Finished restore at 22-MAR-21
Ora spegnere il db.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Riavviamo il db che legge spfile riprisitnato:
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2835349504 bytes
Fixed Size 8796816 bytes
Variable Size 721421680 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7979008 bytes
Ora con il seguente comando legge il control file:
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
L'errore sta nell'identificare il control file perchè quello cercato dal db è sotto
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/orcl/contro
l02.ctl
Mentre quello ripristinato è sotto /u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlorcl.dbf
SQL> alter system set control_files='/u01/app/oracle/product/12.2.0.1/db_1/dbs/cntrlorcl.dbf' scope=spfile;
Occorre riavviare il db per far leggere nell'spfile il nuovo parametro "control_files"
SQL> shutdown immediate;
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2835349504 bytes
Fixed Size 8796816 bytes
Variable Size 721421680 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7979008 bytes
Database mounted.
Ora occorre effetttuare il restore dei datafile.
RMAN> restore database;
.....
RMAN> recover database UNTIL SCN 2858340;
Starting recover at 22-MAR-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-MAR-21
RMAN> alter database open resetlogs;
Statement processed
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete