Setup Oracle Active-Standby step by step
Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2
Assumptions
- You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I've used Oracle Linux 7.2 and Oracle Database 11.2.0.4.
- The primary server (TBDB1) has a running instance.
- The standby server (TBDB2) has a software only installation.
- There is nothing blocking communication between the machines over the listener ports. If you are using the default 1521 port, node 1 should be able to communicate to node 2 on 1521 and node 2 should be able communicate with node 1 on 1521. Check network and local firewalls are not blocking the communication.
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
-- Make sure at leat one logfile is present.
ALTER SYSTEM SWITCH LOGFILE;
Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 50M;
If you want to user flashback database, enable it on the primary now, so it will be enabled on the standby also. It's very useful as you will see below.
ALTER DATABASE FLASHBACK ON;
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "db11g" on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL
SQL>
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. For this example, the standby database will have the value "db11g_stby".
Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
Make sure the STANDBY_FILE_MANAGEMENT parameter is set.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Service Setup
Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.
1ORCL =
1 (DESCRIPTION =
1 (ADDRESS_LIST =
1 (ADDRESS = (PROTOCOL = TCP)(HOST = TBDB1)(PORT = 1521))
1 )
1 (CONNECT_DATA =
1 (SID = ORCL)
1 )
1 )
1
1ORCL_STBY =
1 (DESCRIPTION =
1 (ADDRESS_LIST =
1 (ADDRESS = (PROTOCOL = TCP)(HOST = TBDB2)(PORT = 1521))
1 )
1 (CONNECT_DATA =
1 (SID = ORCL)
1 )
1 )
The "$ORACLE_HOME/network/admin/listener.ora" file on the primary server contains the following configuration. Since the broker will need to connect to the database when it's down, we can't rely on auto-registration with the listener, hence the explicit entry for the database.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =ORCL.WORLD)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TBDB1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
The "$ORACLE_HOME/network/admin/listener.ora" file on the standby server contains the following configuration.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =ORCL_STBY.WORLD)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TBDB2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Once the listener.ora changes are in place, restart the listener on both servers.
lsnrctl stop
lsnrctl start
Standby Server Setup
Prepare for Duplicate
Create a parameter file for the standby database called "/tmp/ initORCL_stby.ora" with the following contents.
*.db_name='db11g'
Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/ORCL/
$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL
$ mkdir -p /u01/app/oracle/admin/ORCL/adump
Create a password file, with the SYS password matching that of the primary database.
$ orapwd file= /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORCL password=Password1 entries=10
Create Standby Using DUPLICATE
Start the auxiliary instance on the standby server by starting it using the temporary "init.ora" file.
$ export ORACLE_SID=ORCL
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/tmp/initORCL_stby.ora';
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication.
$ rman TARGET sys/TBsys2016Auto@ORCL AUXILIARY sys/TBsys2016Auto@ORCL_STBY
Now issue the following DUPLICATE command.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='ORCL_STBY' COMMENT 'Is standby'
NOFILENAMECHECK;
/
If you need to convert file locations, or alter any initialisation parameters, you can do this during the DUPLICATE using the SET command.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
SET db_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
SET log_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
SET job_queue_processes='0'
NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
- FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
- FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafiles, without an additional backup step.
- DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
- SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
- NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start using the broker.
Enable Broker
At this point we have a primary database and a standby database, so now we need to start using the Data Guard Broker to manage them. Connect to both databases (primary and standby) and issue the following command.
ALTER SYSTEM SET dg_broker_start=true;
On the primary server, issue the following command to register the primary server with the broker.
$ dgmgrl sys/Password1@db11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> CREATE CONFIGURATION tbdb_dg_config AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
Configuration "tbdb_dg_config" created with primary database "ORCL"
DGMGRL>
Now add the standby database.
DGMGRL> ADD DATABASE ORCL_STBY AS CONNECT IDENTIFIER IS ORCL_STBY MAINTAINED AS PHYSICAL;
Database "ORCL_STBY" added
DGMGRL>
Now we enable the new configuration.
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>
The following commands show how to check the configuration and status of the databases from the broker.
DGMGRL> SHOW CONFIGURATION;
Configuration - tbdb_dg_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orcl_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> SHOW DATABASE ORCL;
Database - db11g
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
db11g
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE ORCL_stby;
Database - orcl_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
db11g
Database Status:
SUCCESS
DGMGRL>
Configuration RMAN Delete Policy
Login to RMAN and execute command:
1. On the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
2. On the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
|
Setup Oracle Active-Standby step by step
Reviewed by Wiki
on
07:10
Rating:

Setup Oracle Active-Standby Step By Step - Sổ Tay Tri Thức >>>>> Download Now
Trả lờiXóa>>>>> Download Full
Setup Oracle Active-Standby Step By Step - Sổ Tay Tri Thức >>>>> Download LINK
>>>>> Download Now
Setup Oracle Active-Standby Step By Step - Sổ Tay Tri Thức >>>>> Download Full
>>>>> Download LINK