Physical Standby Database

Physical Standby Database.
Windows XP sp2
Oracle Version : 10.1.0.2.0 Enterprise Edition.

Primary Database : ORCL
Standby Database : ORCLSTDY


Preparing the Primary Database for Standby Database Creation
1.Enable Forced Logging

SQL> conn taj as sysdba
Enter password:
Connected.
SQL> --first check database in FORCE_LOGGING mode .
SQL> select FORCE_LOGGING from v$database;

FOR
---
NO

SQL> alter database FORCE LOGGING;

Database altered.

2.Create a Password File
Note: if PRIMARY DATABASE password file is not exists then create new one.
C:\> ORAPWD FILE=%ORACLE_HOME%/database/pwdORCL.ora password=ORACLE entries=5

C:\>dir c:\oracle\product\10.1.0\db_1\database\pwd*.ora
Volume in drive C has no label.
Volume Serial Number is 78B6-6F7D

Directory of c:\oracle\product\10.1.0\db_1\database

03-14-2007 09:42 AM 1,536 PWDorcl.ora
1 File(s) 1,536 bytes
0 Dir(s) 19,444,805,632 bytes free

3.Setting Primary Database Initialization Parameters
Database
ORCL
ORCLSTDY

DB_UNIQUE_NAME
ORCL
ORCLSTDY

Oracle Net Service Name
ORCL
ORCLSTDY

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> alter system set DB_UNIQUE_NAME=orcl scope=spfile;

System altered.

SQL> alter system set SERVICE_NAMES=orcl scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=c:\archive_orcl
2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3 DB_UNIQUE_NAME=orcl';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCLSTDY
2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3 DB_UNIQUE_NAME=orclstdy';

System altered.



SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=enable;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

System altered.

SQL> alter system set LOG_ARCHIVE_FORMAT='ARC%T_%R_%S.log' SCOPE=spfile;

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive SCOPE=spfile;

System altered.

Primary Database: Standby Role Initialization Parameters

SQL> alter system set FAL_SERVER=ORCLSTDY;

System altered.

SQL> alter system set FAL_CLIENT=ORCL;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\ORCL',
3 'c:\oracle\product\10.1.0\oradata\ORCLSTDY' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\ORCL',
3 'c:\oracle\product\10.1.0\oradata\ORCLSTDY' scope=spfile;

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

4.Enable Archiving
If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Note: If ARCHIVELOG enable then you just shutdown + startup oracle server for effect above parametes settings.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP

6.Creating a Physical Standby Database
Create a Backup Copy of the Primary Database Datafiles

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host COPY -
> c:\oracle\product\10.1.0\oradata\ORCL\*.DBF
c:\oracle\product\10.1.0\oradata\ORCL\EXAMPLE01.DBF
0 file(s) copied.


SQL> host COPY -
> c:\oracle\product\10.1.0\oradata\ORCL\*.DBF -
> c:\oracle\product\10.1.0\oradata\ORCLSTDY
c:\oracle\product\10.1.0\oradata\ORCL\EXAMPLE01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\SYSAUX01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\SYSTEM01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\TEMP01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\UNDOTBS01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\USERS01.DBF


Create a Control File for the Standby Database

SQL> startup mount
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

SQL> alter database CREATE STANDBY CONTROLFILE AS -
> 'c:\oracle\product\10.1.0\oradata\ORCLSTDY\control01.ctl';

Database altered.

SQL> alter database open;

Database altered.

Prepare an Initialization Parameter File for the Standby Database

SQL> create PFILE='c:\oracle\product\10.1.0\db_1\database\INITorclstdy.ora'
2 from SPFILE;

File created.

Modifying Initialization Parameters for a Physical Standby Database
Note: Just Modify BOLD parameters.

*.background_dump_dest='C:\oracle\product\10.1.0\admin\ORCLSTDY\bdump'

*.compatible='10.1.0.2.0'

*.control_files='C:\oracle\product\10.1.0\oradata\orclstdy\control01.ctl'

*.core_dump_dest='C:\oracle\product\10.1.0\admin\orclstdy\cdump'

*.db_block_size=8192

*.db_cache_size=25165824

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='c:\oracle\product\10.1.0\oradata\ORCL','c:\oracle\product\10.1.0\oradata\ORCLSTDY'

*.db_name='orcl'

*.db_unique_name='ORCLSTDY'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='ORCLSTDY'

*.fal_server='ORCL'

*.java_pool_size=50331648

*.job_queue_processes=10

*.large_pool_size=8388608

*.log_archive_dest_1='LOCATION=c:\archive_ORCLSTDY
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orclstdy'

*.log_archive_dest_2='SERVICE=ORCL
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'


*.log_archive_format='ARC%T_%R_%S.log'

*.log_file_name_convert='c:\oracle\product\10.1.0\oradata\ORCL','c:\oracle\product\10.1.0\oradata\ORCLSTDY'

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='ORCLSTDY'

*.shared_pool_size=83886080

*.sort_area_size=65536

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:\oracle\product\10.1.0\admin\orclstdy\udump'

INSTANCE_NAME=ORCLSTDY

7.Set Up the Environment to Support the Standby Database

Step 1 Create a Windows-based service.

C:\>oradim -NEW -SID orclstdy -INTPWD oracle -STARTMODE manual
Instance created.

Enable broken connection detection on the standby system.
Add SQLNET.EXPIRE_TIME=2 in SQLNET.ORA FILE.

Create Oracle Net service names.
Add below lines in TNSNAMES.ORA file.

ORCLSTDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.255)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclstdy)
)
)
Note: YOu can also configure through NET CONFIGURATION ASSISTANT (netca).

Create a server parameter file for the standby database.
C:\>set oracle_sid=ORCLSTDY

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 14 10:54:34 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> create SPFILE from PFILE;

File created.

Start the Physical Standby Database

SQL> STARTUP OPEN READ ONLY;

ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

To add temporary files to the physical standby database

SQL> select tablespace_name from dba_tablespaces
2 where contents = 'TEMPORARY';

TABLESPACE_NAME
------------------------------
TEMP

Note:
To create temporary files on the physical standby database that match the temporary files on the primary database, query the V$TEMPFILE view on the primary database to obtain complete information about the primary database temporary files.

SQL> conn taj@ORCL as sysdba
Enter password:
Connected.
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF

SQL> conn sys@ORCLSTDY as sysdba
Enter password:
Connected.

SQL> alter tablespace TEMP
2 add TEMPFILE 'c:\oracle\product\10.1.0\oradata\ORCLSTDY\temp01.dbf'
3 SIZE 100M REUSE;

Tablespace altered.

Start Redo Apply.

SQL> alter database RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Test archival operations to the physical standby database
SQL> conn taj@ORCL as sysdba
Enter password:
Connected.
SQL> alter system switch logfile;

System altered.

Verify the Physical Standby Database Is Performing Properly
On the standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Test archival operations to the physical standby database.
SQL>conn taj@ORCL as sysdba
Password :

SQL> alter system switch logfile;

System altered.

Verify the Physical Standby Database Is Performing Properly
On the standby database
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Force a log switch to archive the current online redo log file.
On the primary database

ALTER SYSTEM ARCHIVE LOG CURRENT;

Verify the new redo data was archived on the standby database.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Verify new archived redo log files were applied.
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

Hope this helps
Taj