Oracle 11gR2 Database Parameters - Reference

Ever since I did the installation of 11g in July this year, I had been longing for a web link of all the database parameters, static and dynamic views of 11g so I could look it for reference just like the 10gR2 and 9iR2, to my surprise - I finally hit it. Master index of parameters and data dictionary description in 11gR2 and a description of v$views in 11gR2 is in here.

Metric Collection Error in Grid Control

Target Name=prod.world 
Target type=Database Instance 
Host=unixhost1 
Occurred At=Aug 27, 2009 10:49:40 PM EDT 
Message=Metric evaluation error start - Target is in broken state. 
Reason - Get dynamic property error,Dynamic Category property error 
Severity=Metric Error Start 
Acknowledged=No 
Notification Rule Name=Database Availability and Critical States 
Notification Rule Owner=SYSMAN 

If you have OEM Grid Control (OEM-GC) configured on your environment, then the above is sometimes a routine alert page for a database which is a 9.2.0.6.0 being monitored by an OMS of version 10.2.0.5.0. The status for such a database in the Database sub-tab of the Targets tab on OEM-GC shows up some thing like below. The details like database name, server name etc have been shaded out for obvious reasons.
Click on the screen shot for an enlarged view of the picture.




Select the database and hit the configure button on top of the page to configure its settings to change the monitoring account from dbsnmp to the sysdba account. The below screenshots show a step by step procedure.




Change the role to SYSDBA to blank out the Monitor USername and enter 'sys' as the username its password.








Now click the Test Connection to check if the password is accepted and the connection was successful to the database. If not, check if the credentials are correct.





Click Next on top of the page and select "Skip these steps" and click Next for a review and submit it for all the changes to take effect.











The final screen shows the result of successful configuration, click OK to be routed back to the Database Target.




The above solution also fixes the issue of a 9.2.0.6.0 target database core dumping when being associated with an OMS of 10.2.0.5.0

Users recreation with quotas, privileges and roles

It is quite often in some Oracle shops to refresh databases or a specific schemas of a database using the traditional exp/imp method while retaining the same roles and privileges and other resources is a nerve racking job. Here are some scripts to make life easy for such a DBA to retain the user quotas, privileges and roles (QPR). These scripts generate the sql statements to recreate the QPRs and are named in a sequential order and are to be run in the same sequetial order. Its a known fact that there are misspellings in the script names, which were created when it was a real tizzy situation.

------------------------------
--- 01_useR_recreation.sql ---
------------------------------

set line 150
select 'create user '||username||' identified by values '||
''''||password||''''||' default tablespace '||
DEFAULT_TABLESPACE||' temporary tablespace '||
TEMPORARY_TABLESPACE||' profile '||PROFILE||';' from dba_users 
order by username;

-------------------------------------
--- 02_useR_Quota_recreateion.sql ---
-------------------------------------

set serveroutput on size 1000000 
set line 150
declare
a number;
cursor q is select username,max_bytes,tablespace_name 
from dba_ts_quotas order by username;
begin
for i in q
loop
if i.max_bytes=-1 then
dbms_output.put_line('alter user '||i.username||
' quota unlimited'||' on '||i.tablespace_name||';');
else
dbms_output.put_line('alter user '||i.username||
' quota '||i.max_bytes||' on '||i.tablespace_name||';');
end if;
end loop;
end;
/


---------------------------------------
--- 03_useR_sys_priv_recreation.sql ---
---------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users 
order by 1;
cursor priv (user_nm in varchar2) is select 
distinct(grantee),privilege from dba_sys_privs where 
grantee=user_nm and grantee not in 
('CONNECT','SYSTEM','RESOURCE','SYS','EXP_FULL_DATABASE',
'TSMSYS','RECOVERY_CATALOG_OWNER','SCHEDULER_ADMIN',
'AQ_ADMINISTRATOR_ROLE','DIP','OEM_ADVISOR','DBA',
'IMP_FULL_DATABASE','OEM_MONITOR','DBSNMP','OUTLN') 
order by grantee;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in priv(i.username)
loop
dbms_output.put_line('grant '||j.privilege||' to '
||i.username||';');
end loop;
end loop;
end;
/


------------------------------------
--- 04_uSer_role_recreateion.sql ---
------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users order 
by 1;
cursor role (user_nm in varchar2) is select distinct 
GRANTEE,granted_role from dba_role_privs where grantee 
not in ('DBSNMP','DIP','OUTLN','SYS','SYSTEM','TSMSYS',
'EXP_FULL_DATABASE','DBA','SELECT_CATALOG_ROLE',
'IMP_FULL_DATABASE','EXECUTE_CATALOG_ROLE',
'LOGSTDBY_ADMINISTRATOR') and grantee=user_nm order by 
grantee;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in role(i.username)
loop
dbms_output.put_line('grant '||j.granted_role||' to '
||i.username||';');
end loop;
end loop;
end;
/


-----------------------------------------
--- 05_user_tab_privs_recreateion.sql ---
-----------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users 
order by 1;
cursor tab_priv (user_nm in varchar2) is select 
distinct(grantee),owner,table_name,privilege
from dba_tab_privs where grantee not in ('SYSTEM',
'PUBLIC','SELECT_CATALOG_ROLE','GATHER_SYSTEM_STATISTICS',
'EXP_FULL_DATABASE','SYS','EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE','LOGSTDBY_ADMINISTRATOR',
'AQ_USER_ROLE','AQ_ADMINISTRATOR_ROLE','HS_ADMIN_ROLE',
'DBA','IMP_FULL_DATABASE','OEM_MONITOR','DBSNMP',
'OUTLN') and grantee=user_nm order by grantee,owner;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in tab_priv(i.username)
loop
dbms_output.put_line('grant '||j.privilege||' on '||j.owner
||'.'||j.table_name||' to '||i.username||';');
end loop;
end loop;
end;
/

Here is a bonus script to only recreate the passwords of users in a database.

----------------------------------------
--- user_old_password_recreation.sql ---
----------------------------------------

set pages 0
set line 100

select 'alter user '||username||' identified by values '||
''''||password||''''||';' from dba_users order by username;

PS: Please format the lines of code once they have been pasted in your editor as the lines may have been broken down to the next ones while publishing the scripts in a web page