Personal Contribution -- Common Success
When an impdp job fails with an error, you want to identify the failing statement
Enable tracing for the impdp job with:
SQL> ALTER SYSTEM SET EVENTS '14401 trace name errorstack level 3';
SQL> ALTER SYSTEM set MAX_DUMP_FILE_SIZE= unlimited ;
SQL> ALTER SYSTEM set TIMED_STATISTICS = true;
Re-run the impdp job.
To disable tracing:
SQL> ALTER SYSTEM SET EVENTS '14401 trace name errorstack off';
Importing data into an Oracle Database 19c with impdp fails with the errors like:
ORA-31693: Table data object <table> failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-14401: inserted partition key is outside specified partition
The table is an interval-range-partitioned table
Use the DATA_OPTIONS=DISABLE_APPEND_HINT option of impdp
Deconfiguring (roothas.sh -deconfig -force) or configuring (roothas.sh) Oracle Restart might fails with the error like:
CLSRSC-752: incorrect invocation of script roothas.pl called on a Grid Infrastructure cluster node
Died at <GI HOME>/crs/install/crsutils.pm line <line_no>
A probable cause of the issue might be the leftover checkpoint file from the previous operations:
$ORACLE_BASE/crsdata/<HOSTNAME>/crsconfig/ckptGridHA_<HOSTNAME>.xml
Remove, if exists, that file and try to deconfigure/configure again
When using Oracle Restart, and trying to add, start, stop, or check status of an Oracle Database, you get the errors below:
$ srvctl status database -db <db_unique_name>
PRCD-1024 : Failed to retrieve instance list for database <db_unique_name>
PRCR-1055 : Cluster membership check failed for node <hostname>
Most probably the cause of the error is that the HOSTING_MEMBERS attribute is set:
# $GRID_HOME/grid/bin/crsctl stat res ora.<db_unique_name>.db -f | grep HOSTING_MEMBERS
HOSTING_MEMBERS=<some_hostname>
To fix the issue, reset the attribute:
# $GRID_HOME/grid/bin/crsctl modify resource "ora.<db_unique_name>.db" -attr "HOSTING_MEMBERS=" -unsupported
After cloning Oracle Restart to another host, reconfiguration might fail with the errors:
# $GRID_HOME/grid/crs/install/roothas.sh
Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/grid/crsdata/<host>/crsconfig/roothas_2025-05-23_10-47-04AM.log
2025/05/23 10:47:06 CLSRSC-363: User ignored prerequisites during installation
2025/05/23 10:47:07 CLSRSC-762: Empty site GUID for the local site name <host>.
Died at /u01/app/19.0.0.0/grid/crs/install/crsutils.pm line 16443.
The command '/u01/app/19.0.0.0/grid/perl/bin/perl -I/u01/app/19.0.0.0/grid/perl/lib -I/u01/app/19.0.0.0/grid/crs/install -I/u01/app/19.0.0.0/grid/xag /u01/app/19.0.0.0/grid/crs/install/roothas.pl ' execution failed
The common reason for this error, that file
$GRID_HOME/grid/crs/install/crsgenconfig_params
stores references for the older host, and reconfiguration is happening on the newer host
As a solution, remove the file and try configuration again:
# rm -f $GRID_HOME/grid/crs/install/crsgenconfig_params
You want to reset RMAN configuration to its default state, quickly with one step
Use the below procedure:
SQL> exec dbms_backup_restore.resetConfig;
This will reset all RMAN configuration to their default values
You need to configure a CDB database for enabling a GoldenGate extract for replication
Perform the following actions on the source CDB database to prepare for a GoldenGate extract:
At CDB$ROOT:
CREATE USER c##ggadmin IDENTIFIED BY "<password>"
PROFILE <profile>
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP;
GRANT DBA to c##ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'C##GGADMIN', CONTAINER => 'ALL' );
alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;
alter system set STREAMS_POOL_SIZE=<#_of_extracts * 1>G scope=both;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
At PDB level:
ALTER SESSION SET CONTAINER=<pdb>;
CREATE USER ggadmin IDENTIFIED BY "<password>"
PROFILE <profile>
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP;
GRANT DBA TO ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
You need a better understanding on how Oracle database triggers work with Oracle GoldenGate replication
By default in an Oracle database, triggers do fire only when a statement executed from a user process, and do not fire in case of Oracle-maintained processes, like Logical standby SQL Apply or Oracle Streams replication processes
The property can be changed by using the procedure:
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY
When set to FALSE, triggers will fire during replication, that is, the statements issued by Replicat processes
You want to create a database link on an Autonomous Database in OCI (Oracle Cloud Infrastructure)
Use commands below to create a db link:
If db link being created in a schema other than ADMIN, grant required privileges:
SQL> grant execute on DBMS_CLOUD_ADMIN to <schema>;
SQL> grant CREATE DATABASE LINK to <schema>;
Create credential;
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => '<credential_name>',
username => '<username>',
password => '<password>'
);
END;
Create database link:
BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => '<db_link_name>',
hostname => 'target_db_host_name',
port => '<port>',
service_name => '<service_name>',
ssl_server_cert_dn => NULL,
credential_name => '<credential_name>',
directory_name => NULL,
private_target => true);
END;
While configuring openssh for cygwin, ssh key authentication does not work, the key does not get accepted
Make sure the .ssh directory and the authorized_keys files have required permissions and ownership:
chown <your_username>:none ~/
chmod g-w ~/
chmod 700 ~/.ssh/
chmod 600 ~/.ssh/authorized_keys