How to update controlfile parameter in a dataguard config
Situations:
Controlfile parameter update can be used to reflect new path or file.
Step summary:
1. Verify if the Standby DB is in recovery mode
2. Stop the recovery
3. Change the STANDBY_FILE_MANAGEMENT on the physical standby db to MANUAL
4. Backup & update the controlfile parameter in pfile
5. Shutdown and start the database in nomount state
6. Perform either of the below command
a. Use restore command in rman
restore standby controlfile from '';
b. Copy the controlfile to the other location
OS Copy command
7. Update standby_file_management backt to auto
7. mount the db
8. Start the db recovery
Step details:
1. Verify if the Standby DB is in recovery mode
set lines 300 set pages 3000 select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database; select process,status,client_process,sequence# from gv$managed_standby; select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
|
Actual output:
SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME NAME SWITCHOVER_STATUS --------- -------------------- ---------------- ------------------------------ --------- -------------------- GGSRC04T MOUNTED PHYSICAL STANDBY GGSRC04TSB1 GGSRC04T NOT ALLOWED
SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CONNECTED ARCH 0 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 MRP0 APPLYING_LOG N/A 29
7 rows selected.
SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
no rows selected
|
2. Stop the recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
|
Actual output:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CONNECTED ARCH 0 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0
6 rows selected. |
3. Change the STANDBY_FILE_MANAGEMENT on the physical standby db to MANUAL
sho parameter pfile;
sho parameter standby_file_management;
alter system set standby_file_management='MANUAL';
sho parameter standby_file_management; |
Actual output:
SQL> sho parameter pfile;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.2.0 .1/db_1/dbs/spfileGGSRC04TSB1. ora SQL> sho parameter standby_file_management;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO SQL> sho parameter convert
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /oradata/GGSRC04T, /oradata/GG SRC04TSB1 log_file_name_convert string /oradata/GGSRC04T, /oradata/GG SRC04TSB1 pdb_file_name_convert string SQL> alter system set standby_file_management='MANUAL';
System altered.
SQL> sho parameter standby_file_management;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL
|
4. Backup & update the controlfile parameter in pfile
create pfile='/home/oracle/dba/pfileGGSRC04TSB1.ora' from spfile;
note down the current controlfile path:
sho parameter control_files; alter system set control_files='/oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl','/oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl' scope=spfile sid='*';
|
Actual output:
SQL> create pfile='/home/oracle/dba/pfileGGSRC04TSB1.ora' from spfile;
File created.
SQL> sho parameter control_files;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /oradata/GGSRC04TSB1/control01 .ctl, /oradata/GGSRC04TSB1/con trol02.ctl SQL> alter system set control_files='/oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl','/oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl' scope=spfile sid='*';
System altered.
SQL> sho parameter control_files;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /oradata/GGSRC04TSB1/control01 .ctl, /oradata/GGSRC04TSB1/con trol02.ctl SQL> select value from v$spparameter where name='control_files';
VALUE --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl /oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl
|
5. Shutdown and start the database in nomount state
shu immediate;
startup nomount; |
Actual output:
SQL> shu immediate; ORA-01109: database not open
Database dismounted. ORACLE instance shut down. SQL> SQL> startup nomount; ORACLE instance started.
Total System Global Area 3221225472 bytes Fixed Size 8625856 bytes Variable Size 1442840896 bytes Database Buffers 1761607680 bytes Redo Buffers 8151040 bytes SQL> select name,open_mode from v$database; select name,open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS ---------------- ------------ GGSRC04TSB1 STARTED
SQL> sho parameter control
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /oradata/GGSRC04TSB1/control01 _GGSRC04TSB1.ctl, /oradata/GGS RC04TSB1/control02_GGSRC04TSB1 .ctl control_management_pack_access string DIAGNOSTIC+TUNING SQL>
|
6. Perform either of the below command
restore standby controlfile from '/oradata/GGSRC04TSB1/control01.ctl';
or copy/paste the old controlfile to the new path in OS.
|
Actual output:
connected to target database: GGSRC04T (not mounted)
RMAN> restore standby controlfile from '/oradata/GGSRC04TSB1/control01.ctl';
Starting restore at 25-SEP-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: copied control file copy output file name=/oradata/GGSRC04TSB1/control01_GGSRC04TSB1.ctl output file name=/oradata/GGSRC04TSB1/control02_GGSRC04TSB1.ctl Finished restore at 25-SEP-23
RMAN> |
7. Set standby file management to AUTO
alter system set standby_file_management='AUTO';
|
Actual output:
SQL> sho parameter standby
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ... standby_file_management string MANUAL SQL> alter system set standby_file_management='AUTO';
System altered.
SQL> sho parameter standby;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ... standby_file_management string AUTO SQL>
|
8. mount the db
Actual output:
SQL> alter database mount;
Database altered.
|
9. start the recovery
set lines 300 set pages 3000 select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database; select process,status,client_process,sequence# from gv$managed_standby; alter database recover automatic managed standby database disconnect from session; select process,status,client_process,sequence# from gv$managed_standby; select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
|
Actual output:
SQL> set lines 300 set pages 3000SQL> SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME NAME SWITCHOVER_STATUS --------- -------------------- ---------------- ------------------------------ --------- -------------------- GGSRC04T MOUNTED PHYSICAL STANDBY GGSRC04TSB1 GGSRC04T NOT ALLOWED
SQL> select controlfile_type from v$database;
CONTROL ------- STANDBY
SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CONNECTED ARCH 0 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0
6 rows selected.
SQL> alter database recover automatic managed standby database disconnect from session;
Database altered.
SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CONNECTED ARCH 0 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 MRP0 APPLYING_LOG N/A 29
7 rows selected.
SQL> .. SQL> /
PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CLOSING ARCH 34 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CLOSING ARCH 29 ARCH CLOSING ARCH 33 MRP0 APPLYING_LOG N/A 35 RFS IDLE Archival 0 RFS IDLE LGWR 35 RFS IDLE UNKNOWN 0 RFS IDLE UNKNOWN 0 RFS IDLE UNKNOWN 0
12 rows selected.
SQL>
|
Outline of the primary database controlfile param change:
Perform health check of the primary database
Stop the recovery @ standby
Update the standby_file_management parameter to MANUAL in standby
Backup and update controlfile path in primary spfile in primary db
Restart the primary db in nomount state and verify the controlfile param
Restore the controlfile from old controlfile in primary
Ensure necessary directory path exists before initiating the restore
restore controlfile from ‘<old controlfile path>’;
Validate the restore in primary db
Mount, Open the database in primary db
Perform health check
Set the standby_file_management parameter to AUTO in standby db
Restart the recovery & perform validation
This should help you perform primary db controlfile change, not much different just few steps execution
location changes and the actual restore command is different. We don’t use ‘standby’ keyword in the primary
db restore scenario.
YouTube video:
No comments:
Post a Comment