Tuesday, May 31, 2016

step by step process of creating physical standby database

Steps to create a Physical standby database:


1). Prepare primary database for standby database creation

      -- Enable archiving

      -- Enable force logging

      -- Set primary database initialization parameters

      -- Configure standby redo logs

2). Prepare parameter file

      -- Create p-file of primary database and then copy that created p-file to standby server.

      -- Now on standby server, Open that p-file and do the required changes of parameter values.

      -- Make required directory structures specified in p-file and change the permissions, owner & group of those directories.


3). Create the standby database instance using prepared p-file.

4). Create password files for both servers.

5). Create Listener services for both the servers.

6). Create TnsNames files for both the servers.

7). Invoke RMAN from primary database.

8). Now Create physical standby database using RMAN from primary. 





Physical standby database Creation:



Primary DB ServerStandby DB Server
Host Name:sri.comram.com
IP Address:192.168.1.10192.168.1.15
DB_Name:primeprime
DB_Unique_Name:primestand
1). Prepare primary database for standby database creation

      -- Enable archiving

      -- Enable force logging

      -- Set primary database initialization parameters

      -- Configure standby redo logs


PRIMARY DB SERVERJ

[root@ram ~]#ping 192.168.1.10

[root@ram ~]#ping 192.168.1.15

[root@ram ~]# su – oracle

 [oracle@ram ~]$ export ORACLE_SID=prime

 [oracle@ram ~]$ sqlplus / as sysdba

SQL> select status from v$instance;

STATUS
------------
OPEN


SQL> select name,open_mode,log_mode,force_logging from v$database;

NAME      OPEN_MODE            LOG_MODE           FOR
--------- -------------------- ------------ ----------------------------
PRIME     READ WRITE           NOARCHIVELOG      NO


  è Database ‘PRIME’ must be in archive log mode (  Enable Archiving  )

SQL> shut immediate

SQL> startup mount

SQL> archive log list

Database log mode                         No Archive Mode
Automatic archival                         Disabled
Archive destination                        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence            1
Current log sequence                     2


SQL> alter database archivelog;

Database altered.


SQL> archive log list

Database log mode                         Archive Mode
Automatic archival                         Enabled
Archive destination                        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence            1
Next log sequence to archive         2
Current log sequence                     2


SQL> alter database open;

Database altered.


SQL> select name,open_mode,log_mode,force_logging from v$database;

NAME      OPEN_MODE            LOG_MODE           FOR
--------- -------------------- ------------ ----------------------------
PRIME     READ WRITE              ARCHIVELOG         NO




   è Database ‘PRIME’ must be in force logging mode (   Enable Forced Logging   )

SQL> select force_logging from v$database;

SQL> alter database force logging;

SQL> select force_logging from v$database;


   è Now we need to set/check some required parameters
 (  Set Primary Database Initialization Parameters  )

SQL> show parameter db_name

SQL> show parameter db_unique_name

SQL> show parameter log_archive_config
SQL> alter system set log_archive_config='dg_config=(prime,stand)' scope=both;
SQL> show parameter log_archive_config


SQL> show parameter log_archive_dest_1
SQL> ho

[oracle@ram ~]$ mkdir -p /u01/app/oracle/oradata/prime/arch
[oracle@ram ~]$ exit

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prime/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prime' scope=both;

SQL> show parameter log_archive_dest_1

SQL> show parameter log_archive_dest_2

SQL> alter system set log_archive_dest_2='SERVICE=stand LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=stand' scope=both;

SQL> show parameter log_archive_dest_2


SQL> show parameter fal
SQL> alter system set fal_server=stand scope=both;
SQL> alter system set fal_client=prime scope=both;
SQL> show parameter fal

SQL> show parameter service
                :prime

SQL> show parameter remote
remote_login_passwordfile              string              EXCLUSIVE


SQL> show parameter stand
SQL> alter system set standby_file_management='AUTO' scope=both;
SQL> show parameter stand

SQL> show parameter convert
SQL> alter system set db_file_name_convert='stand','prime' scope=spfile;
SQL> alter system set log_file_name_convert='stand','prime' scope=spfile;
SQL> show parameter convert

SQL> show parameter log_archive_max_processes
SQL> alter system set log_archive_max_processes=30 scope=both;
SQL> show parameter log_archive_max_processes



   è Now we need to ADD standby logfiles  (  Configure a Standby Redo Log  )

SQL> select member from v$logfile;
SQL> select group#,thread#,members,bytes/(1024*1024) "SIZE IN MB" from v$log;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/prime/sredo01.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/prime/sredo02.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/prime/sredo03.log' size 50m;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/prime/sredo04.log' size 50m;

SQL> select group#,member from v$logfile;
SQL> select member from v$logfile where type='STANDBY';
SQL> select  group#, thread#, sequence#, archived, status  from v$standby_log;




2). Prepare parameter file

      -- Create p-file of primary database and then copy that created p-file to standby server.

      -- Now on standby server, Open that p-file and do the required changes of parameter values.

      -- Make required directory structures specified in p-file and change the permissions, owner & group of those directories.



   è Now we need to create a P-FILE

SQL> create  pfile='/u02/initprime.ora'  from  spfile;



   è Now COPY the P-FILE to standby database server’s  $ORACLE_HOME/dbs location

[root@ram ~]# scp    /u02/initprime.ora    192.168.1.15:/u01/app/oracle/product/11.2.0/dbhome_1/dbs




STANDBY DB SERVER:



   è Now we need to open & modify the P-FILE  parameters


[root@sri ~]# su - oracle

[oracle@sri ~]$ cd $ORACLE_HOME/dbs

[oracle@sri dbs]$ ls

[oracle@sri dbs]$ vi initprime.ora

*.db_unique_name='stand'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prime/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=stand'

*.log_archive_dest_2='SERVICE=prime  LGWR   ASYNC  VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)  DB_UNIQUE_NAME=prime'

*.fal_server='prime'

*.fal_client='stand'

:wq!


[oracle@sri dbs]$ cat initprime.ora



   è Now we need to create required directory structures and change the permissions & owner,group

[oracle@sri dbs]$ mkdir -p /u01/app/oracle/admin/prime/adump
[oracle@sri dbs]$ mkdir -p /u01/app/oracle/flash_recovery_area/prime
[oracle@sri dbs]$ mkdir -p /u01/app/oracle/oradata/prime/arch

[oracle@sri dbs]$ chmod -R 777 /u01/app/oracle/admin/prime/adump
[oracle@sri dbs]$ chmod -R 777 /u01/app/oracle/flash_recovery_area/prime
[oracle@sri dbs]$ chmod -R 777 /u01/app/oracle/oradata/prime/arch

[oracle@sri dbs]$ chown -R oracle:oinstall /u01/app/oracle/admin/prime/adump
[oracle@sri dbs]$ chown -R oracle:oinstall /u01/app/oracle/flash_recovery_area/prime
[oracle@sri dbs]$ chown -R oracle:oinstall /u01/app/oracle/oradata/prime/arch



   è Now we need to update the ORATAB file

[oracle@sri dbs]$ vi /etc/oratab

prime:/u01/app/oracle/product/11.2.0/home:N

:wq!



   è Now we need to open the standby database into NOMOUNT mode

[oracle@sri dbs]$ export ORACLE_SID=prime

[oracle@sri dbs]$ sqlplus / as sysdba


SQL> startup nomount

SQL> select  instance_name,status  from  v$instance;



   è Create a Password file

[oracle@ram ~]$ cd   /u01/app/oracle/product/11.2.0/home/dbs/

[oracle@ram dbs]$ orapwd   file=orapwprime  password=rac   entries=3   force=y



   è Create a listener.ora file

[oracle@ram ~]$ cd   /u01/app/oracle/product/11.2.0/home/network/admin/

[oracle@ram admin]$ ls

[oracle@ram admin]$netca

[oracle@ram admin]$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ram.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prime)
      (GLOBAL_DBNAME=stand_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/home)
    )
  )


[oracle@ram admin]$ lsnrctl reload listener

[oracle@ram admin]$ lsnrctl status listener




PRIMARY DB SERVER:



   è Create a Password file

[oracle@ram ~]$ cd   /u01/app/oracle/product/11.2.0/home/dbs/

[oracle@ram dbs]$ orapwd   file=orapwprime   password=rac   entries=3   force=y



   è Create a listener.ora file

[oracle@ram ~]$ cd   /u01/app/oracle/product/11.2.0/home/network/admin/

[oracle@ram admin]$ ls

[oracle@ram admin]$netca


[oracle@ram admin]$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sri.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prime)
      (GLOBAL_DBNAME=prime_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/home)
    )
  )


[oracle@ram admin]$ lsnrctl reload listener

[oracle@ram admin]$ lsnrctl status listener



STANDBY DB SERVER:



   è Create a tnsnames.ora file

[oracle@ram admin]$ netca

[oracle@ram admin]$ tnsping   to_stand

[oracle@ram admin]$ netca

[oracle@ram admin]$ tnsping   to_prime

[oracle@ram admin]$ cat tnsnames.ora


TO_STAND_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stand_DGMGRL)
      (UR=A)
    )
  )


TO_PRIME_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prime_DGMGRL)
      (UR=A)
    )
  )



PRIMARY DB SERVER:



   è Create a tnsnames.ora file

[oracle@ram admin]$ netca

[oracle@ram admin]$ tnsping to_stand

[oracle@ram admin]$ netca

[oracle@ram admin]$ tnsping to_prime

[oracle@ram admin]$ cat tnsnames.ora


TO_STAND_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stand_DGMGRL)
      (UR=A)
    )
  )

TO_PRIME_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prime_DGMGRL)
      (UR=A)
    )
  )



 è Invoke RMAN:

[oracle@ram ~]$ rman target sys/rac@to_prime auxiliary sys/rac@to_stand



 è Create a Physical standby database from primary db:

RMAN> run{
     allocate channel cha1 device type disk;
     allocate channel cha2 device type disk;
     allocate channel cha3 device type disk;
     allocate auxiliary channel ach1 device type disk;
     duplicate target database for standby from active database nofilenamecheck;
}




STANDBY DB SERVER:

 è Checking Standby database:
$export  ORACLE_SID=prime

$sqlplus  / as sysdba

Sql>startup

Sql> select  status  from  v$instance;

Sql>select  open_mode,database_role,protection_mode   from  v$database;

Sql> selec t  sequence#,first_time,next_time,applied  from  v$archived_log  order by  sequence#;

( open alert log file and check the process)

Sql> select  name  from  v$datafile;

Sql> select  name  from  v$controlfile;

Sql> select  member  from  v$logfile;

Sql> select  group#  from  v$archived_log;

Sql>!

$ps –ef | grep  mrp

$ps  -ef | grep  dmon

$exit

Sql> archive log list




PRIMARY DB SERVER:



Sql> archive  log  list

Sql> selec t  sequence#,first_time,next_time,applied  from  v$archived_log  order by  sequence#;

Sql>alter system switch logfile;

Sql>/

Sql>/

Sql> archive  log  list

Sql> selec t  sequence#,first_time,next_time,applied  from  v$archived_log  order by  sequence#;




STANDBY DB SERVER:



Sql> archive  log  list

Sql> selec t  sequence#,first_time,next_time,applied  from  v$archived_log  order by  sequence#;


Sql> recover  managed standby  database  disconnect  from  session  using  current logfile;


Sql>archive  log  list

Sql> select  sequence#,first_time,next_time,applied  from  v$archived_log  order by  sequence#;


Sql> select  thread#, max(sequence#)  from  v$archived_log  where  applied = ‘YES’  group  by  thread#;

Sql> !

$ ps –eaf | grep  mrp

$ ps –eaf | grep  dmon





 èNow open Standby database using SP-FILE:



sql>show parameter spfile

 sql>create spfile from pfile;

 sql>shut immediate

 sql>startup         ==>> It will be opened in Mount stage.

 sql>select name,status from v$instance;

 sql>show parameter spfile



Database TypeOpen Mode
Physical standbyMOUNT
Active standbyREAD ONLY
Snapshot standbyREAD WRITE
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)