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

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

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

SQL*Plus COPY Command

The SQL*Plus COPY command can copy data between two databases via SQL*Net .  The preferred method of doing this is to use SQL*Plus on the host where the database resides.  If performing the copy command from a client SQL*Net connection, the data is transferred through the client machine.

The copy command copies data from one Oracle instance to another.   The data is simply copied directly from a source to a target.  The format of the copy command is:

SQL> copy from  <source database> TO <target database>  destination_table (column_name, column_name...) USING query

The action can include:

• Create    –  If the destination table already exists, copy will report an error, otherwise the table is created and the data is copied.
• Replace   –  If the destination table exists, copy will drop and recreate the table with the newly copied data. Otherwise, it will create the table and populate it with the data.
• Insert     –  If the destination table exists, copy inserts the new rows into the table. Otherwise, copy reports an error and aborts.
• Append   –  Inserts the data into the table if it exists, otherwise it will create the table and then insert the data.   

 SQL> copy from scott/tiger@test1  to scott/tiger@orcl create new_emp using select * from emp;

Here "test1" is remote database and "orcl" is target database where the table new_emp is created.

Once the command above is executed, the copy utility displays the values of three parameters, each of which can be set with the SQL*Plus set command.  The arraysize specifies the number of rows that SQL*Plus will retrieve from the database at one time.  The copy commit parameter specifies how often a commit is performed and is related to the number of trips – one trip is the number of rows defined in arraysize.  Finally, the long parameter displays the maximum number of characters copied for each column with a LONG datatype.

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table NEW_EMP created.

   14 rows selected from scott@test1

   14 rows inserted into NEW_EMP.

   14 rows committed into NEW_EMP at scott@orcl

SQL> desc new_emp;

  Name                                                    Null?                      Type

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

 EMPNO                                           NOT NULL                 NUMBER(4)

 ENAME                                                                              VARCHAR2(10)

 JOB                                                                                   VARCHAR2(9)

 MGR                                                                                  NUMBER(4)

 HIREDATE                                                                        DATE

 SAL                                                                                   NUMBER(7,2)

 COMM                                                                               NUMBER(7,2)

 DEPTNO                                                                           NUMBER(2)

The command above did not specify column names for the new table (new_emp).  As a result, the new table will have the same column names as the table being copied.  If different column names are required, they can be specified after the table name:

create new_emp (col1, col2, …)

However, if one column name is specified, they all must be specified.

A DBA could perform this same function with a database link from one database pointing to another.  The appeal of the copy command is that it only requires SQL*Net service names and proper privileges to get the job done.  For those environments that restrict the usage of database links, the copy utility can be leveraged.  In addition, the copy command provides many options, as defined by the actions create, replace, insert and append.

If the copy command is executed from a client PC to copy data from remote database DB0 to remote database DB1, the data will be copied from DB0 to the client PC and then to DB1.  For this reason, it is best to use SQL*Plus from either remote host and not require the data to travel through a client machine in order to reach its final destination.

The following command copied the table_with_one_million_rows table to new_table:

SQL> copy from scott/tiger@DB0  to scott/tiger@DB1 create new_table using select * from table_with_one_million_rows;

Array fetch/bind size is 5000. (arraysize is 5000)

Will commit after every 5000 array binds. (copycommit is 5000)

Maximum long size is 80. (long is 80)

Table NEW_TABLE created.

   1000000 rows selected from scott@DB0.

   1000000 rows inserted into NEW_TABLE.

   1000000 rows committed into NEW_TABLE at scott@DB1.

 The copy command becomes handy when we have limited (SELECT) access on the source database or we do not have OS level access for the server hosting the source database.

  

Enjoy     :-)