Steps to create Standby database

Step by step document to create Standby Database


Standby database are very critical for disaster recovery. This article takes you step by step to setup Oracle Standby Database.
1 - Data Guard Operational Prerequisites
Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
The Primary Database must run in ARCHIVELOG mode.
The hardware and Operating system architecture on primary and standby location must be same.
Each primary and standby database must have its own control file.
If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
Primary database must be FORCE LOGGING mode.
2 - Preparing Primary Database for Standby Database creation
2.1 Ensure the primary database in ARCHIVELOG mode Ensure the primary database in ARCHIVELOG mode using following command.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
2.2 Enable database in FORCE LOGGING mode
Place primary database in FORCE LOGGING mode using following SQL statement: SQL> alter database force logging;
Database altered.
3 - Identify the primary database Datafiles SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF
10 rows selected.
4 - Make a copy of Primary Database
Make a closed backup copy of primary database by performing following steps: 4.1 Shutdown the Primary Database Issue the following statement to shutdown the primary database. SQL> shutdown immediate;
4.2 Copy the Datafiles to standby location Copy the redo log files and Datafiles identified in section 3 to standby location. Note: Primary Database must be shutdown while coping the files. 5 - Restart the Primary Database Execute following command to restart the Primary Database.

SQL> startup;
6 - Create Control file for Standby Database Issue the following command on primary database to create control file for the standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\stby\control_sb01.ctl';
Database altered.
The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.
7 - Create pfile from for standby database from the primary database Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.

SQL> CREATE PFILE='C:\oracle\ora92\database\initstby.ora' from spfile;
File created.
8 - Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database. db_name - Not modified. The same name as the primary database. compatible - Not modified. The same as the primary database, 9.2.0.0.0. control_files - Specify the path name and filename for the standby control file. log_archive_start - Not modified. The same as the setting for the primary database, TRUE standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database. db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required. log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required. log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.) standby_file_management -
Set to AUTO. remote_archive_enable -
Set to TRUE. instance_name -
If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host. lock_name_space -

Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter. Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database. (Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window C:\>oradim -new -sid stby -intpwd stby -startmode manual

10 - Configure listeners & tnsnames for standby and primary databases
Configure listeners in listeners.ora as follows LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Orcl)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = Orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = stby)
)
)
Restart the listeners using LSNRCTL utility. % lsnrctl stop
% lsnrctl start
Also make an entry into tnsnames.ora for standby database. stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)


11 - Start Physical standby database

Start up the stand by database using following commands

C:\>set oracle_sid=stby
C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;
Database altered.

12 - Enabling archiving to Physical Standby Database
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined. Issue following commands from primary database session: SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby' SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
System altered.

13 - Initiate Log apply services
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
Now go to primary database prompt

SQL> alter system switch logfile;
Go to stand by database prompt

SQL> alter database open read only;
Database altered.

14 - Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
Archive the current log on the primary database using following statement. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On standby database query the V$ARCHIVED_LOG view SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00
Now connect scott/tiger@orclc on primary database and create table or insert row in any table. Now connect as sys on primary database and execute following SQL statement SQL> alter system switch logfile;
On standby database execute following SQL statements SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
And check whether the changes applied on the standby database or not.
15 - Annexure 15.1 Parameter file for Primary Database *.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\Orclc\bdump'
*.compatible='9.2.0.0.0'
*.control_files='C:\oracle\oradata\Orclc\CONTROL01.CTL',
'C:\oracle\oradata\Orclc\CONTROL02.CTL','C:\oracle\oradata\Orclc\CONTROL03.CTL'
*.core_dump_dest='D:\oracle\admin\Orclc\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='Orclc'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=C:\oracle\ora92\database\archive\ORCLC mANDATORY'
*.log_archive_dest_2='SERVICE=stby'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\Orclc\udump'
15.2 Parameter file for Standby Database *.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\stby\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\stby\CONTROL_SB01.CTL'
*.core_dump_dest='D:\oracle\admin\stby\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='stby'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=D:\oracle\admin\stby\archive'
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='NONE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\stby\udump'
*.standby_archive_dest='C:\oracle\ora92\database\archive\ORCLC'
*.db_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.log_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby
--********----

Standby database

What is a standby database?
A Standby Database is an exact copy of an operational database on a remote (or local*) server, ready to be used for backup, replication, disaster recovery, analysis, shadow environment and reporting, to name a few applications. A standby database is far superior to a normal backup as it is instantly available in the event of a disaster. To restore a backup takes time, and during the restore time the system is not available. With a standby database there is nothing to restore in the event of a disaster as the standby database is always available. It is possible to switch applications over to the standby database in a matter of minutes to allow business continuity.
Overview of standby database:
Exact copy of main database in a remote (or local*) location.
Is held up to date by applying changes from main database.
In the event of a disaster, standby database becomes active.
Users (or applications) are transferred to standby database to continue operation.

Technical overview of standby database:
Database updates are captured in redo logs.
Updates in redo logs are not permanent. They get overwritten, as there is a set number of redo logs and they written to in a cyclic fashion (when the last one is full, the first one is used again).
A copy is made of the redo log which is then called an archive log. This is a permanent copy.
The archive log is transferred to the standby server.
The transferred archive logs are applied to the standby database.
Standby database is up to date with primary database.

Control file is corrupted

go in Pfile give the path of control file that is not corrupted and the take the startup of database

ORACLE SECURITY THROUGH THE LISTENERThe

Oracle TNS listener is a process based on the server that provides network connection to clients, application servers and other databases to an oracle database.The listener is very much vulnerable to hackers. If there is no password set on the LISTENER, someone who knows just a hostname and the default port number which is 1521 can easily have control over the database. Such a person can easily stop the listener, set a password and prevent owners of the database to control the listener. He can also steal detailed information on the listener, database as well as application configurations by writing trace and log files that are accessible to the owner of tnslnr.So without a password set on the listener, any person through the command line can query the listener using the commands belowLSNRCTL.>STATUS
LSNRCTL> statusConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - ProductionStart Date 09-APR-2008 01:37:37Uptime 0 days 7 hr. 26 min. 50 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File C:\U1\network\admin\listener.oraListener Log File C:\U1\network\log\listener.logListener Trace File C:\U1\network\trace\listener.trcListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcons)(PORT=1521)))Services Summary...Service "DESAQ.ORCONS" has 1 instance(s).Instance "desaq", status READY, has 1 handler(s) for this service...Service "DESAQXDB.ORCONS" has 1 instance(s).Instance "desaq", status READY, has 1 handler(s) for this service...Service "DESAQ_XPT.ORCONS" has 1 instance(s).Instance "desaq", status READY, has 1 handler(s) for this service...Service "PLSExtProc" has 1 instance(s).Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...The command completed successfullyLSNRCTL>From the above information, the needed information is shown directly, the port number 1521 is shown as well as the HOST. Since the trace level is OFF, He can decide to set TRACE LEVEL ON to enable him write trace and log files from the listener.To set TRACE LEVEL ON use the command belowLSNRCTL>TRACE 1
LSNRCTL> trace 1Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))Opened trace file: C:\U1\network\trace\listener.trcThe command completed successfully
To observe the current services being run on the database, the hacker can query thelistener's services as shown below
LSNRCTL> servicesConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))Services Summary...Service "ASAREQUAYSON.6RC_GH" has 1 instance(s).Instance "asarequa", status READY, has 2 handler(s) for this service...Handler(s):"DEDICATED" established:8 refused:0 state:readyLOCAL SERVER"D000" established:69 refused:0 current:24 max:1002 state:readyDISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=desicons)(PORT=1069))Service "ASAREQUAYSON_XPT.6RC_GH" has 1 instance(s).Instance "asarequa", status READY, has 2 handler(s) for this service...Handler(s):"DEDICATED" established:8 refused:0 state:readyLOCAL SERVER"D000" established:69 refused:0 current:24 max:1002 state:readyDISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=desicons)(PORT=1069))Service "PLSExtProc" has 1 instance(s).Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0LOCAL SERVERThe command completed successfullyLSNRCTL>
HOW CAN THE DBA PREVENT OTHERS FROM GETTING ACCESS TO THE DATABASE1. BY SETTING ADMIN_RESTRICTION PARAMETERSet ADMIN_RESTRICTIONS in Listener.ora to stop all SET commands from being executed.How is this setting going to help you?
By setting the ADMIN_RESTRICTIONS_ to ON in the listener.ora file. Consequently; hackers cannot execute SET commands both remotely and locally.
Try to do this configuration manually in the listener.ora file. LISTENER.ORA ADMIN_RESTRICTIONS_ = ON After you have made such changes, restart the listener using the RELOAD command in LSNRCTL for this change to be effected. 2. BY SETTING PASSWORD ON THE LISTENERWhy should you set a password on the listener?To enforce security on the database, you need to set a password on the listener. Use the LSNRCTL to set the password instead of the listener.ora .The reason is that, setting password in the listener.ora will expose the password in raw text format without encryption whereas the LSNRCTL will encrypt it.Password can be set on the LISTENER AS FOLLOWS
LSNRCTL> set current_listenerCurrent Listener is LISTENERLSNRCTL> change_passwordOld password:New password:Reenter new password:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))Password changed for LISTENERThe command completed successfullyLSNRCTL> set passwordPassword:The command completed successfullyLSNRCTL> save_configConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))Saved LISTENER configuration parameters.Listener Parameter File C:\APP\oracle\product\10.2.0\db_1\network\admin\listener.oraOld Parameter File C:\APP\oracle\product\10.2.0\db_1\network\admin\listener.bakThe command completed successfullyLSNRCTL>