Configure Primary Database is essential for setting up a reliable and robust disaster recovery solution in Oracle 19c. In this blog, we will explore the steps to configure the primary database and Oracle Net Services to support the creation of the physical standby database and facilitate role transition. This introduction aims to simplify complex concepts, making them accessible for all levels of expertise. Let’s dive into the essentials of Primary Database Configuration and understand their practical applications in real-world scenarios. This will be a helpful intro to Net Services Setup.
Primary Database Setup
Setting up the primary database is the first crucial step in ensuring a seamless configuration for the physical standby database. This involves configuring the SPFILE, password file, and necessary parameters.
SPFILE Configuration:
To configure the SPFILE, first stop the database and create the SPFILE from the PFILE. Then modify the database to use the new SPFILE and restart the database.
Example:
srvctl stop database -d PRIMARYDB
SQL> create spfile='+DATA/spfilePRIMARYDB.ora' from pfile='/location/pfile.ora';
srvctl modify database -d PRIMARYDB -spfile +DATA/spfilePRIMARYDB.ora
srvctl start database -d PRIMARYDB
Primary Database Configuration – Password File Configuration:
Ensure the password file is correctly set up in ASM.
Example:
srvctl modify database -d PRIMARYDB -pwfile +DATA/orapwPRIMARYDB
asmcmd pwcopy /nfs/scripts/server/orapwPRIMARYDB +DATA/orapwPRIMARYDB
Adding Listener Entries:
Configure the listener entries on both primary and standby database servers to ensure proper communication.
Example:
DATAGUARD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary-hostname)(PORT = 1523)(SEND_SDU = 10485760)(RECV_SDU = 10485760))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_DATAGUARD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dataguard_service)
(SID_NAME = dataguard_sid)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = dataguard_dgmgrl)
(SID_NAME = dataguard_sid)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
)
)
Adding TNS Entries:
Add the necessary TNS entries in both the primary and standby tnsnames.ora files to facilitate connectivity.
Example:
PRIMARY_TNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary-host)(PORT = 1523)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
)
(SDU=65535)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary_service)
)
)
STANDBY_TNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1523)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
)
(SDU=65535)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby_service)
)
)
Configure Primary Database – Net Services Configuration
Setting up Oracle Net Services is critical for ensuring smooth communication between the primary and standby databases Configuration. This configuration supports the role transition and data synchronization processes.
Prepare the Primary Database:
Ensure the primary database is correctly configured for the Physical Standby setup.
Example:
ALTER SYSTEM SET DB_UNIQUE_NAME='primary_unique' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_unique, standby_unique)' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_unique' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_service LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 DB_UNIQUE_NAME=standby_unique' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=standby_tns SCOPE=SPFILE;
ALTER SYSTEM SET SERVICE_NAMES='primary_service, dgp_service' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
Start the database in mount mode, enable archive logging, and open the database.
Example:
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE FORCE LOGGING;
Create standby redo logs (one more group than normal redo log groups, so if the database has 3 redolog groups, you should add 4 standby redolog groups with the same size).
Example:
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo01.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo02.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo03.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo04.log' SIZE 50M;Verify the archive destinations.
Example:
SELECT DEST_ID, STATUS, VALID_TYPE, VALID_ROLE, VALID_NOW, ERROR FROM V$ARCHIVE_DEST;
Configure Primary Database – Advanced Configuration and Management
To maximize the efficiency of your Primary Database Setup, it’s crucial to understand advanced configuration and management practices. These practices ensure high availability, reliability, and security of your database connections.
Creating PFILE from SPFILE and Copying to Standby:
Example:
CREATE PFILE='/home/oracle/pfile_primary.init' FROM SPFILE;
scp /home/oracle/pfile_primary.init oracle@standby-host:/u03
Edit the PFILE on the standby database.
Example:
*.db_unique_name='standby_unique'
*.log_archive_config='DG_CONFIG=(primary_unique, standby_unique)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby_unique'
*.log_archive_dest_2='SERVICE=dgp_service LGWR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 DB_UNIQUE_NAME=primary_unique'
*.fal_server='PRIMARY_TNS'
*.service_names='standby_service, dgs_service'
*.STANDBY_FILE_MANAGEMENT=AUTO
Starting the Standby Instance and Creating SPFILE:
Example:
. oraenv
ORACLE_SID = [standby] ? standby_instance
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
SQL> CREATE SPFILE FROM PFILE='/u10/backup/rman/init_standby.ora';
Primary Database Configuration – Running RMAN Commands:
Connect to RMAN from the primary and standby (auxiliary) to run the script and create the standby database.
Example:
rman target sys/password@PRIMARY_TNS auxiliary sys/password@STANDBY_TNS
RUN {
ALLOCATE CHANNEL primary1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL stdy TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
}
Troubleshooting and Monitoring
Verify Standby Mode:
Example:
SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE FROM V$DATABASE;
Primary Database Configuration – Activate Redo Apply on Standby Database:
Example:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; -- Real-time
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT; -- Normal mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- To cancel
SELECT PROCESS, PID, STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
Primary Database Configuration – Verify Archive Destinations:
Example:
SELECT DB.NAME, LG.THREAD#, LG.DEST_ID, LG.SEQUENCE#, LG.FIRST_TIME, LG.NEXT_TIME
FROM V$ARCHIVED_LOG LG, V$DATABASE DB
WHERE LG.RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
ORDER BY LG.THREAD#, LG.SEQUENCE#, LG.DEST_ID;
Primary Database Configuration – Troubleshoot Archive Issues:
Example:
SET LINE 200
COLUMN Archive_dest FORMAT A30
COLUMN Error FORMAT A20
SELECT DEST_ID "ID", STATUS "DB_status", DESTINATION "Archive_dest", ERROR "Error" FROM V$ARCHIVE_DEST;
SELECT INST_ID, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY WHERE PROCESS IN ('RFS', 'LNS', 'MRP0');
Check Data Guard Stats:
Example:
SELECT NAME, VALUE, DATUM_TIME, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME LIKE 'apply lag';
Data Guard Broker in Primary Database Configuration
Enable Data Guard Broker:
Set the DG_BROKER_START parameter to TRUE on both primary and standby databases.
Example:
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH SID='*';
Primary Database Configuration – Configure Data Guard Broker:
Example:
dgmgrl /
CREATE CONFIGURATION 'DGConfig' AS PRIMARY DATABASE IS 'primary_unique' CONNECT IDENTIFIER IS PRIMARY_TNS;
ADD DATABASE 'standby_unique' AS CONNECT IDENTIFIER IS STANDBY_TNS MAINTAINED AS PHYSICAL;
ADD FAR_SYNC far_sync AS CONNECT IDENTIFIER IS FAR_SYNC; -- If using FarSync
ENABLE CONFIGURATION;
Show Configuration:
Example:
SHOW CONFIGURATION;
Conclusion
Mastering Configure Primary Database and Oracle Net Setup is essential for ensuring robust and efficient database connectivity. By understanding the fundamental components and their configurations, as well as advanced management and troubleshooting techniques, you can optimize your Oracle database environment effectively. Whether you are new to Primary Database Configuration or looking to deepen your knowledge, this guide provides a comprehensive starting point. Remember, consistent practice and staying updated with Oracle’s latest features will further enhance your expertise in Net Services Setup. This comprehensive intro to Oracle Net Setup ensures that you can apply these concepts effectively.
See more on Oracle’s website!
Be Oracle DataGuard Certified Professional, this world is full of opportunities for qualified DBAs!
