In this post we will see how to setup Oracle dataguard with Primary and Standby databases running on Different Operating system.
This post will give you a detailed explanation how you can migrate your database from one Operating system to another using Heterogenous Dataguard setup
Please click on the INDEX and browse for more interesting posts.
MOTIVE :
The main motive to setup dataguard between different operating system platform is if you are planning to migrate your database from a OS to another OS with a very minimal downtime
Configuration :
Prerequisites
1. Check the ENDIAN format compatibility
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY 3,1;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID
----------- ---------------------------------------- -------------- ----------
1 Solaris[tm] OE (32-bit) Big 0
2 Solaris[tm] OE (64-bit) Big 0
3 HP-UX (64-bit) Big 0
4 HP-UX IA (64-bit) Big 0
6 AIX-Based Systems (64-bit) Big 0
9 IBM zSeries Based Linux Big 0
16 Apple Mac OS Big 0
18 IBM Power Based Linux Big 0
22 Linux OS (S64) Big 0
5 HP Tru64 UNIX Little 0
7 Microsoft Windows IA (32-bit) Little 0
8 Microsoft Windows IA (64-bit) Little 0
10 Linux IA (32-bit) Little 0
11 Linux IA (64-bit) Little 0
12 Microsoft Windows x86 64-bit Little 0
13 Linux x86 64-bit Little 0
15 HP Open VMS Little 0
17 Solaris Operating System (x86) Little 0
19 HP IA Open VMS Little 0
20 Solaris Operating System (x86-64) Little 0
21 Apple Mac OS (x86-64) Little 0
Here both our Source and Target databases are in the same ENDIAN format, i.e, LITTLE.
So, we wont face any problems with setting up the dataguard.
2. Install Oracle Grid Infrastructure cluster member in the new environment.
3. Install Oracle RAC Database software in the new environment.
4. Create ASM disk group, configure the listener in the new environment.
Primary DB steps :
1. Force logging needs to be enabled
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
2. Archive log needs to be enabled
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 14
Current log sequence 14
3. Check DB Unique name
Set the DB unique name for your primary if not set. In our setup, we have changed the DB unique name
4. Add service name of standby in tnsnames.ora
INFRADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infradb)
)
)
INFRADBSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = crsprod-scan.infraxpertzz.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infradbstby)
)
)
5. Set archive log destinations and enable archive_log_dest_2
SQL> alter system set log_archive_dest_2='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=crsprod-scan.infraxpertzz.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=infradbstby)(SERVER=DEDICATED)))"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="infradbstby" net_timeout=30','valid_for=(online_logfile,all_roles)' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
6. Make DB unique name to be part of dataguard
SQL> alter system set log_archive_config='DG_CONFIG=(infradb,infradbstby)' scope=both;
System altered.
7. Set remote login password to exclusive
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
System altered.
8. Set FAL Server and FAL client
SQL> alter system set fal_server='infradbstby' scope=both;
System altered.
SQL> alter system set fal_client='infradb' scope=both;
System altered.
9. Add standby logfiles to the primary server
The logs on the standby server need to be the same size and the count needs to be 1 more than online logfiles in order for the primary redo to be applied to the standby redo logs.
SQL> select member from v$logfile;
MEMBER
-------------------------------------
D:\ORACLE\ORADATA\INFRADB\REDO03.LOG
D:\ORACLE\ORADATA\INFRADB\REDO02.LOG
D:\ORACLE\ORADATA\INFRADB\REDO01.LOG
SQL>
alter database add standby logfile group 4 'D:\ORACLE\ORADATA\INFRADB\standby_redo04.log' size 200M;
alter database add standby logfile group 5 'D:\ORACLE\ORADATA\INFRADB\standby_redo05.log' size 200M;
alter database add standby logfile group 6 'D:\ORACLE\ORADATA\INFRADB\standby_redo06.log' size 200M;
alter database add standby logfile group 7 'D:\ORACLE\ORADATA\INFRADB\standby_redo07.log' size 200M;
10. Change standby file management to auto
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby string *
standby_archive_dest string ?#/dbs/arch
standby_db_preserve_states string NONE
standby_file_management string MANUAL
SQL> alter system set standby_file_management=AUTO scope=BOTH;
System altered.
SQL> show parameter standby
11. Set the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT and bounce the database
SQL> ALTER SYSTEM SET db_file_name_convert='D:\ORACLE\ORADATA\INFRADB','+DATA/INFRADBSTBY/DATAFILE/' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET log_file_name_convert='D:\ORACLE\ORADATA\INFRADB','+DATA/INFRADBSTBY/ONLINELOG/' SCOPE=SPFILE;
System altered.
SQL> Shut immediate
SQL> startup
12. Copy the pfile, tnsnames, sqlnet, listener and password file to standby server
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\19C\WINDOWS.X64_1930
00_DB_HOME\DATABASE\SPFILEINFR
ADB.ORA
SQL> create pfile from spfile;
File created.
STANDBY DATABASE STEPS :
1. Create Audit Directory
On all standby hosts create the audit directory for the standby database.
[oracle@jack admin]$ mkdir -p /u01/app/oracle/orabase/admin/INFRADBSTBY/adump
2. Place the Standby Password File
First create the database directory in the DATA disk group then place the password file copied from the INFRADB database to /u01/software
[oracle@jack ~]$ export ORACLE_HOME=/u01/app/19c/grid
[oracle@jack ~]$ export ORACLE_SID=+ASM1
[oracle@jack ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@jack ~]$ asmcmd mkdir +DATA/INFRADBSTBY
[oracle@jack ~]$ asmcmd mkdir +DATA/INFRADBSTBY/PASSWORD
[oracle@jack software]$ cp /u01/software/PWDinfradb.ora /u01/software/orapwdINFRADBSTBY
[oracle@jack software]$ asmcmd pwcopy /u01/software/orapwdINFRADBSTBY +DATA/INFRADBSTBY/PASSWORD/orapwdINFRADBSTBY
copying /u01/software/orapwdINFRADBSTBY -> +DATA/INFRADBSTBY/PASSWORD/orapwdINFRADBSTBY
ASMCMD> cd DATA
ASMCMD> ls
ASM/
DB_UNKNOWN/
INFRA/
INFRADBSTBY/
crsprod/
orapwasm
orapwasm_backup
ASMCMD> cd INFRADBSTBY/
ASMCMD> ls
PASSWORD/
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
PASSWORD/
ASMCMD> cd FRA
ASMCMD> ls
INFRA/
ASMCMD> mkdir INFRADBSTBY/
ASMCMD> cd INFRADBSTBY/
ASMCMD> ls
ASMCMD> mkdir ARCHIVELOG/
ASMCMD> mkdir AUTOBACKUP/
ASMCMD> mkdir CONTROLFILE/
ASMCMD> mkdir ONLINELOG/
3. Add TNS entries to the standby
INFRADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infradb)
)
)
INFRADBSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = crsprod-scan.infraxpertzz.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infradbstby)
)
)
4. Tnsping to the primary database
Check whether you are able to successfully tnsping your Primary database or not
[oracle@jack ~]$ tnsping infradb
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-AUG-2021 12:05:40
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = infradb)))
OK (0 msec)
5. Edit the entries in init.ora
The entries in BOLD needs to modified or should be present
infradb.__data_transfer_cache_size=0
infradb.__db_cache_size=1342177280
infradb.__inmemory_ext_roarea=0
infradb.__inmemory_ext_rwarea=0
infradb.__java_pool_size=0
infradb.__large_pool_size=16777216
infradb.__oracle_base='/u01/app/oracle/orabase'#ORACLE_BASE set from environment
infradb.__pga_aggregate_target=637534208
infradb.__sga_target=1895825408
infradb.__shared_io_pool_size=100663296
infradb.__shared_pool_size=419430400
infradb.__streams_pool_size=0
infradb.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/orabase/admin/INFRADBSTBY/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='+DATA/INFRADBSTBY/CONTROLFILE/control01.ctl','+FRA/INFRADBSTBY/CONTROLFILE/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='D:\ORACLE\ORADATA\INFRADB','+DATA/INFRADBSTBY/DATAFILE/'
*.db_name='infradb'
*.db_unique_name='infradbstby'
INFRADB1.instance_name='INFRADB1'
INFRADB2.instance_name='INFRADB2'
INFRADB1.instance_number=1
INFRADB2.instance_number=2
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle/orabase'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=infradbXDB)'
*.fal_client='infradbstby'
*.fal_server='infradb'
*.local_listener=’-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(infradb,infradbstby)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.168.190.4)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=infradb)(SERVER=DEDICATED)))"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="infradb" net_timeout=30','valid_for=(online_logfile,all_roles)'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='D:\ORACLE\ORADATA\INFRADB','+DATA/INFRADBSTBY/ONLINELOG/'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=600m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1799m
*.standby_file_management='AUTO'
INFRADB1.thread=1
INFRADB2.thread=2
INFRADB1.undo_tablespace='UNDOTBS1'
INFRADB2.undo_tablespace='UNDOTBS2'
6. Create the spfile
From the edited pfile, create the spfile for the standby database (the instance has not been started).
[oracle@jack software]$sqlplus "/ as sysdba"
SQL> create spfile='+DATA/INFRADBSTBY/PARAMETERFILE/spfileINFRASTBY.ora' from pfile='/u01/software/INITINFRADB.ORA';
File created.
7. Register the standby with clusterware
[oracle@jack]$ export ORACLE_HOME=/u01/app/oracle/product/19c/db_1
[oracle@jack]$ export ORACLE_SID=infradbstby1
[oracle@jack]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@jack]$srvctl add database -db INFRADBSTBY -oraclehome /u01/app/oracle/product/19c/db_1 -dbtype RAC -dbname INFRADB -diskgroup DATA,FRA -role physical_standby -spfile '+DATA/INFRADBSTBY/PARAMETERFILE/spfileINFRASTBY.ora' -pwfile '+DATA/INFRADBSTBY/PASSWORD/orapwdINFRADBSTBY'
[oracle@jack]$ srvctl add instance -database INFRADBSTBY -instance INFRADBSTBY1 -node JACK
[oracle@jack]$ srvctl add instance -database INFRADBSTBY -instance INFRADBSTBY2 -node JILL
8. Start the database in nomount mode
[oracle@jack software]$ srvctl start database -d INFRADBSTBY -o nomount
9. Restore standby controlfile
[oracle@jack ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 18 12:25:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INFRADB (not mounted)
RMAN> restore standby controlfile from service 'INFRADB';
Starting restore at 18-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service INFRADB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/INFRADBSTBY/CONTROLFILE/control01.ctl
output file name=+FRA/INFRADBSTBY/CONTROLFILE/control02.ctl
Finished restore at 18-AUG-21
10. Mount the database
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
Or Stop the database and start in mount mode using srvctl command
11. Restore the Standby Database from the primary1 database service
Restoring the datafiles from the primary1 to standby for instantiation is initiated on the standby cluster. Maximizing the use of the channels configured for parallelization by the previous step can be improved in some cases by using the RMAN SECTION SIZE clause.
RESTORE DATABASE FROM SERVICE
In order to initiate the copy of files, connect to the standby database and issue the restore command below using the descriptor in the tnsnames.ora for the primary1 database. In this example, that is the primary1 db_unique_name.
SYNTAX :
RMAN> restore database from service section size 64G;
RMAN> restore database from service INFRADB section size 64G;
Starting restore at 18-AUG-21
Starting implicit crosscheck backup at 18-AUG-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=268 device type=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 18-AUG-21
Starting implicit crosscheck copy at 18-AUG-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-AUG-21
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service INFRADB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/INFRADBSTBY/DATAFILE/system01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service INFRADB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +DATA/INFRADBSTBY/DATAFILE/sysaux01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service INFRADB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to +DATA/INFRADBSTBY/DATAFILE/undotbs01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service INFRADB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to +DATA/INFRADBSTBY/DATAFILE/infradb01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service INFRADB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to +DATA/INFRADBSTBY/DATAFILE/users01.dbf
channel ORA_DISK_1: restoring section 1 of 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 18-AUG-21
RMAN> exit
Recovery Manager complete.
12. Login to the database and start MRP Process
SQL> alter database recover managed standby database disconnect from session;
13. Check whether the Standby database is in SYNC with the Primary
SELECT A.THREAD#, B.LAST_SEQ, A.APPLIED_SEQ, to_char(A.LAST_APP_TIMESTAMP ,'DD-MON-YYYY HH24:MI:SS') "Applied Time", B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF FROM ( SELECT THREAD#, MAX (SEQUENCE#) APPLIED_SEQ, MAX (NEXT_TIME) LAST_APP_TIMESTAMP FROM GV$ARCHIVED_LOG WHERE APPLIED = 'YES' GROUP BY THREAD#) A, ( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ FROM GV$ARCHIVED_LOG GROUP BY THREAD#) B WHERE A.THREAD# = B.THREAD#;
Please click on the INDEX and browse for more interesting posts.