Hello Readers,
In this article , we will see Performing Flashback Restore on Oracle 19c Two Node RAC Servers.
When the time of Testing applications or Checking performance issues on databases , As a DBA we need to restore the databases to the previous states.
In Such cases these steps are very useful.
Pre-checks :
Steps :
1.Check the database details.
2.Check the Instance details.
3.Check FRA Size and Restore Points created on database.
4.Check User connections on database.
5.Shutdown the database using SRVCTL.
6.Mount the database with only Single Instance using SRVCTL.
7.Restore the database using flashback restore database command.
8.Open the database with resetlogs.
9.Shutdown the database using SRVCTL.
10. Start the database with all the nodes using SRVCTL
Post Checks :
11.Check the status of Databases and Services .
12.Check the datafile status and any files need to recover .
13.Perform the log switch .
1. Check Database Details
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 16:17:33 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> set linesize 300 SQL> select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- AJAYDB - ajaydb1@rac1.ajay.com - ajaydb - 19.0.0.0.0 - READ WRITE - 20-FEB-2020 15:34:33 - PRIMARY |
2.Check the Instance details
|
1 2 3 4 5 6 7 8 9 10 |
SQL> set linesize 300 SQL> col host_name for a40 SQL> col instance_role for a20 SQL> col startup_time for a25 SQL> select INST_ID,INSTANCE_NAME,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') startup_Time, INSTANCE_ROLE from gv$instance order by inst_id; INST_ID INSTANCE_NAME HOST_NAME STARTUP_TIME INSTANCE_ROLE ---------- ---------------- --------------------------------- 1 ajaydb1 rac1.ajay.com 20-FEB-2020 15:34:33 PRIMARY_INSTANCE 2 ajaydb2 rac2.ajay.com 20-FEB-2020 15:35:49 PRIMARY_INSTANCE |
3.Check FRA Size
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> set linesize 300 SQL> col file_type for a40 SQL> select * from V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ------------------ ------------------ ------------------------- --------------- ---------- CONTROL FILE .16 0 1 0 REDO LOG 6.7 0 4 0 ARCHIVED LOG 2.48 0 8 0 BACKUP PIECE .16 0 1 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 6.7 0 4 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 |
Check Restore points available on database
|
1 2 3 4 5 6 7 8 9 10 |
SQL> set linesize 300 SQL> col time for a35 SQL> set numwidth 30 SQL> col name for a30 SQL> col RESTORE_POINT_TIME for a25 SQL> select SCN,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,PRESERVED,NAME from v$restore_point; SCN GUA STORAGE_SIZE TIME PRE NAME ------- ----- ------------- ---------------------- ---- ----- 1577339 YES 419430400 20-FEB-20 03.40.24.00000 PM YES CLEAN_DB_SYS_20-02-2020 |
4.We need to check User connections made to database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> set linesize 300 SQL> col machine for a40 SQL> select inst_id, username,count(*),machine from gv$session group by username,inst_id,machine order by inst_id; INST_ID USERNAME COUNT(*) MACHINE --------- ---------------------- -------------- 1 SYS 3 rac1.ajay.com 1 SYSRAC 3 rac1.ajay.com 1 77 rac1.ajay.com 2 SYS 1 rac1.ajay.com 2 SYS 2 rac2.ajay.com 2 SYSRAC 2 rac2.ajay.com 2 77 rac2.ajay.com 7 rows selected. |
5.Shutdown the database using SRVCTL
|
1 2 3 4 |
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle@rac1 ~]$ srvctl stop database -d ajaydb -o immediate |
6.Mount the database with only Single Instance using SRVCTL.
|
1 |
oracle@rac1 ~]$ srvctl start instance -d ajaydb -i ajaydb1 -o mount |
7.Restore the database using flashback restore database command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 16:22:58 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> set linesize 300 SQL> select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- AJAYDB - ajaydb1@rac1.ajay.com - ajaydb - 19.0.0.0.0 - MOUNTED - 20-FEB-2020 16:22:06 - PRIMARY SQL> flashback database to restore point CLEAN_DB_SYS_20-02-2020; Flashback complete. |
8.Open the database with resetlogs.
|
1 2 3 |
SQL> alter database open resetlogs; Database altered. |
9.Shutdown the database using SRVCTL.
|
1 2 3 4 |
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle@rac1 ~]$ srvctl stop database -d ajaydb -o immediate |
10. Start the database with all the nodes using SRVCTL
|
1 |
oracle@rac1 ~]$ srvctl start database -d ajaydb -o open |
11.Check the status of Databases and Services
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 16:28:33 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> set linesize 300 SQL> select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- AJAYDB - ajaydb1@rac1.ajay.com - ajaydb - 19.0.0.0.0 - READ WRITE - 20-FEB-2020 16:27:24 - PRIMARY |
12.Check the datafiles status and any files need to recover
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select * from v$recover_file; no rows selected SQL> select distinct status from v$datafile; STATUS ------- SYSTEM ONLINE |
Note :
On the above lines No files need to recover and all the datafiles are Online.
13.Perform the log switch
|
1 2 3 |
SQL> alter system archive log current; System altered. |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
