Physical Standby Database Creation

Primary DataBase : KEYSTONE

Standby DataBase : KEYSTONESTDY

Primary Database – ARHIVELOG Mode

Version : Oracle 10.2.0

Enable Force logging.

SQL> show user

USER is "SYS"

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

YES

Configuring primary database initialization parameter’s

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE

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

db_name string KEYSTONE

SQL> SHOW PARAMETER DB_UNIQUE_NAME

NAME TYPE VALUE

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

db_unique_name string KEYSTONE

SQL> SHOW PARAMETER SERVICE_NAMES

NAME TYPE VALUE

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

service_names string KEYSTONE

Note:

If not the above parameter in your primary init.ora file. Just alter the above three parameter in your database using alter command.

Example:

ALTER SYSTEM SET DB_UNIQUE_NAME=KEYSTONE SCOPE=SPFILE;

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> ALTER SYSTEM SET log_archive_format='ARC%T_%R_%S.LOG' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystone_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONE';

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.

Standy database initialization parameter:

The below parameter only need to configure standby database using primary database.

SQL> ALTER SYSTEM SET FAL_SERVER=KEYSTONESTDY;

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT=KEYSTONE;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;

System altered.

SQL> ALTER SYSETM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Primary database offline backup

Take Operating System level data file backup in primary database to standby database

SQL> HOST COPY -

> F:\oracle\product\10.2.0\oradata\KEYSTONE\*.DBF -

> F:\oracle\product\10.2.0\oradata\KEYSTONESTDY

F:\oracle\product\10.2.0\oradata\KEYSTONE\EXAMPLE01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSAUX01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSTEM01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\TEMP01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\UNDOTBS01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\USERS01.DBF

6 file(s) copied.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes

Variable Size 100664000 bytes

Database Buffers 180355072 bytes

Redo Buffers 7139328 bytes

Database mounted.

To Create Standby Control file

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'F:\oracle\product\10.2.0\oradata\KEYSTONESTDY\CONTROL01.CTL';

Database altered.

Create initialization file for standby database.

SQL> CREATE PFILE='F:\oracle\product\10.2.0\db_1\database\INITKEYSTONESTDY.ORA' FROM SPFILE;

File created.

Edit the following information in init.ora file (standby) : Edit BOLD line only.

KEYSTONESTDY.__db_cache_size=180355072

KEYSTONESTDY.__java_pool_size=4194304

KEYSTONESTDY.__large_pool_size=4194304

KEYSTONESTDY.__shared_pool_size=92274688

KEYSTONESTDY.__streams_pool_size=0

*.audit_file_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/adump'

*.background_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/bdump'

*.compatible='10.2.0.1.0'

*.control_files='F:\oracle\product\10.2.0/oradata/KEYSTONESTDY/\control01.ctl'

*.core_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'

*.db_name='KEYSTONE'

db_unique_name='KEYSTONESTDY'

*.db_recovery_file_dest='F:\oracle\product\10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

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

*.fal_client='KEYSTONESTDY'

*.fal_server='KEYSTONE'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY'

*.log_archive_format='ARC%S_%R.%T'

*.log_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'

*.open_cursors=300

*.pga_aggregate_target=95420416

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

service_names='KEYSTONESTDY'

*.sga_target=287309824

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/udump'

INSTANCE_NAME=KEYSTONESTDY

Create standby instance

C:\>ORADIM -NEW -SID KEYSTONESTDY -INTPWD oracle -STARTMODE manual

Instance created.

Configure Net Service's names

Add the following line in SQLNET.ORA file

sqlnet.expire_time=2

And configure tnsnames.ora file. like

KEYSTONESTDY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adaptime75)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KEYSTONESTDY)
)
)

C:\>SET ORACLE_SID=KEYSTONESTDY

C:\>SQLPLUS SYS AS SYSDBA

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 16 19:48:34 2007

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

Enter password:

Connected to an idle instance.

Create server parameter file

SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> STARTUP OPEN READ ONLY

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes

Variable Size 100664000 bytes

Database Buffers 180355072 bytes

Redo Buffers 7139328 bytes

Database mounted.

Database opened.

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='TEMPORARY';

TABLESPACE_NAME

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

TEMP

SQL> SELECT * FROM V$TEMPFILE;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED

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

BYTES BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

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

1 535157 16-MAY-07 3 1 ONLINE READ WRITE

20971520 2560 20971520 8192

F:\ORACLE\PRODUCT\10.2.0\ORADATA\KEYSTONESTDY\TEMP01.DBF

SQL> SHOW USER

USER is "SYS"

SQL>

Create Redo log file for standby database

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

Database altered.

Note:

Thanks for visit my blog. Please try physical database creation in testing env.If any problem please post your comments..