ORA-16047: DGID mismatch between destination setting and standby

SQL> show parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /db/app/oracle/Db_1/dbs/dr1ORC
                                                 LDG.dat
dg_broker_config_file2               string      /db/app/oracle/Db_1/dbs/dr2ORC
                                                 LDG.dat
log_archive_config                   string

You can see the last parameter was not set on standby database that is why i was receiving error.
ORA-16047: DGID mismatch between destination setting and standby

Reason: the log archive config parameter wasnot set on standby db.

solution: set the parameter 

ORA-16006: audit_trail destination incompatible with database open mode on startup standby database

ORA-16006:	audit_trail destination incompatible with database open mode
Cause:	The audit_trail initialization parameter was set to "DB" (or TRUE), which is incompatible with a database opened for read-only access.
Action:	When the database is opened for read-only access, the audit_trail initialization parameter can only be set to "OS" or "NONE" (FALSE).

Reason: audit trail parameter should be in OS or NONE when standby database configured.
Solution: change the audit trail parameter value to OS and NONE. and start the standby database.

tnsping + 12560 protocol adaptor error

redhat 5.2 10gr2 (10.2.0.4)

i was getting error while trying to tnsping standby server

$tnsping orcldg 10
error: tns 12560 protocal adaptor error

NOTE: orcldg is a name of standby database

after little searched i found the below details

clause:
port 1521 is blocked on standby server

we can check port is blocked or not by below command

cmd>telnet servername 1521
connection failed --- this command failed if port is blocked

solution:
on standby server connect with root user and execute the following command to disable firewall and unblock 1521 port

# /etc/init.d/iptables save
# /etc/init.d/iptables stop



NOTE: if you want to again block or enable firewall setting then execute the following command

# /etc/init.d/iptables start

ORA-16006: audit_trail destination incompatible with database open mode

on redhat 5.2 10gr2 (10.2.0.4) standby database.

while i was trying to open standby database got below error message

ORA-16006: audit_trail destination incompatible with database open mode

at little search on metalink found below note: ID 835638.1

solution:

SQL>alter system set audit_trail=OS scope=spfile;

SQL> startup force

ORA-16401: archivelog rejected by RFS

Yesterday I configured Data Guard on our production box after that i got below error message in primary database alertsid.log file.

Errors in file f:\ora10g_home\admin\orcl\bdump\orcl_arc0_5132.trc:
ORA-16401: archivelog rejected by RFS
FAL[server, ARC0]: Complete FAL archive (thread 1 sequence 34687 destination orcldg)

After searching on metalink and oracle forums I come up with below suggestion.

1. It is just an informational message, no more action is required.

2. If Above message appear in "physical standby" database then it is reported as a Bug 4376276 - ORA-16401 can occur on standby Doc ID: Note:4376276.8

3. If "log_archive_dest_1" & "standby_archive_dest" parameter at standby site set for same location then above error can occur in alertsid.log file so make it differ to each other.

Standby Database Creation Oracle 9ir1(9.0.1)

Standby Database Creation

Primary Database: 9ir1(9.0.1.0.1)

Standby Database: 9ir1(9.0.1.0.1)

Platform: windows XP 32bit

Standby & Primary Database Same System/Machine

Primary database name: ORA9I

Standby database name: ORA9ISTD

NOTE: Database Name should not be excced more than 8 character.

TNSENTRY Primary DB: ORA9I

TNSENTRY Standby DB: ora9istd

Location of datafile/controlfile/redofile

Primary site: $ORACLE_HOME/oradata/ORA9I

Standby site: c:\ora9istd

Perform following task at Primary database
1. Enable archivelog mode
2. take primary site database backup for standby
3. create standby controlfile
4. modify init.ora file at primary site for standby db
5. create init.ora file for standby site
6. startup standby database

1. How to enable archivelog mode

http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html

2. Backup

I am using HOT backup through USER MANAGED method

3. Create CONTROLFILE to standby database
SQL> alter database create standby controlfile as 'c:\ora9istd\control01.ctl';
Database altered.

4. COPY database backup and standby controlfile to STANDBY location

5. Modify init.ora file at primary site FOR PRIMARY DB.

standby_file_management=AUTO scope=spfile

STANDBY_ARCHIVE_DEST='LOCATION=c:\ora9i\archive'

log_archive_dest_1='LOCATION=c:\ora9ihome\rdbms'

log_archive_dest_2='SERVICE=ora9istd'

log_archive_dest_state_1=ENABLE

log_archive_dest_state_2=ENABLE

remote_archive_enable=TRUE

NOTE: Restare Oracle server ( shutdown + startup) to take effect of static parameter setting.

6. Modify init.ora file at primary site FOR STANDBY DB.

*.lock_name_space=ORA9I

*.FAL_SERVER=ora9istd

*.FAL_CLIENT=ora9i

7. Create standby init.ora file
use OS copy command to copy init.ora(primary site) and paste at standby site and modify below parameter.

*.STANDBY_ARCHIVE_DEST='LOCATION=c:\ora9istd\archive'
*.background_dump_dest='C:\ora9istd'
*.compatible='9.0.0'
*.control_files='C:\ora9istd\control01.ctl',

*.core_dump_dest='C:\ora9istd'
*.fal_client='ORA9ISTD'
*.fal_server='ORA9I'
*.instance_name='ora9iSTD'
*.lock_name_space='ORA9ISTD'
*.log_archive_dest_1='LOCATION=c:\ora9istd\archive'
*.log_archive_dest_state_1='ENABLE'
*.remote_archive_enable=TRUE
*.standby_archive_dest='LOCATION=c:\ora9istd\archive'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='C:\ora9istd'

8. Create standby services through ORADIM & create password file through ORAPWD and startup the standby database

C:\ora9ihome\BIN>oradim -NEW -SID ora9istd
C:\Ora9ihome\BIN>orapwd file=c:\ora9ihome\database\pwdora9istd.ora password=oracle entries=5

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup NOMOUNT pfile='c:\ora9istd\initora9istd.ora';

ORACLE instance started.
[output cut]

NOTE: STARTUP NOMOUNT is required.

SQL> alter database mount standby database;
Database altered.

10. RENAME DATAFILE OR REDOLOG FILE TO POING STANDBY LOCATION

SQL> alter database rename file

2 'C:\ORA9IHOME\ORADATA\ORA9I\INDX01.DBF',

3 'C:\ORA9IHOME\ORADATA\ORA9I\REDO01.LOG',

4 'C:\ORA9IHOME\ORADATA\ORA9I\REDO02.LOG',

5 'C:\ORA9IHOME\ORADATA\ORA9I\REDO03.LOG',

6 'C:\ORA9IHOME\ORADATA\ORA9I\SYSTEM01.DBF',

7 'C:\ORA9IHOME\ORADATA\ORA9I\TOOLS01.DBF',

8 'C:\ORA9IHOME\ORADATA\ORA9I\UNDOTBS01.DBF',

9 'C:\ORA9IHOME\ORADATA\ORA9I\USERS01.DBF'

10

to

11 'c:\ora9istd\INDX01.DBF',

12 'c:\ora9istd\REDO01.DBF',

13 'c:\ora9istd\REDO02.DBF',

14 'c:\ora9istd\REDO03.DBF',

15 'c:\ora9istd\SYSTEM01.DBF',

16 'c:\ora9istd\TOOLS01.DBF',

17 'c:\ora9istd\UNDOTBS01.DBF',

18 'c:\ora9istd\USERS01.DBF';
Database altered.


After that If required then create STANDBY redolog file then configure TNSNAME.ORA file for primary db will connect to standby database and standby database will connect to primary db.

Source: http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88808/stdbyconfig.htm#46558

Logical Standby Database

Logical Standby Database Creation Procedure

We have to first create physical standby database after we configure physical standby database to logical standby database.

Step:1
For logical standby we have to first create PHYSICAL STANDBY DATABASE and sure physical standby database working properly.

Go through below link for PHYSICAL STANDBY DATABASE CREATION
http://dbataj.blogspot.com/2007/03/standby-database.html

Step:2
Prepare the Primary Database to Support a Logical Standby Database


1. Ensure Supplemental Logging Is Enabled

SQL> alter database add supplemental log data
( primary key, unique index) columns;

Database altered.

If you enable supplemental logging on a primary database in a Data Guard configuration that also contains physical standby databases, then you must issue the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement on each physical standby database to ensure future switchovers work correctly.

On Physical Standby Database

SQL> alter database add supplemental log data
2 (PRIMARY KEY, UNIQUE INDEX) columns;

Database altered.

Step:3
Prepare the Primary Database for Role Transitions


Primary Database: Logical Standby Role Initialization Parameters

Create new folder for archive_orcl1 archive log on Primary Database.

SQL> alter system set
2 log_archive_dest_3=
3 'LOCATION=c:\oracle\product\10.1.0\archive_orcl1
4 valid_for=(standby_logfiles,standby_role)
5 db_unique_name=orcl';

System altered.


SQL> alter system set
2 LOG_ARCHIVE_DEST_STATE_3=ENABLE;

System altered.

SQL> alter system set undo_retention=3600;

System altered.

Step:3


Prepare to Transition to a Logical Standby Database



Ensure Supplemental Logging Is Enabled

On Physical Standby DB

SQL> ed
Wrote file afiedt.buf

1 SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
2 SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
3* FROM V$DATABASE
SQL> /

PK_ UI_
--- ---
YES YES

Create new folder for archive_orclstdy1 archive log on Physical standby DB

SQL> alter system set
2 log_archive_dest_3=
3 'LOCATION=c:\oracle\product\10.1.0\archive_orclstdy1
4 valid_for=(standby_logfiles,standby_role)
5 db_unique_name=orclstdy';

System altered.


SQL> alter system set
2 LOG_ARCHIVE_DEST_STATE_3=ENABLE;

System altered.

SQL> alter system set undo_retention=3600;

System altered.


Shut down the logical standby database.



SQL> SHUTDOWN IMMEDIATE;


Step:4


Create a Control File for the Logical Standby Database




SQL> alter database create logical standby controlfile
2 as 'c:\oracle\product\10.1.0\oradata\orclstdy\controlstdy01.ctl';

Database altered.

Note: Above Created control file transfer logical standby database if logical standby database is remote site. otherwise directly create control file in logical standby folder in some host. like above example :ORCLSTDY: is logical/physical standby database folder.

Step:5


Start the Logical Standby Database

On Logical Standby Database
Start and mount the logical standby database.



SQL> startup mount ;
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

Must sure your able to connect primary database through TNS_ENTRY
eg: scott/tiger@ORCL


On Logical Standby Database
Prepare for SQL Apply


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;


On Logical Standby Database
Activate the logical standby database.

SQL> alter database activate standby database;

Database altered.

Reset the database name of the logical standby database.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL>


C:\>nid TARGET=sys/oracle@orclstdy DBNAME=orclstdy
DBNEWID: Release 10.1.0.5.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database ORCL (DBID=1152774966)

Connected to server version 10.1.0

Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\CONTROLSTDY01.CTL

Change database ID and database name ORCL to ORCLSTDY? (Y/[N]) => Y


Proceeding with operation
Changing database ID from 1152774966 to 3530778477
Changing database name from ORCL to ORCLSTDY
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\CONTROLSTDY01.CTL - m
odified
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\SYSTEM01.DBF - dbid chang
ed, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\UNDOTBS01.DBF - dbid chan
ged, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\SYSAUX01.DBF - dbid chang
ed, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\USERS01.DBF - dbid change
d, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\EXAMPLE01.DBF - dbid chan
ged, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\SYSTEM02.DBF - dbid chang
ed, wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLSTDY\CONTROLSTDY01.CTL - d
bid changed, wrote new name
Instance shut down

Database name changed to ORCLSTDY.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCLSTDY changed to 3530778477.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.


Create New PASSWORD files


C:\>ORAPWD FILE=C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\PWDORCL.ORA PASSWORD=ORAC
LE ENTRIES=5 FORCE=Y


C:\>ORAPWD FILE=C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\PWDORCLSTDY.ORA PASSWORD=
ORACLE ENTRIES=5 FORCE=Y


Chnage DB_NAME parameter in "INITORCLSTDY.ORA" file


C:\>set oracle_sid=orclstdy

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Jun 17 18:39:42 2007

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

Enter password:
Connected to an idle instance.

SQL> create pfile from spfile;

File created.


SQL> --modify db_name parameter >>> orcl to orclstdy <<<
SQL> create spfile from pfile;

File created.

Restart the logical standby database

SQL>startup mount

SQL> alter database open resetlogs;

Database altered.


Change the logical standby database global name


SQL> alter database rename global_name to orclstdy;

Database altered.


Create a new temporary file for the logical standby database.


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

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

SQL> select * from v$tempfile;

no rows selected


SQL> alter tablespace temp
2 add tempfile 'c:\oracle\product\10.1.0\oradata\orclstdy\temp01.dbf'
3 size 40m reuse;

Tablespace altered.


Start SQL Apply.



SQL> alter database start logical standby apply;

Database altered.

Step:6

Verify the Logical Standby Database Is Performing Properly



Verify the archived redo log files were registered.

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select sequence#, first_time, next_time, dict_begin,dict_end
2 from dba_logstdby_log
3 order by 1;


SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC
---------- -------------------- -------------------- --- ---
337 18-jun-2007 23:58:54 19-jun-2007 00:01:05 NO NO
338 19-jun-2007 00:01:05 19-jun-2007 00:39:07 NO NO
339 19-jun-2007 00:39:07 19-jun-2007 01:55:34 NO NO
340 19-jun-2007 01:55:34 19-jun-2007 03:14:26 NO NO
341 19-jun-2007 03:14:26 19-jun-2007 04:44:27 NO NO
342 19-jun-2007 04:44:27 19-jun-2007 06:13:28 NO NO
343 19-jun-2007 06:13:28 19-jun-2007 07:29:38 NO NO
344 19-jun-2007 07:29:38 19-jun-2007 08:54:20 NO NO



Send redo data to the standby database.


SQL> conn sys@ORCL as sysdba
Enter password:
Connected.
SQL> --On primary database issue below command
SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.


Query the DBA_LOGSTDBY_LOG view again.



SQL> conn sys@ORCLSTDY as sysdba
Enter password:
Connected.
SQL> --On logical standby database

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select sequence#, first_time, next_time, dict_begin,dict_end
2 from dba_logstdby_log
3 order by 1;


SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC
---------- -------------------- -------------------- --- ---
337 18-jun-2007 23:58:54 19-jun-2007 00:01:05 NO NO
338 19-jun-2007 00:01:05 19-jun-2007 00:39:07 NO NO
339 19-jun-2007 00:39:07 19-jun-2007 01:55:34 NO NO
340 19-jun-2007 01:55:34 19-jun-2007 03:14:26 NO NO
341 19-jun-2007 03:14:26 19-jun-2007 04:44:27 NO NO
342 19-jun-2007 04:44:27 19-jun-2007 06:13:28 NO NO
343 19-jun-2007 06:13:28 19-jun-2007 07:29:38 NO NO
344 19-jun-2007 07:29:38 19-jun-2007 08:54:20 NO NO

345 19-jun-2007 08:54:20 19-jun-2007 10:14:30 NO NO
346 19-jun-2007 10:14:30 19-jun-2007 10:14:35 NO NO
347 19-jun-2007 10:14:35 19-jun-2007 10:15:13 NO NO



Verify redo data is being applied correctly.



SQL> column name format a30
SQL> column value format a30
SQL> select name,value from v$logstdby_stats where name = 'coordinator state';

NAME VALUE
------------------------------ ------------------------------
coordinator state APPLYING



View the V$LOGSTDBY view to see current SQL Apply activity.



SQL> column status format a50
SQL> column type format a12
SQL> select type,high_scn,status from v$logstdby;

TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR 1824043 ORA-16116: no work available
READER 1824043 ORA-16116: no work available
BUILDER 1824028 ORA-16116: no work available
PREPARER 1824027 ORA-16116: no work available
ANALYZER 1824013 ORA-16116: no work available
APPLIER 1823998 ORA-16116: no work available
APPLIER 1824013 ORA-16116: no work available
APPLIER 1823949 ORA-16116: no work available
APPLIER 1823972 ORA-16116: no work available
APPLIER 1823606 ORA-16116: no work available

10 rows selected.

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