ORA-28000 ,ORA-28001, ORA-28002 : The Account locked ,expired or password will expire within xx days

ORA-28000 specifies the user's account is locked .The common reason of occurring this error is when it gets locked internally based on the profile resource limit. This error may also occur when the user has entered wrong password consequently for maximun no. of times as specified by the user's profile parameter i.e, Failed_Login_Attempts. To solve this error either wait for the Password_lock_time or the DBA can fire the below command to solve this issue :

SQL> alter user abc identified by password account unlock ;

ORA-28001 specifies the user account is expired . This error commonly occurs when the expiry time is reached . By default the expiry date for a newly created user is of 180 days . Hence to solve this issue, increase the limit of the password expiry date. For this check the profile assigned to the user and then limit the password expiry date. To solve this issue increase the password expiry periods .

SQL>select username,profile from dba_users where username='TEST' ;

SQL> alter profile  profile_name limit  PASSWORD_LIFE_TIME  UNLIMITED;

ORA-28002 specifies that  the user's account is about to about to expire and the password needs to be changed. This can be solved either by changing the password or by changing the user profile. If we do want this behavior, we need to do the following:

1.) Logon to the product database as the SYSTEM user (not the application administration user).

2.) Find the profile that has the PASSWORD_LIFE_TIME set to anything but UNLIMITED.

SQL> select * from dba_profiles where RESOURCE_NAME LIKE  'PASSWORD_LIFE_TIME';

If the user name say "test" and password is also "test" then check the profile assign to the user as

SQL>select username,profile from dba_users where username='TEST' ;

Once ,we have profile, we alter the profile and password .

3.)  Alter the profiles with the following statement:

SQL> alter user test identified by test  account unlock ;

SQL> alter profile  profile_name limit  PASSWORD_LIFE_TIME  UNLIMITED;

where profile_name is the name of the profile where wer need to set the password life to UNLIMITED.

This should remove the password life message.


Enjoy   :-) 

Configuring Oracle ASM On Windows

Automatic Storage Management(ASM) is oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems or files which can be made to look like disks as long as the device is raw. ASM uses its own database instance to manage the disks, it has its own processes and pfile or spfile, it uses ASM disk groups to manage disks as one logical unit. The benefits of ASM are

• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
• Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
• Uses redundancy features available in intelligent storage arrays
• The storage system can store all types of database files
• Using disk group makes configuration easier, as files are placed into disk groups
• ASM provides stripping and mirroring (fine and coarse gain)
• ASM and non-ASM oracle files can coexist
• ASM is free!!!!!!!!!!!!!

Preparing Diskgroups for Automatic Storage Management

Step 1 : Create Disk Partitions :
For this sample installation I use only one Disk and create logical partitions on it. For production implementation create a primary partition on each disk.

To create partitions,

C:\> Diskpart

DISKPART> list disk

DISKPART> Select disk 0
To create a logical partition, the size is in MB.

DISKPART> Create partition logical size=1024
To create a primary partition , the size is in MB

DISKPART> Create partition primary size=1024

To view the partitions created

DISKPART> List partition

Repeat for other disks

The created volumes must be unformatted, that is, the Fs column must be blank in the following command. In the following output NTFS indicates NT File system and hence cannot be used as ASM disk. 

Stamp ASM Disks 

To use ASM, the disks must be stamped with a header. Use ASMTOOL to stamp each partition with an ASM label so that oracle can recognize these partitions as candidate disk. Navigate the oracle media to find the asmtool folder. Click the asmtoolg.exe.

Select the Add or change label dialog and press Next button.

Select the Candidate Disk and assign a asmdisk group name. Click Next.

Click Next.

Click Finish.

Repeat the process for each disk group.

Now, we have done installation and ASM  instance Creation.

Step 1:   Invoke the Oracle Universal Installer. In the Welcome Screen select Advanced Installation and click Next button.

 

Step 2: In the Select Installation Type, select the appropriate choice. In our case we select Standard Edition.

Step 3 : Specify the ASM Home details.

Click Next.

 

Step 4: Check for any errors and correct it if any. Click Next.

 

Step 5 : Choose the Configure Automatic Storage Management option and specify the SYS password. Click Next.

 

Step 6: In the next screen do the following,

1.     Specify the Disk Group name.

2.     Choose the redundancy type.

3.     Select the candidate disks you want to assign for this disk group.

Note: We must have stamped the disks before to make Oracle recognize the disks. If we have not stamped the disks already, click the stamp disks button to invoke theasmtool utility. we can then stamp the disks as above in the Stamp disks section .

Click Next

 

Step 7: Click Install.

Click the Exit button. The ASM installation is completed.
Note:We can use the DBCA to create addition disk groups.

Here we have discussed the ASM installation.Now we will see the Oracle Installation

Step 1 : Invoke the OUI. Select the Advanced installation option and then click Next.

Step 2 : Select the installation type for eg., Enterprise, standard edition etc.

Step 3: Specify the Oracle Home details. Choose a different path from ASM home.

Step 4: In the next page select the option to create the database.

Step 5: Specify a Database Name. Choose the character set.

 

Step 6: Select Database control for Database Management.

Step 7: In the select database storage option page, select ASM.

 

Step 8: In the next page click the option that best matches our requirement.

 

Step 9: Select the ASM Disk Group.

If the space available is not enough it will show the following screen.

Select the disks so that the required space is available. Click Next

Step 10: Click Install.

Click Exit. The oracle RDBMS is installed.

Reference : http://oracledba-vinod.blogspot.com/2009/12/install-oracle-10g-on-windows-xp-using.html


Enjoy       :-) 

Identify and Remove the duplicate rows in a Table

In case if we want to identify duplicates rows of a table and want to remove them from a table.  Below are the command which will  identify and remove the duplicare rows from a Table.

1.) Identify duplicates

SQL> select count(*) from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2
MINUS
select max(rowid) from ADDRESS2
GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

COUNT(*)
———-
251

Here 251 duplicates exist – these can be deleted with command below :

2.) Removing the Rows :

SQL> delete from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2

MINUS

select max(rowid) from ADDRESS2

GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

Enjoy  :-) 

Standby Database in Oracle 11g

This post explains the method of creating a Physical Standby Database using 11g RMAN Duplicate from active database feature which was introduced on 11gR1 onwards. This method is very efficient because we do not need the backup of primary database. Here in my case, I have created the standby on same server as testing standby. I will post the standby database on different machine later on. So below the steps to create the standby database. 

Primary Database  : DB_UNIQUE_NAME:   prim 

Standby Database : DB_UNIQUE_NAME:   stand

Follow the below steps as in order  :

Step 1 : Enable Force Logging on the Primary database : It is a best practice to place the primary database in force logging mode so that all operation are captured in the redo stream.

On primary database:

C:\>sqlplus sys/xxx@prim as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 23 11:23:37 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> alter database force logging;

Database altered.

Step 2 :  Create the password file for the Standby  (for os other than window)

$ orapwd file=$ORACLE_HOME/dbs/orapwstand.ora  password=xxxx

In case of window  create service as

C:\>oradim -new -sid stand -intpwd  xxxx  -startmode m

Instance created.

Note  -  Ensure that the same password is used as the one used while creating the password file on the Primary host or we can copy and paste the same password file and futher rename as pwd<sid>.ora in case of window (for example, in this case pwdstand.ora)

Step 3 :  Update network configuration files(tnsnames.ora)

Add the following entries to the tnsnames.ora file .

prim =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = prim)

    )

  )

stand =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = stand)

    )

  )

C:\>tnsping stand

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-JUN-2011 12:24:24

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

D:\app\Neerajs\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stand)))

OK (30 msec)

Step 4  : Add a static entry in the listener.ora file  and reload or restart the listener.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = prim)

      (ORACLE_HOME = D:\app\Neerajs\product\11.2.0\dbhome_1)

      (SID_NAME = prim)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = stand)

      (ORACLE_HOME = D:\app\Neerajs\product\11.2.0\dbhome_1)

      (SID_NAME = stand)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))

  )

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-JUN-2011 12:22:41

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))

The command completed successfully

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                        LISTENER

Version                    TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

Start Date                22-JUN-2011 09:56:31

Uptime                    1 days 2 hr. 26 min. 15 sec

Trace Level             off

Security                   ON: Local OS Authentication

SNMP                    OFF

Listener Parameter File   D:\app\Neerajs\product\11.2.0\dbhome_1\network\admin\listener.ora

Listener Log File         d:\app\neerajs\product\11.2.0\dbhome_1\log\diag\tnslsnr\xxxx\listener

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=1521)))

Services Summary...

Service "prim" has 2 instance(s).

  Instance "prim", status UNKNOWN, has 1 handler(s) for this service...

  Instance "prim", status READY, has 1 handler(s) for this service...

Service "primXDB" has 1 instance(s).

  Instance "prim", status READY, has 1 handler(s) for this service...

Service "stand" has 1 instance(s).

  Instance "stands", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Step 5 :  Create a init.ora file on the Standby host with just a single parameter 

Open notepad and add parameter and save it as stand.ora

DB_NAME=prim

Step 6 : Create the required directories on the Standby host 

Check the value of the parameter used in parameter file of the Primary database. Ensure that this directory structure also exists on the Standby host. If the directory structure differs in any way on the Primary and Standby server, we will have to ensure that the RMAN rcv file to create the Standby database is amended to reflect this as well.In my case, I  have create a folder inside the "oradata" folder as  stand (D:\app\Neerajs\oradata\stand) and pfile,dpdump,audit resp. inside "admin" folder (D:\app\Neerajs\admin\stand) . Here we have not add the parameter for diverting the logfile and datafile .By default is taking the "oradata" and  "admin"  folder. If we want to change the location of datafile or logfile then we change the location by using the below location .

db_file_name_convert  = ('Primary_location_datafile','Standby_location_file')  

log_file_name_convert = ('Primary_location_logfile','Standby_location_logfile')

We can add this parameter in  active_standby.rcv

Step  7 :  Create the active_standby.rcv file

DUPLICATE TARGET DATABASE  FOR STANDBY

FROM ACTIVE DATABASE  DORECOVER 

SPFILE 

SET DB_UNIQUE_NAME='stand' 

SET LOG_ARCHIVE_DEST_1=’LOCATION=D:\STAND\’ 

SET LOG_ARCHIVE_DEST_2='service=prim LGWR SYNC REGISTER VALID_FOR= (online_logfile,primary_role)' 

SET STANDBY_FILE_MANAGEMENT='AUTO' 

SET FAL_SERVER='prim'

SET FAL_CLIENT='stand' 

SET CONTROL_FILES='D:\app\Neerajs\oradata\stand \CONTROL.CTL’ ; 

Step  8 : Start the Standby database instance in NOMOUNT state

C:\>sqlplus sys/xxxxx@stand as sysdba 

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Mar 15 15:40:21 2010

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

Connected to an idle instance.

SQL> startup nomount pfile=’D:\stand.ora’

ORACLE instance started.

Total System Global Area      217157632 bytes

Fixed Size                              2152328 bytes

Variable Size                          159385720 bytes

Database Buffers                    50331648 bytes

Redo Buffers                          5287936 bytes

Step 9 : From the Primary database,run the following RMAN command to create the Standby Database 

C:\> rman target sys/xxxx@prim  auxiliary  sys/xxxx@stand cmdfile=D:\receive.rcv.txt 

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 23 13:35:00 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database:      PRIM (DBID=4046782611)

connected to auxiliary database:   PRIM (not mounted)

RMAN> DUPLICATE TARGET DATABASE

2> FOR STANDBY

3> FROM ACTIVE DATABASE

4> DORECOVER

5> SPFILE

6> SET DB_UNIQUE_NAME='stand'

7> SET LOG_ARCHIVE_DEST_1='LOCATION=D:\stand\'

8> SET LOG_ARCHIVE_DEST_2='service=prim LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'

9> Set STANDBY_FILE_MANAGEMENT='AUTO'

10> SET FAL_SERVER='prim'

11> SET FAL_CLIENT='stand'

12> SET CONTROL_FILES='D:\app\Neerajs\oradata\stand\CONTROL.CTL'

13> ;

Starting Duplicate Db at 23-JUN-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  'D:\app\Neerajs\product\11.2.0\dbhome_1\DATABASE\PWDprim.ORA' auxiliary format

 'D:\app\Neerajs\product\11.2.0\dbhome_1\DATABASE\PWDstand.ORA'   targetfile

 'D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPRIM.ORA' auxiliary format

 'D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTAND.ORA'   ;

   sql clone "alter system set spfile= ''D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTAND.ORA''";

}

executing Memory Script

Starting backup at 23-JUN-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=145 device type=DISK

Finished backup at 23-JUN-11

sql statement: alter system set spfile= ''D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTAND.ORA''

contents of Memory Script:

{

   sql clone "alter system set  db_unique_name =

 ''stand'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_ARCHIVE_DEST_1 =

 ''LOCATION=D:\stand\'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_ARCHIVE_DEST_2 =

 ''service=prim 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 =

 ''prim'' comment=

 '''' scope=spfile";

   sql clone "alter system set  FAL_CLIENT =

 ''stand'' comment=

 '''' scope=spfile";

   sql clone "alter system set  CONTROL_FILES =

 ''D:\app\Neerajs\oradata\stand\CONTROL.CTL'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_unique_name =  ''stand'' comment= '''' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_1 =  ''LOCATION=D:\stand\'' comment= '''' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''service=prim 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 =  ''prim'' comment= '''' scope=spfile

sql statement: alter system set  FAL_CLIENT =  ''stand'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''D:\app\Neerajs\oradata\stand\CONTROL.CTL'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     318046208 bytes

Fixed Size                     1374332 bytes

Variable Size                213911428 bytes

Database Buffers              96468992 bytes

Redo Buffers                   6291456 bytes

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  'D:\APP\NEERAJS\ORADATA\STAND\CONTROL.CTL';

}

executing Memory Script

Starting backup at 23-JUN-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFPRIM.ORA tag=TAG20110623T133533 RECID=5 STAMP=754580140

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 23-JUN-11

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:

{

   set newname for tempfile  1 to

 "D:\APP\NEERAJS\ORADATA\STAND\TEMP01.DBF";

   switch clone tempfile all;

   set newname for datafile  1 to

 "D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF";

   set newname for datafile  2 to

 "D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF";

   set newname for datafile  3 to

 "D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF";

   set newname for datafile  4 to

 "D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF";

   set newname for datafile  5 to

 "D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF";

   backup as copy reuse

   datafile  1 auxiliary format

 "D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF"   datafile

 2 auxiliary format

 "D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF"   datafile

 3 auxiliary format

 "D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF"   datafile

 4 auxiliary format

 "D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF"   datafile

 5 auxiliary format

 "D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\APP\NEERAJS\ORADATA\STAND\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

Starting backup at 23-JUN-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=D:\APP\NEERAJS\ORADATA\PRIM\SYSTEM01.DBF

output file name=D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=D:\APP\NEERAJS\ORADATA\PRIM\SYSAUX01.DBF

output file name=D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=D:\APP\NEERAJS\ORADATA\PRIM\EXAMPLE01.DBF

output file name=D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF tag=TAG20110623T133631

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=D:\APP\NEERAJS\ORADATA\PRIM\UNDOTBS01.DBF

output file name=D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=D:\APP\NEERAJS\ORADATA\PRIM\USERS01.DBF

output file name=D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 23-JUN-11

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "D:\ARCHIVE\ARC0000000010_0754480982.0001" auxiliary format

 "D:\STAND\ARC0000000010_0754480982.0001"   archivelog like

 "D:\ARCHIVE\ARC0000000011_0754480982.0001" auxiliary format

 "D:\STAND\ARC0000000011_0754480982.0001"   ;

   catalog clone archivelog  "D:\STAND\ARC0000000010_0754480982.0001";

   catalog clone archivelog  "D:\STAND\ARC0000000011_0754480982.0001";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 23-JUN-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=10 RECID=8 STAMP=754580181

output file name=D:\STAND\ARC0000000010_0754480982.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=11 RECID=9 STAMP=754580349

output file name=D:\STAND\ARC0000000011_0754480982.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 23-JUN-11

cataloged archived log

archived log file name=D:\STAND\ARC0000000010_0754480982.0001 RECID=1 STAMP=754580356

cataloged archived log

archived log file name=D:\STAND\ARC0000000011_0754480982.0001 RECID=2 STAMP=754580357

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=754580357 file name=D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=754580358 file name=D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=754580358 file name=D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=754580358 file name=D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=754580358 file name=D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF

contents of Memory Script:

{

   set until scn  1062459;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 23-JUN-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=13 device type=DISK

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file D:\STAND\ARC0000000010_0754480982.0001

archived log for thread 1 with sequence 11 is already on disk as file D:\STAND\ARC0000000011_0754480982.0001

archived log file name=D:\STAND\ARC0000000010_0754480982.0001 thread=1 sequence=10

archived log file name=D:\STAND\ARC0000000011_0754480982.0001 thread=1 sequence=11

media recovery complete, elapsed time: 00:00:03

Finished recover at 23-JUN-11

Finished Duplicate Db at 23-JUN-11

Recovery Manager complete.

Step 10 : Change the init.ora parameters related to redo transport and redo apply

On primary :

SQL> alter system set fal_server='stand' scope=both;

System altered.

SQL> alter system set fal_client='prim' scope=both;

System altered.

SQL> alter system set standby_file_management=AUTO scope=both ;

System altered.

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

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=stand LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand' ;

System altered.

Step  11 : Shutdown the Standby database, add the Standby log files and then start real time recovery 

On standby :

SQL> shutdown immediate 

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup 

ORACLE instance started.

Total System Global Area 5344731136 bytes

Fixed Size                  2153536 bytes

Variable Size            3154117568 bytes

Database Buffers         2147483648 bytes

Redo Buffers               40976384 bytes

Database mounted.

Database opened.

SQL> alter database add standby logfile group 4 size 50m ; 

Database altered.

SQL> alter database add standby logfile group 5 size 50m ; 

Database altered.

SQL> alter database add standby logfile group 6 size 50m ; 

Database altered.

SQL>  alter database add standby logfile group 7 size 50m ; 

Database altered.

Now to start real time redo data to be applied without waiting for the current standby redo log file to be archived, enable the real-time apply.

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

Media recovery complete.

Step 12 : Change the Protection Level of the Standby Database to MAXIMIZE AVAILABILITY 

On the Primary database:

SQL> shut immediate;

Database dismounted.

ORACLE instance shut down.

SQL> startup mount 

ORACLE instance started.

Total System Global Area        318046208 bytes

Fixed Size                               1374332 bytes

Variable Size                           251660164 bytes

Database Buffers                     58720256 bytes

Redo Buffers                           6291456 bytes

Database mounted.

SQL> alter database add standby logfile group 4 size 50m ; 

Database altered.

SQL> alter database add standby logfile group 5 size 50m ;

Database altered.

SQL> alter database add standby logfile group 6 size 50m ; 

Database altered.

SQL>  alter database add standby logfile group 7 size 50m ; 

Database altered.

The Data Guard "alter database set standby to maximize performance" is the default behavior.  This  command sets the highest level of performance, and protects from failure of any single component.  Oracle says that "'alter database set standby to maximize performance" is useful for applications that can tolerate some data loss. For example, if the last standby is unavailable, processing will continue on the primary instance. When the standby becomes available, re-synchronization is done automatically.  The maximize availability mode protects from failure of any single Data Guard component .

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE                        PROTECTION_LEVEL

-----------------------------                  ---------------------------------

MAXIMUM AVAILABILITY               MAXIMUM AVAILABILITY

Enjoy   :-)