Posts

RMAN-10038: database session for channel prm3 terminated unexpectedly

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/testdb/datafile/welldoc…

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…