DBA Maestro - Helpful DBA tips

Problem: I had an issue where I was trying to build a 1.7 TB Data Guard environment and kept receiving the highlighted error below.  I thought it was some type of fluke so I restarted the job 2 more times only to receive the same error. Fix: After researching the issue, I found it was because the sqlnet.ora needed to be configured correctly.  I added the entry " SQLNET.EXPIRE_TIME = 10 " to the sqlnet.ora on both the source and the target database servers under the RDBMS $ORACLE_HOME/network/admin.  Upon restarting the RMAN active database standby build, it was able to move past this error and built successfully. Starting backup at 07-MAY-17 channel prm1: starting datafile copy input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.262.940412881 channel prm2: starting datafile copy input datafile file number=00129 name=+DATA/testdb/datafile/welldoc1.390.940509955 channel prm3: starting datafile copy input datafile file number=00130 name=+DATA/test

Have you ever needed to recreate/drop a database link for another user/schema owner in an Oracle database?  Here's how I did it.  --  recreate Database link CREATE PROCEDURE schema_owner.create_db_lnk AS BEGIN     EXECUTE IMMEDIATE 'CREATE DATABASE LINK COGNOS.WORLD '             ||'CONNECT TO CRUD IDENTIFIED BY "password123" '             ||'USING ''(description=(address=(protocol=tcp)(host=host-scan)(port=1521))(connect_data=(service_name=test.artisdba.com)))'''; END create_db_lnk; / exec schema_owner.create_db_lnk; drop procedure schema_owner.create_db_lnk; --  Drop database link CREATE PROCEDURE schema_owner.drop_db_lnk AS BEGIN      EXECUTE IMMEDIATE 'DROP DATABASE LINK COGNOS.WORLD'; END drp_db_lnk; / exec schema_owner.drop_db_lnk; drop procedure schema_owner.drop_db_lnk;

Here is a script that I use to dynamically generate the SQL to recreate tablespaces in another database.  I essentially want all tablespaces that are non-system related.  You can model this script by whatever you need to use the package DBMS_METADATA.GET_DDL in your DBA life. -- @gen_ddl_tbsp.sql spool tbsp_ddl_gen.sql set lines 120 head off feed off pages 0 long  1000000 select 'set lines 120 pages 0 long 1000000' from dual; select 'spool tbsp_ddl.sql' from dual; select distinct 'SELECT DBMS_METADATA.GET_DDL(' || '''' || 'TABLESPACE' || '''' || ',' || '''' || tablespace_name || '''' ||') || ' ||'''' ||' /' ||'''' ||' FROM DUAL;' from dba_tablespaces where tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','USERS','SYSAUX','UNDOTBS1','UN

I was in the middle of configuring OGG for a client and came across an issue when getting to the portion of installing support for sequences.  This script creates some procedures to be used by OGG processes.   I hope this helps someone as it took me some digging around to figure this one out.  Enjoy! SQL> @sequence Please enter the name of a schema for the GoldenGate database objects: GGADMIN Setting schema name to GGADMIN SP2-0804: Procedure created with compilation warnings SP2-0804: Procedure created with compilation warnings UPDATE_SEQUENCE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- 58/14      PLW-06009: procedure "UPDATESEQUENCE" OTHERS handler does not end            in RAISE or RAISE_APPLICATION_ERROR GETSEQFLUSH Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors SEQTRACE Line/pos   Er