Creating Stand By Database (Steps)

I have created standby database with following steps:
You will also find Primary Database initfile and Standby Database initfile at the bottom of the page
PRIMARY DATABASE
------------------------------
1. Change initrock.ora file
*.log_archive_dest_1='LOCATION=D:\oracle\Archive_rock'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_2='SERVICE=ROCKSTD LGWR ASYNC'
*.log_archive_dest_state_2=ENABLE
*.fal_server='ROCKSTD'
*.fal_client='ROCK'
*.db_unique_name='rock'

2. Shutdown immediate;
2.1. Startup mount;
3. alter database create standby controlfile as 'd:\rockstd_control.ctl';
4. alter database open;
5.0 Shutdown immediate;
5.1 Startup Nomount;
5.2 Create Spfile from Pfile;
5.3 D:\oracle\product\10.2.0\db_1\database\SPFILEROCK.ORA, changes are recorded in current spfile and copy pfile from (D:\oracle\product\10.2.0\db_1\database) to standby database.
6. add at TNSNAMES.ora file
Rockstd is service name, which will be created at Standby database.
ABM-D0011.abc.com is the hostname where standby database will be created.

rockstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ABM-D0011.abc.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rockstd)
)
)

Copied all datafiles, archivelogFiles, redologFiles, ControlFile, Initfile to STAND BY Database...
-----------------------------------------------------------------------------------
STAND BY DATABASE
--------------------------
1. Added at TNSNAMES.ORA
Rock is the service name of Primary Database.
HOST = 10.142.192.28 is the IP address of Primary Database.
rock =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.142.192.28)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rock)
)
)
2. copied datafiles, redologs and rockstd_control at rock (new folder in OraData)
3. copy & paste initrock at D:\oracle\product\10.2.0\db_1\database and rename it to initrockstd.ora
4. check initrockstd file parameters and create folder (rock) and subfolders( adump, bdump, cdump, udump) in D:\oracle\product\10.2.0\admin
5. create folder (flash_recovery_area) at D:\oracle\product\10.2.0\rock\flash_recovery_area. the path is defined in parameter db_recovery_file_dest in initrockstd file.
6. intit file
*.fal_server='ROCK'
*.fal_client='ROCKSTD'
*.db_unique_name='rockstd'
*.standby_file_management=AUTO
7. Place the initrockstd file in oracle_home database folder.
8. REMOVE LOG_ARCHIVE_DEST_2='service=standby LGWR ASYNC' PARAMETER. (and log_archive_dest_state_1=ENABLE)
9. CHANGE LOG_ARCHIVE_DEST_1 PARAMETER and specify path for archived log file destination, copy all archives and paste at specified destination. MoreOver datafiles, redologs and control file must be in same specified path in Standby database as it has been specified at Primary Database.
10. create service rockstd using ORADIM Utility.

H:\>oradim -new -sid rockstd -syspwd rock -startmode m -pfile D:\oracle\product\10.2.0\db_1\database\initrockstd.ora
password should be same as primary sys password.

11. H:\>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 3 12:34:46 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys@rockstd as sysdba
Enter password:
Connected to an idle instance.

NOTE: If unable to connect to standby database and getting Listener error the ..

(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME=standby)
)

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 62915964 bytes
Database Buffers 96468992 bytes
Redo Buffers 7139328 bytes

SQL> alter database mount standby database;

Database altered.

12. Add additional Standby Redo logs

SQL> alter database add standby logfile 'D:\oracle\product\10.2.0\oradata\rock\REDO04.log' size 50m;
Database altered.
SQL> alter database add standby logfile 'D:\oracle\product\10.2.0\oradata\rock\REDO05.log' size 50m;

Database altered.
SQL> alter database add standby logfile 'D:\oracle\product\10.2.0\oradata\rock\REDO06.log' size 50m;

Database altered.
13. Start Recovery
alter database recover managed standby database disconnect from session;

14. Name and applied status of Archived.
select name, applied from v$archived_log;
15.
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
MRP0 WAIT_FOR_LOG

ARCH.... receives archives from primary db. (Maximum 30 can be enabled.)
RFS....... applies received archives to standby db.
MRP0....resolves gaps of archived b/w fal_server & fal_client.

16. check alert_rockstd at D:\oracle\product\10.2.0\admin\rock\bdump for alerts.
------------------------------------------------------------------------------------

Adding datafile or creating tablespace at Primary Database.
If you have not set the STANDBY_FILE_MANAGEMENT initialization parameter to auto, you must re-create the control file on the standby database.

http://download-uk.oracle.com/docs/cd/A97630_01/server.920/a96653/sbydb_manage_ps.htm


If you have created another tablespace or added datafile to an existing tablespace at Primary Database and didn't set the STANDBY_FILE_MANAGEMENT initialization parameter to auto Then Archived at Standby database will not apply, and obviously there will be a gap between Primary & StandBy database archivelogs.
I have added tablespace testing1 and added datafile testing1.dbf at Primary database. (Shown below at Primary Database Section)

When you query the select name from v$datafile, you will find unnamed00004 filename instead of testing1.dbf.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\ABAMCO_TEST01.DBF

6 rows selected.
We learn that datafiles are not copid at standby database, for that one need to manually copy the datafiles.

Following steps must be followed after adding tablespace/datafile at Primary Database.
----------------------------------------------------------------------------------------PRIMARY Database.1. You need to manually copy datafile & recreate controlfile, Copy and paste both files at Standby Database.

SQL> create tablespace testing1
2 datafile 'D:\oracle\product\10.2.0\oradata\rock\testing1.dbf' size 50m;
Tablespace created.

SQL> alter tablespace testing1 begin backup;
Tablespace altered.
Copy the testing1.dbf datafile at any location.
SQL> alter tablespace testing1 end backup;
Tablespace altered.
SQL> alter database create standby controlfile as 'd:\rockstd_control.ctl';
Database altered.
SQL>
----------------------------------
Standby Database
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>

1. Copy and paste controlfile & datafile at D:\oracle\product\10.2.0\oradata\rock
2.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 62915964 bytes
Database Buffers 96468992 bytes
Redo Buffers 7139328 bytes
3.
SQL> alter database mount standby database;
Database altered.
4.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
------------------------------------------------------
Primary Database initrock.ora (Initfile)
-------------------------------------------rock.__db_cache_size=83886080
rock.__java_pool_size=4194304
rock.__large_pool_size=4194304
rock.__shared_pool_size=67108864
rock.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/rock/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/rock/bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0oradata\rock\control01.ctl',
'D:\oracle\product\10.2.0\oradata\rock\control02.ctl',
'D:\oracle\product\10.2.0\oradata\rock\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/rock/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='rock'
*.db_unique_name='rock'
*.db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rockXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/rock/udump'
*.log_archive_dest_1="location=D:\oracle\Archive_rock"
*.log_archive_format='arc_%s_%t_%r.arc'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_2='SERVICE=rockstd LGWR ASYNC'
*.log_archive_dest_state_2=ENABLE
*.fal_server='ROCKSTD'
*.fal_client='ROCK'
-------------------------------------------

Stnadby Database initrockstd.ora file
rock.__db_cache_size=83886080
rock.__java_pool_size=4194304
rock.__large_pool_size=4194304
rock.__shared_pool_size=67108864
rock.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/rock/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/rock/bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0oradata\rock\rockstd_control.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/rock/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='rock'
*.db_unique_name='rockstd'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\oradata\rock\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rockXDB)
'*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/rock/udump'
*.log_archive_dest_1="location=D:\oracle\product\10.2.0oradata\rock\archive"
*.log_archive_format='arc_%s_%t_%r.arc'
*.log_archive_dest_state_2=ENABLE
*.fal_server='ROCK'
*.fal_client='ROCKSTD'
*.standby_file_management=auto--------------------------------------------------------------------------
Standby Database Maintenance
The Standby Database is maintained by setting the database in Recovery Mode. In this particular state the database is in a mount (not open) state and the database is allowed to receive the archived log files from the primary database and apply them for synchronization. In this mode the standby database is not open for any user connections and it only receives the archived logs from the primary database via the Log Transfer Services and applies the changes recorded in them through Log Apply Services.
In order to put the standby database in recover mode we issue the following commands:
--Mount the standby database and makes sure that it is not open for any user connections.
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

-- Put the standby database in recovery mode and ready to receive archived logs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The command above puts the database in recovery mode and ready to receive the archived logs from the primary database. It also ensures that and users cannot connect to the standby database and any previously connected users are disconnected.

The standby database can also be put into read only mode for reporting purposes. This is accomplished by issuing the commands:

-- Cancel the recovery mode and stop the reception of applied logs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Put the standby database in read only mode for reporting purposes.
SQL> ALTER DATABASE OPEN READ ONLY;
This process can also be reversed and the database can be put back into recovery mode by reissuing the command
-- Put the standby database back into recovery mode.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Switchover and Failover Operations
Standby Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a SWITCHOVER and can be performed using the following statements:

-- Connect to primary database and switchover to standby
SQL> CONNECT sys/password@ICPORA AS SYSDBA
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown the primary database
SQL> SHUTDOWN IMMEDIATE;

-- Mount the old primary database as the new standby database
SQL> STARTUP NOMOUNT PFILE=C:\oracle\ora92\database\initICPORA.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- Convert the standby database to a primary database
SQL> CONNECT sys/password@ICPSTD AS SYSDBA
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SQL> SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
SQL>STARTUP PFILE=C:\oracle\ora92\database\initICPSTD.ora
The SWITCHOVER operation is useful in scenarios where the primary database needs to be temporarily taken offline or unavailable for user sessions for maintenance purposes. Switchover is a temporary switch from the primary database to the standby database.
Standby Database Failover

A FAILOVER occurs when database failover causes a standby database to be converted to a primary database. This process is not temporary and the failing over to the standby database will cause the primary to be unavailable to be online again.

A FAILOVER operation is initiated by issuing the following commands:

-- Finish the recovery mode on the standby database
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

--Activate standby database as the new primary database
SQL>ALTER DATABASE ACTIVATE STANDBY DATABASE;

It is important to note that one should not FAILOVER to a standby database other than in an emergency, because the failover operation is an unplanned transition that may result in the loss of application data.

Once a FAILOVER operation is performed, there is no going back. This is because the original primary database is not operational anymore and the standby database that you fail over to the primary role is no longer capable of returning to being a standby database in the original configuration.
-------------
-------------
When there is Link down or Network Problem, then obviously it's gonna be gap between Primary and Standby Databases Archived logs, So there are two solutions for that:
1. Manuall resolve Gaps,
2. Automatically
1. Manual (Recommended for few logs)
Step.1: Manually copy all missing archived logs from Primary Database and paste at Standby Database Archived Log Folder. (Copy one file at a time Recommended)
Step.2: Register Archived logs at Standby Database (One by One)1. Conn sys/password@standbydb as sysdba
2. Shutdown Immediate;
3. Startup nomount;
4. Alter database mount Standby Database;
5. alter database register logfile 'd:\abc\abc.log';
6. alter database recover managed standby database disconnect from session;
2. Automatic (Recommended by large number of logs)The Production/Primary database must be taken offline, so its required that Automatic option should be used when there is NO or LESS activity on the Primary Database.
Step.1: Connect at Standby Database
conn sys/password@standbydb as sysdba
alter database recover managed standby database cancel;
Step.2: Connect at Primary Database
1. Shutown immediate;
2. Startup nomount;
3. Alter database mount;
4. Alter database open;
Step.3: Connect at Standby database1. Alter database recover managed standby database parrallel 8 nodelay disconnect from session;