SQL LOADER

DESIGNATION:

  • SQL * LOADER loads data from external files into tables of an oracle database.
  • We do not insert the data into the table using SQL * LOADER directly.
  • We need a control file for inserting the data.
  • SQL * LOADER is used to just execute the commands of the control file.
  • The flatfile saving process is normally we will save .txt files.
  • The Control file saving process is normally we will save .ctl files.

BAD FILE:

  • A bad file is used to store the rejected records.
  • The rejected records will store in a bad file automatically.
  • If you wrongly type the data into a flat file, you will surely get some errors. So that error during SQL * LOADER process which stores in bad file.

TYPES OF FLAT FILE:

Variable record flatfile

This is with delimeter

eg., 101, abc, 2000
Fixed record flatfile

This is without delimeter

eg., 101abc2000

The following picture illustrates the SQL * LOADER process:

sql loadet

 

SYNTAX:

$sqlldr <space> userid = username / password

control = ‘path of the controlfile’ <space> log = test.log

Creating a controlfile for variable record flatfile:

BY USING INSERT LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

insert / append / truncate / replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

OVERALL STEPS:

To execute the SQL * LOADER tool, you need at least three files.

  • The input data files store delimited data
  • The parameter file stores the location of the input and output files.
  • The control file contains the specification on how data is loaded.

Step 1: Create the table at the database level

View the table :

Step 2: Create the text document by using the vi editor in os level

View the text file

Step 3: Create the control file by using the vi editor in the os level

View the control file 

Step 4: Execute the SQL * LOADER syntax

Step 5: View the  table

 

BY USING APPEND LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

append

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the APPEND output:

BY USING TRUNCATE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

truncate

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the TRUNCATE output:

BY USING REPLACE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the REPLACE output:

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

DATABASE SWITCHOVER ORACLE 19C USING DGMGRL

DATABASE SWITCHOVER ORACLE 19C USING DGMGRL

Description:
In this blog, we are going to see data guard switch over by using data guard broker DGMGRL.

Data Guard Switchover:

  • A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss.
  • This is typically done for planned maintenance of the primary system.
  • During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
  • The transition occurs without having to re-enable either database.

Overall steps:

1. Connect the primary database with DGMGRL Data Guard broker.
2. Switch over primary to standby database.
3. Connect Standby database.
4. Switch over standby to primary.

Step 1: Connect the primary database with the DGMGRL Data Guard broker.

[oratest@oracle admin]$ dgmgrl sys/oracle@orcl

DGMGRL for Linux: Release 19.0.0.0.0 - Production on 
Fri Nov 12 13:19:44 2021 Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.Welcome to DGMGRL, type "help" for information.

Connected to "orcl"

Connected as SYSDBA.

Step 2: Switch over primary to the standby database.

DGMGRL> switchover to orcl_stby;
Performing switchover NOW, please wait...

Operation requires a connection to instance "orcl" on database 
"orcl_stby"

Connecting to instance "orcl"...

Connected as SYSDBA.

New primary database "orcl_stby" is opening...

Operation requires start up of instance "orcl" on database "orcl"

Starting instance "orcl"...ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "orcl_stby"

DGMGRL>

Let's switch back to the original primary. Connect to the 
new primary (orcl_stby) and switchover to the new standby database (orcl).

Step 3: Connect the Standby database.

[oratest@oracle admin]$ dgmgrl sys/oracle@orcl_stby

DGMGRL for Linux: Release 19.0.0.0.0 - Production on 
Fri Nov 12 13:31:56 2021Version 19.9.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. 
All rights reserved.
Welcome to DGMGRL, type "help" for information.

Connected to "orcl_stby"
Connected as SYSDBA.

Step 4: Switch over standby to primary

DGMGRL> switchover to orcl ;

Performing switchover NOW, please wait...

Operation requires a connection to instance "orcl" on 
database "orcl"

Connecting to instance "orcl"...

Connected as SYSDBA.

New primary database "orcl" is opening...

Operation requires start up of instance "orcl" on database 
"orcl_stby"

Starting instance "orcl"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "orcl"

DGMGRL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Data Guard Manual Switchover

Oracle Data Guard Manual Switchover.

Description:
          In this blog, we are going to see step by step process of oracle data guard manual switchover process physical standby to primary.

Switch Over:
          A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-enable either database.

Overall Steps:

1. Check Primary and Standby database role and open_mode.
2. Change nls_date_format and check current log sync status.
3. Check switch over status both primary and standby servers.
4. Primary side switch over process.
5. Standby side switch over process.
6. Check the log sync status both sides.
7. New primary database parameter configuration.
8. Switch log files and check the log sync status.

Step 1:- Check primary and standby database role and open_mode.

SQL> select name,open_mode,database_role from v$database;
Step 2:- Change nls_date_format and check current log sync status.

check the log sequence on both primary and standby sides.
SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
Step 3:- Check switch over status both primary and standby servers.
SQL> select switchover_status from v$database;

Step 4:- Primary side switch over process.

Switch over to standby.
SQL> alter database commit to switchover to standby;
Database altered.
Bounce the database.
SQL> shut immediate
SQL> startup nomount
Mount standby database.
SQL> alter database mount standby database;
Start MRP process.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
check the database role.
SQL> select name,open_mode,database_role from v$database;
Step 5:- Standby side switchover process:

Stop the MRP process.
SQL> alter database recover managed standby database cancel;
Database altered.
Switch Over to Primary.
SQL> alter database commit to switchover to primary;
Database altered.
Check database role changed or not and open_mode.
SQL> select name,open_mode,database_role from v$database;
Open the database.
SQL> alter database open;
Database altered.
Check the database role and open_mode.
SQL> select name,open_mode,database_role from v$database;
Step 6:- Check the log sync status both sides.
SQL> archive log list

Check both side log sync status.

Step 7:- New primary database parameter configuration for log sync.
SQL> alter system set log_archive_dest_1 =’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=local’ scope=spfile;
System altered.

SQL> alter system set log_archive_dest_2 =’service=cloud async valid_for=(online_logfiles,primary_role) db_unique_name=cloud’ scope=spfile;
System altered.

SQL> alter system set db_file_name_convert=’/u01/app/oracle/oradata/LOCAL/datafile/’,’/u01/app/oracle/oradata/CLOUD/datafile/’ scope=spfile;
System altered.

SQL> alter system set log_file_name_convert=’/u01/app/oracle/oradata/LOCAL/onlinelog/’,’/u01/app/oracle/oradata/CLOUD/onlinelog/’ scope=spfile;
System altered.

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(local,cloud)’ SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET fal_server=’cloud’ SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET fal_client=’local’ SCOPE=both;
System altered.

SQL> select DEST_NAME,STATUS,ERROR from v$ARCHIVE_DEST where status!=’INACTIVE’;
DEST_NAME
——————————————————————————–
STATUS ERROR
——— —————————————————————–
LOG_ARCHIVE_DEST_1
VALID
Step 8:-Switch log files and check the log sync status.
Primary side switch logfiles:
enable log_archive_dest_state_2.
SQL> alter system switch logfile;
SQL> archive log list
Check log file sync status on standby side:-
Restart the MRP process.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
check log sync status.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
APPL.SEQUENCE# “Last Sequence Applied”,
(ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Data Guard Manual Failover

Oracle Data Guard Manual Failover

Description:
          In this blog, we are going to see step by step process on the oracle data guard manual failover process.

Failover:
          A failover is a role transition in which one of the standby databases is transitioned to the primary role after the primary database fails or has become unreachable. A failover may or may not result in data loss depending on the protection mode in effect at the time of the failover.

Data Guard Configuration Details:-
Overall Steps:-

1. Check the database role,open_mode in standby server.
2. Cancel MRP process.
3. Change Standby to Primary Database.
4. Bounce the database.

Step 1:- Check the database role,open_mode in the standby server.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
CLOUD MOUNTED PHYSICAL STANDBY

Step 2:- Cancel MRP process:

Check back ground process:
[oracle@primary dbs]$ ps -ef | grep pmon
oracle 10854 1 0 13:26 ? 00:00:00 ora_pmon_cloud
oracle 27875 4420 0 14:37 pts/1 00:00:00 grep –color=auto pmon

Cancel the MRP process:
SQL> recover managed standby database cancel;
Media recovery complete.

Finish the MRP process:
SQL> alter database recover managed standby database finish;
Database altered.
Step 3:- Change Standby to Primary Database.
SQL> alter database activate standby database;
Database altered.

Check the role changed or not:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
CLOUD MOUNTED PRIMARY
Step 4:- Bounce the Database.
SQL> shut immediate
SQL> startup
Check the database role and status.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
CLOUD READ WRITE PRIMARY


Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Standby Database Refresh using RMAN Incremental SCN Backup

Standby Database Refresh Using RMAN SCN Backup

Description :-
In this Blog we are going to see standby log sync failure and sync the standby database using incremental scn backup.

Refresh Using RMAN SCN Backup:

In case of any log sync gap has been occur in standby database or archivelogs received but not applied in standby database we can refresh the standby database using SCN based RMAN backup.

Overall Steps:

1. Check the archivelog GAP both primary and standby database.
2. Check the SCN number in standby.
3. Take RMAN incremental SCN backup in primary database.
4. Create Standby Control file in primary database.
5. Transfer Backup and control file to standby.
6. Replace the standby control file.
7. Catalog backuppiece on standby database.
8. cancel MRP process.
9. recover database using backup piece.

 

Step 1:- Check the archive log GAP for both primary and standby database.
Primary Database:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE


SYSTEM READ WRITE PRIMARY

SQL> select max(sequence#) from v$archived_log where archived=’YES’;

MAX(SEQUENCE#)
————–
34

Standby database:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE


SYSTEM MOUNTED PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where archived=’YES’;

MAX(SEQUENCE#)
————–
30

Step 2:- Check the SCN number in Standby.

SQL> select current_scn from v$database;

CURRENT_SCN
———–
2058645

Step 3:-Take RMAN incremental SCN backup in the primary database.

RMAN> backup incremental from SCN 2058645 database format '/u02/oracle/backup/DELL_BACKUP/database_%d_%u_%s';

Starting backup at 24-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_system_jwbgjd16_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_sysaux_jwbgktdq_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_users_jwbglnno_.dbf
channel ORA_DISK_1: starting piece 1 at 24-DEC-21
channel ORA_DISK_1: finished piece 1 at 24-DEC-21
piece handle=/u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0d0hil0t_13 tag=TAG20211224T230253 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-DEC-21
channel ORA_DISK_1: finished piece 1 at 24-DEC-21
piece handle=/u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0e0hil21_14 tag=TAG20211224T230253 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-DEC-21

RMAN> exit

Step 4:-Create Standby Control file in primary database

SQL> alter database create standby controlfile as ‘/u02/oracle/backup/DELL_BACKUP/std_control.ctl’;

Database altered.

Step 5:- Transfer Backup and control file to standby

[oracle@oracle DELL_BACKUP]$ scp * [email protected]:/u02/backup
[email protected]’s password:
database_SYSTEM_0d0hil0t_13 100% 74MB 37.0MB/s 00:02
database_SYSTEM_0e0hil21_14 100% 10MB 46.6MB/s 00:00
[oracle@oracle DELL_BACKUP]$ ll
total 97160
-rw-rw—-. 1 oracle oracle 77619200 Dec 24 23:03 database_SYSTEM_0d0hil0t_13
-rw-rw—-. 1 oracle oracle 10977280 Dec 24 23:03 database_SYSTEM_0e0hil21_14
-rw-rw—-. 1 oracle oracle 10895360 Dec 24 23:08 std_control.ctl
[oracle@oracle DELL_BACKUP]$ scp std_control.ctl [email protected]:/u02/backup
[email protected]’s password:
std_control.ctl 100% 10MB 59.1MB/s 00:00
[oracle@oracle DELL_BACKUP]$ scp std_control.ctl [email protected]:/u02/backup
[email protected]’s password:
std_control.ctl 100% 10MB 62.2MB/s 00:00
[oracle@oracle DELL_BACKUP]$

Step 6:- Replace the control file start the database in the mount stage.

[oracle@local MONITOR]$ mv std_control.ctl standby1.ctl

SQL> startup mount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 754974720 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> exit

Step 7:- Catalog backup piece on standby database.

[oracle@local dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Dec 24 12:45:33 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: SYSTEM (DBID=1671570, not open)

RMAN> catalog backuppiece ‘/u02/backup/database_SYSTEM_0d0hil0t_13’;

Starting implicit crosscheck backup at 24-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 24-DEC-21

Starting implicit crosscheck copy at 24-DEC-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-DEC-21

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_7_jwbnc21n_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_6_jwbnc2jy_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_9_jwbnc3bo_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_10_jwbncmjz_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_11_jwbnq5ng_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_13_jwbnq5oh_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_12_jwbnq5ol_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_15_jwbnqc96_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_18_jwbtbchp_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_19_jwbtbcjq_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_20_jwbtbcyb_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_17_jwbtbd6d_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_16_jwbtbd6j_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_21_jwbtbd71_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_23_jwbtbdpg_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_24_jwbv467w_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_26_jwbv469p_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_25_jwbv46cg_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_27_jwbv4720_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/28.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/29.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_28_jwbvp1mg_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_29_jwbvp2ql_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_32_jwbvrx0o_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/30.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/31.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc

channel ORA_DISK_1: cataloged backup piece
backup piece handle=/u02/backup/database_SYSTEM_0d0hil0t_13 RECID=8 STAMP=1092142036

RMAN> catalog backuppiece ‘/u02/backup/database_SYSTEM_0e0hil21_14’;

channel ORA_DISK_1: cataloged backup piece
backup piece handle=/u02/backup/database_SYSTEM_0e0hil21_14 RECID=9 STAMP=1092142060

RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 10.20M DISK 00:00:00 24-DEC-21
BP Key: 1 Status: EXPIRED Compressed: NO Tag: TAG20211224T092140
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/autobackup/2021_12_24/o1_mf_s_1092129700_jwbjydcj_.bkp
SPFILE Included: Modification time: 24-DEC-21
SPFILE db_unique_name: SYSTEM
Control File Included: Ckp SCN: 2032248 Ckp time: 24-DEC-21

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2 173.98M DISK 00:00:02 24-DEC-21
BP Key: 2 Status: EXPIRED Compressed: NO Tag: TAG20211224T115303
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_annnn_TAG20211224T115303_jwbst7c2_.bkp

List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 2008822 24-DEC-21 2032572 24-DEC-21
1 6 2032572 24-DEC-21 2037484 24-DEC-21
1 7 2037484 24-DEC-21 2037797 24-DEC-21
1 8 2037797 24-DEC-21 2040754 24-DEC-21
1 9 2040754 24-DEC-21 2040789 24-DEC-21
1 10 2040789 24-DEC-21 2040796 24-DEC-21
1 11 2040796 24-DEC-21 2041066 24-DEC-21
1 12 2041066 24-DEC-21 2041524 24-DEC-21
1 13 2041524 24-DEC-21 2041533 24-DEC-21
1 14 2041533 24-DEC-21 2041659 24-DEC-21
1 15 2041659 24-DEC-21 2042175 24-DEC-21
1 16 2042175 24-DEC-21 2054365 24-DEC-21
1 17 2054365 24-DEC-21 2054426 24-DEC-21
1 18 2054426 24-DEC-21 2054437 24-DEC-21
1 19 2054437 24-DEC-21 2054442 24-DEC-21
1 20 2054442 24-DEC-21 2054630 24-DEC-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Incr 0 1.14G DISK 00:00:48 24-DEC-21
BP Key: 3 Status: EXPIRED Compressed: NO Tag: TAG20211224T115310
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_nnnd0_TAG20211224T115310_jwbstgsy_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf
3 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf
4 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
7 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 26.50K DISK 00:00:00 24-DEC-21
BP Key: 4 Status: EXPIRED Compressed: NO Tag: TAG20211224T115407
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_annnn_TAG20211224T115407_jwbsw7qd_.bkp

List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 21 2054630 24-DEC-21 2054749 24-DEC-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 10.20M DISK 00:00:01 24-DEC-21
BP Key: 5 Status: EXPIRED Compressed: NO Tag: TAG20211224T115408
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/autobackup/2021_12_24/o1_mf_s_1092138848_jwbsw9gv_.bkp
SPFILE Included: Modification time: 24-DEC-21
SPFILE db_unique_name: SYSTEM
Control File Included: Ckp SCN: 2054766 Ckp time: 24-DEC-21

BS Key Type LV Size
------- ---- -- ----------
6 Incr 74.02M
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf
3 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf
4 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
7 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf

Backup Set Copy #1 of backup set 6
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:28 24-DEC-21 NO TAG20211224T230253

List of Backup Pieces for backup set 6 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
6 1 EXPIRED /u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0d0hil0t_13

Backup Set Copy #2 of backup set 6
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:28 24-DEC-21 NO TAG20211224T230253

List of Backup Pieces for backup set 6 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
8 1 AVAILABLE /u02/backup/database_SYSTEM_0d0hil0t_13

BS Key Type LV Size
------- ---- -- ----------
7 Incr 10.45M
Control File Included: Ckp SCN: 2100521 Ckp time: 24-DEC-21

Backup Set Copy #1 of backup set 7
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 24-DEC-21 NO TAG20211224T230253

List of Backup Pieces for backup set 7 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
7 1 EXPIRED /u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0e0hil21_14

Backup Set Copy #2 of backup set 7
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 24-DEC-21 NO TAG20211224T230253

List of Backup Pieces for backup set 7 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
9 1 AVAILABLE /u02/backup/database_SYSTEM_0e0hil21_14

Step 8:- Cancel MRP Process.

SQL>  recover managed standby database cancel;
Media recovery complete.

Step 9:- Recover standby database using the backup piece.

RMAN> RECOVER DATABASE;

Starting recover at 24-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/database_SYSTEM_0d0hil0t_13
channel ORA_DISK_1: piece handle=/u02/backup/database_SYSTEM_0d0hil0t_13 tag=TAG20211224T230253
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery

archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc
archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_35_jwbwwc6o_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc thread=1 sequence=33
archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc thread=1 sequence=34
archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_35_jwbwwc6o_.arc thread=1 sequence=35
media recovery complete, elapsed time: 00:00:02
Finished recover at 24-DEC-21

RMAN> exit

Step 10:- enable the MRP process.

SQL> alter database recover managed standby database disconnect from session ;

Step 11:- Defer and enable dest 2 in primary database

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Step 12:- Check the log sync primary and standby.

Primary side:

SQL> select max(sequence#) from v$archived_log where archived=’YES’;

MAX(SEQUENCE#)
————–
37

Standby side:

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 2 3 4

Thread Last Sequence Received Last Sequence Applied Difference


1                               37                             37                       0

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8