Creating a Physical Standby Database
Purpose
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 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 standby1 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:
Viewing Screenshots
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.
Before starting this tutorial, you should:
| 1. | Install Oracle Database 11g. |
| 2. | Create a directory named wkdir. Download and unzip physstdby.zip into the wkdir directory. |
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. Use SQL*Plus to execute 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 thecr_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.
 | ||||||||||||||||
| 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. These include:
Verify your settings for DB_NAME and DB_UNIQUE_NAME.
 | ||||||||||||||||
| 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.
 |
Creating the Physical Standby Database
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 Oracle network services on both servers accordingly.
| A | Create an Oracle Net service name for your physical standby database. |
| B. | Configure an entry for your standby database in the listener.ora file. |
| C. | Creating the standby database. |
A. Create an Oracle Net service name for your physical standby database.
| 1. | Your Data Guard configuration will use Oracle Net service names to reference different databases. Add a service name for your physical standby database. This can be achieved by appending the entry provided in tns_entry.txt to your existing$ORACLE_HOME/network/admin/tnsnames.ora file as shown below. Note: If you are familiar with Oracle Net services use the netmgr utility to configure the new service entry. Note: It is likely that you already have a service name defined for you primary database as illustrated above. If not, be sure to add a service name entry for that database as well. |
| 1. | In the following section you will use RMAN to create your standby database. To achieve this you will need to add a database service entry to your listener.ora file. This can be achieved by appending the entry provided in sid_entry.txt to your existing$ORACLE_HOME/network/admin/listener.ora file as shown below. Note: If you are familiar with Oracle Net services use the netmgr utility to configure the new service entry. |
| 1. | Stop and re-start the Oracle network listener using the lsnrctl stop and lsnrctl start commands.  |
| 2. | Change directory to $ORACLE_HOME/dbs and copy the remote login password file (orapworcl) from the primary database system to the $ORACLE_HOME/dbs directory on the standby database system, renaming it to orapwstandby1.Note: 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 different servers. |
| 3. | In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstandby1.oracontaining a single parameter: DB_NAME=<physical standby SID> (i.e. standby1) |
| 4. | On the standby system, change to the /u01/app/oracle/admin directory. Create a directory with a name that matches your physical standby SID (i.e. standby1). Change to the newly created directory (i.e. standby1) and create an adump directory. |
| 5. | On the standby system, create a directory with a name that matches your physical standby SID (i.e. standby1) in$ORACLE_BASE/oradata for the data files. Note: Depending on how you configured your existing primary database you may need to also create a similar directory in your fast recovery area (i.e. $ORACLE_BASE/flash_recovery_area) |
| 6. | On the standby system, set the ORACLE_SID environment variable to your physical standby SID (i.e. standby1) and start the instance in NOMOUNT mode with the text initialization parameter file created earlier. |
| 7. |
On the primary system, ensure the ORACLE_SID environment variable is set to your primary database SID (i.e. orcl). Verify that your current directory contains the cr_phys_sby1.txt RMAN script.
|
| 8. | On the primary system, invoke RMAN and connect as SYSDBA to the target database. Connect as SYSDBA to the auxiliary database. Enter <your password> for SYS. |
| 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. standby1) and start the managed recovery process. |
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.
Note: Depending on your machine performance, you may see that both archived redo logs have been applied at the time that you query the view.
|
| 2. | On the primary database, issue a number of ALTER SYSTEM SWITCH LOGFILE statements to archive a number of redo log files.
|
| 3. |
On the standby database, re-query the V$ARCHIVED_LOG view to verify the redo data was received and applied on the standby database.
|
The cleanup.sh script has been provided to stop and remove the physical standby database created in this OBE. Please note that other Data Guard OBE's use the physical standby database created in this tutorial so it is advised that you complete these before executing this clean up procedure.
| 1. |
Execute the cleanup.sh script to stop and remove the physical standby database.
|
In this tutorial, you learned how to:
 | Prepare the Primary Database for Standby Database Creation | |
| Create the Physical Standby Database over the network | |
| Verify that the Physical Standby Database is Performing Correctly | |
No comments:
Post a Comment