Posts

Recreate/drop Oracle database link for another schema/user

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;

Dynamically gather DDL for Oracle database tablespaces

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 for 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','UNDOTBS2','TOOLS…

GoldenGate - PLW-06009: procedure "XXX" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

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   Error ---------- ----------------------------------------------------------------…

Upgrade from 11gR2 RAC to 12cR1 RAC using cloning methodology

12.1.0.2 Grid Infrastructure Upgrade: From 11.2.0.4 To 12.1.0.2 Using GI Clone

I used the following methodology to upgrade from 11.2.0.4 to 12.1.0.2 RAC.  This is particularly useful from a time perspective to avoid having to do a separate patching when it comes to applying bundle patches (Exadata) or PSUs.  I tested this process in my non-production environment and highly advise you to do the same to ensure the process is seamless.


A) Prepare SOURCE GI home (In this case it is 12.1.0.2) to be cloned:
********************************
Follow documentation for "Preparing the Oracle Grid Infrastructure Home for Cloning" at https://docs.oracle.com/database/121/CWADD/clonecluster.htm#CWADD92116

Oracle® Clusterware Administration and Deployment Guide
12c Release 1 (12.1)
Part Number E16794-16
********************************


A01) Stop all databases via srvctl (run by oracle)

srvctl stop database -d {db_unique_name}

A02) Stop CRS (run by root)

export ORACLE_HOME=/u01/app/12.1.0.2/gr…

Recover from datafile block corruption leveraging physical standby

Here are the steps I used to recover my primary database by leveraging a Dataguard environment.

## on standby

1. stop managed recovery on standby.

SQL> alter database recover managed standby database cancel;

2. Backup as a copy of the datafile from standby.

RMAN> backup as copy datafile 2 format '/app/oracle/temp/sysaux_01.dbf';

3. SCP the backup copy of the datafile to the primary database server.

scp /app/oracle/temp/sysaux_01.dbf <target_server>:/app/oracle/temp


## on primary

4. Take corrupt datafile offline.

SQL> alter database datafile 2 offline;

5. Catalog datafile to the primary database so the controlfile knows about it.

RMAN> catalog datafilecopy '/app/oracle/temp/sysaux_01.dbf';

6. Restore and recover the datafile in question.

RMAN> restore datafile 2;

RMAN> recover datafile 2;

7. Bring the datafile back online.

SQL> alter database datafile 2 online;

8. Validate that the corruption is no longer present.

RMAN> validate check logical datafi…

RMAN-05001: auxiliary file name conflicts with a file used by the target database

RMAN-05001: auxiliary file name <file_name> conflicts with a file used by the target database
I ran into an issue where I was trying to clone a new copy of non-production database (using production database as the source) by leveraging the RMAN clone via active database to save me some time and work.  

I ran into the error where I couldn't successfully clone the database.  I made sure that my environment, pfile, RMAN script were configured correctly.  After much time of troubleshooting, I figured out the issue. After remembering that the source database had a DG environment, it occurred to me that was what was causing the error.  Reason being, the source had log_file_name_convert and db_file_name_convert configured which was being "carried over" onto my auxiliary instance hence causing the error mentioned above.  So all I needed to do was simply reset those two parameters within my RMAN script (as seen below) and I was able to successfully clone the database without…

Apply PSU to OEM 12cR4

Below are the steps I followed for patching OMS for OEM 12c R4.  I came across an issue with creating my property file.  I give an example of what it should look like upon completion.

Creating the property file : 

1. Run the following script to create the WebLogic encrypted configuration and key files.


mkdir /app/oracle/Middleware/oms/oms_encr<OMS_HOME>/OPatch/wlskeys/createkeys.sh –oh <full path of platform OMS Oracle Home> -location <location to put the encrypted files>e.g. $OMS_HOME/OPatch/wlskeys/createkeys.sh -oh $OMS_HOME -location /app/oracle/Middleware/oms/oms_encrPlease enter weblogic admin server username: weblogic Please enter weblogic admin server password: Creating the key file can reduce the security of your system if it is not kept in a secured location after it is created. Creating new key... Trying to get configuration and key files for the given inputs... This operation will take some time. Please wait for updates... User configuration file created: /…