Author: dbalifeeasy

Process RSM0, PID = 23456, will be killed

Standby database DG log:

11/09/2022 11:53:34
Creating process RSM0
11/09/2022 11:53:37
Process RSM0 re-created with PID = 20918
11/09/2022 11:59:37
Process RSM0, PID = 20918, will be killed
11/09/2022 11:59:51

Primary database DG log:

Data Guard Broker Status Summary:
Type Name Severity Status
Configuration DG_CDRDB Warning ORA-16607
Primary Database PCDRDB Error ORA-16778
Physical Standby database SCDRDB Error ORA-16810

SOLUTION

  1. Check and resolve network issues between primary db server and standby server.
  2. Increase the OperationTimeout property value :
DGMGRL> show configuration OperationTimeout;
  OperationTimeout = '30'
 
DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=90;
Property "operationtimeout" updated

DGMGRL>  show configuration OperationTimeout;
  OperationTimeout = '90'

Oracle Goldengate : start replicat ATCSN or AFTERCSN ?

There is a following question on the web with different answers:

You must synchronize the starting point for Replicat with the restore point of the target database. How should you accomplish this?

A. Use the command option AFTERCSN.
B. Use the command option ATCSN.
C. Use the command option UNTIL CANCEL.
D. Use the command option RESUME.

There are more than one correct answer, i think.

A is correct, if datapump has been used to instantiate the target database. expdp performed consistency as of FLASHBACK_SCN included.

B is correct, if RMAN has been used to instantiate the target database. Oracle RMAN restore or recovery using UNTIL SCN which specifies an SCN as an upper limit  but not including the specified SCN.

For Oracle GoldenGate 12.2 and above, neither AFTERCSN nor ATCSN is required anymore, if “DBOPTIONS ENABLE_INSTANTIATION_FILTERING” is enabled in replicat parameter file.

Reference : Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database
Version 12c
Document ID 1276058.1

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.