Generating AWR/ADDM reports at database level and instance level in 11gR2 RAC

Purpose:

Generate AWR/ADDM reports both at instance level and database level for an  Oracle 11g R2 RAC database.

We can query DBA_HIST_SNAPSHOT view to get the snapshot IDs and their begin and end timings.

For eg:

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from DBA_HIST_SNAPSHOT where INSTANCE_NUMBER=7 order by 1;


1. Generate AWR Report

a) Instance level

    Use the script awrrpt.sql available in rdbms/admin to generate AWR report for the particular node
    Specify the begin/end snapshots for the report and give a name

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus sys as sysdba
SQL> @awrrpt.sql

b) Database level

    Use the script awrgrpt.sql available in rdbms/admin to generate AWR report for the entire database where g stands for global
    Specify the begin/end snapshots for the report and give a name

$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus sys as sysdba
SQL> @awrgrpt.sql

2. Generate ADDM Report

ADDM report can be executed in three different modes in 11gR2. The same package  DBMS_ADDM is being called in all three cases albeit with different procedures.

a. Database Level

Use this mode to analyze all the instances in the RAC

VAR rname VARCHAR2(30);
BEGIN
  :rname := 'ADDM for DB Mar21-23';
  DBMS_ADDM.ANALYZE_DB(:rname,  4877, 4913);
END;
/

where 4877 and 4913 are the begin and end snapshot IDs
Get the report:

set long 1000000

spool ADDM_Full_DB.txt

SELECT DBMS_ADDM.GET_REPORT( :rname) FROM DUAL;

spool off;

b. Instance Level:

It is run just like the DB mode except for that we pass the instance number also as part of parameters.

VAR rname VARCHAR2(30);
BEGIN
  :rname := 'ADDM OHADI 7 for Mar21-23';
  DBMS_ADDM.ANALYZE_INST(:rname,4877,4913, 7);
END;
/

Get the report:

set long 1000000

spool ADDM_Instance.txt

SELECT DBMS_ADDM.GET_REPORT( :rname) FROM DUAL;

spool off;

We can run addmrpt.sql script under $ORACE_HOME/rdbms/admin and get the same instance level output.

c. Partial Mode:

VAR rname VARCHAR2(30);
BEGIN
  :rname := 'ADDM OHADI 7 and 8 for Mar21-23';
  DBMS_ADDM.ANALYZE_PARTIAL(:rname, '7,8', 4877,4913);
END;
/

where 7 and 8 are instance name

set long 1000000

spool ADDM_Partial.txt

SELECT DBMS_ADDM.GET_REPORT( :rname) FROM DUAL;

spool off;

Note:

We can still make use of awrrpti.sql and addmrpti.sql to generate AWR and ADDM reports at instance level. These scripts would prompt for DBID and Instance number

ORA-27300 with ORA-00600: internal error code, arguments: [4194]

Error:

ORA-27300: OS system dependent operation:semctl failed with status: 22
ORA-00600: internal error code, arguments: [4194], [], [^B^@], [], [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced


Platform:

Oracle 11.2.0.1.0 EE on Red Hat Enterprise Linux Server release 5.5 32 Bit

Issue:

Database instance is terminated. On trying to startup the database instance, it crashes after mount stats throwing the following error stack
SQL> startup
ORACLE instance started.
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 13023
Session ID: 387 Serial number: 5

The alert log pointed to following error messages
ORA-27300: OS system dependent operation:semctl failed with status: 22
ORA-27301: OS failure message: Invalid argument
ORA-27302: failure occurred at: sskgpwpost1
ORA-27303: additional information: semid = 131073
ORA-00600: internal error code, arguments: [4194], [], [^B^@], [], [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
Doing block recovery for file 3 block 4069
No block recovery was needed
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x418D04E0] [PC:0x9BD1E9A, kgegpa()+36] [flags: 0x0, count: 1]
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x418D04E0] [PC:0x9BD1E9A, kgegpa()+36] [flags: 0x2, count: 2]
Wed Mar 26 05:39:40 2014
ORA-1092 : opitsk aborting process
Wed Mar 26 05:39:57 2014
ORA-1092 : opitsk aborting process

Cause:

ORA-27300 normally denotes failure at O/S level and ORA-27302  points to removed shared memory segments. So in this case some thing happened at O/S level with 
shared memory segments.

The second part of the error message is an internal error with first arguement [4194]
This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

It happens if Oracle cannot validate that the next undo record to be added to the undo block is equal to current maximum number plus one.

Solution:

So first we have to identify and remove the shared memory segment associated with the failed instance and then fix the next part related to ORA-00600.
Use ipcs and ipcrm commands to identify and remove the shared memory segments as follows

0-oracle@DBServer=> ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 1540097    root      644        52         2                       
0x00000000 1572867    root      644        16384      2                       
0x00000000 1605636    root      644        268        2                       
0x5f08cb8c 1802245    oracle    660        524320768  0          

63-oracle@DBServer=> ipcrm -m 1802245

Then to fix the ORA-00600 error we need to create a second undo tablespace after leaving out the current undo.
Please refer to metalink Note 1428786.1 to have a detailed idea about the issue. Below given are the steps I tried and which worked for me.

1. Create pfile from spfile and shutdown the database
2. Edit pfile and set undo_management=manual. 
3. Startup the DB using pfile
4. Create new undo tablespace as shown in example below
   Create undo tablespace undotbs2 datafile '<NAME>' size 100m;
5. Drop old undo tablespace
   drop tablespace undotbs1 including contents and datafiles;
6. shutdown immediate
7. Edit pfile to change undo_management=auto and change the undo_tablespace parameter to new undo tablespace, undotbs2
8. startup nomount and  create spfile from pfile 
9. startup force. 

The reason we create a new undo tablespace first is to use new undo segment numbers that are higher then the current max number to be used.

"ORA-01075: you are currently logged on" with ORA-4031

Version: 

Oracle 11g EE 11.2.0.3.0 - 64bit Production


Issue:

ORA-01075: you are currently logged on after getting ORA-4031

Cannot login to the SQLPlus as sysdba.


60-oracle@DBServer1=> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 01:04:36 2014

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

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys as sysdba
Enter password: 
ERROR:
ORA-01075: you are currently logged on


Verification:

The alert log file had so many errors related to ORA-4031

ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select file#, block#, ts# fr...","Typecheck","kgghtInit")

I was able to login as system user. Tried to flush the shared_pool, but ended up getting memory error

SQL> alter system flush shared_pool;
alter system flush shared_pool
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
pool","select t.ts#,t.file#,t.block...","sga heap(1,0)","kglsim object batch")


Solution:


We need to solve the ORA-4031 issue first. Once ORA-4031 issue is resolved, the ORA-1075 will be resolved by itself.

ORA-1075 is because of the after effect of ORA-4031. The only work around is to connect as system user and flush the shared_pool. If that is also not working, then kill the background process, restrat the DB and increase shared pool. The steps are mentioned below

Step 1. Identify and kill the DB background process

65-oracle@DBServer1=> ps -ef|grep pmon|grep orcl
oracle   22255     1  0 Mar18 ?        00:00:07 ora_pmon_orcl

kill -9 22255

Step 2. Connect to DB and start the instance

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 01:25:05 2014

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> startup

Step 3. Increase the shared pool size

Increase sga_target size or shared_pool_size parameter.

12C: EM Express for CDB/PDB/Non-CDB

EM Express:

EM express is a web based light weight tool that will help in managing Oracle 12C databases.  It offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning. To manually set up EM Express, simply configure the HTTPS or HTTP port by logging into the database and setting the port using dbms_xdb_config.sethttpsport (https) and dbms_xdb_config.sethttpport (http)

Requirement:

To setup and access EM Express for  all three types of 12C databases

1) CDB

2) PDB

3) Non-CDB

1) EM express for CDB

Get the port number on which EM express is configured.

You can find it out either from lsnrctl status or using SQL query as shown below.

lsnrctl status | grep http

...........

DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=MyHost.oracle.com)(PORT=5500))(Security=(my_wallet_directory=MyWallet_Dir_Path))(Presentation=HTTP)(Session=RAW))

OR

Login to CDB as SYS user and issue the following query

select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

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

5500

We can then access the EM express by entering following URL in the browser

https://<hostname>:5500/em

If the port is not configured, then we can do so by setting the HTTP/HTTPS port. 

SQL> exec dbms_xdb_config.sethttpsport(5500);

2) EM express for PDB

Login to PDB and then configure a port. In the example my PDB name is PDB1.

SQL> alter session set container=pdb1;

sho con_name

CON_NAME

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

PDB1

Configure the port for EM express

Here we are going to configure port number 5501 for EM express

SQL> exec dbms_xdb_config.sethttpsport(5501);

Confirm that EM has been configured on specified port (1501)

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

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

5501

Access  EM express by entering following URL in the browser:

https://<hostname>:5501/em

3) EM Express for Non-CDB

Login to non-CDB. The container name would come as Non consolidated in case of Non-CDB

SQL> conn sys/pwd as sysdba

sho con_name

CON_NAME

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

Non Consolidated

Get the port configured for EM Express with following SQL query

SQL > select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

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

5502

Access  EM express by entering following URL in the browser:

https://<hostname>:5502/em

Note:

Above method of configuring EM Express will only work if DB is registered with the default listener running on port 1521. If the DB is registered with the non-default listener then you need to add an entry for the listener in tnsnames.ora file and also set init parameters LOCAL_LISTENER (to the non default listener) and DISPATCHERS  

local_listener=<host_name>:<port no>