Creating a Physical Standby Database
Creating a Physical Standby Database
This tutorial shows you how to create a physical standby database.
The steps described configure the standby database for maximum performance mode,
which is the default data protection mode. This tutorial shows you how to:
- Use the new DUPLICATE
FROM ACTIVE DATABASE RMAN command
- Enable Maximum Performance standby mode
- Verify that data is getting to the Physical standby
- Examine views to monitor Primary and Physical Standby
Databases using SQL*Plus
NOTE: This OBE represents a Data Guard configuration
where orcl is the primary database
and orclsby1 is the physical
standby database SID. For simplicity, in this OBE, both the primary and standby
databases are running on a single server.
Approximately 1 hour
This tutorial covers the following topics:
Place
the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: This action loads all
screenshots simultaneously, so response time may be slow depending on your Internet
connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
Oracle Database 11g RMAN introduces the FROM
ACTIVE DATABASE capability to the DUPLICATE
FOR STANDBY command. This alleviates the previous need for interim storage
on both the Primary and Standby systems, and the limitation of single stream
network traffic.
Now when taking the backup of the Primary database you can
simultaneously create and restore the standby database over
the network in parallel streams. Apart from some simple Oracle Net
setup, and creating a couple of directories and an interim password file, the
whole standby creation can be done in one RMAN script.
RMAN will automatically copy the server parameter file to
the standby host, start the auxiliary instance with the server parameter file,
restore a backup control file, and copy all necessary database files and archived
redo logs over the network to the standby host.
Prerequisites
Before you perform this tutorial, you should:
| 1. |
Perform an Oracle Database 11g installation (software
and db installed) for a single server environment OR Oracle Database
11g installation (software and db installed) on 1 server and Oracle
Database 11g software only on 2nd server for a dual server environment. |
| 2. |
Download and unzip the physstby.zip
file into your working directory (i.e.wkdir) |
Back to Topic List
In this practice, you verify that the primary database
is configured correctly to support a physical standby database.
You only need to perform these preparatory tasks once. After
you complete these steps, the database is prepared to serve as the primary database
for one or more standby databases. You should perform the following steps:
|
1. |
Determine if FORCE
LOGGING is enabled. If it is not enabled, enable FORCE
LOGGING mode. This statement may take some time to complete, because
it waits for all unlogged direct write I/O to finish. You use the following
commands:
Select FORCE_LOGGING from V$DATABASE;
ALTER DATABASE FORCE LOGGING;
|
2. |
Configure redo transport authentication.
Data Guard uses Oracle Net sessions to transport redo
data and control messages between the members of a Data Guard configuration.
These redo transport sessions are authenticated using either the Secure
Sockets Layer (SSL) protocol or a remote login password file.
In this OBE, we will be using a remote login password
file which will be created in a subsequent step.
|
|
3. |
Configure the primary database to receive redo data,
by adding the standby logfiles to the primary. You can use the cr_sby_redologs.sql
script, after validating the directory paths reflect your environment.
It is highly recommended that you have one more standby
redo log group than you have online redo log groups as the primary database.
The files must be the same size or larger than the primary database’s
online redo logs.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl01.log' size 52428800
3 /
Database altered.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl02.log' size 52428800
3 /
Database altered.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl03.log' size 52428800
3 /
Database altered.
SQL> alter database add standby logfile
2 '/u01/app/oracle/oradata/orcl/srl04.log' size 52428800
3 /
Database altered.
|
|
4. |
Set primary database initialization parameters
On the primary database, you define initialization parameters
that control redo transport services while the database is in the primary
role. You use SQL commands similar to the following to verify these settings:
Ensure the following parameters are set to reflect the
Data Guard configuration.
| DB_NAME |
Specifies the database name. Must be orcl. |
| DB_UNIQUE_NAME |
Specify a unique name for each database. Does not change even if
DG roles change. Must be orcl. |
| CONTROL_FILES |
Specifies the local path name for the control files on the primary
database. |
| LOG_ARCHIVE_CONFIG |
Uses the DG_CONFIG
attribute to list the DB_UNIQUE_NAME
of the primary and standby databases. |
| LOG_ARCHIVE_DEST_1 |
Defaults to archive destination for the local archived redo log
files. |
| LOG_ARCHIVE_DEST_2 |
Valid only for the primary role, this destination transmits redo
data to the remote physical standby destination orclsby1. |
| REMOTE_LOGIN_PASSWORDFILE |
Must be EXCLUSIVE
or SHARED if a remote
login password file is used (default = EXCLUSIVE) |
| LOG_ARCHIVE_DEST_STATE_n |
Must be ENABLE (default) |
Use the following commands to set the LOG_ARCHIVE_CONFIG
and LOG_ARCHIVE_DEST_2
indicating the primary and standby databases.
|
| 5. |
Issue the following statements to determine your database's
archival state, and then put the primary database in ARCHIVELOG
mode to enable automatic archiving.
|
Back to Topic List
In this practice, you configure the network environment and
create a physical standby database.
NOTE: In this OBE, you configure the network
for a single server environment. If you are configuring an environment on two
different servers, you would need to configure Net Manager on both servers accordingly.
Back to Topic List
A. Use Oracle Net
Manager to create an Oracle Net service name for your physical standby database.
| 1. |
Launch Net Manager.
netmgr
|
| 2. |
Expand Local. Select Service Naming and click the green plus
sign
|
| 3. |
Enter your <physical standby SID> (i.e. orclsby1) in the Net Service Name field and
click Next
|
| 4. |
Select TCP/IP (Internet Protocol) and click Next
|
| 5. |
Enter your fully qualified <hostname> (i.e. edtdr9p1.us.oracle.com)
and click Next.
|
| 6. |
Enter your <physical standby hostname> (i.e. orclsby1.us.oracle.com) in the Service Name
field and click Next
|
| 7. |
Click Finish.
|
| 8. |
Click File -> Save Network Configuration
to save the information to the
tnsnames.ora file.
|
Back to Topic
B. Use Oracle Net Manager
to configure an entry for your standby database in the listener.ora
file.
| 1. |
Expand Listeners
|
| 2. |
Select LISTENER.
|
| 3. |
Select Database Services
in the drop-down list.
|
| 4. |
Click Add Database.
|
| 5. |
Enter your <physical standby hostname> (i.e. orclsby1.us.oracle.com) in the
Global Database Name field. Enter your <oracle_home> and your
<physical standby sid> (i.e. orclsby1) in the SID field.
|
| 6. |
Select File -> Save Network Configuration.
|
| 7. |
Select File -> Exit.
|
Back to Topic
C. Creating the standby
database over the network
| 1. |
Reload the listener.
|
| 2. |
Navigate to ORACLE_HOME/dbs
folder to perform this steps.
Copy the remote login password file (orapworcl)
from the primary database system to the standby database system, renaming
it to orapworclsby1.
The password file must be re-copied each time the SYSDBA
or SYSOPER privilege
is granted or revoked and whenever the login password of a user with
these privileges is changed. You may need FTP, or some other remote
file transfer mechanism, if you are using differenet servers.
NOTE: This step is required if you are using Oracle
Database 11g encryption, or OS authentication for administrative
users.
|
| 3. |
In the ORACLE_HOME/dbs
folder, for the standby system, create an initialization parameter file
named initorclsby1.ora
containing a single parameter: DB_NAME=<physical standby SID> (i.e. orclsby1)
|
| 4. |
For the standby system, change to the /u01/app/oracle/admin
directory. Create your <physical standby SID> (i.e. orclsby1) directory. Change to
your <physical standby SID> (i.e. orclsby1) directory and create the adump
directory.
|
| 5. |
For the standby system, create your <physical standby SID> (i.e. orclsby1)
directory in $ORACLE_BASE/oradata for the data files.
NOTE: Depending on how you installed Oracle Database
11g, you may need to also add the following directory paths:
$ORACLE_BASE/flash_recovery_area
and $ORACLE_BASE/oradata
|
| 6. |
On the standby system, set the ORACLE_SID
environment variable to your <physical standby SID> (i.e. orclsby1)
and start the instance in NOMOUNT
mode with the text initialization parameter file.
|
| 7. |
On the primary system, ensure the ORACLE_SID
environment variable is set to your primary DB <SID> (i.e. orcl).
|
| 8. |
On the primary system, invoke RMAN and connect as SYSDBA
to the target database. Connect to the auxiliary database.
|
| 9. |
Execute the cr_phys_sby1.txt
script from RMAN on the primary system. When this script finishes you
will have a new standby database that was created over the network without
any interim storage.
|
| 10. |
Perform a log switch on the primary database and redo
will start being sent to the standby.
|
| 11. |
On the standby system, ensure the ORACLE_SID
environment variable is set to your <physical standby SID> (i.e. orclsby1)
and start the MRP process.
|
Back to Topic
Once you create the physical standby database and set up redo
transport services, you may want to verify database modifications are being
successfully transmitted from the primary database to the standby database.
To see that redo data is being received on the standby database, you should
first identify the existing archived redo log files on the standby database,
force a log switch and archive a few online redo log files on the primary database,
and then check the standby database again. The following steps show how to perform
these tasks.
| 1. |
On the standby database, identify the
existing archived redo log files by querying the V$ARCHIVED_LOG
view.
You can use the query_archived_log.sql
file.
|
| 2. |
On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current
online redo log file group.
|
| 3. |
On the standby database, re-query the
V$ARCHIVED_LOG view to
verify the redo data was received and archived on the standby database:
|
| 4. |
On the standby database, query the V$ARCHIVED_LOG
view to verify the archived redo log files were actually applied. Please
requery until you see a YES in the APPLIED
column.
At the completion of this step, the physical standby
database is running and provides the maximum performance level of data
protection.
|
In this tutorial, you learned how to:
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark