作者：david_zhang@sh 【转载时请以超链接形式标明文章】
http://www.cnblogs.com/david-zhang-index/p/5042640.html
参照文档：https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-configuring-oracle-data-guard/?cdn=disable#implement-the-physical-standby-database-environment
You’ve set the Virtual Machine names as “Primary1” for the primary VM and “Standby1” for the standby VM with Oracle VM VirtualBox.
You’ve set the ORACLE_HOME environment variable to point to the same oracle root installation path in the primary and standby Virtual Machines, such as /home/oracle/OracleBase/product/11.2.0.3.0/db_1.
In this tutorial, you will:
Implement the physical standby database environment
Create a primary database
-
Prepare the primary database for standby database creation
- Enable forced logging
- Create a password file
- Configure a standby redo log
- Enable Archiving
- Set primary database initialization parameters
Create a physical standby database
Prepare an initialization parameter file for standby database
-
Configure the listener and tnsnames to support the database on primary and standby machines
- Configure listener.ora on both servers to hold entries for both databases
- Configure tnsnames.ora on the primary and standby Virtual Machines to hold entries for both primary and standby databases
- Start the listener and check tnsping on both Virtual Machines to both services.
Startup the standby instance in nomount state
Use RMAN to clone the database and to create a standby database
Start the physical standby database in managed recovery mode
Verify the physical standby database
IMPORTANT:
This tutorial has been setup and tested against the following hardware and software configuration:
| Primary Database | Standby Database | |
| Oracle Release | Oracle11g Enterprise Release (11.2.0.3.0) | Oracle11g Enterprise Release (11.2.0.3.0) |
| Machine Name | Primary1 | Standby1 |
| Operating System | Oracle Linux Server 6.5 | Oracle Linux Server 6.5 |
| Oracle SID | Primary | Standby |
| Memory | Min 2 GB | Min 2 GB |
| Disk Space | Min 20 GB | Min 20 GB |
For subsequent releases of Oracle Database and Oracle Data Guard, there might be some additional changes that you need to implement. For the most up-to-date version specific information, see Data Guard and Oracle Database documentation at Oracle web site.
Implement the physical standby database environment
1. Create a primary database
- Create a primary database “PRIMARY” in the primary Virtual Machine. For information, see Creating and Configuring an Oracle Database.
-
Connect to your database as the SYS user with SYSDBA role in the SQL*Plus command prompt and run the following statement to see the name of your database:
SQL> select name from v$database; The result will display like the following: NAME
---------
primary -
Next, query the names of the database files from the dba_data_files system view:
SQL> select file_name from dba_data_files; FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/OracleBase/oradata/primary/users01.dbf
/home/oracle/OracleBase/oradata/primary/undotbs01.dbf
/home/oracle/OracleBase/oradata/primary/sysaux01.dbf
/home/oracle/OracleBase/oradata/primary/system01.dbf
2. Prepare the primary database for standby database creation
Before creating a standby database, it’s recommended that you ensure the primary database is configured properly. The following is a list of steps that you need to perform:
- Enable forced logging
- Create a password file
- Configure a standby redo log
- Enable Archiving
- Set primary database initialization parameters
Enable forced logging
In order to implement a Standby Database, we need to enable 'Forced Logging' in the primary database. This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged into the redo logs. Therefore, we make sure that everything in the primary database is logged and replication to the standby includes all operations in the primary database. Run the alter database statement to enable force logging:
SQL> ALTER DATABASE FORCE LOGGING; Database altered.
Create a password file
To be able to ship and apply archived logs from the Primary server to the Standby server, the sys password must be identical on both primary and standby servers. That’s why you create a password file on the primary database and copy it to the Standby server.
IMPORTANT:
When using Oracle Database 12c, there is a new user, SYSDG, which you can use to administer Oracle Data Guard. For more information, see Changes in Oracle Database 12c Release.
In addition, make sure that the ORACLE_HOME environment is already defined in Primary1. If not, define it as an environment variable using the vi editor Environment.After setting up the environment variables, close the existing Windows command prompt and open up a new one.
Run the following statement to switch to the Oracle_Home directory, such as /home/oracle/OracleBase/product/11.2.0.3.0/db_1.
cd $ORACLE_HOME/dbs
Then, create a password file using the password file creation utility, ORAPWD. In the same Windows command prompt in Primary1, run the following command by setting the password value as the password of SYS:
orapwd FILE=orapwprimary PASSWORD=oracle FORCE=y
This command creates a password file, named as orapwprimary, in the ORACLE_HOME/dbs directory. You should copy this file to ORACLE_HOME/dbs directory in Standby1 and rename orapwdstandby manually.
Configure a standby redo log
Then, you need to configure a Standby Redo Log so that the primary can correctly receive the redo when it becomes a standby. Pre-creating them here also allows the standby redo logs to be automatically created on the standby. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs. The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
Run the following statement in the SQL*PLUS command prompt in Pramary1. The v$logfile is a system view that contains information about redo log files.
SQL> select file_name from dba_data_files;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/OracleBase/oradata/primary/redo03.log NO
2 ONLINE /home/oracle/OracleBase/oradata/primary/redo02.log NO
1 ONLINE /home/oracle/OracleBase/oradata/primary/redo01.log NO
Next, query the v$log system view, displays log file information from the control file.
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
Note that 52428800 is 50 megabytes.
Then, in the SQL*Plus window, run the following statements to add a new standby redo log file group to a standby database and specify a number that identifies the group using the GROUP clause. Using group numbers can make administering standby redo log file groups easier:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/home/oracle/OracleBase/oradata/primary/redo04.log' SIZE 50M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/home/oracle/OracleBase/oradata/primary/redo05.log' SIZE 50M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/home/oracle/OracleBase/oradata/primary/redo06.log' SIZE 50M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/home/oracle/OracleBase/oradata/primary/redo07.log' SIZE 50M; Database altered.
Next, run the following system view to list information about redo log files. This operation also verifies that the standby redo log file groups were created:
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/OracleBase/oradata/primary/redo03.log NO
2 ONLINE /home/oracle/OracleBase/oradata/primary/redo02.log NO
1 ONLINE /home/oracle/OracleBase/oradata/primary/redo01.log NO
4 STANDBY /home/oracle/OracleBase/oradata/primary/redo04.log NO
5 STANDBY /home/oracle/OracleBase/oradata/primary/redo05.log NO
6 STANDBY /home/oracle/OracleBase/oradata/primary/redo06.log NO
7 STANDBY /home/oracle/OracleBase/oradata/primary/redo07.log NO
6 rows selected.
Enable Archiving
Then, enable archiving by running the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving. You can enable archive log mode by mounting the database and then executing the archivelog command.
First, log in as sysdba. In the Windows command prompt, run:
[oracle@Primary1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 13 11:38:26 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
Then, shutdown the database in the SQL*Plus command prompt:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Then, execute the startup mount command to mount the database. This ensures that Oracle associates the instance with the specified database.
SQL> startup mount;
ORACLE instance started. Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 838861320 bytes
Database Buffers 301989888 bytes
Redo Buffers 9371648 bytes
Database mounted.
Then, run:
SQL> alter database archivelog; Database altered.
Then, run the Alter database statement with the Open clause to make the database available for normal use:
SQL> alter database open; Database altered.
Set primary database initialization parameters
To configure the Data Guard, you need to create and configure the standby parameters on a regular pfile (text initialization parameter file) first. When the pfile is ready, you need to convert it to a server parameter file (SPFILE).
You can control the Data Guard environment using the parameters in the INIT.ORA file. When following this tutorial, you need to update the Primary database INIT.ORA so that it can hold both roles: Primary or Standby.
SQL> create pfile from spfile; File created.
Next, you need to edit the pfile to add the standby parameters. To do this, open the initprimary.ora file in the location of ORACLE_HOME/dbs. Next, append the following statements to the initprimary.ora file. Note that the naming convention for your initprimary.ora file is initstandby.ora
db_name='primary'
db_unique_name='primary'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/home/oracle/OracleBase/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2= 'SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
# Standby role parameters --------------------------------------------------------------------
fal_server=standby
fal_client=primary
standby_file_management=auto
db_file_name_convert='standby','primary'
log_file_name_convert='standby','primary'
# ---------------------------------------------------------------------------------------------
The previous statement block includes three important setup items: - LOG_ARCHIVE_CONFIG...: You define the unique database ids using this statement. -LOG_ARCHIVE_DEST_1...: You define the local archive folder location using this statement. We recommend that you create a new directory for your database’s archiving needs and specify the local archive location using this statement explicitly rather than using Oracle’s default folder ORACLE_BASE/dbs/arch. - LOG_ARCHIVE_DEST_2 .... LGWR ASYNC...: You define an asynchronous log writer process (LGWR) to collect transaction redo data and transmit it to standby destinations. Here, the DB_UNIQUE_NAME specifies a unique name for the database at the destination standby server.
Once the new parameter file is ready, you need to create the spfile from it.
First, shutdown the database:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
Next, run startup nomount command as follows:
SQL> startup nomount pfile='/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/initprimary.ora';
ORACLE instance started. Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 855638536 bytes
Database Buffers 285212672 bytes
Redo Buffers 9371648 bytes
Now, create a spfile:
SQL> create spfile from pfile='/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/initprimary.ora'; File created.
Then, shutdown the database:
SQL> shutdown immediate;
ORA-01507: database not mounted ORACLE instance shut down.
Then, use the startup command to start an instance:
SQL> startup;
ORACLE instance started. Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 855638536 bytes
Database Buffers 285212672 bytes
Redo Buffers 9371648 bytes
Database mounted.
Database opened.
Create a physical standby database
This section focuses on the steps that you must perform in Standby to prepare the physical standby database.
Then, on the Standby Server (Standby1), create all the necessary folders for the standby database, such as /home/oracle/OracleBase/archive. While following this tutorial, make sure that the folder structure matches the folder structure on Primary to keep all the necessary files, such as controlfile, datafiles, redologfiles, udump, bdump and cdump files. In addition, define the ORACLE_HOME and ORACLE_BASE environment variables in Standby. open up a new one to see the changes.
Next, follow these steps:
Prepare an initialization parameter file for standby database
-
Configure the listener and tnsnames to support the database on primary and standby machines
- Configure listener.ora on both servers to hold entries for both databases
- Configure tnsnames.ora on the primary and standby Virtual Machines to hold entries for both primary and standby databases
- Start the listener and check tnsping on both Virtual Machines to both services.
Startup the standby instance in nomount state
Use RMAN to clone the database and to create a standby database
Start the physical standby database in managed recovery mode
Verify the physical standby database
1. Prepare an initialization parameter file for standby database
This section demonstrates how to prepare an initialization parameter file for the standby database. To do this, first copy the initprimary.ora file from Primary1 to Strandby1 manually. You should be able to see the initprimary.ora file in the ORACLE_HOME/dbs folder in both machines. Then, modify the initprimary.ora to initstandby.ora in Standby1 and to set it up for the standby role as specified below:
db_name='primary'
db_unique_name='standby'
db_create_file_dest='/home/oracle/OracleBase/oradata/standby'
db_file_name_convert='primary','standby'
log_file_name_convert='primary','standby'
fal_server='primary'
fal_client='standby' job_queue_processes=10
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/OracleBase/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1='ENABLE'
LOG_ARCHIVE_DEST_STATE_2='ENABLE'
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=30
The previous statement block includes two important setup items:
- *.LOG_ARCHIVE_DEST_1: You need to create the /home/oracle/OracleBase/archive folder in Standby1 manually.
- *.LOG_ARCHIVE_DEST_2: This is an optional step. You set this as it might be needed when the primary machine is in maintenance and the standby machine becomes a primary database.
Then, you need to start the standby instance. On the standby database server, enter the following command to create an Oracle instance :
startup nomount pfile='/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/initstandby.ora';
Note that the start nomount command creates an Oracle instance but does not start it.
Configure the listener and tnsnames to support the database on primary and standby machines
Before you create a standby database, you need to make sure that the primary and standby databases in your configuration can talk to each other. To do this, you need to configure both the listener and TNSNames either manually or by using the network configuration utility NETCA. This is a mandatory task when you use the Recovery Manager utility (RMAN).
Configure listener.ora on both servers to hold entries for both databases
Remote desktop to Primary1 and edit the listener.ora file as specified below. When you edit the listener.ora file, always make sure that the opening and closing parenthesis line up in the same column. You can find the listener.ora file in the following folder: /home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin.
# listener.ora Network Configuration File: /home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools. LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Primary1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
) SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=primary)
(SID_NAME = primary)
(ORACLE_HOME = /home/oracle/OracleBase/product/11.2.0.3.0/db_1)
)
) ADR_BASE_LISTENER = /home/oracle/OracleBase
Next, remote desktop to Standby1 and edit the listener.ora file as follows: # listener.ora Network Configuration File: /home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin.
# listener.ora Network Configuration File: /home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools. LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Standby1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
) SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=standby)
(SID_NAME = standby)
(ORACLE_HOME = /home/oracle/OracleBase/product/11.2.0.3.0/db_1)
)
) ADR_BASE_LISTENER = /home/oracle/OracleBase
Configure tnsnames.ora on the primary and standby Virtual Machines to hold entries for both primary and standby databases
Remote desktop to Machine1 and edit the tnsnames.ora file as specified below. You can find the tnsnames.ora file in the following folder: /home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin.
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Primary1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
) standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Standby1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
Remote desktop to Standby1 and edit the tnsnames.ora file as follows:
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Primary1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
) standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Standby1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
Start the listener and check tnsping on both Virtual Machines to both services.
Open up a new Windows command prompt in both primary and standby Virtual Machines and run the following statements:
[oracle@Primary1 ~]$ tnsping primary TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-DEC-2015 01:59:33 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files:
/home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (40 msec) [oracle@Primary1 ~]$ tnsping standby TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-DEC-2015 01:59:38 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files:
/home/oracle/OracleBase/product/11.2.0.3.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby)))
OK (10 msec)
Startup the standby instance in nomount state
You need to set up the environment to support the standby database on the standby Virtual Machine (Standby1).
First, copy the password file from the primary machine (Primary1) to the standby machine (Standby1) manually. This is necessary as the sys password must be identical on both machines.
Then, open the Windows command prompt in Standby1, and setup the environment variables to point to the Standby database as follows:
startup nomount pfile='/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/initstandby.ora';
Next, start the Standby database in nomount state and then generate an spfile.
Start the database:
SQL> shutdown immediate;
ORA-01507: database not mounted ORACLE instance shut down.
SQL> startup nomount pfile='/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/initstandby.ora';
ORACLE instance started. Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
Use RMAN to clone the database and to create a standby database
You can use the Recovery Manager utility (RMAN) to take any backup copy of the primary database to create the physical standby database.
Remote desktop to the standby Virtual Machine (Standby1) and run the RMAN utility by specifying a full connection string for both the TARGET (primary database, Primary1) and AUXILLARY (standby database, Standby1) instances.
IMPORTANT:
Do not use the operating system authentication as there is no database in the standby server machine yet
[oracle@Standby1 admin]$ rman target sys/oracle@primary auxiliary sys/oracle@standby Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 14 02:20:00 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1735823365)
connected to auxiliary database: PRIMARY (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 14-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/orapwprimary' auxiliary format
'/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/orapwstandby' ;
}
executing Memory Script Starting backup at 14-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
Finished backup at 14-DEC-15 contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/OracleBase/oradata/standby/STANDBY/controlfile/o1_mf_c6vfr29c_.ctl';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/home/oracle/OracleBase/oradata/standby/STANDBY/controlfile/o1_mf_c6vfr29c_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script Starting backup at 14-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/OracleBase/product/11.2.0.3.0/db_1/dbs/snapcf_primary.f tag=TAG20151214T022046 RECID=1 STAMP=898395647
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-DEC-15 sql statement: create spfile from memory Oracle instance shut down connected to auxiliary database (not started)
Oracle instance started Total System Global Area 238034944 bytes Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes sql statement: alter system set control_files = ''/home/oracle/OracleBase/oradata/standby/STANDBY/controlfile/o1_mf_c6vfr29c_.ctl'' comment= ''Set by RMAN'' scope=spfile Oracle instance shut down connected to auxiliary database (not started)
Oracle instance started Total System Global Area 238034944 bytes Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script sql statement: alter database mount standby database contents of Memory Script:
{
set newname for tempfile 1 to
"/home/oracle/OracleBase/oradata/standby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/OracleBase/oradata/standby/system01.dbf";
set newname for datafile 2 to
"/home/oracle/OracleBase/oradata/standby/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/OracleBase/oradata/standby/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/OracleBase/oradata/standby/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/OracleBase/oradata/standby/system01.dbf" datafile
2 auxiliary format
"/home/oracle/OracleBase/oradata/standby/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/OracleBase/oradata/standby/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/OracleBase/oradata/standby/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /home/oracle/OracleBase/oradata/standby/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 14-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/OracleBase/oradata/primary/system01.dbf
output file name=/home/oracle/OracleBase/oradata/standby/system01.dbf tag=TAG20151214T022124
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/OracleBase/oradata/primary/sysaux01.dbf
output file name=/home/oracle/OracleBase/oradata/standby/sysaux01.dbf tag=TAG20151214T022124
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/OracleBase/oradata/primary/undotbs01.dbf
output file name=/home/oracle/OracleBase/oradata/standby/undotbs01.dbf tag=TAG20151214T022124
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/OracleBase/oradata/primary/users01.dbf
output file name=/home/oracle/OracleBase/oradata/standby/users01.dbf tag=TAG20151214T022124
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 14-DEC-15 sql statement: alter system archive log current contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=898395850 file name=/home/oracle/OracleBase/oradata/standby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=898395850 file name=/home/oracle/OracleBase/oradata/standby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=898395850 file name=/home/oracle/OracleBase/oradata/standby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=898395850 file name=/home/oracle/OracleBase/oradata/standby/users01.dbf
Finished Duplicate Db at 14-DEC-15
RMAN>exit
Start the physical standby database in managed recovery mode
This tutorial demonstrates how to create a physical standby database. For information on creating a logical standby database, see the Oracle documentation.
Open up SQL*Plus command prompt and enable the Data Guard on the standby Virtual Machine or server (Standby1) as follows:
[oracle@Standby1 admin]$ sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 14 02:28:39 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> conn /as sysdba
Connected.
SQL> select status from v$instance; STATUS
------------
MOUNTED SQL> alter database recover managed standby database disconnect from session; Database altered.
When you open the standby database in MOUNT mode, the archive log shipping continues and the managed recovery process continues log applying on the standby database. This ensures that the standby database remains up-to-date with the primary database. Note that the standby database cannot be accessible for reporting purposes during this time.
When you open the standby database in READ ONLY mode, the archive log shipping continues. But the managed recovery process stops. This causes the standby database to become increasingly out of date until the managed recovery process is resumed. You can access the standby database for reporting purposes during this time but data may not reflect the latest changes.
In general, we recommend that you keep the standby database in MOUNT mode to keep the data in the standby database up-to-date in case of failure of the primary database. However, you can keep the standby database in READ ONLY mode for reporting purposes depending on your application’s requirements. The following steps demonstrate how to enable the Data Guard in read-only mode using SQL*Plus:
ALTER DATABASE OPEN READ ONLY;
Verify the physical standby database
This section demonstrates how to verify the high availability configuration as an administrator.
Open up SQL*Plus command prompt window and check archived redo log on the Standby Virtual Machine (Standby1):
SQL> show parameters db_unique_name; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string standby
SQL> SELECT NAME FROM V$DATABASE; NAME
---------
PRIMARY SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------ ------------ ---------
10 13-DEC-15 14-DEC-15 YES
11 14-DEC-15 14-DEC-15 YES
12 14-DEC-15 14-DEC-15 YES
Open up SQL*Plus command prompt window and switch logfiles on the Primary machine (Primary1):
SQL> alter system switch logfile; System altered. SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/OracleBase/archive
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
Check archived redo log on the Standby Virtual Machine (Standby1):
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER; SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------ ------------ ---------
10 13-DEC-15 14-DEC-15 YES
11 14-DEC-15 14-DEC-15 YES
12 14-DEC-15 14-DEC-15 YES
13 14-DEC-15 14-DEC-15 YES SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/OracleBase/archive
Oldest online log sequence 12
Next log sequence to archive 0
Current log sequence 14
Check for any gap on the Standby Virtual Machine (Standby2):
SQL> SELECT * FROM V$ARCHIVE_GAP; no rows selected
Another verification method could be to failover to the standby database and then test if it is possible to failback to the primary database. To activate the standby database as a primary database, use the following statements:
SQL> select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PRIMARY PRIMARY TO STANDBY SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. SQL> shutdown immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@Primary1 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 14 03:00:34 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started. Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 872415752 bytes
Database Buffers 268435456 bytes
Redo Buffers 9371648 bytes
SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PRIMARY PHYSICAL STANDBY TO PRIMARY
on standby1
SQL> select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PRIMARY PHYSICAL STANDBY TO PRIMARY SQL> alter database commit to switchover to primary; Database altered. SQL> shutdown immediate;
ORA-01109: database not open Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started. Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile; System altered. SQL> select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PRIMARY PRIMARY TO STANDBY
check SEQUENCE# on Primary1 and Standby1
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#)
--------------
17 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#)
--------------
17
If you have not enabled flashback on the original primary database, it’s recommended that you drop the original primary database and recreate as a standby database.
We recommend that you enable flashback database on the primary and the standby databases. When a failover happens, the primary database can be flashed back to the time before the failover and quickly converted to a standby database.