Automatic Workload Repository (AWR) || How to generate AWR report

Oracle have provided many performance gathering and reporting tools over the years.In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

The AWR is used to collect 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 statements.

Snapshots:

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 following procedure.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;

Extra snapshots can be taken and existing snapshots can be removed, as shown below.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/

Baselines:

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 210,
end_snap_id => 220,
baseline_name => 'batch baseline');
END;
/

If you do not have Oracle Enterprise Manager tool installed then you can create the AWR reports manually using the following commands:

1. Creating Snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

The list of the snapshot Ids along with database Ids is availabe in the view DBA_HIST_SNAPSHOT.

2. Dropping a Range of Snapshots.
Refer to the DBA_HIST_SNAPSHOT view column SNAP_ID to view available snapshots. To delete contain SNAP_ID from from 102 to 122,

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 102, high_snap_id => 122, dbid => 8187786345);
END;
/

3. Modifying Snapshot Settings
If you want to modify the retention period as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100 then use following:

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 7123356265);
END;
/
The dbid is optional.

4. Extract the AWR Data
The awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. Once created, this dump file can be transported to another system where the extracted data can be loaded. To run the awrextr.sql script, you need to be connected to the database as the SYS user.

To extract AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql

5. Load the AWR Data
Once the export dump file is transported to the target system, you can load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sqlscript, you need to be connected to the database as the SYS user.

To load AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

6. Generate AWR Reports
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the number of days for which you want to list snapshot Ids.

Enter value for num_days: 2

After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 95
Enter value for end_snap: 97

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:
Using the report name awrrpt_1_95_97

The workload repository report is generated.

awrrpt.sql
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

awrrpti.sql
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.

awrsqrpt.sql
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

awrsqrpi.sql
The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

awrddrpt.sql
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

awrddrpi.sql

The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

Workload Repository Views:

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.

Re-sizing Redo Logs

Every DBA knows that the size of their redo logs is very important.  Too small, and the frequent log switches tie-up the LGWR, ARCH and DBWR background processes.  Too large and you risk losing data during an instance crash.

As a general rule, you size your online redo logs not to switch more then 5 times per hour during peak DML times.  Here is a script that measures redo log sizing and log switches.

Some things to consider with changing the size of online redologs:

You do it for performance reasons – mostly because your original files are too small and you get log switches too often
It is an Online Operation – no end user will even notice about it – apart from maybe better performance afterwards
You will not put your Database at risk with it – if you do it right :-)

Let me give you an example:

Current redo file information is as below:

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 1          1 INACTIVE                 50
 2          1 INACTIVE                 50
 3          1 CURRENT                  50


SQL> select group#,member from v$logfile;

 GROUP# MEMBER
---------- ------------------------------------------------------------
 3 /u01/app/oracle/oradata/orcl/redo03.log
 2 /u01/app/oracle/oradata/orcl/redo02.log
 1 /u01/app/oracle/oradata/orcl/redo01.log

Now , to get 100m sized logfiles and we want them mirrored[Multiplexing] for security reason. First, we create new log groups of the desired size:

SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g4m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g4m2.rdo')
     size 100m;

Database altered.

SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g5m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g5m2.rdo')
     size 100m;

Database altered.
SQL> alter database add logfile
     ('/u01/app/oracle/oradata/orcl/redo_g6m1.rdo',
      '/u01/app/oracle/oradata/orcl/redo_g6m2.rdo')
     size 100m;

Database altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 1          1 INACTIVE                 50
 2          1 INACTIVE                 50
 3          1 CURRENT                  50
 4          2 UNUSED                  100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

6 rows selected.

Here we are using suffix rdo as it is not so easy misinterpreted as a mere ascii textfile that can be deleted if space is needed.

Now we are going to drop the small files – with SQL commands first!

SQL> alter database drop logfile group 1;
Database altered.

That was easy, wasn’t it? Notice that the OS file is not gone yet  – we have to delete them manually (if they are not OMF). All groups that have the status INACTIVE can be dropped that way.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 2          1 INACTIVE                 50
 3          1 CURRENT                  50
 4          2 UNUSED                  100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

SQL> alter database drop logfile group 2;

Database altered.

If the status is CURRENT or ACTIVE, it won’t be possible to drop the group – you can do nothing wrong here!

SQL>  alter database drop logfile group 3;
 alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

CURRENT means that LGWR is actually using this group to write the redo protocol into. If a log switch happens, the status can become ACTIVE or INACTIVE:

SQL> alter system switch logfile;

System altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 3          1 ACTIVE                   50
 4          2 CURRENT                 100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

ACTIVE means, the checkpoint associated to the redo protocol in that group is not yet finished. It is in other words still needed for a possible instance recovery and cant be dropped therefore. But we can force the checkpoint:

SQL> alter system checkpoint;

System altered.
SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

 GROUP#    MEMBERS STATUS                   MB
---------- ---------- ---------------- ----------
 3          1 INACTIVE                 50
 4          2 CURRENT                 100
 5          2 UNUSED                  100
 6          2 UNUSED                  100

SQL> alter database drop logfile group 3;
Database altered.

Now we are finished with my SQL commands and we can continue with deleting the files of the small groups from the OS. The instance was OPEN all the time and no user was in any way concerned by this whole action. By the way, we mirrored the log groups to protect my database against really serious problems that would arise otherways, if a whole log group gets lost – for example because someone deletes a file from the OS.

Cloning Oracle Database

CPU intensive reports should be generated to another database than the production database. For such type of cases we need to create another db for processing purpose. As the effects the performance negatively in the production server.

1.set ORACLE_SID='dbname'

2.sqlplus / as sysdba

3.SQL> Select name from v$database;
>> dbname
4.Select log_mode from v$database;

5. in case of archive log we will get >> ARCHIVELOG

6.SQL> archive log list

7. For the cloning purpose, we need to create the DB folder in the corresponding location as per the instance. Let assume we want to clone the database in the same server with the name dbname2, For this reason, we will create a folder with this name in the oradata folder , inside this directory we will create the ARCHIVED_LOGS directory also.

8. Then in the oracle_home location ..\db_1\database make a file names initdbname2.ora following the another init file. The content of the file should be db_name=dbname2 [as per our example]

9. Now we need to make a instance for this newly created database.
oradim -new -sid dbname2
net start OracleServicedbname2
[For windows environment; not for Unix or Linux Enviroment]

10. Now again after going back to my main databse,
SQL> Selct * from v$backup;
SQL> select tablespace_name from dba_tablespaces;

11. To make the main db to backupmode:
SQL> alter database begin backup;

12. now after executing the below query:
      SQL> Select * from v$backup;
      we get the status of the backup in the active mode

13. Now going to the file location of the database oradata\dbname folder
      cp *dbf /usr/home/usrname/filelocation

14. now again through sqlplus we need to stop the back mode of the main database:
     SQL> alter database end backup;
     after that for checking we can use Select * from v$backup; which will indicate ‘Not Active’

15. SQL> alter system archived log current

16. SQL> alter system backup controfile to trace as ‘/usr/home/usrname/filelocation/trace.sql’ reuse;

17. Now in the trace.sql file, we need to change the dbname by dbname2.
    and CREATE CONTROLFILE REUSE DATABASE dbname NORESETLOGS should be        
    changed as REATE CONTROLFILE SET DATABASE dbname RESETLOGS

18. Now we can copy the already copied database to dbname2 file location
      cp /usr/home/usrname/filelocation/* to .
19. Now we will access dname2 by sqlplus
SQL> set ORALCE_SID=dbname2
SQL> sqlplus / as sysdba
SQL>@/usr/home/usrname/filelocation/trace.sql;

database will be started successfully. No we can get all the database of dbname in dbname2
SQL> Select name from v$database;
SQL> alter system archive log current;

now we need to take the archive log of the dbname for dbname2 for recover the database.
SQL> set log source ‘arhive log direcotry of dbname’
SQL> recover database using backup control file until cancel;
SQL> alter database open reset logs;
SQL> Select Open_Mode from v$database;

Redo Log File || Operations

Redo Log files records every change made by users. This guarantees that we will not lose any change, even if it was not written in Data Files due to instance failure.The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again.

The redo log of a database consists of two or more redo log files. The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if the database is in ARCHIVELOG mode).

As the part of circular writting, a log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it.

Multiplexing is the procedure to keep the multiple copy of the redo log files for the confirmation of recovery steps in case of failure. It is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.

For the new allocation, Block Size is maintained for redo log files. It might be 4K and 512B.

However, with a block size of 4K, there is increased redo wastage. In fact, the amount of redo wastage in 4K blocks versus 512B blocks is significant. You can determine the amount of redo wastage by viewing the statistics stored in the V$SESSTAT and V$SYSSTAT views. SQL> SELECT name, value FROM v$sysstat WHERE name = 'redo wastage';

Beginning with Oracle Database 11g Release 2, you can specify the block size of online redo log files with the BLOCKSIZE keyword in the CREATE DATABASE, ALTER DATABASE, and CREATE CONTROLFILE statements. The permissible block sizes are 512, 1024, and 4096.

Adding Redo Log File:

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause. The following statement adds a new group of redo logs to the database: ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 100M; You can also specify the number that identifies the group using the GROUP clause: ALTER DATABASE ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 100M BLOCKSIZE 512; To create new redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOGFILE MEMBER clause. The following statement adds a new redo log member to redo log group number 2: ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2; Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group. When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example: ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo' TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

Steps for Renaming Redo Log Members:

• Shut Down the database: SHUTDOWN
• Copy the redo log files to the new location.
• Startup the database, mount, but do not open it.

CONNECT / as SYSDBA STARTUP MOUNT

• Rename the redo log members.
• Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database redo log files.

ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';

• Open the database for normal operation.
• The redo log alterations take effect when the database is opened.

ALTER DATABASE OPEN;

Dropping Redo log File;

To drop a redo log member, you must have the ALTER DATABASE system privilege. ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo'; When a redo log member is dropped from the database, the operating system file is not deleted from disk.

Forcing Log Switches: ALTER SYSTEM SWITCH LOGFILE;

Clearning Log File:

The following statement clears the log files in redo log group number 3: ALTER DATABASE CLEAR LOGFILE GROUP 3; If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Redo Log Data Dictionary Views:

The following views provide information on redo logs.

View	Description
V$LOG	Displays the redo log file information from the control file
V$LOGFILE	Identifies redo log groups and members and member status
V$LOG_HISTORY	Contains log history information

Target: Exporting and importing Oracle Data

1. Create directory for making location:

create directory as dirname ‘/usr/paul/home/location’

   2. Grant permission on that directory

grant read, write on directory dirname to username

  3. Export command

expdp user_id/pass@db_sid tables=, DIRECTORY=

DUMPFILE= LOGFILE=

  4. Import Command:

impdp user_id/pass@db_sid tables=, DIRECTORY=
DUMPFILE= LOGFILE=