Privileges Consideration when Refresh Oracle Schema

When DBA refreshes an Oracle schema or objects, privileges should be taken care carefully , specially for long time running complex environment.

After dropping a schema, all the granted privileges will be lost. So before refresh, DBA should record all the privileges granted to users, roles, etc.

Export a Schema from Source Database

$ expdp Username/Password directory=  dumpfile= logfile= SCHEMAS= FLASHBACK_TIME=systimestamp

Record / Extract the Granted Privileges

Granted Column Privileges

SQL> select * from dba_col_privs where GRANTOR='SCHEMANAME';

Here ‘SCHEMANAME’ is the schema to be refreshed.

Granted Objects Privileges

 SQL>select GRANTEE,OWNER||'.'||TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE from dba_tab_privs where GRANTOR ='SCHEMANAME';

Other privileges you may be interested from following views:

  • User_role_privs, dba_role_privs ( Users granted with with roles )
  • User_sys_privs, dba_sys_privs
  • Role_role_privs, role_sys_privs, role_tab_privs
  • Session_privs, session_roles

Refresh Schema

Drop Schema Objects

 SQL> select 'drop '||object_type|| ' '||owner||'.'||object_name ||' ;' from dba_objects where owner='SCHEMANAME';

Import Schema by Data Pump

$ impdp username/password directory= dumpfile= logfile= schemas=sourceschemaname remap_schema= remap_tablespace=

Fix Invalid Objects

Fix all invalid objects, check all synonyms, etc

Collect Stats

SQL>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'SCHEMANAME');

Column Privileges in Oracle

Table column privileges include only INSERT,UPDATE without SELECT unfortunately.

How to Grant Column Privileges ?

SQL> GRANT update (columnname1),insert (columnname2, columnname3)  ON user1.table1 TO user2;

How to Check Column Privileges ?

SQL> select * from dba_col_privs ;

How to Remove Column Privileges ?

SQL> revoke insert (columnname2, columnname3) ON user1.table1 from user2;
revoke insert (columnname2, columnname3) ON user1.table1 from user2;
              *
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column

SQL> revoke insert  on  user1.table1 from user2;
Revoke succeeded.

Upgrade RMAN Catalog Fails with “RMAN-01005: Error while converting X lock to S lock”

The following errors occur while upgrading RMAN catalog from version 19.10 to version 19.16:

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;


recovery catalog upgraded to version 19.16.00.00.00
DBMS_RCVMAN package upgraded to version 19.16.00.00
DBMS_RCVCAT package upgraded to version 19.16.00.00.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-01005: Error while converting X lock to S lock

INVESTIGATION

Check the RMAN debug trace file with following messages:

...
..
.
DBGSQL: RCVCAT> declare ret integer; str varchar2(120); begin str := 'begin :ret := '||dbms_catowner||'.get_lock_on_catalog(dbms_lock.S_MODE); end;'; EXECUTE IMMEDIATE str USING OUT ret; :retlock := ret; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :retlock = NULL
DBGSQL: error: ORA-06550: line 1, column 50: (krmkosqlerr)
DBGSQL: PLS-00201: identifier 'DBMS_LOCK' must be declared (krmkosqlerr)
DBGSQL: ORA-06550: line 1, column 9: (krmkosqlerr)
DBGSQL: PL/SQL: Statement ignored (krmkosqlerr)
DBGSQL: ORA-06512: at line 1 (krmkosqlerr)
...
..
.

SOLUTION

Grant execute privilege to RMAN user, here we assume it is ‘rman’ user used in most environment.

SQL> grant execute on DBMS_LOCK to rman;

Grant succeeded.
RMAN>  UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>  UPGRADE CATALOG;

recovery catalog upgraded to version 19.16.00.00.00
DBMS_RCVMAN package upgraded to version 19.16.00.00
DBMS_RCVCAT package upgraded to version 19.16.00.00.

ORA-00600: internal error code, arguments: [17287]

ORA-00600 error occurs while startup 19c RAC database in ExaCC:

ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [17287], [0x7FA1EDB80130],
[0x22F3A9C18], [], [GSMADMIN_INTERNAL], [DBMS_GSM_CLOUDADMIN], [11], [1], [],
[], [], []
Process ID: 335484
Session ID: 2850 Serial number: 17388

INCIDENT TRACE FILE with CALL STACK

...
..
.
----- Incident Context Dump -----
Address: 0x7fa1f83fa060
Incident ID: 197141
Problem Key: ORA 600 [ORA-00600: internal error code, arguments: [17287], [0x7FA1EDB80130], [0x22F3A9C18], [], [GSMADMIN_INTERNAL], [DBMS_GS]
Error: ORA-600 [ORA-00600: internal error code, arguments: [17287], [0x7FA1EDB80130], [0x22F3A9C18], [], [GSMADMIN_INTERNAL], [DBMS_GSM_CLOUDADMIN], [11], [1], [], [], [], []
] [] [] [] [] [] [] [] [] [] [] []
[00]: dbgexProcessError [diag_dde]
[01]: dbgePostErrorKGE [diag_dde]
[02]: dbkePostKGE_kgsf [rdbms_dde]
[03]: kgeade []
[04]: kgeseml []
[05]: kgesem []
[06]: OCIKSEC []<-- Signaling
[07]: gwm_refresh_params []
[08]: gwm_init []
[09]: gwm_notifier []
[10]: gwm_pdb_notifier []
[11]: kscdnfy [VOS]
[12]: kpdbaSwitchOpenClose [PROGINT]
[13]: kpdbSwitchRunAsSysCbk [PROGINT]
[14]: rpiswu2 [RPI]
[15]: kpdbSwitch [PROGINT]
[16]: kpdbaOpenPdb [PROGINT]
[17]: kpdbaOpenClose [PROGINT]
[18]: kpdbaSwitchStateFromRoot [PROGINT]
[19]: kpdbRestorePdbStates_int [PROGINT]
[20]: kpdbRestorePdbStates [PROGINT]
[21]: kpdbCDBOpen [PROGINT]
[22]: adbdrv_options []
[23]: opiexe [Time_Limit]
[24]: opiosq0 [OPI]
[25]: kpooprx [PROGINT_MISC]
[26]: kpoal8 [PROGINT_MISC]
[27]: opiodr [OPI]
[28]: ttcpip []
[29]: opitsk [OPI]
[30]: opiino [OPI]
[31]: opiodr [OPI]
[32]: opidrv [OPI]
[33]: sou2o []
[34]: opimai_real [OPI]
[35]: ssthrdmain []
[36]: main []
...
..
.

CAUSE

User has run a couple of SQL scripts to have revoked some privileges from PUBLIC for security compliance purpose. Hence there are many invalid objects for SYS, PUBLIC, GSMADMIN_INTERNAL, etc.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

How to Create SQL Plan Baseline from AWR

In another post How to Create SQL Plan Baseline from Cursor Cache. Here we will demonstrate how to create SQL plan baseline from AWR.

By using the SQL in Find Oracle SQL Execution History Details, we get the best execution plan for sql_id=’6fqb4wf9sgfyv’ and plan_hash_value= 815656277.

Create STS (  SQL Tuning Set )

 BEGIN  
  DBMS_SQLTUNE.CREATE_SQLSET (  
     sqlset_name  => 'STS_6fqb4wf9sgfyv',  
     description  => 'SQL Tuning Set for 6fqb4wf9sgfyv');  
 END;  

Load the Good Plan into STS

DECLARE  
  cur sys_refcursor;  
 BEGIN  
  OPEN cur FOR  
   SELECT VALUE(P)  
   FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>101300,end_snap=>101304,basic_filter=>'sql_id = ''6fqb4wf9sgfyv''',attribute_list=>'ALL')  
        ) p;  
    DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_6fqb4wf9sgfyv', populate_cursor=>cur);  
  CLOSE cur;  
 END;  

  /
PL/SQL procedure successfully completed.

Check the Good SQL Loaded into STS

SQL>  select OWNER,NAME,DESCRIPTION,STATEMENT_COUNT from DBA_SQLSET where OWNER='SYS' and NAME='STS_6fqb4wf9sgfyv';

OWNER                          NAME
------------------------------ ------------------------------
DESCRIPTION                                                                                                                                                                                                                                  STATEMENT_COUNT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
SYS                            STS_6fqb4wf9sgfyv
SQL Tuning Set for 6fqb4wf9sgfyv                                                                                                                                                                                                                   1
SQL> select * from table(dbms_xplan.display_sqlset('STS_6fqb4wf9sgfyv','6fqb4wf9sgfyv'));
...
..
.

Load the Plan From STS

SQL> DECLARE
 good_plans pls_integer;
 BEGIN
  good_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => 'STS_6fqb4wf9sgfyv',
   basic_filter=>'plan_hash_value = ''815656277'''
   );
 END;   

 /

PL/SQL procedure successfully completed.

Check the Plan Loaded Successfully

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,REPRODUCED,AUTOPURGE,OPTIMIZER_COST , EXECUTIONS,ELAPSED_TIME,CPU_TIME from DBA_SQL_PLAN_BASELINES where SQL_TEXT like 'SELECT O.ORDER_ID, O.ORDER_RE%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX REP AUT OPTIMIZER_COST EXECUTIONS ELAPSED_TIME   CPU_TIME
------------------------------ ------------------------------ --- --- --- --- --- -------------- ---------- ------------ ----------
SQL_4e7889c88773e98f           SQL_PLAN_4wy49t23r7ucgbe7808e2 YES YES NO  YES YES         229777          1     61311779   53542413
SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_4wy49t23r7ucgbe7808e2'));
SQL>  SELECT * FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_4e7889c88773e98f'));