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.
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
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..
No comments:
Post a Comment