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.xoffice.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 : xxx ---à 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.xoffice.com
$ ssh devr12.xofficec.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.xoffice.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.xoffice.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = CLONE)
)
)
CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.xoffice.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.xoffice.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.xoffice.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONE)
)
)
CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.xoffice.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.xoffice.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.xoffice.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.xoffice.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CLONE)))
OK (10 msec)
[test@test dbs]$
--------------------------------------------------------------------------------------------------------------------------------
*** $tnsping 192.168.0.99 , tnsping test.xoffice.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/pwd@CLONE_STANDBY as sysdba
$sqlplus system/sadha@CLONE_STANDBY as sysdba
Enter user name : system
Pwd : sadha
*** From Standby side
$ [test@test ~]$ sqlplus sys/pwd@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
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.xoffice.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 : xxx ---à 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.xoffice.com
$ ssh devr12.xofficec.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.xoffice.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.xoffice.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = CLONE)
)
)
CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.xoffice.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.xoffice.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.xoffice.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONE)
)
)
CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.xoffice.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.xoffice.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.xoffice.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.xoffice.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CLONE)))
OK (10 msec)
[test@test dbs]$
--------------------------------------------------------------------------------------------------------------------------------
*** $tnsping 192.168.0.99 , tnsping test.xoffice.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/pwd@CLONE_STANDBY as sysdba
$sqlplus system/sadha@CLONE_STANDBY as sysdba
Enter user name : system
Pwd : sadha
*** From Standby side
$ [test@test ~]$ sqlplus sys/pwd@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
Comments
Post a Comment