Granting Access to run AWR/ADDM reports to a non priv user

In our environment many developers like/need(has knowledge) to look at the awr report to analyze and tune their problamatic queries and also understand the load details and wait events the database is facing. They request for privileges to run AWR/ADDM reports.

So see below simple steps we do to achieve this:

SQL>  create user awraddm identified by whatever 
2  default tablespace users
3  temporary tablespace temp;

User created.

SQL> grant connect, resource, advisor to awraddm;

Grant succeeded.

SQL> grant select any dictionary to awraddm;

Grant succeeded.

SQL> grant advisor to awraddm;

Grant succeeded.

SQL> grant execute on dbms_workload_repository to awraddm;

Grant succeeded.

…..And thats it…the awraddm user can now run awrrpt.sql and addmrpt.sql

Enable Archivelog Mode in a RAC database

Hi All. Its been a long time since I posted so thought i’d better start again :-)

This is a pretty trivial thing but I always forget the syntax hence thought i’d post it for the benefit of others and myself

1. Change the cluster database parameter so the database can be mounted in exclusive mode which is required to enable archive logging

alter system set cluster_database=false scope=spfile;

2. Shutdown the database using srvctl

srvctl stop database -d ORCL

3. Startup one of the instances upto the mount state

sqlplus / as sysdba

startup mount

4. Enable archivelog mode

alter database archivelog;

5. Change the cluster_database parameter back to true in the spfile

alter system set cluster_database=true scope=spfile;

6. Shutdown the instance

shutdown immediate

7. Startup the database using srvctl

srvctl start database -d ORCL

8. Once the database is back up you can verify the change by connecting to one of the database instances

sqlplus / as sysdba

archive log list

For example:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archlogs/ORCL/
Oldest online log sequence     111
Next log sequence to archive   113
Current log sequence           113