
Dưới đây là hướng dẫn về cài đặt Oracle Active – standby 2 Node mình đã cài đặt thành công:
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.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TBDB1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TBDB2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
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;
|
——————————-FINISH——————————————
Shutdown immediate;
startup mount;
ALTER DATABASE RECOVER
MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
alter database recover
managed standby database cancel;
alter database open;
alter database recover
managed standby database using current logfile disconnect;
SELECT
log_mode,open_mode FROM v$database;
select
process,sequence#,status from v$managed_standby;
|
Database
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. This process
is known as a Switchover and can be performed using the following commands.
Connect to the primary
database (orcl) and switchover to the
standby database (orcl_stby).
$ 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> 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.
New primary database “orcl_stby”
is opening…
Operation requires startup of
instance “db11g” 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 (db11g_stby) and switchover to the
new standby database (db11g).
$ dgmgrl
sys/Password1@db11g_stby
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> SWITCHOVER TO orcl;
Performing switchover NOW,
please wait…
Operation requires a connection
to instance “orcl” on database “orcl“
Connecting to instance “orcl“…
Connected.
New primary database “orcl”
is opening…
Operation requires startup of
instance “orcl” on database “orcl_stby”
Starting instance “orcl“…
ORACLE instance started.
Database mounted.
Switchover succeeded, new
primary is “orcl“
DGMGRL>
Database Failover
If the primary database is
not available the standby database can be activated as a primary database using
the following statements. Connect to the standby database (db11g_stby) and
failover.
$ dgmgrl
sys/Password1@db11g_stby
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> FAILOVER TO
db11g_stby;
Performing failover NOW, please
wait…
Failover succeeded, new primary
is “db11g_stby”
DGMGRL>
Since the standby
database is now the primary database it should be backed up immediately.
The original primary
database can now be configured as a standby. If flashback database was enabled
on the primary database, then this can be done relatively easily with the following
command.
DGMGRL> REINSTATE
DATABASE db11g;
Reinstating database
“db11g”, please wait…
Operation requires shutdown of
instance “db11g” on database “db11g”
Shutting down instance
“db11g”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of
instance “db11g” on database “db11g”
Starting instance
“db11g”…
ORACLE instance started.
Database mounted.
Continuing to reinstate
database “db11g” …
Operation requires shutdown of
instance “db11g” on database “db11g”
Shutting down instance
“db11g”…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of
instance “db11g” on database “db11g”
Starting instance
“db11g”…
ORACLE instance started.
Database mounted.
Continuing to reinstate
database “db11g” …
Reinstatement of database
“db11g” succeeded
DGMGRL>
If flashback database is
not enabled, you would have to manually recreate db11g as a standby. The basic
process is the reverse of what you did previously.
# 1) Cleanup the old instance.
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
rm -Rf
/u01/app/oracle/oradata/db11g/*
rm -Rf
/u01/app/oracle/fast_recovery_area/db11g
rm -Rf
/u01/app/oracle/admin/db11g
mkdir -p
/u01/app/oracle/fast_recovery_area/db11g
mkdir -p
/u01/app/oracle/admin/db11g/adump
rm
$ORACLE_HOME/dbs/spfiledb11g.ora
export ORACLE_SID=db11g
sqlplus / as sysdba <<EOF
STARTUP NOMOUNT
PFILE=’/tmp/initdb11g_stby.ora’;
EXIT;
EOF
# 2) Connect to RMAN.
$ rman TARGET
sys/Password1@db11g_stby AUXILIARY sys/Password1@db11g
# 3) Duplicate the database.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name=’DB11G’ COMMENT ‘Is
standby’
NOFILENAMECHECK;
# 4) Connect to DGMDRL on the
current primary.
$ dgmgrl
sys/Password1@db11g_stby
# 5) Enable the new standby.
DGMGRL> ENABLE DATABASE
db11g;
Flashback Database
It was already mentioned
in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a
switchover/switchback is safe for both the primary and standby database, a
failover renders the original primary database useless for converting to a
standby database. If flashback database is not enabled, the original primary
must be scrapped and recreated as a standby database.
An alternative is to
enable flashback database on the primary (and the standby if desired) so in the
event of a failover, the primary can be flashed back to the time before the
failover and quickly converted to a standby database, as shown above.
Read-Only Standby and Active Data Guard
Once a standby database
is configured, it can be opened in read-only mode to allow query access. This
is often used to offload reporting to the standby server, thereby freeing up
resources on the primary server. When open in read-only mode, archive log
shipping continues, but managed recovery is stopped, so the standby database
becomes increasingly out of date until managed recovery is resumed.
To switch the standby
database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed
recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE DISCONNECT FROM SESSION;
In 11g, Oracle
introduced the Active Data Guard feature. This allows the standby database to
be open in read-only mode, but still apply redo information. This means a
standby can be available for querying, yet still be up to date. There are
licensing implications for this feature, but the following commands show how
active data guard can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE DISCONNECT FROM SESSION;
Since managed recovery
continues with active data guard, there is no need to switch back to managed
recovery from read-only mode in this case.
Snapshot Standby
Introduced in 11g,
snapshot standby allows the standby database to be opened in read-write mode.
When switched back into standby mode, all changes made whilst in read-write
mode are lost. This is achieved using flashback database, but the standby
database does not need to have flashback database explicitly enabled to take
advantage of this feature, thought it works just the same if it is.
Connect to the primary
(db11g) database and convert the standby database (db11g_stby) to a snapshot
standby.
$ 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> CONVERT DATABASE
db11g_stby TO SNAPSHOT STANDBY;
Converting database
“db11g_stby” to a Snapshot Standby database, please wait…
Database “db11g_stby”
converted successfully
DGMGRL>
When you are finished
with the snapshot standby, convert it back to a standby database.
$ 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> CONVERT DATABASE
db11g_stby TO PHYSICAL STANDBY;
Converting database
“db11g_stby” to a Snapshot Standby database, please wait…
Database “db11g_stby”
converted successfully
DGMGRL> CONVERT DATABASE
db11g_stby TO PHYSICAL STANDBY;
Converting database
“db11g_stby” to a Physical Standby database, please wait…
Operation requires shutdown of
instance “db11g” on database “db11g_stby”
Shutting down instance
“db11g”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of
instance “db11g” on database “db11g_stby”
Starting instance
“db11g”…
ORACLE instance started.
Database mounted.
Continuing to convert database
“db11g_stby” …
Operation requires shutdown of
instance “db11g” on database “db11g_stby”
Shutting down instance
“db11g”…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance
“db11g” on database “db11g_stby”
Starting instance
“db11g”…
ORACLE instance started.
Database mounted.
Database “db11g_stby”
converted successfully
DGMGRL>
The standby is once
again in managed recovery and archivelog shipping is resumed. Notice that
flashback database is still not enabled.
DGMGRL> SHOW
CONFIGURATION;
Configuration – my_dg_config
Protection Mode: MaxPerformance
Databases:
db11g
– Primary database
db11g_stby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
Để lại một phản hồi