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


    

RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

 

Below error i got while taking RMAN backup:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/12/2021 03:29:07
RMAN-06183: datafile or datafile copy +ORA_DATA/wwibetest_bosqeumcsdb/datafile/undotbs1.779.1077535587 (file number 3) larger than MAXSETSIZE

Finding and solution:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 30 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/wwi/wwdb/db/oracle/product/11.2.0.4/db_1/dbs/snapcf_WWIBEMCS.f’; # default

RMAN> CONFIGURE MAXSETSIZE TO 35 G;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 30 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 35 G;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 35 G;

 

Now I ran the RMAN backup it went successful.

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

 

RMAN Database Restore ASM

RMAN Database Restore ASM

Description:
In this blog, we are going to see take RMAN backup in asm storage and restore the database to another server.

RMAN Restore ASM:
Take the backup using RMAN in source host copy the backup into destination host, using that backup files to restore the database.

Overall Steps:

Source server:
1. Backup Database using RMAN
2. Create pfile from spfile
3. Copy the backup files to the destination server

Destination server:
1. Edit pfile
2. Create Required Folders in destination server
3. Add the database entry in oratab
4. Startup using pfile Nomount stage
5. Restore control file
6. Mount database
7. Catalog backup pieces
8. Restore and recover the database
9. Change dbname/DBID uisng NID
10. verify the database name and id.

Source Server:

Step 1: Take backup using RMAN:

Create backup directory:

Backup script:
run {
allocate channel t1 type disk;
allocate channel t1 type disk;
allocate channel t1 type disk;
backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
release channel t1;
}
sql ‘alter system archive log current’;
run {
allocate channel a1 type disk;
backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
release channel a1;
}
run {
allocate channel c1 type disk;
backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
release channel c1;
}
exit

Connect RMAN and execute the script:

[oracle@asm ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:01:54 2022
Version 19.3.0.0.0

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

connected to target database: ORAASM (DBID=1977555372)

RMAN> @backup.rcv

RMAN> run {
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> allocate channel t3 type disk;
5> backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
6> release channel t1;
7> release channel t2;
8> release channel t3;
9> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=78 device type=DISK

allocated channel: t2
channel t2: SID=89 device type=DISK

allocated channel: t3
channel t3: SID=88 device type=DISK

Starting backup at 21-JAN-22
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORAASM/DATAFILE/system.257.1092813819
channel t1: starting piece 1 at 21-JAN-22
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORAASM/DATAFILE/sysaux.258.1092813853
input datafile file number=00014 name=+DATA/ORAASM/DATAFILE/tblspace.281.1093044531
input datafile file number=00007 name=+DATA/ORAASM/DATAFILE/users.260.1092813869
channel t2: starting piece 1 at 21-JAN-22
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869
input datafile file number=00015 name=+DATA/ORAASM/DATAFILE/test.282.1094538795
input datafile file number=00013 name=+DATA/ORAASM/DATAFILE/tblspace.280.1093044407
channel t3: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_080jqqo1_8 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:16
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375
input datafile file number=00011 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_060jqqns_6 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:01:10
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_070jqqns_7 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:01:13
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375
input datafile file number=00012 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459
channel t2: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_090jqqop_9 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:49
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0a0jqqq2_10 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:12
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0d0jqqqg_13 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0b0jqqq9_11 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0c0jqqqf_12 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:26
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544235.283.1094544237 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: t1

released channel: t2

released channel: t3

RMAN> sql ‘alter system archive log current’;
sql statement: alter system archive log current

RMAN> run {
2> allocate channel a1 type disk;
3> backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
4> release channel a1;
5> }
allocated channel: a1
channel a1: SID=78 device type=DISK

Starting backup at 21-JAN-22
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=1 STAMP=1094544242
input archived log thread=1 sequence=17 RECID=2 STAMP=1094544243
channel a1: starting piece 1 at 21-JAN-22
channel a1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/arch_ORAASM_0f0jqqrk_15 tag=TAG20220121T080403 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544247.289.1094544249 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: a1

RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
4> release channel c1;
5> }
allocated channel: c1
channel c1: SID=78 device type=DISK

Starting backup at 21-JAN-22
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 21-JAN-22
channel c1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/Control_ORAASM_0h0jqqrs_17 tag=TAG20220121T080412 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544256.290.1094544257 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: c1

RMAN> exit

Recovery Manager complete.

Check the backup file location:

Step 2: Create pfile from spfile

create pfile=’/home/oracle/initasmora.ora’ from spfile;
Step 3: Copy the backup file into destination server

[oracle@asm backup]$ scp * [email protected]:/u01/backup/

Destination server:

Step 1: Edit pfile
Change DB_UNIQUE_NAME in the pfile:

*.audit_file_dest=’/u01/app/oracle/admin/oraasm/adump’
*.audit_trail=’db’
*.compatible=’19.0.0′
*.control_files=’+DATA/ASMORA/CONTROLFILE/current.262.1092813933′,’+DATA/ASMORA/CONTROLFILE/current.261.1092813933′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_name=’oraasm’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=12732m
*.db_unique_name=’asmora’                —–>change db unique name
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraasmXDB)’
*.enable_pluggable_database=true
*.local_listener=’LISTENER_ORAASM’
*.open_cursors=300
*.pga_aggregate_target=629m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1886m
*.undo_tablespace=’UNDOTBS1′
[oracle@asm dbs]$

Step 2: Create Required Folders

Create required folders in the destination server
[oracle@asmnew ~]$ mkdir -p /u01/app/oracle/admin/ASMORA/adump/

Step 3: Add the Database entry in oratab file.

ASMORA:/u01/app/oracle/product/19.0.0/dbhome_1:N

Step 4: startup database using pfile nomount stage

[oracle@asmnew asmora]$ export ORACLE_SID=asmora
[oracle@asmnew asmora]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jan 21 08:18:06 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/asmora/initasmora.ora’;
ORACLE instance started.

Total System Global Area 1979709696 bytes
Fixed Size 9136384 bytes
Variable Size 452984832 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7639040 bytes

Step 5: restore control file from backup location

[oracle@asmnew asmora]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:22:04 2022
Version 19.3.0.0.0

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

connected to target database: ORAASM (not mounted)

RMAN> restore controlfile from ‘/u01/share/backup/Control_ORAASM_0h0jqqrs_17’;

Starting restore at 21-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/ORAASM/CONTROLFILE/current.262.1092813933
output file name=+DATA/ORAASM/CONTROLFILE/current.261.1092813933
Finished restore at 21-JAN-22

RMAN>

Step 6: Mount the database.

SQL> alter database mount;

Database altered.

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

NAME OPEN_MODE DB_UNIQUE_NAME


ORAASM MOUNTED asmora

Step 7: Catalog backup files

CATALOG BACKUPPIECE ‘/u01/asmora/arch_ORAASM_0f0jqqrk_15’;
CATALOG BACKUPPIECE ‘/u01/asmora/Control_ORAASM_0h0jqqrs_17’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_030jqo9c_3’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_060jqqns_6’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_070jqqns_7’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_080jqqo1_8’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_090jqqop_9’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0a0jqqq2_10’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0b0jqqq9_11’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0c0jqqqf_12’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0d0jqqqg_13’;

Step 8: Restore and recover database

List backup archivelogs

RMAN> list backup of archivelog all;

recover database using rman

run
{
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
ALLOCATE CHANNEL d2 DEVICE TYPE disk;
set newname for datafile 1 to ‘+DATA’;
set newname for datafile 2 to ‘+DATA’;
set newname for datafile 3 to ‘+DATA’;
set newname for datafile 4 to ‘+DATA’;
set newname for datafile 5 to ‘+DATA’;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_3.268.1092813965”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_2.266.1092813951”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_1.265.1092813949”
to ”+DATA”” ;
SET UNTIL SEQUENCE 12; <— 11+1
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

check logfile and database role

Step 9:change database name and ID

modify pfile
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

add this controlfile entry to pfile

SQL> startup nomount pfile='initasmora.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount;

Database altered.

SQL>

set db name and id:

[oracle@asm asmora]$ . oraenv
ORACLE_SID = [asmora] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@asm asmora]$ nid target=sys dbname=asmora

DBNEWID: Release 19.0.0.0.0 – Production on Fri Jan 21 09:09:50 2022

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

Password:
Connected to database ORAASM (DBID=1977555372)

Connected to server version 19.3.0

Control Files in database:
+DATA/ASMORA/CONTROLFILE/current.262.1092813933
+DATA/ASMORA/CONTROLFILE/current.261.1092813933

Change database ID and database name ORAASM to ASMORA? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1977555372 to 65466533
Changing database name from ORAASM to ASMORA
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – modified
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – modified
Datafile +DATA/ORAASM/DATAFILE/system.257.109281381 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/sysaux.258.109281385 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/undotbs1.259.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.109281441 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/users.260.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.109281545 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.280.109304440 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.281.109304453 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/test.282.109453879 – dbid changed, wrote new name
Datafile +DATA/ORAASM/TEMPFILE/temp.269.109281404 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CB418C2B91B66E053867EA8C0C5A0/TEMPFILE/temp.273.109281445 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/TEMPFILE/temp.278.109281541 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – dbid changed, wrote new name
Instance shut down

Database name changed to ASMORA.
Modify parameter file and generate a new password file before restarting.
Database ID for database ASMORA changed to 65466533.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

[oracle@asm asmora]$

Step 10: check the DB name and id

SQL> alter database open resetlogs;

Database altered.

SQL> select name, open_mode, db_unique_name, dbid from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME DBID


ASMORA READ WRITE asmora 65466533

 

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

ASM TOOLS and Configuration Basic Commands.

1. check the asm process running:

[oracle@asm ~]$ ps -ef|grep smon
oracle 8648 23558 0 16:52 pts/0 00:00:00 grep –color=auto smon
oracle 9349 1 0 09:50 ? 00:00:00 ora_smon_oraasm
oracle 30582 1 0 Jan01 ? 00:00:02 asm_smon_+ASM
[oracle@asm ~]$ ps -ef|grep d.bin
oracle 10968 23558 0 16:54 pts/0 00:00:00 grep –color=auto d.bin
oracle 26445 1 0 Jan01 ? 00:09:46 /u01/app/grid/bin/ohasd.bin reboot
oracle 26662 1 0 Jan01 ? 00:12:17 /u01/app/grid/bin/oraagent.bin
oracle 26733 1 0 Jan01 ? 00:04:50 /u01/app/grid/bin/evmd.bin
oracle 26796 26733 0 Jan01 ? 00:04:47 /u01/app/grid/bin/evmlogger.bin -o /u01/app/grid/log/[HOSTNAME]/evmd/evmlogger.info -l /u01/app/grid/log/[HOSTNAME]/evmd/evmlogger.log
oracle 27085 1 0 Jan01 ? 00:00:02 /u01/app/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 30264 1 0 Jan01 ? 00:05:25 /u01/app/grid/bin/cssdagent
oracle 30302 1 0 Jan01 ? 00:05:13 /u01/app/grid/bin/ocssd.bin
[oracle@asm ~]$ hostname
asm.localdomain
[oracle@asm ~]$ uname -r
3.10.0-957.el7.x86_64

2. check cluster resource status:

[oracle@asm ~]$ export PATH=$GRID_HOME/bin:$PATH
[oracle@asm ~]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE asm STABLE
ora.LISTENER.lsnr
ONLINE ONLINE asm STABLE
ora.asm
ONLINE ONLINE asm Started,STABLE
ora.ons
OFFLINE OFFLINE asm STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 ONLINE ONLINE asm STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE asm STABLE
ora.oraasm.db
1 ONLINE ONLINE asm Open,HOME=/u01/app/o
racle/product/19.0.0
/dbhome_1,STABLE
——————————————————————————–

3. check database running status and start stop database:

[oracle@asm ~]$ srvctl status database -d oraasm
Database is running.
[oracle@asm ~]$

srvctl stop database -d oraasm
srvctl start database -d oraasm

4. check oracle home in grid:

[oracle@asm ~]$ . grid.env
[oracle@asm ~]$ env |grep ORA
ORACLE_SID=+ASM
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=asm.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/grid
[oracle@asm ~]$

5. check oracle home in database:

[oracle@asm ~]$ . db.env
[oracle@asm ~]$ env |grep ORA
ORACLE_SID=oraasm
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=asm.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@asm ~]$

6. List the disks:

[root@asm ~]# cd /dev/
[root@asm dev]# ll sd*
brw-rw—-. 1 root disk 8, 0 Dec 31 10:45 sda
brw-rw—-. 1 root disk 8, 1 Dec 31 10:45 sda1
brw-rw—-. 1 root disk 8, 2 Dec 31 10:45 sda2
brw-rw—-. 1 root disk 8, 3 Dec 31 10:45 sda3
brw-rw—-. 1 root disk 8, 4 Dec 31 10:45 sda4
brw-rw—-. 1 root disk 8, 5 Dec 31 10:45 sda5
brw-rw—-. 1 root disk 8, 16 Dec 31 11:57 sdb
brw-rw—-. 1 root disk 8, 17 Dec 31 12:00 sdb1
brw-rw—-. 1 root disk 8, 32 Dec 31 11:58 sdc
brw-rw—-. 1 root disk 8, 33 Dec 31 12:00 sdc1

ASM TOOLS:
INSTALLATION TOOLS -oracleasm
ADMINSTRATION – asmcmd
DEBUGGING – kfod kfed ocrcheck

7. scan and list the disk using oracleasm:
[root@asm dev]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks…
Scanning system for ASM disks…
[root@asm dev]# oracleasm listdisks
DISK1
DISK2
[root@asm dev]#

8. asmcmd tool:

[oracle@asm ~]$ asmcmd
ASMCMD>
ASMCMD>

I. list the disk group
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 30712 25860 0 25860 0 N DATA/
ASMCMD>

II. list datafile,controlfile

ASMCMD> cd DATA/
ASMCMD> ls
ASM/
ORAASM/
orapwasm
ASMCMD> cd ORAASM
ASMCMD> ls
86B637B62FE07A65E053F706E80A27CA/
CONTROLFILE/
D47CB418C2B91B66E053867EA8C0C5A0/
D47CEAB72C2F2513E053867EA8C0538D/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.258.1092813853
SYSTEM.257.1092813819
UNDOTBS1.259.1092813869
USERS.260.1092813869
ASMCMD> pwd
+DATA/ORAASM/DATAFILE
ASMCMD>

9. check css,has status:

[oracle@asm ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is online
[oracle@asm ~]$ crsctl check css
CRS-4529: Cluster Synchronization Services is online
[oracle@asm ~]$

10. check has version:

[oracle@asm ~]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]
[oracle@asm ~]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [19.0.0.0.0]
[oracle@asm ~]$

11. asmca —> GUI for create and manage diskgroup:

12. KFOD – discovering disks with the help of asm disk strings:
KFED – discovering disk headers

[oracle@asm ~]$ cd $ORACLE_HOME/bin
[oracle@asm bin]$ pwd
/u01/app/grid/bin
[oracle@asm bin]$ ll kfod
-rwxrwxr-x. 1 oracle oinstall 11453 Dec 31 12:22 kfod
[oracle@asm bin]$ ll kfed
-rwxrwxr-x. 1 oracle oinstall 142432 Dec 31 12:20 kfed

[oracle@asm bin]$ kfod status=TRUE disks=ALL
——————————————————————————–
Disk Size Header Path User Group
================================================================================
1: 20479 MB MEMBER /dev/oracleasm/disks/DISK1 oracle oinstall
2: 10239 MB MEMBER /dev/oracleasm/disks/DISK2 oracle oinstall
——————————————————————————–
ORACLE_SID ORACLE_HOME
================================================================================
+ASM /u01/app/grid
[oracle@asm bin]$

[oracle@asm bin]$ kfed read /dev/oracleasm/disks/DISK1 | egrep ‘name|size|type’
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfdhdb.dskname: DATA_0000 ; 0x028: length=9
kfdhdb.grpname: DATA ; 0x048: length=4
kfdhdb.fgname: DATA_0000 ; 0x068: length=9
kfdhdb.secsize: 512 ; 0x0b8: 0x0200
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000
kfdhdb.ausize: 4194304 ; 0x0bc: 0x00400000
kfdhdb.dsksize: 5119 ; 0x0c4: 0x000013ff
[oracle@asm bin]$

13. check olr and ocr location :
[oracle@asm ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 82368
Available space (kbytes) : 409316
ID : 786336035
Device/File Name : /u01/app/grid/cdata/localhost/local.ocr
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[oracle@asm ~]$
[oracle@asm ~]$ ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 83496
Available space (kbytes) : 408188
ID : 476403298
Device/File Name : /u01/app/oracle/crsdata/asm/olr/asm_19.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[oracle@asm ~]$ ocrcheck -local -config
Oracle Local Registry configuration is :
Device/File Name : /u01/app/oracle/crsdata/asm/olr/asm_19.olr
[oracle@asm ~]$

14. Check the asm status in sysasm admin:

[oracle@asm ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 2 17:57:52 2022
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

15. check instance type:

SQL> show parameter INSTANCE_TYPE

NAME TYPE VALUE
———————————— ———– ——————————
instance_type string ASM

16. spfile location:

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/ASM/ASMPARAMETERFILE/reg
istry.253.1092745689

17. pfile creation:
SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
[oracle@asm ~]$ cd $ORACLE_HOME/dbs
[oracle@asm dbs]$ ls
ab_+ASM.dat hc_+ASM.dat init+ASM.ora init.ora
[oracle@asm dbs]$ cat init+ASM.ora
+ASM.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from in memory value
.asm_diskstring=’/dev/oracleasm/disks’
.asm_power_limit=1
.large_pool_size=12M
.remote_login_passwordfile=’EXCLUSIVE’
[oracle@asm dbs]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 2 18:01:05 2022
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

18. instance type and status:

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
—————- ————
+ASM STARTED

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
[oracle@asm dbs]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE asm STABLE
ora.LISTENER.lsnr
ONLINE ONLINE asm STABLE
ora.asm
ONLINE ONLINE asm Started,STABLE
ora.ons
OFFLINE OFFLINE asm STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 ONLINE ONLINE asm STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE asm STABLE
ora.oraasm.db
1 ONLINE ONLINE asm Open,HOME=/u01/app/o
racle/product/19.0.0
/dbhome_1,STABLE
——————————————————————————–
[oracle@asm dbs]$

19. ASMCMD check datafile and controlfile:

[oracle@asm dbs]$ asmcmd
ASMCMD>
ASMCMD>
ASMCMD>
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 30712 25852 0 25852 0 N DATA/
ASMCMD> cd +DATA/oraasm/datafile
ASMCMD> ls
SYSAUX.258.1092813853
SYSTEM.257.1092813819
UNDOTBS1.259.1092813869
USERS.260.1092813869
ASMCMD> ls -l
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE JAN 02 17:00:00 Y SYSAUX.258.1092813853
DATAFILE UNPROT COARSE JAN 02 17:00:00 Y SYSTEM.257.1092813819
DATAFILE UNPROT COARSE JAN 02 17:00:00 Y UNDOTBS1.259.1092813869
DATAFILE UNPROT COARSE JAN 02 17:00:00 Y USERS.260.1092813869
ASMCMD>

ASMCMD> cd +DATA/oraasm/parameterfile

ASMCMD> ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE JAN 02 17:00:00 Y spfile.274.1092815069
ASMCMD>

ASMCMD> ls -l
Type Redund Striped Time Sys Name
ASMPARAMETERFILE UNPROT COARSE DEC 31 12:00:00 Y REGISTRY.253.1092745689
ASMCMD> pwd
+DATA/ASM/ASMPARAMETERFILE
ASMCMD>

20. query to check asm_disk and asm disk_group:

SQL> select disk_number,name,path,header_status,mode_status,state,total_mb,free_mb from v$asm_disk;

DISK_NUMBER NAME PATH HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB
—————————————————————————————————-
0 DATA_0000 /dev/oracleasm/disks/DISK1 MEMBER ONLINE NORMAL 20476 17228

1 DATA_0001 /dev/oracleasm/disks/DISK2 MEMBER ONLINE NORMAL 10236 8624

 

SQL> select group_number,name,state,type from v$asm_diskgroup;

GROUP_NUMBER NAME STATE TYPE
———— —————————— ———– ——
1 DATA MOUNTED EXTERN

SQL>

 

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

Adding NEW disk group in ASM

Description:
          In this blog, we are going to see step by step process for adding a disk in ASM disk group.

Rebalance ASM Disks:
          Oracle ASM automatically rebalances disk groups when their configuration changes. You might want to do a manual rebalance operation to control the speed of what would otherwise be an automatic rebalance operation.

Pre request-
Add needed disk space in virtual machine.

Steps:-

1. Login as root user source the bash profile:

[oracle@asm ~]$ . .bash_profile
[oracle@asm ~]$ su root
Password:
[root@asm oracle]#
[root@asm oracle]# cd

2. New disk configuration:

[root@asm ~]# fdisk /dev/sdd
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only until you decide to write them.
Be careful before using the write command.

The device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xf8f5f5c6.

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
g create a new empty GPT partition table
G create an IRIX (SGI) partition table
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition’s system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-10485759, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-10485759, default 10485759):
Using default value 10485759
Partition 1 of type Linux and of size 5 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

3. List the disks:

[root@asm ~]# fdisk -l

Disk /dev/sda: 107.4 GB, 107374182400 bytes, 209715200 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x0001d229

Device Boot Start End Blocks Id System
/dev/sda1 * 2048 14338047 7168000 83 Linux
/dev/sda2 14338048 98306047 41984000 83 Linux
/dev/sda3 98306048 182274047 41984000 83 Linux
/dev/sda4 182274048 209715199 13720576 5 Extended
/dev/sda5 182276096 196612095 7168000 82 Linux swap / Solaris

Disk /dev/sdb: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xeba933f3

Device Boot Start End Blocks Id System
/dev/sdb1 2048 41943039 20970496 83 Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes, 20971520 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x330f95dd

Device Boot Start End Blocks Id System
/dev/sdc1 2048 20971519 10484736 83 Linux

Disk /dev/sdd: 5368 MB, 5368709120 bytes, 10485760 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xf8f5f5c6

Device Boot Start End Blocks Id System
/dev/sdd1 2048 10485759 5241856 83 Linux

4. Configure oracle ASM:

[root@asm ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on the boot and what permissions it will have. The current values
will be shown in brackets (‘[]’). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface [oracle]:
Default group to own the driver interface [oinstall]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
[root@asm ~]#

5. OS level disk creation:

[root@asm ~]# /usr/sbin/oracleasm init
[root@asm ~]# /usr/sbin/oracleasm createdisk DISK3 /dev/sdd1
Writing disk header: done
Instantiating disk: done
[root@asm ~]# cd /dev/oracleasm/disks/
[root@asm disks]#
[root@asm disks]#
[root@asm disks]#
[root@asm disks]#
[root@asm disks]# ls -lrt
total 0
brw-rw—-. 1 oracle oinstall 8, 49 Jan 6 06:15 DISK3
brw-rw—-. 1 oracle oinstall 8, 33 Jan 6 06:16 DISK2
brw-rw—-. 1 oracle oinstall 8, 17 Jan 6 06:16 DISK1
[root@asm disks]# oracleasm listdisks
DISK1
DISK2
DISK3
[root@asm disks]#
[root@asm disks]#
[root@asm disks]#
[root@asm disks]#

6. Create the disk using rebalance:

[oracle@asm ~]$ . .bash_profile
[oracle@asm ~]$ . grid.env
[oracle@asm ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Jan 6 06:13:02 2022
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

7. Check the disk group status:
SQL> set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,b.header_status
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number order by b.group_number,b.name;SQL> SQL> SQL> SQL> 2 3

DISKGROUP DISKNAME TOTAL_MB USED_MB FREE_MB PATH HEADER_STATU
————— ————— ———- ———- ———- ———————————– ————
0 0 0 /dev/oracleasm/disks/DISK3 PROVISIONED
DATA DATA_0000 20476 3380 17096 /dev/oracleasm/disks/DISK1 MEMBER
DATA DATA_0001 10236 1676 8560 /dev/oracleasm/disks/DISK2 MEMBER

8. Create a disk in asm using rebalance method:

SQL> alter diskgroup DATA add disk ‘/dev/oracleasm/disks/DISK3’ NAME DISK3 rebalance power 100;

Diskgroup altered.

9. Check the status of new disk group its shown as RUN:

SQL> col ERROR_CODE for a10
col PASS for a10
select * from v$asm_operation;SQL> SQL>

GROUP_NUMBER OPERA PASS STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE CON_ID
———— —– ———- —- ———- ———- ———- ———- ———- ———– ———- ———-
1 REBAL COMPACT WAIT 100 100 0 0 0 0 0
1 REBAL REBALANCE RUN 100 100 163 183 6515 0 0
1 REBAL REBUILD DONE 100 100 0 0 0 0 0

10. Check after some time it will show no rows selected so it can be created fine:

SQL> select * from v$asm_operation;
no rows selected

11. View the disk group:

SQL> set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,b.header_status
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number order by b.group_number,b.name;SQL> SQL> SQL> SQL> 2 3

DISKGROUP DISKNAME TOTAL_MB USED_MB FREE_MB PATH HEADER_STATU
————— ————— ———- ———- ———- ———————————– ————
DATA DATA_0000 20476 2884 17592 /dev/oracleasm/disks/DISK1 MEMBER
DATA DATA_0001 10236 1456 8780 /dev/oracleasm/disks/DISK2 MEMBER
DATA DISK3 5116 728 4388 /dev/oracleasm/disks/DISK3 MEMBER

 

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

 

ASM File Handling Basics

ASM File Handling Basics

Description:
          In this blog we are going to see datafile,logfile,and archivelog create,resize delete in the ASM oracle database server.

Oracle ASM:

         Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations. Oracle ASM is Oracle’s recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.

ASM Disk Groups:

         Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit. Within a disk group, Oracle ASM exposes a file system interface for Oracle database files.

  1. Check datafile logfile and archivelog location in ASM

SQL> select name from v$datafile;
NAME
————————————————————————————————–

+DATA/ORAASM/DATAFILE/system.257.1092813819

+DATA/ORAASM/DATAFILE/sysaux.258.1092813853

+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421

+DATA/ORAASM/DATAFILE/users.260.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459

+DATA/ORAASM/DATAFILE/tblspace.280.1093044407

+DATA/ORAASM/DATAFILE/tblspace.281.1093044531

13 rows selected.

Logfiles :

SQL>  select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

6 rows selected.

Archivelogs:

SQL> archive log list

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     14

Next log sequence to archive   16

Current log sequence        16

SQL> show parameter db_recovery_file_dest

NAME      TYPE  VALUE

———————————— ———– ——————————

db_recovery_file_dest      string  +DATA

db_recovery_file_dest_size      big integer 12732M

  1. Create new Datafiles,logfiles, archivelogs in ASM Disks

Datafile:

SQL> create tablespace test datafile’+data’;

Tablespace created.

SQL> select name from v$datafile;

NAME
————————————————————————————————–

+DATA/ORAASM/DATAFILE/system.257.1092813819

+DATA/ORAASM/DATAFILE/sysaux.258.1092813853

+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421

+DATA/ORAASM/DATAFILE/users.260.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459

+DATA/ORAASM/DATAFILE/tblspace.280.1093044407

+DATA/ORAASM/DATAFILE/tblspace.281.1093044531

+DATA/ORAASM/DATAFILE/test.282.1094538795

14 rows selected.

SQL>

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name=’TEST’;

TABLESPACE_NAME   FILE_NAME

TEST                           +DATA/ORAASM/DATAFILE/test.282.1094538795

Logfiles: 

SQL> select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

6 rows selected.

SQL> alter database add logfile;

Database altered.

SQL>  select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

+DATA/ORAASM/ONLINELOG/group_4.283.1094538977

+DATA/ORAASM/ONLINELOG/group_4.284.1094538977

8 rows selected.

SQL>

  1. Alter / Resize existing Datafiles,logfiles, archivelogs in ASM Disks:

Datafiles:

alter database datafile ‘+DATA/ORAASM/DATAFILE/test.282.1094538795’ resize 150m;

 select file_name,bytes/1024/1024mb from dba_data_files where tablespace_name=’TEST’ order by file_name;

SQL> alter database datafile ‘+DATA/ORAASM/DATAFILE/test.282.1094538795’ resize 150m;

Database altered.

SQL> select file_name,bytes/1024/1024mb from dba_data_files where tablespace_name=’TEST’ order by file_name;

FILE_NAME                                                                       MB

+DATA/ORAASM/DATAFILE/test.282.1094538795           150

SQL> 

Logfiles:
SQL> alter database drop logfile group 4;

Database altered.

SQL> select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

6 rows selected.

SQL>

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 (‘+DATA’,’+DATA’) SIZE 1G;

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 (‘+DATA’,’+DATA’) SIZE 1G;

Database altered.

SQL> select thread#,group#,bytes/1024/1024,members,status from v$log;

 

   THREAD#     GROUP# BYTES/1024/1024  MEMBERS STATUS

———- ———- ————— ———- —————-

 1     1   200        2 CURRENT

 1     2   200        2 INACTIVE

 1     3   200        2 INACTIVE

 1     4   100        2 UNUSED

 1     5  1024        2 UNUSED

SQL>

 

 

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