CREATE A PHYSCIAL STANDBY DATABASE - ORACLE 10G DATAGUARD CONCEPT.

1. Os versions / Database Versions / Patchset Level should be same.
2. SID should be same , if not use convert parameter in pfile.
· Here SID = CLONE is the same between two servers.

Primary side

Hostname : devr12.pittioffice.com
Sid : CLONE
Ip : 192.168.0.209

a).Install the Linux Operating system , and #setup -à Disable Firewall at OS Level Mandatory
b).Install the Oracle 10g Software
System pwd : sadha ; Sys pwd : pitti ---à these two password should same into two nodes.

Rman configuration -- if require.
Database in the Archive log mode.


c). configure ssh between two servers.

**
Ssh configuration between to nodes ( servers )
1 st Node side side : 192.168.0.209 Primary side.

a). Login – oracle user
b). mkdir ~/.ssh
chmod 755 ~/.ssh
$/usr/bin/ssh-keygen –t rsa
$ /usr/bin/ssh-keygen –t -dsa
$ cd .ssh
$ cat id_rsa.pub > devr12
Cat id_dsa.pub >> devr12

2nd Node side: 192.168.0.99 Standby side.

a). Login – oracle user
b). mkdir ~/.ssh
chmod 755 ~/.ssh
$/usr/bin/ssh-keygen –t rsa
$ /usr/bin/ssh-keygen –t -dsa
$ cd .ssh
$ cat id_rsa.pub > test
Cat id_dsa.pub >> test
$ scp test devr12:/p01/oratest/.ssh






1 st Node side

$ cd .ssh
$ cat devr12 > authorized_keys
$ cat test > > authorized_keys

$ scp authorized_keys test:/p01/oratest/.ssh
**** Establish user equableness
$/usr/bin/ssh-agent $SHELL
$/usr/bin/ssh-add

2 nd Node side
**** Establish user equableness
$/usr/bin/ssh-agent $SHELL
$/usr/bin/ssh-add


**** check the without password we can connect through ssh between 2 servers ( both nodes side )

$ ssh test
$ ssh devr12
$ ssh test.pittioffice.com
$ ssh devr12.pittiofficec.om
$ ssh 192.168.0.99
$ ssh 192.168.0.209










*** Create environment file.

[test@devr12 db_1]$ cat clone.env

export ORACLE_HOME=/p01/oratest/oracle/product/10.2.0/db_1
export ORACLE_SID=CLONE
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/p01/oratest/oracle/product/10.2.0/db_1/network/admin

[test@devr12 db_1]$






d). Configure the Listener.ora at primiary side.

[test@devr12 admin]$ pwd
/p01/oratest/oracle/product/10.2.0/db_1/network/admin

[test@devr12 admin]$ cat listener.ora
# listener.ora Network Configuration File: /p01/oratest/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

CLONE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

SID_LIST_CLONE =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLONE)
(ORACLE_HOME = /p01/oratest/oracle/product/10.2.0/db_1)
)
)






















e).Configure Tnsnames.ora at primary side.


CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = CLONE)
)
)

CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522))
)
(CONNECT_DATA =
(SID=CLONE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLONE)
(PRESENTATION = RO)
)
)






******************************************************************************

** startup the listener at primary side.

$ lsnrctl start CLONE.

f).* Create password file at Primary side. And copy to standby side

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y

Copy to orapwd file to Standby database side.



g). Ad the Standby redolog at Primary side. With same size.


ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/p01/oratest/product/10.2.0/oradata/CLONE/redo004.log') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/p01/oratest/product/10.2.0/oradata/CLONE/redo004.log') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/p01/oratest/product/10.2.0/oradata/CLONE/redo004.log') SIZE 50M;
SQL> select group# from v$standby_log;






















H).Configure Listener.ora at Standby side.
** STANDBY SIDE : listener.ora



CLONE_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522 ))
)
)

SID_LIST_CLONE_STANDBY =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /p01/oratest/oracle/product/10.2.0/db_1)
(SID_NAME=CLONE)
)
)
























i).Configure Tnsnames.ora at Stand by side.

** STANDBY SIDE : tnsnames.ora


CLONE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONE)
)
)

CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID=clone)
)
)


EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)








J. *** check the listener/ tnsnames ping between servers


1. *** from Primary side to ping standby server.

[test@devr12 dbs]$ tnsping clone_standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-JAN-2011 17:03:44

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/p01/oratest/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = CLONE_STANDBY)))
OK (0 msec)
[test@devr12 dbs]$

=========================================================================
2. *** From Standby side to ping primary server


[test@test dbs]$ hostname
test.pittioffice.com
[test@test dbs]$ tnsping CLONE

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-JAN-2011 05:23:12

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/p01/oratest/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CLONE)))
OK (10 msec)
[test@test dbs]$
--------------------------------------------------------------------------------------------------------------------------------
*** $tnsping 192.168.0.99 , tnsping test.pittioffice.com , tnsping TNSNAME.

* Some time : TNS:12560 Tns Protocol adapter error ------ solution At OS level disable firewall




3. * Database connectivity checking from two servers.

** from Primary side.


$ sqlplus sys/pitti@CLONE_STANDBY as sysdba

$sqlplus system/sadha@CLONE_STANDBY as sysdba
Enter user name : system
Pwd : sadha



*** From Standby side

$ [test@test ~]$ sqlplus sys/pitti@CLONE as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 6 22:42:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

$ $sqlplus system/sadha@CLONE as sysdba



















PRIMARY SIDE
** Modify initCLONE.ora file from Primary Side ( Pfile ).

CLONE.__db_cache_size=905969664
CLONE.__java_pool_size=16777216
CLONE.__large_pool_size=16777216
CLONE.__shared_pool_size=285212672
CLONE.__streams_pool_size=0
*.audit_file_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/adump'
*.background_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/p01/oratest/oracle/product/10.2.0/oradata/CLONE/control01.ctl','/p01/oratest/oracle/product/10.2.0/oradata/CLONE/control02.ctl','/p01/oratest/oracle/product/10.2.0/oradata/CLONE/control03.ctl'
*.core_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest_size=39728447488
*.db_recovery_file_dest='/p01/oratest/oracle/product/10.2.0/rman_bkp/flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
*.open_cursors=300
*.pga_aggregate_target=412090368
*.processes=150
*.sga_target=1237319680
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/udump'
*.db_name='CLONE'

# Datagurad – Physcial standby database – parameters

*.db_unique_name='CLONE'
*.fal_client='CLONE'
*.fal_server='CLONE_STANDBY'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/p01/oratest/oracle/product/10.2.0/arch_bkp VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CLONE'

*.log_archive_dest_2='SERVICE=CLONE_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CLONE_STANDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='CLONE_%t%s%r.arc'
*.log_archive_max_processes=30
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_archive_dest='/p01/oratest/oracle/product/10.2.0/arch_bkp'
*.standby_file_management='AUTO'

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



Note : copy the initClone.ora file to Standby Database Location
Note: Dataguard is using spfile file.

Create spfile from primary side.

$sqlplus ‘/as sysdba’
$startup nomount;
$create spfile from pfile;
$ shut immediate;
$exit

** Next login default database is using spfile.




----------------------------------------------------------------------------------------------------------------------------------
STAND BY SIDE.

** Create the all directory structures as per the primary spfile into to the Standby server

** Modify initCLONE.ora file from Standby Side ( Pfile ).

CLONE.__db_cache_size=905969664
CLONE.__java_pool_size=16777216
CLONE.__large_pool_size=16777216
CLONE.__shared_pool_size=285212672
CLONE.__streams_pool_size=0
*.audit_file_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/adump'
*.background_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/p01/oratest/sadhanew/clone.ctl'
*.core_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.open_cursors=300
*.pga_aggregate_target=412090368
*.processes=150
*.sga_target=1237319680
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/udump'
*.db_recovery_file_dest_size=39728447488
*.db_recovery_file_dest='/p01/oratest/oracle/product/10.2.0/rman_bkp/flash_recovery_area'


*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
*.job_queue_processes=10

**** Dataguard / standby database parameters at standby side.

*.db_name='CLONE'
*.db_unique_name='CLONE_STANDBY'

*.fal_client='CLONE_STANDBY'
*.fal_server='CLONE'

*.log_archive_dest_1='LOCATION=/p01/oratest/oracle/product/10.2.0/arch_bkp VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CLONE_STANDBY'

*.log_archive_dest_2='SERVICE=CLONE_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CLONE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.log_archive_max_processes=30
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_archive_dest='/p01/oratest/oracle/product/10.2.0/arch_bkp'
*.standby_file_management='AUTO'







Create spfile from standby side.

$sqlplus ‘/as sysdba’
$startup nomount;
$create spfile from pfile;
$ shut immediate;
$exit

** Next login default database is using spfile.










* PRIMARY SIDE :

* CREATE STANDBY CONTROL FILE AND TAKE COLD BACKUP / copy data to stand by side

Creae standby controlfile

$sqlplus ‘/as sysdba’
Sql>startup
Sql> alter database create standby controlfile as ‘/p01/oratest/sdhanew/standby.ctl’;


Cold backup

Sql> shut immediate;
$ cd /p01/oratest/oracle/product/10.2.0/oradata/CLONE
$ copy *.* /backup.

And copy all the files to Standby Database.

$ scp *.log *.dbf *.ctl test@192.168.0.99:/p01/oratest/oracle/product/10.2.0/oradata/CLONE/.



Copy Standby ctl to Standby side.

$ cd /p01/oratest/sadhanew/
$scp standby.ctl test@192.168.0.99:/p01/oratest/sadhanew/.



















*** Standby Side


a). change the controlfile location in the initClone.ora ( spfile ).

*.control_files='/p01/oratest/sadhanew/clone.ctl'

Save and exit :wq --- enter

$sqlplus ‘/as sysdba’
Sql>startup nomount;
Sql>create spfile from pfile;
Sql>shut immediate;
Sql>exit

b). At standby side database put into MRM mode.

$sqlplus ‘/as sysdba’
Sql>startup nomount;
Sql> alter databse mount standby database;
Sql> alter database recover managed standby database disconnect from session;




==================================================================
· Startup the database at PRIMARY SIDE.

Sql> startup.
Sql> archive log list; -------------- check the current log sequence.

Sql> alter system switch log file ---à manual log switch.


Sql> check the Log apply .. at Standby side. ( check into Alert log file ).


Other Help commands

**
1. Stop Redo apply to Standby
At Standby side:
Sql> alter database recover managed standby database cancel;

2. check that primary and standby are synchronized.
Primary side:
Sql> archive log list;
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PRIMARY READ WRITE

Standby side
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
PHYSICAL STANDBY MOUNTED


3. ACTIVATE the Physical standby database.
Sql> ALTER DATABASE ACTIVATE STANDBY DATABASE;
FOR reverse.
Sql> Alter database covert to physical standby;


4. Register the missed redo.

At standby side ;

sql> alter database register logfile '/............................/......xx.arc'

Help urls:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239.pdf