Created
July 15, 2022 14:34
-
-
Save flandersen/36bd737b5bc6874732a5e9bafcbc38a7 to your computer and use it in GitHub Desktop.
Oracle database recovery write-up
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # ---------------------------------------------------------------------------------------- | |
| # Restoring the database of the development system with a backup from the live system. | |
| # Using Trivadis BasEnv as standardization tool for setting Oracle environment variables. | |
| # ---------------------------------------------------------------------------------------- | |
| # 1. Stop the database. | |
| TYPE (Cluster|DG) : SID/PROCESS STATUS HOME [2021-03-18 10:01:34] | |
| ---------------------------------------------------------------------------------------- | |
| Dummy rdbms : OraDB19Home1 n/a D:\app\oracle\product\19.0.0a | |
| Dummy grinf : OraGI19Home1 n/a D:\app\grid\19.0.0 | |
| ASM-instance : +ASM1 up D:\app\grid\19.0.0 | |
| DB-instance (S|N) : MAINDB up D:\app\oracle\product\19.0.0a | |
| Listener : Listener up OraGI19Home1 | |
| Listener : ListenerASMNET1LSNR_ASM up OraGI19Home1 | |
| Listener : ListenerLISTENER_SCAN1 up OraGI19Home1 | |
| DBSRV01:flandersen:ORADB19HOME1 C:\>srvctl stop database -d MAINDB | |
| # 2. Delete and create all folders from ASM. | |
| DBSRV01:flandersen:ORADB19HOME1 C:\>+asm | |
| DBSRV01:flandersen:+ASM1 C:\>asmcmd rm -rf +DATA/MAINDB/ | |
| DBSRV01:flandersen:+ASM1 C:\>asmcmd mkdir +DATA/MAINDB/ | |
| DBSRV01:flandersen:+ASM1 C:\>asmcmd rm -rf +FRA/MAINDB/ | |
| DBSRV01:flandersen:+ASM1 C:\>asmcmd mkdir +FRA/MAINDB/ | |
| # 3. Copy the SPFILE and CONTROLFILE backup to C:\temp | |
| - INIT_<DB_SID>_<Date_of_Backup> | |
| - controlfile_<DB_SID>_<Date_of_Backup> | |
| # 4. Restore the SPFILE. | |
| DBSRV01:flandersen:+ASM1 C:\>MAINDB | |
| DBSRV01:flandersen:MAINDB C:\>sqh | |
| SQL> startup nomount pfile='C:\temp\INIT_MAINDB_2021-03-15_22-00-09' | |
| SQL> create spfile='+DATA/MAINDB/spfile' from pfile='C:\temp\INIT_MAINDB_2021-03-15_22-00-09'; | |
| File created. | |
| SQL> shutdown immediate | |
| ORA-01507: database not mounted | |
| ORACLE instance shut down. | |
| SQL> exit | |
| # 5. Restore the CONTROLFILE. | |
| DBSRV01:flandersen:MAINDB C:\>rmanc | |
| connected to target database: MAINDB (not started) | |
| RMAN> startup nomount; | |
| Oracle instance started | |
| Total System Global Area 26843542360 bytes | |
| Fixed Size 12821336 bytes | |
| Variable Size 5033164800 bytes | |
| Database Buffers 21743271936 bytes | |
| Redo Buffers 54284288 bytes | |
| RMAN> restore controlfile from 'C:\temp\controlfile_maindb_2021-03-15_22-00-09'; | |
| Starting restore at 16-MAR-21 | |
| allocated channel: ORA_DISK_1 | |
| channel ORA_DISK_1: SID=13 device type=DISK | |
| channel ORA_DISK_1: copied control file copy | |
| output filename=+DATA/MAINDB/CONTROLFILE/current.256.1067330847 | |
| output file name=+FRA/MAINDB/CONTROLFILE/current.268.1067330847 | |
| Finished restore at 16-MAR-21 | |
| # 6. Open the database in mount mode. | |
| RMAN> alter database mount; | |
| released channel: ORA_DISK_1 | |
| Statement processed. | |
| # 7. Perform a crosscheck of available backup files. | |
| RMAN> crosscheck backup; | |
| Starting implicit crosscheck backup at 2021-03-16 13:34:22 | |
| allocated channel: ORA_DISK_1 | |
| channel ORA_DISK_1: SID=13 device type=DISK | |
| ... | |
| Crosschecked 87 objects | |
| Finished implicit crosscheck backup at 2021-03-16 13:34:26 | |
| ... | |
| backup piece handle=+FRA/MAINDB/AUTOBACKUP/2021_03_15/s_1069711375.288.1069711375 RECID=851 STAMP=1069711375 | |
| Crosschecked 62 objects | |
| # 8. Point-in-time recovery (PITR) of the database. | |
| RMAN> run | |
| { | |
| set until time '2021-03-15 09:13:00'; | |
| restore database; | |
| recover database; | |
| alter database open resetlogs; | |
| } | |
| Starting restore at 16-MAR-21 | |
| using channel ORA_DISK_1 | |
| channel ORA_DISK_1: starting datafile backup set restore | |
| channel ORA_DISK_1: specifying datafile(s) to restore from backup set | |
| channel ORA_DISK_1: restoring datafile 00001 to +DATA/MAINDB/DATAFILE/system.260.1066999411 | |
| channel ORA_DISK_1: restoring datafile 00003 to +DATA/MAINDB/DATAFILE/sysaux.262.1066999421 | |
| channel ORA_DISK_1: restoring datafile 00005 to +DATA/MAINDB/DATAFILE/undotbs1.264.1066999423 | |
| channel ORA_DISK_1: restoring datafile 00007 to +DATA/MAINDB/DATAFILE/users.268.1066999455 | |
| channel ORA_DISK_1: restoring datafile 00012 to +DATA/MAINDB/DATAFILE/ts_audit.276.1067274595 | |
| channel ORA_DISK_1: reading from backup piece Y:\BCK_TO_NFS_6HVPR5BG_1_2 | |
| channel ORA_DISK_1: piece handle=Y:\BCK_TO_NFS_6HVPR5BG_1_2 tag=BCK_TO_NFS | |
| channel ORA_DISK_1: restored backup piece 1 | |
| channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 | |
| channel ORA_DISK_1: starting datafile backup set restore | |
| channel ORA_DISK_1: specifying datafile(s) to restore from backup set | |
| channel ORA_DISK_1: restoring datafile 00008 to +DATA/MAINDB/4C9EB80EC19D45EA84CBD04102FD53D2/DATAFILE/system.272.1067003567 | |
| channel ORA_DISK_1: restoring datafile 00009 to +DATA/MAINDB/4C9EB80EC19D45EA84CBD04102FD53D2/DATAFILE/sysaux.271.1067003567 | |
| channel ORA_DISK_1: restoring datafile 00010 to +DATA/MAINDB/4C9EB80EC19D45EA84CBD04102FD53D2/DATAFILE/undotbs1.270.1067003567 | |
| channel ORA_DISK_1: restoring datafile 00011 to +DATA/MAINDB/4C9EB80EC19D45EA84CBD04102FD53D2/DATAFILE/users.274.1067003595 | |
| channel ORA_DISK_1: reading from backup piece Y:\BCK_TO_NFS_6IVPR5CJ_1_2 | |
| channel ORA_DISK_1: piece handle=Y:\BCK_TO_NFS_6IVPR5CJ_1_2 tag=BCK_TO_NFS | |
| channel ORA_DISK_1: restored backup piece 1 | |
| channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 | |
| channel ORA_DISK_1: starting datafile backup set restore | |
| channel ORA_DISK_1: specifying datafile(s) to restore from backup set | |
| channel ORA_DISK_1: restoring datafile 00002 to +DATA/MAINDB/CE80FCBC036149FC8B1054A211FEDA7B/DATAFILE/system.261.1066999415 | |
| channel ORA_DISK_1: restoring datafile 00004 to +DATA/MAINDB/CE80FCBC036149FC8B1054A211FEDA7B/DATAFILE/sysaux.263.1066999421 | |
| channel ORA_DISK_1: restoring datafile 00006 to +DATA/MAINDB/CE80FCBC036149FC8B1054A211FEDA7B/DATAFILE/undotbs1.265.1066999425 | |
| channel ORA_DISK_1: reading from backup piece Y:\BCK_TO_NFS_6JVPR5CQ_1_2 | |
| channel ORA_DISK_1: piece handle=Y:\BCK_TO_NFS_6JVPR5CQ_1_2 tag=BCK_TO_NFS | |
| channel ORA_DISK_1: restored backup piece 1 | |
| channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 | |
| Finished restore at 16-MAR-21 | |
| Starting recover at 16-MAR-21 | |
| using channel ORA_DISK_1 | |
| starting media recovery | |
| channel ORA_DISK_1: starting archived log restore to default destination | |
| channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=37 | |
| channel ORA_DISK_1: reading from backup piece Y:\BCK_TO_NFS_6KVPR5D3_1_2 | |
| channel ORA_DISK_1: piece handle=Y:\BCK_TO_NFS_6KVPR5D3_1_2 tag=BCK_TO_NFS | |
| channel ORA_DISK_1: restored backup piece 1 | |
| channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 | |
| archived log file name=+FRA/MAINDB/ARCHIVELOG/2021_03_16/thread_1_seq_37.256.1067331135 thread=1 sequence=37 | |
| channel default: deleting archived log(s) | |
| archived log file name=+FRA/MAINDB/ARCHIVELOG/2021_03_16/thread_1_seq_37.256.1067331135 RECID=22 STAMP=1067331136 | |
| media recovery complete, elapsed time: 00:00:02 | |
| Finished recover at 16-MAR-21 | |
| # 9. Clear the configuration of the currently specified password file. | |
| DBSRV01:flandersen:MAINDB C:\> srvctl modify database -d MAINDB-pwfile | |
| # 10. Create a new password file. | |
| DBSRV01:flandersen:MAINDB C:\> orapwd file=+DATA dbuniquename=MAINDB | |
| Enter password for SYS: *********** | |
| # 11. Start the database service. | |
| DBSRV01:flandersen:MAINDB C:\> srvctl start database -d MAINDB | |
| # 12. Test a database switchover to the redudant server. | |
| DBSRV01:flandersen:MAINDB C:\> srvctl relocate database -d MAINDB -node DBSRV02 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment