Tutorials to .com

Tutorials to .com » Database » Sqlserver » uard configuration StepbyStep

uard configuration StepbyStep

Print View , by: iSee ,Total views: 14 ,Word Count: 2829 ,Date: Wed, 26 Aug 2009 Time: 10:15 AM

uard configure Step by Step
link: http://www.eygle.com/ha/dataguard-step-by-step.htm 1. the primary node back up and generate the standby database control file to set the primary node to force Logging mode (for bi-directional switch, the proposed standby node also is set to force logging mode)
ALTER database FORCE LOGGING;

Set the primary node for the archive log mode

Landing master node, the database backup, and generate the standby database control file




Last login: Mon Aug 9 16:46:47 2004 from 172.16.32.65 [root @ standby root] # su - oracle [oracle @ standby oracle] $ sqlplus "/ as sysdba" SQL * Plus: Release 9.2.0.4.0 -- Production on Mon Aug 16 10:16:18 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total system Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> select name from v $ datafile; NAME ------------------------------ ------------------------------/opt/oracle/oradata/primary/system01.dbf/opt/oracle/oradata/primary / undotbs01.dbf/opt/oracle/oradata/primary/users01.dbfSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2. 0.4.0 - Production [oracle @ standby oracle] $ lsadmin dictionary.ora jre oradata oraInventory oui product soft [oracle @ standby oracle] $ tar-cvf oradata.tar oradataoradata / oradata / primary / oradata / primary / archive / oradata / primary / control01.ctloradata/primary/control02.ctloradata/primary/control03.ctloradata/primary/redo01.logoradata/primary/redo02.logoradata/primary/redo03.logoradata/primary/system01.dbforadata/primary/undotbs01.dbforadata/primary/temp01 .dbforadata/primary/users01.dbf [oracle @ standby oracle] $ ls-l *. tar-rw-r - r - 1 oracle dba 576512000 Aug 16 10:22 oradata.tar [oracle @ standby oracle] $ iduid = 800 (oracle) gid = 800 (dba) groups = 800 (dba) [oracle @ standby oracle] $ hostnamestandby [oracle @ standby oracle] $ sqlplus "/ as sysdba" SQL * Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 10:27:54 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> archive log list; Database log mode Archive ModeAutomatic archival EnabledArchive destination / opt / oracle / oradata / primary / archiveOldest online log sequence 88Next log sequence to archive 90Current log sequence 90SQL> alter database create standby controlfile as / opt / oracle / stdcotrl.ctl; Database altered.SQL>! ls [oracle @ standby oracle] $ lsadmin dictionary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl








2. From the main node to create pfile file



SQL> create pfile from spfile;

File created.

SQL>!

[oracle @ standby oracle] $ cd $ ORACLE_HOME / dbs
[oracle @ standby dbs] $ ls
initdw.ora init.ora initprimary.ora lkPRIMARY orapwprimary spfileprimary.ora sqlnet.log







3. Landing standby node, ftp access to database files, backup control files and parameter files






Last login: Mon Aug 16 08:47:58 2004 from 172.16.32.65 [root @ eygle root] # su - oracle [oracle @ eygle oracle] $ lsadmin doc jre oradata oraInventory oui product [oracle @ eygle oracle] $ df-kFilesystem 1K-blocks Used Available Use% Mounted on/dev/sda1 5154852 3360600 1532396 69% / / dev/sda7 101089 25744 70126 27% / home/dev/sda5 4127076 2686152 1231280 69% / optnone 515296 0 515296 0% / dev / shm / dev/sda2 4127108 2218172 1699288 57% / usr/dev/sda6 2063504 107744 1850940 6% / var [oracle @ eygle oracle] $ ftp 172.16.33.58Connected to 172.16.33.58 (172.16.33.58) .220 (vsFTPd 1.2.0 ) Name (172.16.33.58: root): oracle331 Please specify the password.Password: 230 Login successful.ftp> ls227 Entering Passive Mode (172,16,33,58,222,252) 150 Here comes the directory listing.drwxr-xr-x 3 800 800 4096 Jun 30 07:02 admin-rw-r - r - 1 800 800 5422 222 Jul 13 11:58 dictionary.ora-rw-r--r - 1 800 800 1165 Aug 16 02:51 initprimary. oradrwxrwxr-x 4 800 800 4096 Jun 30 06:29 jredrwxrwxr-x 12 800 800 4096 Jun 30 06:44 oraInventorydrwxr-xr-x 3 800 800 4096 Jul 01 06:15 oradata-rw-r - r - 1 800 800 576512000 Aug 16 02:22 oradata.tardrwxrwxr-x 6 800 800 4096 Jun 30 06:29 ouidrwxr-xr-x 3 800 800 4096 Jun 30 05:18 productdrwxr-xr-x 6 800 800 4096 Jun 30 04:24 soft -rw-r ----- 1 800 800 1662 976 Aug 16 02:37 stdcotrl.ctl226 Directory send OK.ftp> bin200 Switching to Binary mode.ftp> mget oradata.tarmget oradata.tar? y227 Entering Passive Mode (172, 16,33,58,238,132) 150 Opening BINARY mode data connection for oradata.tar (576512000 bytes) .226 File send OK.576512000 bytes received in 49.2 secs (1.1e +04 Kbytes / sec) ftp> mget *. ctlmget stdcotrl.ctl ? y227 Entering Passive Mode (172,16,33,58,73,35) 150 Opening BINARY mode data connection for stdcotrl.ctl (1662976 bytes) .226 File send OK.1662976 bytes received in 0.14 secs (1.2e +04 Kbytes / sec) ftp> mget initprimary.oramget initprimary.ora? y227 Entering Passive Mode (172,16,33,58,194,239) 150 Opening BINARY mode data connection for initprimary.ora (1165 bytes) .226 File send OK.1165 bytes received in 0.000325 secs (3.5e +03 Kbytes / sec) ftp> bye221 Goodbye. [oracle @ eygle oracle] $ lsadmin doc initprimary.ora jre oradata oradata.tar oraInventory oui product stdcotrl.ctl [oracle @ eygle oracle] $ mv initprimary.ora $ ORACLE_HOME / dbs [oracle @ eygle oracle] $ cd $ ORACLE_HOME / dbs [oracle @ eygle dbs] $ lsa.sql initdw.ora init.ora initprimary.ora initrac1.ora initrac2.ora initrac.ora orapw orapwrac1 orapwrac2 spfilerac.ora unpack data file [oracle @ eygle oracle] $ lsadmin doc jre oradata oradata.tar oraInventory oui product stdcotrl.ctl [oracle @ eygle oracle] $ tar-xvf oradata.taroradata / oradata / primary / oradata / primary / archive / oradata / primary/control01.ctloradata/primary/control02.ctloradata/primary/control03.ctloradata/primary/redo01.logoradata/primary/redo02.logoradata/primary/redo03.logoradata/primary/system01.dbforadata/primary/undotbs01.dbforadata/primary / temp01.dbforadata/primary/users01.dbf modify the control file changes initprimary.ora file name and path (and the original configuration if different) to increase the number of parameters, modified as follows: [oracle @ eygle dbs] $ cat initprimary.ora *. aq_tm_processes = 1 *. background_dump_dest = / opt / oracle / admin / primary / bdump *. compatible = 9.2.0.0.0 *. control_files = / opt / oracle / oradata / primary / stdcotrl.ctl *. core_dump_dest = / opt / oracle / admin / primary / cdump ...*. log_archive_dest_1 = LOCATION = / opt / oracle / oradata / primary / archive *. log_archive_dest_2 =*. log_archive_format =% t_% s.dbf *. log_archive_start = true ...*. user_dump_dest = / opt / oracle / admin / primary / udump *. utl_file_dir = / opt / oracle *. standby_archive_dest = / opt / oracle / oradata / primary / stdarch *. fal_server = PRIMARY *. fal_client = STANDBY *. standby_file_management = AUTO to create the necessary directory [oracle @ eygle oracle] $ cd $ ORACLE_BASE / admin [oracle @ eygle admin] $ mkdir primary [oracle @ eygle admin] $ lsprimary rac [oracle @ eygle admin] $ cd primary / [oracle @ eygle primary] $ ls [oracle @ eygle primary] $ mkdir bdump cdump udump




4. Configure the master node listener and the tnsnames.ora file configured as follows:



[oracle @ standby oracle] $ cd / opt/oracle/product/9.2.0/network/admin / [oracle @ standby admin] $ cat listener.ora # LISTENER.ORA Network Configuration File: / opt/oracle/product/9.2 .0/network/admin/listener.ora # Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC))) (ADDRESS_LIST = (ADDRESS = ( PROTOCOL = TCP) (HOST = standby) (PORT = 1521))))) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = / opt/oracle/product/9.2.0) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = / opt/oracle/product/9.2.0) (SID_NAME = primary))) [oracle @ standby admin] $ cat tnsnames.ora # TNSNAMES.ORA Network Configuration File: / opt/oracle/product/9.2.0/network/admin/tnsnames.ora # Generated by Oracle configuration tools.STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.46) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.58) (PORT = 1521))) ( CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) [oracle @ standby admin] $ lsnrctl startLSNRCTL for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:31 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.Starting / opt/oracle/product/9.2.0/bin/tnslsnr: please wait ... TNSLSNR for Linux: Version 9.2.0.4.0 - ProductionSystem parameter file is / opt / oracle / product/9.2.0/network/admin/listener.oraLog messages written to / opt/oracle/product/9.2.0/network/log/listener.logListening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC))) Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = standby) (PORT = 1521))) Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC))) STATUS of the LISTENER ------------------------ Alias LISTENERVersion TNSLSNR for Linux: Version 9.2.0.4.0 - ProductionStart Date 16-AUG-2004 10: 46:31 Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity OFFSNMP OFFListener Parameter File / opt/oracle/product/9.2.0/network/admin/listener.oraListener Log File / opt/oracle/product/9.2.0 / network / log / listener.logListening Endpoints Summary ... (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC))) (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = standby) (PORT = 1521 ))) Services Summary ... Service "PLSExtProc" has 1 instance (s). Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service ... Service "primary" has 1 instance (s). Instance "primary", status UNKNOWN, has 1 handler (s) for this service ... The command completed successfully




5. Configure standby database listener and the tnsnames.ora file

Configuration file is as follows:




[oracle @ eygle admin] $ cd $ ORACLE_HOME / network / admin [oracle @ eygle admin] $ cat listener.ora # LISTENER.ORA Network Configuration File: / opt/oracle/product/9.2.0/network/admin/listener. ora # Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = eygle) (PORT = 1521))))) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = ( GLOBAL_DBNAME = primary) (ORACLE_HOME = / opt/oracle/product/9.2.0) (SID_NAME = primary))) [oracle @ eygle admin] $ cat tnsnames.ora # TNSNAMES.ORA Network Configuration File: / opt / oracle / product / 9.2.0/network/admin/tnsnames.ora # Generated by Oracle configuration tools.STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.46) (PORT = 1521))) ( CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.58) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) [oracle @ eygle admin] $




6. In standby node test network connectivity using tnsping




[oracle @ standby admin] $ tnsping standbyTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:50 Copyright (c) 1997 Oracle Corporation. All rights reserved.Used parameter files: / opt / oracle/product/9.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.46) (PORT = 1521) )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) OK (10 msec) [oracle @ standby admin] $ tnsping primaryTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:46:55 Copyright (c) 1997 Oracle Corporation. All rights reserved.Used parameter files: / opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = ( ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.58) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) OK (0 msec) [oracle @ eygle admin] $ tnsping primaryTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:01 Copyright (c) 1997 Oracle Corporation. All rights reserved.Used parameter files: / opt/oracle/product/9.2 .0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.33.58) (PORT = 1521))) (CONNECT_DATA = ( SERVER = DEDICATED) (SERVICE_NAME = primary))) OK (50 msec) [oracle @ eygle admin] $ tnsping standbyTNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 16-AUG-2004 10:10:06 Copyright ( c) 1997 Oracle Corporation. All rights reserved.Used parameter files: / opt/oracle/product/9.2.0/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = ( PROTOCOL = TCP) (HOST = 172.16.33.46) (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary))) OK (10 msec)




7. Standby database



[oracle @ eygle primary] $ hostname
eygle
[oracle @ eygle primary] $ sqlplus "/ as sysdba"

SQL * Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 11:09:40 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.









8. In the primary node, set the archive path



SQL> alter system set log_archive_dest_2 = service = standby mandatory reopen = 60;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>

Observation of the log on the standby node

[oracle @ eygle bdump] $ tail-f alert_primary.log
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 90
Datafile 1: / opt/oracle/oradata/primary/system01.dbf
Starting datafile 2 recovery in thread 1 sequence 90
Datafile 2: / opt/oracle/oradata/primary/undotbs01.dbf
Starting datafile 3 recovery in thread 1 sequence 90
Datafile 3: / opt/oracle/oradata/primary/users01.dbf
Media Recovery Waiting for thread 1 seq # 90
Mon Aug 16 11:10:50 2004
Completed: alter database recover managed standby database di
Mon Aug 16 11:13:34 2004
Media Recovery Log / opt/oracle/oradata/primary/stdarch/1_90.dbf
Media Recovery Waiting for thread 1 seq # 91
Media Recovery Log / opt/oracle/oradata/primary/stdarch/1_91.dbf
Media Recovery Waiting for thread 1 seq # 92
Mon Aug 16 12:09:38 2004
Media Recovery Log / opt/oracle/oradata/primary/stdarch/1_92.dbf







9. In the same master node configuration, in order to continue after the log transfer switch



[oracle @ standby oracle] $ ls
admin dictionary.ora initprimary.ora jre oradata oradata.tar oraInventory oui product soft stdcotrl.ctl
[oracle @ standby oracle] $ cd oradata
[oracle @ standby oradata] $ ls
primary
[oracle @ standby oradata] $ cd primary /
[oracle @ standby primary] $ ls
archive control02.ctl redo01.log redo03.log temp01.dbf users01.dbf
control01.ctl control03.ctl redo02.log system01.dbf undotbs01.dbf
[oracle @ standby primary] $ mkdir stdarch
[oracle @ standby primary] $ exit
exit


SQL> alter system set standby_archive_dest = / opt / oracle / oradata / primary / stdarch;

System altered.







10. To stop the primary database, enable the standby database




SQL> alter database commit to switchover to physical standby; Database altered.SQL> shutdown immediateORA-01507: database not mountedORACLE instance shut down. In the standby mode to enable the primary data SQL> startup nomount; ORACLE instance started.Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database; Database altered.SQL> select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v $ database; NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE --------- -- --------- -------------------- ---------------- PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBYSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. Open the standby database [oracle @ eygle oracle] $ sqlplus "/ as sysdba" SQL * Plus: Release 9.2.0.4.0 - Production on Mon Aug 16 12:11 : 11 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Real application Clusters, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> alter database commit to switchover to primary; Database altered.SQL> shutdown immediate; ORA-01507: database not mountedORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> alter system switch logfile; System altered. in the main library to observe the application of the log [oracle @ standby bdump] $ tail-f alert_primary.log Starting datafile 2 recovery in thread 1 sequence 93Datafile 2 : / opt/oracle/oradata/primary/undotbs01.dbfStarting datafile 3 recovery in thread 1 sequence 93Datafile 3: / opt/oracle/oradata/primary/users01.dbfMedia Recovery Log / opt/oracle/product/9.2.0/dbs / arch1_93.dbfMon Aug 16 15:08:43 2004Media Recovery Log / opt/oracle/oradata/primary/stdarch/1_94.dbfMedia Recovery Waiting for thread 1 seq # 95Media Recovery Log / opt/oracle/oradata/primary/stdarch/1_95. dbfMedia Recovery Waiting for thread 1 seq # 96




11. For data changes




SQL> create table t as select * from dba_users; Table created.SQL> alter system switch logfile; System altered. From the library to read only open the database, execute the query SQL> select username from t; select username from t * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables / views onlySQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.SQL> ALTER DATABASE OPEN READ ONLY; Database altered.SQL> select username from t; USERNAME ------------------------------ SYSSYSTEMDBSNMPOUTLNWMSYSSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered.SQL>




12. To switch back to the primary node in the database




The primary node SQL> alter database commit to switchover to physical standby; Database altered.SQL> shutdown immediateORA-01507: database not mountedstatORACLE instance shut down.SQL> startup nomount; ORACLE instance started.Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesSQL> alter database mount standby database; Database altered.SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. on the standby node SQL> alter database commit to switchover to primary; Database altered.SQL > shutdown immediate; ORA-01507: database not mountedORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 135337420 bytesFixed Size 452044 bytesVariable Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.




Complete freedom to switch


Microsoft SQL Server Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.