Switchover to Physical Standby Database in Oracle 11g

Once the standby database is configured and works fine then we can switchover to standby database for testing purpose to reduce the primary database downtime .Primary database may need down-time for many  reasons like OS upgradation, Hardwares upgradation  and for many other issues .

Whenever we switchover the primary database to standby database , there is no loss of data during the switchover. Once the maintainance of the primary database is over , then we can again switchover to standby database.

In this scenario , the Primary database is "NOIDA" and standby database is "RED". Here i will switchover the primary database to standby database i.e, from "noida" to "red". Before switching, we should check some prerequisites .

Step 1 :  Verify whether it is possible to perform a switchover 

On the current primary database, query the "switchover_status" column of the V$DATABASE fixed view on the primary database to verify it is possible to perform switchover.

SQL> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY

The TO STANDBY value in the "switchover_status" column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the configuration is functioning correctly .  (for example, verify all  "log_archive_dest_n"  parameter values are specified correctly). If the value in the switchover_status column is SESSIONS ACTIVE or FAILED DESTINATION then click here .

Step  2  : Check that there is no active users connected to the databases.
SQL> select distinct osuser,username from v$session;

Step  3 : Switch the current online redo log file on primary database and verify that it has been appleid 

SQL>alter system switch logfile ;
System altered.

Step  4 : Connect with primary database and initiate the switchover 
C:\>sqlplus sys/xxxx@noida as sysdba
SQL> alter database commit to switchover to physical standby;
Database altered.

Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file,if necessary.
If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

In order to perform a switchover, run below command on the primary database.

SQL>alter database commit to switchover to physical standby with session shutdown ;

The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.

Step  5 : Shut down and restart the  primary instance(RED).
SQL>shutdown immediate; 

SQL> startup mount ;

Step  6 : Verify the switchover status in the v$database view.

After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status"  column of the v$database fixed view on the target standby database.

On old Primary database(noida)
SQL> select name,open_mode,db_unique_name from v$database;
NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
NOIDA     MOUNTED                noida                       TO PRIMARY

On old standby database (RED)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME        OPEN_MODE         DB_UNIQUE_NAME     SWITCHOVER_STATUS
------         ------------         -----------------            ---------------------
NOIDA       MOUNTED               red                          TO PRIMARY

Step 8 : Switch the target physical standby database role to the primary role

We can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following sql statement on the physical standby database that we want to change to the primary role:

SQL>alter database commit to switchover to primary ;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area        263639040 bytes
Fixed Size                               1373964 bytes
Variable Size                           213911796 bytes
Database Buffers                     41943040 bytes
Redo Buffers                          6410240 bytes
Database mounted.
Database opened.

Step  9  : Check the new primary database(RED) and switch logfile : 

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE

Note :  it's a good idea to perform a log switch on the primary .


SQL> alter system switch logfile;
System altered.

Step 10  :  Open new standby database(Noida) in read-write 

SQL> alter database open;
Database altered

SQL> select name,open_mode ,db_unique_name,switchover_status  from v$database;

NAME      OPEN_MODE    DB_UNIQUE_NAME     SWITCHOVER_STATUS
------      -------------      ------------------        -----------------------
NOIDA     READ ONLY        noida                        RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select  name,open_mode  from  v$database;
NAME              OPEN_MODE          
---------          ---------------------------------
NOIDA            READ ONLY WITH APPLY

The switchover_status column of v$database can have the following values:

Not Allowed :  Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases. 

Session Active : Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
Switchover Pending : This is a standby database and the primary database switchover request has been received but not processed.

Switchover Latent :  The switchover was in pending mode, but did not complete and went back to the primary database. 

To Primary :  This is a standby database, with no active sessions, that is allowed to switch over to a primary database. 

To Standby :  This is a primary database, with no active sessions, that is allowed to switch over to a standby database. 

Recovery Needed : This is a standby database that has not received the switchover request.

Enjoy   :-) 

ORA-10456: cannot open standby database; media recovery session may be in progress

Once while starting my standby database i found that database is not opening in normal mode. It throws the error-10456 :cannot standby database. 

On performing some R&D and googling comes to the conclusion that this error is generally occurs because a media recovery or RMAN session may have been in progress on a mounted instance of a standby database when an attempt was made to open the standby database. Hence to solve of this issue we have to cancel any conflicting recovery session and then open the standby database.

Here is the issue what i have experienced .

C:\>sqlplus sys/xxxx@red as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 3 12:47:32 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from hr.aa;
select * from hr.aa
                 *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select name,open_mode from v$database;
NAME           OPEN_MODE
---------      --------------------
NOIDA          MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect ; 
Database altered.

Hence, finally we solve the issues.


Enjoy    :-)

Active Standby Database In Oracle 11g

A Standby Database is an exact binary copy of an operational database on a remote server, ready to be used for backup, replication, disaster recovery, analysis, shadow environment and reporting, to name a few applications.

The most exiting feature of Active Standby Database is that we can open the standby database  in read only mode and at the sometime MRP process will be active, so we can redirect users to connect standby to perform select operations for reporting purpose. So that we can control much load on production database and there are plenty of option for active dataguard .

Here we will setup the standby database with active duplicate database feature available in 11g where we can create standby database without having any rman backup.In this setup,there is no need to copy  the datafiles manually. Datafiles are copeid over the network . As i have setup the standby database on same machine in my earlier POST. Now i will step the standby database on two different machine.

Lets have the details of setup :

Primary Database :
Machine   ==>  tech-199
Database  ==>  NOIDA

Standby Database :
Machine   ==>  tech-284
Database  ==>  RED(standby)

Platform used is WINDOW XP 

While configuring the standby database lets' have a look on the directory structure to avoid any confusion .On Primary database all the datafiles and redologs file in directory C:\app\neerajs\oradata\noida\'  and archive destination  is in directory "D:\archive" on machine tech-199  where as in case of the Standby database all the datafile,redologs and control files are in directory 'D:\app\standby\oradata\'   i.e, on machine tech-284 . In standby database, i have set the archive destination in  'D:\archive\'  . Let us configure standby database step-by-step.

Step 1 : Enable force logging on the Primary database :

SQL> alter database force logging ;
Database altered.

Following steps are performed on Standby database Machine (i.e; tech-284)
Step 2 :  Create Oracle Instance : 

C:\> oradim -new -sid red -intpwd xxxx -startmode m
instance created

Note :  Password should be same as that of  user "sys" of production database.
Step 3 : Update Listener.ora on standby Machine 

(SID_DESC =
      (GLOBAL_DBNAME = noida)
      (ORACLE_HOME = D:\app\Bishwanath\product\11.2.0\dbhome_1)
      (SID_NAME = red)
)
Stop and start the listener on standby 

Step 4 : Update the tnsnames.ora file on standby database : 

red =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech-284)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = red)
    )
  )

Step 5  : Create pfile for standby database 
Add just one parameter in pfile. i.e;
db_name=Noida 
save the pfile as initred.ora in $ORACLE_HOME\database\   folder.

Step 6 : Startup standby Instance in nomount state

C:\>sqlplus sys/xxxx@red as sysdba
SQL>startup nomount

ORACLE instance started.
Total System Global Area      263639040 bytes
Fixed Size                             1373964 bytes
Variable Size                         213911796 bytes
Database Buffers                   41943040 bytes
Redo Buffers                         6410240 bytes

Step 7 : On production database ,connect with RMAN and establish connection with auxiliary i.e; to standby  database 

SQL> host rman target sys/xxxx@noida auxiliary sys/xxxx@red
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 1 16:56:17 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database:       NOIDA (DBID=1515011070)
connected to auxiliary database:   NOIDA (not mounted)

RMAN> DUPLICATE  TARGET  DATABASE
 FOR  STANDBY
 FROM  ACTIVE  DATABASE
 NOFILENAMECHECK
 DORECOVER
 SPFILE
SET  DB_UNIQUE_NAME='red'
SET  LOG_ARCHIVE_DEST_2='service=noida LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'
 Set  STANDBY_FILE_MANAGEMENT='AUTO'
SET  FAL_SERVER='noida'
SET  FAL_CLIENT='RED'
SET  CONTROL_FILES='D:\app\standby\oradata\CONTROL01.CTL'
SET  DB_FILE_NAME_CONVERT 'C:\app\neerajs\oradata\noida\','D:\app\standby\oradata\'
SET  LOG_FILE_NAME_CONVERT 'C:\app\neerajs\oradata\noida\','D:\app\standby\oradata\' 

set  log_archive_dest_1='location=D:\archive\' 

set diagnostic_dest='D:\app\standby\diag\'

set db_recovery_file_dest='D:\app\standby\FRA\' ;

Starting Duplicate Db at 01-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  'C:\app\neerajs\product\11.2.0\dbhome_1\DATABASE\PWDnoida.ORA' auxiliary format
 'D:\app\Bishwanath\product\11.2.0\dbhome_1\DATABASE\PWDred.ORA'   targetfile
 'C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILENOIDA.ORA' auxiliary format
 'D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA'   ;
   sql clone "alter system set spfile= ''D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA''";
}
executing Memory Script
Starting backup at 01-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Finished backup at 01-OCT-11
sql statement: alter system set spfile= ''D:\APP\BISHWANATH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILERED.ORA''
contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''red'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_2 =
 ''service=noida LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  STANDBY_FILE_MANAGEMENT =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_SERVER =
 ''noida'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_CLIENT =
 ''RED'' comment=
 '''' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
 ''D:\app\standby\oradata\CONTROL01.CTL'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''C:\app\neerajs\oradata\noida\'', ''D:\app\standby\oradata\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_FILE_NAME_CONVERT =
 ''C:\app\neerajs\oradata\noida\'', ''D:\app\standby\oradata\'' comment=
 '''' scope=spfile";

sql clone "alter system set  log_archive_dest_1 =
 ''location=D:\archive\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  diagnostic_dest =
 ''D:\app\standby\diag\'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''D:\app\standby\FRA\'' comment=
 '''' scope=spfile";

   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''red'' comment= '''' scope=spfile
sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''service=noida LGWR SYNC REGISTER
VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=spfile
sql statement: alter system set  STANDBY_FILE_MANAGEMENT =  ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set  FAL_SERVER =  ''noida'' comment= '''' scope=spfile
sql statement: alter system set  FAL_CLIENT =  ''RED'' comment= '''' scope=spfile
sql statement: alter system set  CONTROL_FILES =  ''D:\app\standby\oradata\CONTROL01.CTL'' comment= ''''
scope=spfile
sql statement: alter system set  db_file_name_convert =  ''C:\app\neerajs\oradata\noida\'',
''D:\app\standby\oradata\'' comment= '''' scope=spfile
sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''C:\app\neerajs\oradata\noida\'',
''D:\app\standby\oradata\'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=D:\archive\'' comment= '''' scope=spfile
sql statement: alter system set  diagnostic_dest =  ''D:\app\standby\diag\'' comment= '''' scope=spfile
sql statement: alter system set  db_recovery_file_dest =  ''D:\app\standby\FRA\'' comment= '''' scope=spfile

Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     263639040 bytes
Fixed Size                     1373964 bytes
Variable Size                192940276 bytes
Database Buffers              62914560 bytes
Redo Buffers                   6410240 bytes
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  'D:\APP\STANDBY\ORADATA\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=C:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFNOIDA.ORA tag=TAG20111001T165811
RECID=11 STAMP=763405095
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 01-OCT-11
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
   set newname for tempfile  1 to
 "D:\APP\STANDBY\ORADATA\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "D:\APP\STANDBY\ORADATA\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\APP\STANDBY\ORADATA\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\APP\STANDBY\ORADATA\USERS01.DBF";
   set newname for datafile  5 to
 "D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF";
   set newname for datafile  6 to
 "D:\APP\STANDBY\ORADATA\TEST01.DBF";
   backup as copy reuse
   datafile  1 auxiliary format
 "D:\APP\STANDBY\ORADATA\SYSTEM01.DBF"   datafile
 2 auxiliary format
 "D:\APP\STANDBY\ORADATA\SYSAUX01.DBF"   datafile
 3 auxiliary format
 "D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF"   datafile
 4 auxiliary format
 "D:\APP\STANDBY\ORADATA\USERS01.DBF"   datafile
 5 auxiliary format
 "D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF"   datafile
 6 auxiliary format
 "D:\APP\STANDBY\ORADATA\TEST01.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\STANDBY\ORADATA\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\NEERAJS\ORADATA\NOIDA\SYSTEM01.DBF
output file name=D:\APP\STANDBY\ORADATA\SYSTEM01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=C:\APP\NEERAJS\ORADATA\NOIDA\SYSAUX01.DBF
output file name=D:\APP\STANDBY\ORADATA\SYSAUX01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\NOIDA\EXAMPLE01.DBF
output file name=D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=C:\APP\NEERAJS\ORADATA\NOIDA\UNDOTBS01.DBF
output file name=D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=C:\APP\NEERAJS\ORADATA\NOIDA\TEST01.DBF
output file name=D:\APP\STANDBY\ORADATA\TEST01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\APP\NEERAJS\ORADATA\NOIDA\USERS01.DBF
output file name=D:\APP\STANDBY\ORADATA\USERS01.DBF tag=TAG20111001T165829
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-OCT-11
sql statement: alter system archive log current
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "D:\ARCHIVE\ARC0000000053_0761068614.0001" auxiliary format
 "D:\ARCHIVE\ARC0000000053_0761068614.0001"   ;
   catalog clone archivelog  "D:\ARCHIVE\ARC0000000053_0761068614.0001";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 01-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=53 RECID=38 STAMP=763405284
output file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
Finished backup at 01-OCT-11
cataloged archived log
archived log file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 RECID=1 STAMP=763405200
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=763405201 file name=D:\APP\STANDBY\ORADATA\TEST01.DBF
contents of Memory Script:
{
   set until scn  2184111;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 01-OCT-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK
starting media recovery
archived log for thread 1 with sequence 53 is already on disk as file
D:\ARCHIVE\ARC0000000053_0761068614.0001
archived log file name=D:\ARCHIVE\ARC0000000053_0761068614.0001 thread=1 sequence=53
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-OCT-11
Finished Duplicate Db at 01-OCT-11
RMAN> **end-of-file**

Step 8  :  On Primary database 

SQL> alter system set standby_file_management=AUTO  scope=both;
System altered.

SQL> alter system set fal_server=red scope=both;
System altered.

SQL>  alter system set fal_client=noida scope=both;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=red LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=red' scope=both;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\archive\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=noida' ;
System altered.

Step 9 : On standby database : shutdown the Standby and enable managed recovery (active standby mode) 

C:\>sqlplus sys/xxxx@red as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:14:12 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;
OPEN_MODE
-----------------
MOUNTED

SQL> alter system set standby_file_management=AUTO scope=both;
System altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area       263639040 bytes
Fixed Size                              1373964 bytes
Variable Size                          205523188 bytes
Database Buffers                    50331648 bytes
Redo Buffers                          6410240 bytes
Database mounted.
Database opened.
SQL>  recover managed standby database using current logfile disconnect;
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

Standby redo logs are required to enable real time apply of redo data onto the standby.This standby redo logs are populated with redo information as fast as the primary redo logs, rather than waiting for the redo log to be archived and shipped to the standby.  This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.Oracle recommends the below formula to calculate the number of Standby redo logs file as 

(maximum number of logfiles for each thread + 1) * maximum number of threads

Since , I have three redo logs file so i will create four standby redo logs file. Oracle recommends that we should create standby redo logs on both i.e,primary and standby database so that we can safely switchover in future . Here, i am creating standby redo logs on standby database only .

SQL> alter database add standby logfile group 4 'D:\APP\STANDBY\ORADATA\REDO04.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 'D:\APP\STANDBY\ORADATA\REDO05.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 'D:\APP\STANDBY\ORADATA\REDO06.LOG' size 50m;
Database altered.
SQL>recover managed standby database using current logfile disconnect ;
Media recovery complete .

( On standby database )

SQL> select open_mode from V$database ; 
OPEN_MODE
--------------------------
READ ONLY WITH APPLY

(The above output "read only with apply"  shows the active mode is activated )

Following is the Command Used for Active Duplication

FROM ACTIVE DATABASE : (This is supplied if we want to do active database duplication) Specifies that the files for the standby database should be provided directly from the source database and not from a backup of the source database

NOFILENAMECHECK:  Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.

The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if we want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then we must specify NOFILENAMECHECK

SPFILE: Copies the server parameter file from the source database to the operating system-specific default location for this file on the standby database.

RMAN uses the server parameter file to start the auxiliary instance for standby database creation. Any remaining options of the DUPLICATE command are processed after the database instance is started with the server parameter file.

If we execute DUPLICATE with the SPFILE clause, then the auxiliary instance must already be started with a text-based initialization parameter file. In this case, the only required parameter in the temporary initialization parameter file is DB_NAME, which can be set to any arbitrary value. RMAN copies the binary server parameter file, modifies the parameters based on the settings in the SPFILE clause, and then restarts the standby instance with the server parameter file. When we specify SPFILE, RMAN never uses the temporary text-based initialization parameter file to start the instance.

DORECOVER: Specifies that RMAN should recover the standby database after creating it. If we  specify an until Clause, then RMAN recovers to the specified SCN or time and leaves the database mounted.

RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. After RMAN creates the standby database, we must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-onlymode.For more detail about parameters  click here 

Enjoy    :-) 

External OS User Authentication in Oracle

Oracle users can be authenticated in different ways,We generally login into our database with two ways . i.e, either as

C:\> sqlplus / as sysdba        or
C:\> sqlplus user/password@SID as sysdba.

This is only valid when we are the member of  "ORA_DBA"  OS  group(window) . If we are not the member of the "ORA_DBA" group,then we cannot login into database . Let have a look :  Here i have created a osuser  "oraext" and login with this user and try to connect with database.

Step 1 : Create OSuser 

C:\>net user oraext orapass /add
The command completed successfully.

Check the domain
C:\> echo %userdomain%
TECH-199

Step 2:  Login with "oraext"  user in  window machine and try to connect the database as:

c:\> sqlplus sys/sys@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 14:46:39 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR: ORA-01017:  invalid username/password; logon denied
Enter user-name:

Here,we are getting invalid username error,this is because the os user "oraext" is not the member of  the ORA_DBA group. Therefore to handle such case Oracle provide OS authentication method to connect database without authenticating any database username and password. Let's check 

In order to create the external user authentication we perform the following as :

1.) Check the values of the parameter "os_authentication_prefix"

SQL> SHOW PARAMETER os_authent_prefix
NAME                                 TYPE                       VALUE
---------------------             -----------                 -------------
os_authent_prefix            string                         OPS$

As we can see, the default value is "ops$". If this is not appropriate it can be changed using the alter system command.

2.) Create a database user with same name as the OS user which is prefixed by os_authent_prefix values followed by domain name. On Windows platforms we would expect an Oracle username of   "OPS$DOMAIN\xxxx"  for the Windows user "xxxx".

Now we know the OS authentication prefix, we can create a database user to allow an OS authenticated  connection. To do this, we create an Oracle user in the normal way, but the username must be the prefix value concatenated to the domain-name and OS username . Therefore the username seems like "ops$tech-199\oraext"

SQL> create user "ops$tech-199\oraext" identified externally;

3.) Grant connect privileges to them .

SQL> grant connect to "ops$tech-199\oraext";

Now Login as user "oraext" in window  and open the cmd and connect as  :

C:\>sqlplus / 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 30 17:27:31 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Note: The parameter in sqlnet.ora file be SQLNET.AUTHENTICATION_SERVICES= (NTS)

Advantage of the OS authentication:

1.) Without OS Authentication applications must store passwords in a variety of applications each with their own security model and vulnerabilities.

2.) Domain authentication already has to be secure because if it is not then database security just slows down access to the database, but cannot prevent it.

3.) Users that only have to remember one domain password can be made to create more secure domain passwords more easily than they can be made to create even less secure database passwords as the number of different databases they must connect to increases.

Enjoy  :-) 

Oracle Session Information From SYS_CONTEXT

SYS_CONTEXT is a standard Oracle Database function used to retrieve session-level information. SYS_CONTEXT  allow us to retrieve a set of session parameters via the namespace parameter ‘USERENV’.Basically, these are global variables that Oracle stores on the current session/user. Here is a sample of some session and user-specific information that can be obtained in one function call:

SQL> select sys_context('USERENV','AUTHENTICATION_TYPE') 
          ,sys_context('USERENV','CURRENT_SCHEMA') 
          ,sys_context('USERENV','CURRENT_SCHEMAID') 
          ,sys_context('USERENV','CURRENT_USER') 
          ,sys_context('USERENV','CURRENT_USERID') 
          ,sys_context('USERENV','DB_DOMAIN') 
          ,sys_context('USERENV','DB_NAME') 
          ,sys_context('USERENV','INSTANCE') 
         ,sys_context('USERENV','IP_ADDRESS') 
         ,sys_context('USERENV','ISDBA') 
         ,sys_context('USERENV','LANG') 
         ,sys_context('USERENV','LANGUAGE') 
         ,sys_context('USERENV','NETWORK_PROTOCOL') 
         ,sys_context('USERENV','NLS_CALENDAR') 
         ,sys_context('USERENV','NLS_CURRENCY') 
         ,sys_context('USERENV','NLS_DATE_FORMAT') 
        ,sys_context('USERENV','NLS_DATE_LANGUAGE') 
        ,sys_context('USERENV','NLS_TERRITORY') 
        ,sys_context('USERENV','OS_USER') 
        ,sys_context('USERENV','SESSION_USER') 
        ,sys_context('USERENV','SESSION_USERID') 
       ,sys_context('USERENV','SESSIONID') 
       ,sys_context('USERENV','TERMINAL') from dual ;

The output of the above sample is :

Enjoy      :-)