AWS Administration - 100% Lab - Online Training | AKSWAVE
09
Jul

Oracle 12c Physical standby database configuration (step-by-step)

  • 4.9
  • Reviews : 344

Here will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is also installed on standby server.

Course : ORACLE 12C DBA


Creating Oracle database 12c Standby database manually step by step.

Your primary database is named as orcl and create a standby database name as stdby.

Here we are using two terminals ,terminal-1 is used for the primary database and terminal-2 is used for the standby database

 

TERMINAL-1:

1.       Connect as sysdba and Enable FORCE LOGGING mode of database.

$ sqlplus / as sysdba

SQL> alter database force logging;

Check the force logging using following command

SQL> SELECT force_logging FROM v$database;

FORCE_LOGGING

---------------------------------------

YES

2.       Configure the standby redo logs on the primary database, check if OMF is not enabled, enable it first.

SQL> alter system set db_create_file_dest='/disk1/oradata/orcl';

To check the OMF

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string /disk1/oradata/orcl

Configure the FRA if it is not set

SQL> alter system set db_recovery_file_dest_size=5g;

SQL> alter system set db_recovery_file_dest='/disk3/oradata/orcl/fra';

Check the db_recovery_file_dest using following query

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /disk3/oradata/orcl/fra

 

SQL> alter database add standby logfile;

SQL> alter database add standby logfile;

Check the logfile information

SQL> select group#, member from v$logfile;

3.       Set the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 initialization parameters on the primary database.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

 

SQL> alter system set log_archive_dest_2='SERVICE=tns_stdby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';

SQL> alter system set log_archive_config='dg_config=(prod,stdby)';

Check the above parameters using following query

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST

SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG

4.       Configure the ARCHIVELOG mode, if its not enabled fallow the below steps to enable it.

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 563

Current log sequence 564

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> EXIT

5.       Invoke RMAN and make a whole database backup.

$ rman target /

RMAN> backup database plus archivelog;

RMAN> EXIT

 

TERMINAL-2:

6.       Configure LISTENER on Standby database by logging into standby server.

$ export ORACLE_SID=stdby

$ cd $HOME/network

$ vi listener.ora

-------------------------------------------------------------------

LIS_stdby =

    (DESCRIPTION_LIST =

        (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.48)(PORT = 1531))

        )

    )

SID_LIST_LIS_stdby =

    (SID_LIST =

        (SID_DESC =

            (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)

            (SID_NAME = stdby)

        )

    )

-------------------------------------------------------------------

7.  Check the listener configuration & start listener service.

$ cd

$ vi .bash_profile

export TNS_ADMIN=$HOME/network

$ . .bash_profile

$ lsnrctl start lis_stdby

 

 

 

TERMINAL-1:

8.       On your primary database system, create an tns alias for your physical standby database.

$ cd $HOME/network

$ vi tnsnames.ora

Add the below new entries mentioning host & port details of Standby database.

-------------------------------------------------------------------

tns_stdby =

    (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.48)(PORT = 1531))

        (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = stdby)

        )

    )

-------------------------------------------------------------------

9.       Check the tns entries configured properly.

$ cd

$ vi .bash_profile

export TNS_ADMIN=$HOME/network

$ . .bash_profile

Now from primary server use tnsping command and check the listener availability.

$ tnsping tns_stdby

10.   Copy your primary database password file to the standby database system.

$ cd $ORACLE_HOME/dbs

$ cp orapworcl orapwstdby

11.   On the primary database host, create PFILE and copy it to standby server as initstdby.ora in $ORACLE_HOME/dbs location.

$ sqlplus / as sysdba

SQL> create pfile='$HOME/initprod.ora' from spfile;

Copy the pfile into the oracle’s default location $ORACLE_HOME/dbs

$ cp $HOME/initorcl.ora $ORACLE_HOME/dbs/initstdby.ora

12.   Edit initstdby.ora file on standby server.

$ cd $ORACLE_HOME/dbs

$ vi initstdby.ora

-> remove all *.__ parameter entries

-> wherever "orcl", replace it with "stdby" for control_files, db_create_file_dest, db_recovery_file_dest, diagnostic_dest

-> change "stdby" to "orcl" for parameter log_archive_dest_2.

-> add new parameters, from below and save the file.

db_name=orcl

log_archive_max_processes='5'

standby_file_management='AUTO'

fal_server='orcl'

fal_client='stdby'

db_file_name_convert='/disk1/oradata/orcl','/disk1/oradata/stdby',

’/disk2/oradata/orcl','/disk2/oradata/stdby'

 

log_file_name_convert='/disk1/oradata/orcl','/disk1/oradata/stdby',

’/disk2/oradata/orcl','/disk2/oradata/stdby'

TERMINAL-2:

13.   On the standby host, create required directories in /disk1, /disk2 & /disk3

$ mkdir /disk1/oradata/stdby

$ mkdir /disk2/oradata/stdby

$ mkdir /disk3/oradata/stdby/fra

14.   Start the instance with your parameter file just updated.

$ sqlplus / as sysdba

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initstdby.ora';

SQL> EXIT

 

TERMINAL-1:

15.   On the primary database host, Invoke RMAN and connect as SYSDBA to the target database & auxiliary database.

$ rman target sys/oracle auxiliary sys/[email protected]_stdby

Execute script to create standby database

RMAN> run {

duplicate target database for standby from active database

spfile parameter_value_convert 'orcl','stdby' set db_unique_name='stdby';

}

RMAN> EXIT

 

Verifying That the Physical Standby Database Is Performing Correctly

 

TERMINAL-2:

16.   On the standby database, identify the existing archived redo log files.

SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;

 

TERMINAL-1:

17.   Perform a log switch on the primary database.

$ sqlplus / as sysdba

SQL> alter system switch logfile;

SQL> alter system switch logfile;

 

TERMINAL-2:

 

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

18.   On physical standby database, Start Redo Apply.

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;







Anusha
Software Trainer

Test

Related Course

Oracle 12C DBA

( 5)
Reviews : 2,567

Classroom

 10,000

 14,000

29% Off

Online

 20,000

 22,000

9% Off

Related Documents

Oracle 12c Container database creation manually ( step-by-step)

Oracle 12c database is a MULTITENANT CONTAINER DATABASE provides space for creating pluggable databases (Eg Pluggable databases are applications like any OLTP databases etc.,) Oracle 12c database is a Multi-tenant container database in which a seed  database called pdb$seed comes with it, Using which we can create pluggable databases with in the container database.  In One Container 253 pdbs can be created including pdbseed.

Oracle 12c Physical standby database configuration (step-by-step)

Here will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is also installed on standby server.

12c Oracle Database Creation manually (step-by-step)

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.

0 Comments

Post a Comment