This article presents how to configure physical standby database for Oracle Database 12C release 1 (12.1). It’s not focusing on transportation method, duplication method, protection modes nor extra functionality available for physical standbys. All above will be part of future articles :).
This presentation is based on the following article Install Oracle 12C Release 1 (12.1) on Oracle Linux 6 (OEL6).
Architecture
Final standby configuration is very simple:
- one host: oel6.dbaora.com – already configured with latest binaries Oracle Database 12C
- primary database: ORA12C – already installed container database has one pluggable database PORA12C1 and SEED template database
- listener LISTENER – already installed
- standby database: SORA12C – this article shows how to configure it
- standby configuration will work in default mode MAXIMUM PERFORMANCE
Configuration consists of the following steps:
For primary database
- prepare space for archivelog and turn on archivelog mode
- switch to force logging on database level or tablespace level
- add standby redo logs
- prepare initialization parameters
- prepare database for quick backups
General steps
- network configuration
For standby database
- generate standby controlfile(on primary database)
- copy password file from primary database
- copy spfile(from primary database) as pfile and modify parameters
- prepare directory structure
- duplicate primary database
- start recover process
Before starting
Modify default prompt for sqlplus to show where code is executed. Modify following file.
[oracle@oel6 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
and put there following line
SET SQLPROMPT "_user'@'_connect_identifier'> '"
so every time you connect to a database you should see more details about your connection
--so for following connection export ORACLE_SID=ORA12C sqlplus / as sysdba --default prompt should like this SQL> --new prompt looks like this :) SYS@ORA12C>
It will help identify where commands are executed and by who
Configuration steps
Prepare space for archivelogs on primary database. Increase default settings to 8GB in db recovery area.
SYS@ORA12C> SELECT name, value FROM v$parameter WHERE name LIKE 'db_recovery%'; NAME VALUE --------------------- ------------------------------ db_recovery_file_dest /ora01/app/oracle/recovery_area db_recovery_file_dest_size 4560M ALTER SYSTEM SET db_recovery_file_dest_size=8196m SCOPE=BOTH;
Turn on archivelog mode on primary database ORA12C
SYS@ORA12C> SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; SELECT log_mode, open_mode FROM v$database; LOG_MODE OPEN_MODE ------------ ------------ ARCHIVELOG READ WRITE
Turn on force logging on primary database ORA12C. It’s optional step however it allows to avoid problems with unrecoverable transactions executed on primary database that are alter applied on standby. There is option to specify it on tablespace level instead of database (usually used in warehouses)
SYS@ORA12C> ALTER DATABASE FORCE LOGGING; SELECT log_mode, open_mode, force_logging FROM v$database; LOG_MODE OPEN_MODE FORCE_LOGGING ------------ ----------- --------------- ARCHIVELOG READ WRITE YES
Add standby logfiles on primary database ORA12C
SYS@ORA12C> SELECT group#, type FROM v$logfile; GROUP# TYPE ---------- ------- 3 ONLINE 2 ONLINE 1 ONLINE ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m; ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m; ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m; ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m; SELECT group#, type FROM v$logfile; GROUP# TYPE ---------- ------- 3 ONLINE 2 ONLINE 1 ONLINE 4 STANDBY 5 STANDBY 6 STANDBY 7 STANDBY
Set instance parameters for primary database ORA12C. Following parameters are already set on my database
- DB_NAME – specify name of database when it was created
- DB_UNIQUE_NAME – specify unique name for database. It sets parameter INSTANCE_NAME(if not set manually) to the same value.
- LOG_ARCHIVE_FORMAT – specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%r)
- REMOTE_LOGIN_PASSWORDFILE – must be set to EXCLUSIVE or SHARED
SYS@ORA12C> SELECT name, value FROM v$parameter WHERE name IN ( 'db_name', 'db_unique_name', 'log_archive_format', 'remote_login_passwordfile' ) ORDER BY NAME; NAME VALUE --------------------------- --------------- db_name ORA12C db_unique_name ORA12C log_archive_format %t_%s_%r.dbf remote_login_passwordfile EXCLUSIVE
set extra parameters on primary database ORA12C
- LOG_ARCHIVE_CONFIG – specify all databases in data guard configuration. it is list of all DB_UNIQUE_NAME separated by comma
- LOG_ARCHIVE_DEST_n – specify where redo data are archived
- LOG_ARCHIVE_DEST_1 – valid for both roles. Specify where to store redo data generated by the primary database in local archived redo log files
- LOG_ARCHIVE_DEST_2 – is valid only for the primary role. This destination transmits redo data to the remote physical standby destination
SYS@ORA12C> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG ='DG_CONFIG=(ORA12C,SORA12C)' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA12C' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=SORA12C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SORA12C' SCOPE=BOTH;
Set extra parameters when primary database ORA12C becomes standby. It requires to restart database.
- FAL_SERVER – specify server name where to request missing archived logs
- DB_FILE_NAME_CONVERT – specify how to convert datafile names from primary database
- LOG_FILE_NAME_CONVERT – specify how to convert logfile names from primary database
- STANDBY_FILE_MANAGEMENT – if set to AUTO files added or dropped on primary database are automatically added or dropped on standby
SYS@ORA12C> ALTER SYSTEM SET FAL_SERVER = SORA12C SCOPE=BOTH; ALTER SYSTEM SET DB_FILE_NAME_CONVERT = '/SORA12C/','/ORA12C/' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT = '/SORA12C/','/ORA12C/' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO SCOPE=BOTH; STARTUP FORCE;
Create standby control file on primary database ORA12C for standby database SORA12C. It’s optional step and during cloning method used in this article the generated control file won’t be used but it’s worth to know this method in case you need to restore standby control file.
SYS@ORA12C> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/sora12.ctl';
Create pfile from spfile for standby database on primary database ORA12C
SYS@ORA12C> CREATE PFILE='/tmp/initSORA12C.ora' FROM SPFILE;
Modify generated standby pfile.
!!! Be careful here to avoid mistakes !!!
[oracle@oel6 ~]$ vi /tmp/initSORA12C.ora *.audit_file_dest='/ora01/app/oracle/admin/SORA12C/adump' *.control_files= '/ora01/app/oracle/oradata/SORA12C/control01.ctl', '/ora01/app/oracle/recovery_area/SORA12C/control02.ctl' *.db_file_name_convert='/ORA12C/','/SORA12C/' *.db_name='ORA12C' *.db_unique_name='SORA12C' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA12CXDB)' *.fal_server='ORA12C' *.log_archive_config='DG_CONFIG=(ORA12C,SORA12C)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SORA12C' *.log_archive_dest_2='SERVICE=ORA12C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12C' *.log_file_name_convert='/ORA12C/','/SORA12C/' *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO'
copy the modified standby pfile to $ORACLE_HOME/dbs
[oracle@oel6 ~]$ cp /tmp/initSORA12C.ora $ORACLE_HOME/dbs
copy primary database password file for standby password file
[oracle@oel6 ~]$
cp $ORACLE_HOME/dbs/orapwORA12C $ORACLE_HOME/dbs/orapwSORA12C
To make successful cloning proper directories must be created
[oracle@oel6 ~]$ --directories used by standby instance SORA12C mkdir -p /ora01/app/oracle/admin/SORA12C/adump mkdir -p /ora01/app/oracle/admin/SORA12C/dpdump mkdir -p /ora01/app/oracle/admin/SORA12C/pfile mkdir -p /ora01/app/oracle/admin/SORA12C/xdb_wallet --directories for standby database container files mkdir /ora01/app/oracle/oradata/SORA12C mkdir /ora01/app/oracle/recovery_area/SORA12C --directories for seed template and pluggable databases mkdir /ora01/app/oracle/oradata/SORA12C/pdbseed mkdir /ora01/app/oracle/oradata/SORA12C/PORA12C1
Modify $ORACLE_HOME/network/admin/listener.ora file
File before modifications
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
Modify it. If you are lazy you can use netmgr tool :).
[oracle@oel6 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
File after modifications. Both ORA12C and SORA12C are using static registration in the LISTENER.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORA12C.dbaora.com) (ORACLE_HOME = /ora01/app/oracle/product/12.1.0/db_1) (SID_NAME = ORA12C) ) (SID_DESC = (GLOBAL_DBNAME = SORA12C.dbaora.com) (ORACLE_HOME = /ora01/app/oracle/product/12.1.0/db_1) (SID_NAME = SORA12C) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /ora01/app/oracle
Reload listener
[oracle@oel6 ~]$ lsnrctl reload
Modify $ORACLE_HOME/network/admin/tnsnames.ora
Before modifications
ORA12C = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA12C.dbaora.com) ) )
Modify it
[oracle@oel6 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
After modifications
SORA12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SORA12C.dbaora.com) ) ) ORA12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA12C.dbaora.com) ) )
check response from listeners
[oracle@oel6 ~]$ tnsping ora12c TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-DEC-2014 21:49:25 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /ora01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA12C.dbaora.com))) OK (0 msec) [oracle@oel6 ~]$ tnsping sora12c TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-DEC-2014 21:49:29 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /ora01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.dbaora.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = SORA12C.dbaora.com))) OK (0 msec)
Connect to standby database SORA12C using OS authentication then generate spfile from pfile and start database in nomount mode.
[oracle@oel6 ~]$ export ORACLE_SID=SORA12C sqlplus / as sysdba SYS@SORA12C> CREATE SPFILE FROM PFILE; STARTUP NOMOUNT;
In case you have received “ORA-00845: MEMORY_TARGET not supported on this system” during staring standby database increase your host memory. I had to increase my virtual box memory to 6GB :). You can as well try to change database memory parameters (decrease them) if you are short with memory.
It’s time to clone primary database to standby database.
Use rman to connect primary database ORA12C(target database) and to standby database SORA12C(auxiliary database).
[oracle@oel6 admin]$ rman RMAN> CONNECT TARGET sys@ora12c target database Password: connected to target database: ORA12C (DBID=242648173) RMAN> CONNECT AUXILIARY sys@sora12c auxiliary database Password: connected to auxiliary database: ORA12C (not mounted)
once connected start duplicate command
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
start recovery mode on standby database SORA12C
SYS@SORA12C> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
verify recovery process on standby database SORA12C
SYS@SORA12C> SELECT client_process, process, thread#, sequence#, status FROM v$managed_standby WHERE client_process='LGWR' or process='MRP0' ORDER BY PROCESS; CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ N/A MRP0 1 29 APPLYING_LOG LGWR RFS 1 29 IDLE
check protection mode – as default it’s set to MAXIMUM PERFORMANCE
SYS@ORA12C> SELECT protection_mode FROM v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE
switch logfile on primary database ORA12C
SYS@ORA12C> alter system switch logfile;
it’s applied on standby. Previously archivelog sequence was 29.
SYS@SORA12C> SELECT client_process, process, thread#, sequence#, status FROM v$managed_standby WHERE client_process='LGWR' or process='MRP0' ORDER BY PROCESS; CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ N/A MRP0 1 30 APPLYING_LOG LGWR RFS 1 30 IDLE
Have a fun 🙂
Tomasz
Hi, Thomasz, how are you..
Well, you description it’s much more completed in present day..However this step “RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE; ” is very important while description is display screen, because the new DBA have not experience about the passage and development..Another observation it’s..This execution is in same server with one VM instance with different names?..If yes, Okay..Understood..
Thanks a lot.
regards
I have been searching for a good instruction to set up my first physical standby in the past weeks and this is the best with all the details. Thank you very much for the sharing. Jim
Hello Tomasz.
Thanks a lot for this guide – it helps me bringing up my 1st physical Standby DB.
There’s a tiny fuzziness in the section
“Set instance parameters for primary database”: In your text you wrote:
“REMOTE_LOGIN_PASSWORD – must be set to …” instead of “REMOTE_LOGIN_PASSWORDFILE – must be set to …”
Greetings and THX very much!