Saturday, June 25, 2011

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  :-) 


Thursday, June 23, 2011

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   :-)


Wednesday, June 22, 2011

ORA-28056: Writing audit records to Windows Event Log failed


This Problem occurs when we are trying to connect as sysdba. This error may occur at anytime when there Event Viewer log become full. In my case it occurs when connecting with sys user as sysdba privilage  and once while creating the database(through dbca when it has completed 85% ).

C:\> sqlplus sys/xxxx@noida as sysdba
ORA-28056: Writing audit records to Windows Event Log failed.

Cause of the Problem : The problem happened because Event Viewer log is full and not able to log anymore events.

Solution of the Problem :  This was because the Event Viewer log is full and could not log anymore events.
The solution is to clear the event log .To solve this issue follow any of the following steps.

1) When a log is full, it stops recording new events. Clearing the log is one way to free the log and start recording new events. To do so, on the Action menu (left click)or on the left side Application/System/Security (as available) right click and select Clear All events.

2) We can also free a log and start recording new events by overwriting old events. To overwrite events, on the Action menu, or on the left side Application/System/Security (as available) click Properties, and then click Overwrite events as needed. This ensures that all new events are written to the log, even when the log is full. 

3) We can also start logging new events by increasing the maximum log size. To increase the log size, on the Action menu or on the left side Application/System/Security (as available) click Properties, and then increase the Maximum log size by typing a bigger value.


Enjoy  :-)


RMAN-05001 :Auxiliary Filename Conflicts with a File Used by the Target Database


If we lookup the cause of error   RMAN :05001 we find that this error has occur because  RMAN is attempting to use the specified file name as a restore destination in the auxiliary database, but this name is already in use by the target database. 

If we are experiencing RMAN-05001: auxiliary filename conflicts with a file used by the target database, then we may be confused as I was about how to proceed. If we are converting ALL files from various locations to new locations, then use db_file_name_convert. If we want to retain the exact same locations with no conversion, we use NOFILENAMECHECK. But what if we want to convert some, and not convert others? I was not sure whether NOFILENAMECHECK would then override db_file_name_convert, but it turns out that we can use both in concert. For those files that are in the same location, it will not complain, but the others that we want to convert will be converted properly from the db_file_name_convert entries in our pfile.


Enjoy   :-) 


Monday, June 20, 2011

Difference Between Dataguard and Standby


There is common confusion about oracle standby database and dataguard. I found many people who consider dataguard and standby are same . Here are the difference between Dataguard and standby : 

Dataguard  :  Dataguard is mechanism/tool to maintain standby database. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. 

The dataguard is set up between primary and standby instance .DataGuard can manage both physical and logical standby.  DataGuard requires the enterprise edition, while we could write our own standby scripts using the standard edition. we have got support and testing issues-- if something goes wrong, we are on our own to debug our scripts. If we are using DataGuard, there is a variety of documentation available and a variety of options for support. Oracle has also invested quite a bit of time testing DataGuard with various failure scenarios-- it's likely that we wouldn't have that much time to test our own scripts. On a day-to-day basis, DataGuard provides integration with various monitoring utilities (i.e. Enterprise Manager) and provides a rather nice set of tables to view information about what's going on. DataGuard also provides functionality like the ability to automatically detect and resolve gaps in the archived log files that your scripts would not likely be written to handle. And, of course, our scripts could only manage a physical standby database. 

Data Guard was originally a set of scripts, but now is the entire environment including a set of processes that control the extraction of redo (directly from log bugger, from redo logs or archive redo logs) from the primary, shipping to the standby, ensuring that the logs are applied. Data Guard processes also include the mechanics needed to make the standby database active automatically (failover) or manually (switchover) and also to re-sync and make the original database active again (switchback).

All that said, Data Guard is only available on Enterprise Edition. Standby capability is available on Standard Edition. And there are commercial products around that provide capability similar to Data Guard for Standard Edition. For more detail click here

Standby Database :  Physical standby database provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.   A physical standby database can be used for business purposes other than disaster recovery on a limited basis.

Enjoy     :-)


Thursday, June 16, 2011

Automatic Workload Repository (AWR) in Oracle


Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).The AWR is a repository of performance information collected by the database to aid in the tuning process for DBAs.

Oracle 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when we create a new Oracle database. We can disable and enable the schedule job by following command:

we can disable this job by using the dbms_scheduler.disable procedure as below :

Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

And we can enable the job using the dbms_scheduler.enable procedure as below :

Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

AWR consists of a collection of performance statistics including :
  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the v$sess_time_model and v$sys_time_model views.
  • Active Session History (ASH) statistics from the v$active_session_history view.
  • Some system and session statistics from the v$sysstat and v$sesstat views.
  • Object usage statistics.
  • Resource intensive SQL and PL/SQL.

The resource intensive SQL and PL/SQL section of the report can be used to focus tuning efforts on those areas that will yield the greatest returns.  The statements are ordered by several criteria including :
  • SQL ordered by Elapsed Time
  • SQL ordered by CPU Time
  • SQL ordered by Gets
  • SQL ordered by Reads
  • SQL ordered by Executions
  • SQL ordered by Parse Calls
  • SQL ordered by Sharable Memory

Several of the automatic database tuning features require information from the AWR to function correctly, including:
  • Automatic Database Diagnostic Monitor
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor

 How to generate AWR report ?  

There are two scripts that are provided by oracle to generate the AWR report. The scripts are available in the directory  $ORACLE_HOME\rdbms\admin. The two scripts are 

1.) awrrpt.sql   : If we have only One Oracle Database then run awrrpt.sql sql script.

2.) awrrpti.sql  : If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the below procedure :

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 15);          -- Minutes. Current value retained if NULL.
END;
/
                  Here we have  alter the snapshot interval to 15min. It is recommended that 15 Minutes is enough in two snapshot for better performance bottleneck.

AWR using Enterprise Manager    :   The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows us to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Here is the Demo of the AWR report .

C:\>sqlplus sys/xxxx@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 16 11:42:19 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> @D:\app\Neerajs\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~
   DB Id                       DB Name        Inst Num          Instance
-----------                      ------------           --------          ------------
 1281052636                ORCL                1                    orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: HTML

Type Specified:  html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id                        Inst Num       DB Name        Instance           Host
------------                      --------           ------------        ------------        ------------
* 1281052636                1                   ORCL              orcl                xxxx

Using 1281052636 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:       (Press Enter to see all the snapshots)

Listing all Completed Snapshots
                                                       
Instance     DB Name        Snap Id       Snap Started               Level
---------       ------------         ---------         ------------------              -----
orcl             ORCL                 1             08 Jun 2011 11:30          1
                                               3             08 Jun 2011 14:41         1
                                               4             08 Jun 2011 15:30         1
                                 .
 Data is truncated
         .            
                                                120           16 Jun 2011 05:30       1
                                                121           16 Jun 2011 06:30       1
                                                122            16 Jun 2011 07:30      1
                                                123            16 Jun 2011 08:30      1
                                                124            16 Jun 2011 09:30      1
                                                125            16 Jun 2011 10:30      1
                                                126            16 Jun 2011 11:30      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 125
Begin Snapshot Id specified: 125

Enter value for end_snap: 126
End   Snapshot Id specified: 126
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_125_126.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:          (Press enter if you want to use the above name)
Using the report name awrrpt_1_125_126.html
.
.
Report is truncated 
.
.
End of Report
</body></html>
Report written to awrrpt_1_125_126.html
SQL>

In the above report the line which are shaded with red colour are the entered values when it prompts.


Enjoy     :-)