Performing Database Point-in-Time Recovery in Oracle 19c RAC
In this article we will be doing PITR for Oracle RAC database on 19c as below.
Step 1=> Get current status of database as below.
SQL> select log_mode,flashback_on from gv$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
ARCHIVELOG NO
Step 2=> Create sample table with records as below.
[oracle@racsetn1 trace]$ sqlplus ABHI_TEST/Oracle_4U
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> select * from time_Data;
TIME_DATE
---------------------------------------------------------------------------
16-DEC-22 01.09.03.000000 PM
16-DEC-22 01.10.18.000000 PM
16-DEC-22 01.10.21.000000 PM
16-DEC-22 01.11.10.000000 PM
SQL>
Step3=> Get SEQUNECE# till where we will be doing PITR as below.
SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3;
SEQUENCE# FIRST_CHANGE# TO_CHAR(
---------- ------------- --------
9 3837409 20:04:23
9 3837413 20:04:25
10 3837441 20:04:44
10 3837445 20:04:45 <<<<< we will revert till this window.
Step4=> Insert few more records in sample table as below .
[oracle@racsetn1 trace]$ sqlplus ABHI_TEST/Oracle_4U
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> insert into time_Data values (sysdate);
1 row created.
SQL> /
..
..
SQL> commit;
Commit complete.
SQL> select * from time_Data;
TIME_DATE
---------------------------------------------------------------------------
16-DEC-22 01.09.03.000000 PM
16-DEC-22 01.10.18.000000 PM
16-DEC-22 01.10.21.000000 PM
16-DEC-22 01.11.10.000000 PM
16-DEC-22 03.12.32.000000 PM
16-DEC-22 08.06.19.000000 PM
16-DEC-22 08.06.20.000000 PM
16-DEC-22 08.06.20.000000 PM
16-DEC-22 08.06.21.000000 PM
16-DEC-22 08.06.21.000000 PM
16-DEC-22 08.06.21.000000 PM
11 rows selected.
Step5=>Check logfile sequence# details as below.
SQL>
SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from gv$log order by 3;
SEQUENCE# FIRST_CHANGE# TO_CHAR(
---------- --------------------- --------
10 3837445 20:04:45
10 3837445 20:04:45
11 3837655 20:08:05
11 3837655 20:08:05
11 3837660 20:08:07
11 3837660 20:08:07
12 3837678 20:08:11
12 3837678 20:08:11
8 rows selected.
Step6=> Take RMAN backup of database as below.
RMAN> RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/ora_home/bkp/%d_D_%T_%u_s%s_p%p'
DATABASE
PLUS ARCHIVELOG
FORMAT '/ora_home/bkp/%d_A_%T_%u_s%s_p%p';
RELEASE CHANNEL ch11;
backup current controlfile format '/ora_home/bkp/ctl_%d%u%s%u';
}
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ch11
….
..
channel ch11: finished piece 1 at 16-DEC-22
piece handle=/ora_home/bkp/TNT_A_20221216_cv1fi32e_s415_p1 tag=TAG20221216T200429 comment=NONE
channel ch11: backup set complete, elapsed time: 00:00:03
Finished backup at 16-DEC-22
..
Starting backup at 16-DEC-22
channel ch11: starting full datafile backup set
channel ch11: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/TNT/DATAFILE/system.282.1119135915
input datafile file number=00003 name=+DATA/TNT/DATAFILE/sysaux.283.1115903017
input datafile file number=00004 name=+DATA/TNT/DATAFILE/undotbs1.284.1115903031
input datafile file number=00007 name=+DATA/TNT/DATAFILE/users.285.1115903033
input datafile file number=00002 name=+DATA/TNT/DATAFILE/ro_ts.294.1117385373
….
time: 00:00:01
Finished backup at 16-DEC-22
released channel: ch11
….
piece handle=/ora_home/bkp/ctl_TNTd31fi333419d31fi333 tag=TAG20221216T200451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-22
SQL>
SQL> create pfile='/ora_home/bkp/pfile_TNT.ora' from spfile;
File created.
SQL> exit
Step 7=> Now we will DROP database as below.
[oracle@racsetn1 bkp]$ srvctl stop database -d TNT
[oracle@racsetn1 bkp]$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 671088640 bytes
Database Buffers 2063597568 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
SQL> alter system set cluster_database=FALSE scope=spfile;
System altered.
SQL> shut abort;
ORACLE instance shut down.
SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 671088640 bytes
Database Buffers 2063597568 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL>drop database;
Database dropped.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exit
Step 8=>Now we will Nomount DB and then restore Controlfile from Backup and then Mount database
[oracle@racsetn1 bkp]$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup nomount pfile='/ora_home/bkp/pfile_TNT.ora';
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 671088640 bytes
Database Buffers 2063597568 bytes
Redo Buffers 7876608 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racsetn1 bkp]$ rman target /
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TNT (not mounted)
RMAN> restore controlfile from '/ora_home/bkp/ctl_TNTd31fi333419d31fi333';
Starting restore at 16-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=TNT1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/TNT/CONTROLFILE/current.293.1123618745
output file name=+FRA/TNT/CONTROLFILE/current.734.1123618745
Finished restore at 16-DEC-22
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>exit.
Step 9=>We will restore and recover database till SEQUNECE# 10 as we checked above and then RESETLOGS as below.
(No need to catalog as this is already available at same path).
[oracle@racsetn1 bkp]$ rman target /
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TNT (DBID=1427195521, not open)
RMAN> RUN
{
SET UNTIL SEQUENCE 10;
RESTORE DATABASE;
RECOVER DATABASE;
}
2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 16-DEC-22
Starting implicit crosscheck backup at 16-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
…
…
List of Cataloged Files
=======================
File Name: +FRA/TNT/ARCHIVELOG/2022_12_16/thread_1_seq_10.791.1123618087
File Name: +FRA/TNT/ARCHIVELOG/2022_12_16/thread_2_seq_10.785.1123618089
File Name: +FRA/TNT/ARCHIVELOG/2022_12_16/thread_1_seq_11.800.1123618093
File Name: +FRA/TNT/ARCHIVELOG/2022_12_16/thread_1_seq_12.862.1123618417
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
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/TNT/DATAFILE/system.282.1119135915
channel ORA_DISK_1: restoring datafile 00002 to +DATA/TNT/DATAFILE/ro_ts.294.1117385373
channel ORA_DISK_1: restoring datafile 00003 to +DATA/TNT/DATAFILE/sysaux.283.1115903017
channel ORA_DISK_1: restoring datafile 00004 to +DATA/TNT/DATAFILE/undotbs1.284.1115903031
channel ORA_DISK_1: restoring datafile 00005 to +DATA/TNT/DATAFILE/undotbs2.290.1115904057
channel ORA_DISK_1: restoring datafile 00007 to +DATA/TNT/DATAFILE/users.285.1115903033
channel ORA_DISK_1: restoring datafile 00008 to +DATA/TNT/DATAFILE/ts_nolog.295.1119133025
channel ORA_DISK_1: restoring datafile 00009 to +DATA/TNT/DATAFILE/ts_nolog.296.1119117123
channel ORA_DISK_1: restoring datafile 00010 to +DATA/TNT/DATAFILE/tnt_test.297.1119134723
channel ORA_DISK_1: restoring datafile 00011 to +DATA/TNT/DATAFILE/undotbs01.298.1121735245
channel ORA_DISK_1: restoring datafile 00012 to +DATA/TNT/DATAFILE/undotbs02.299.1121735249
channel ORA_DISK_1: reading from backup piece /ora_home/bkp/TNT_D_20221216_d01fi32i_s416_p1
channel ORA_DISK_1: piece handle=/ora_home/bkp/TNT_D_20221216_d01fi32i_s416_p1 tag=TAG20221216T200433
…
Starting recover at 16-DEC-22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_12_16/thread_1_seq_9.796.1123617887
archived log for thread 2 with sequence 9 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_12_16/thread_2_seq_9.762.1123617887
archived log file name=+FRA/TNT/ARCHIVELOG/2022_12_16/thread_1_seq_9.796.1123617887 thread=1 sequence=9
archived log file name=+FRA/TNT/ARCHIVELOG/2022_12_16/thread_2_seq_9.762.1123617887 thread=2 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-DEC-22
RMAN>
RMAN>
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
RMAN>
RMAN> exit
Recovery Manager complete.
Step 10=> Switch to grid user and create required Path as below.
[oracle@racsetn1 bkp]$ srvctl config database -d TNT
Database unique name: TNT
Database name: TNT
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA/TNT/PARAMETERFILE/spfile.293.1115904179
Password file: +DATA/TNT/PASSWORD/pwdtnt.281.1115902969
Domain: ace2oracle.lab
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oper
Database instances: TNT1,TNT2
Configured nodes: racsetn1,racsetn2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@racsetn1 bkp]$ su - grid
[grid@racsetn1 ~]$ asmcmd
ASMCMD> cd DATA
ASMCMD> ls
DBTAR/
TEST/
TNT/
ASMCMD> cd TNT
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PASSWORD/
TEMPFILE/
ASMCMD> mkdir +DATA/TNT/PARAMETERFILE
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
PASSWORD/
TEMPFILE/
ASMCMD>
ASMCMD> exit
Step 11=>Change Controlfile path in pfile and create spfile as below.
*.control_files='+DATA/TNT/CONTROLFILE/current.293.1123618745','+FRA/TNT/CONTROLFILE/current.734.1123618745'#Restore Controlfile
[oracle@racsetn1 bkp]$ srvctl stop database -d TNT
[oracle@racsetn1 bkp]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 16 20:38:02 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/TNT/PARAMETERFILE/spfile.ora' from pfile='/ora_home/bkp/pfile_TNT.ora';
File created.
SQL> exit
Disconnected
Step 12=>Modify spfile in cluster as below.
[oracle@racsetn1 bkp]$ srvctl modify database -db TNT -spfile '+DATA/TNT/PARAMETERFILE/spfile.ora'
[oracle@racsetn1 bkp]$ srvctl config database -d TNT
Database unique name: TNT
Database name: TNT
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA/TNT/PARAMETERFILE/spfile.ora
Password file: +DATA/TNT/PASSWORD/pwdtnt.281.1115902969
Domain: ace2oracle.lab
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oper
Database instances: TNT1,TNT2
Configured nodes: racsetn1,racsetn2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed.
Step13=> Now we start database on both nodes as below.
[oracle@racsetn1 bkp]$ srvctl start database -d TNT
[oracle@racsetn1 bkp]$ ps -eaf |grep d.bin
root 2171 1 0 11:23 ? 00:02:34 /grid_home/app/19c/grid/bin/ohasd.bin reboot
root 3258 1 0 11:23 ? 00:01:29 /grid_home/app/19c/grid/bin/orarootagent.bin
Step 14=> Verfiy if database is UP.
[oracle@racsetn1 bkp]$ /grid_home/app/19c/grid/bin/crsctl stat res -t
ora.tnt.db
1 ONLINE ONLINE racsetn1 Open,HOME=/ora_home/
app/19c/db,STABLE
2 ONLINE ONLINE racsetn2 Open,HOME=/ora_home/
app/19c/db,STABLE
Step 15=> Verify Sample schema data as below.
[oracle@racsetn1 trace]$ sqlplus ABHI_TEST/Oracle_4U
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from time_data;
TIME_DATE
---------------------------------------------------------------------------
16-DEC-22 01.09.03.000000 PM
16-DEC-22 01.10.18.000000 PM
16-DEC-22 01.10.21.000000 PM
16-DEC-22 01.11.10.000000 PM
16-DEC-22 03.12.32.000000 PM <<<< SO our data is available till which we recovred.
SQL>
Step 16=> Check incarnation(optional).
[oracle@racsetn1 ~]$ rman target /
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TNT (DBID=1427195521)
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TNT 1427195521 PARENT 1 17-APR-19
2 2 TNT 1427195521 PARENT 1920977 20-SEP-22
3 3 TNT 1427195521 PARENT 3119366 26-OCT-22
4 4 TNT 1427195521 PARENT 3137448 26-OCT-22
5 5 TNT 1427195521 PARENT 3147316 26-OCT-22
6 6 TNT 1427195521 PARENT 3661409 28-NOV-22
7 7 TNT 1427195521 PARENT 3792834 16-DEC-22
8 8 TNT 1427195521 CURRENT 3837442 16-DEC-22
RMAN> exit
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444