RMAN Restore errors RMAN-03002,RMAN-10015, RMAN-10014

If you got this error below while trying to restore you database,

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03010: fatal error during library cache pre-loading
RMAN-03099: job cancelled at user request



 re-compile irman binaries as follows:


servername:oraotm:/home/oraotm/rman> cd $ORACLE_HOME/rdbms/lib
servername:oraotm:/dbotm/oracle/products/11.2/rdbms/lib> make -f ins_rdbms.mk irman


Best Regards,
Paulo Portugal

Examplos of enabling/disabling TRACE in Oracle sessions

I prefer oradebug but as you can see below there are a lot of ways to enable and disable trace in sessions:

--Get the SPID from v$session and use it below
SQL> oradebug setospid 17039468
SQL> oradebug event 10046 trace name context forever, level 12; --Here you enable
SQL> --Wait for 10 Mins
SQL> oradebug event 10046 trace name context off ; --Here you disable


-- In your session
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL> EXEC DBMS_MONITOR.session_trace_enable;

SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);

SQL> EXEC DBMS_MONITOR.session_trace_disable;

--In another session or service /module

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>253, serial_num=>45525, waits=>TRUE, binds=>TRUE);

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);

SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'client_id_test');

SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'client_id_test', waits=>TRUE, binds=>FALSE);

SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'client_id_test');

SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');

SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running', -

> waits=>TRUE, binds=>FALSE);

SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');

Best Regards,

Paulo Portugal

sqlplus / as sysdba Error 5 initializing SQL*Plus NLS initialization error

If you are getting this error after a clone of your binary files, you can fix it using this script below:

$ORACLE_HOME/install/changePerm.sh


If it didn't work, clone he binary again and test.


Best Regards,
Paulo Portugal 

Creating a simple RMAN Backup Script to clone an instance to another environment (TEST, HOMOLOG, etc)

You have many ways to clone your database. This example is a simple backup that I used to use when I want to clone a database to a test environment (I prefer using physical standby method or the 11g from ACTIVE that you don't need a backup).

See these example scripts below:


vi backup_for_HML.sh
rman target / nocatalog @/backup/SIGE_BKP/scripts/BKP_for_HML.rcv log /backup/SIGE_BKP/scripts/log/BKP_for_HML.log "append"

vi backup_for_HML.rcv
run {
  allocate channel dsk1 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk2 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk3 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk4 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk5 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk6 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk7 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';
  allocate channel dsk8 device type disk format '/backup/SIGE_BKP/bkp/BKP_for_HML_SIGE_%d_%U.bkp';

  backup as  backupset full database plus archivelog;
  sql 'alter system switch logfile';
  sql 'alter system checkpoint';
  sql 'alter system switch logfile';
  sql 'alter system switch logfile';
  backup archivelog all format '/backup/SIGE_BKP/bkp/bkp_archives_for_HML.bkp';
  backup current controlfile formta '/backup/SIGE_BKP/bkp/ctl_for_HML.ctl';

  release channel dsk1;
  release channel dsk2;
  release channel dsk3;
  release channel dsk4;
  release channel dsk5;
  release channel dsk6;
  release channel dsk7;
  release channel dsk8;
}

################################################################
Now you run your backup:

nohup sh backup_for_HML.sh > backup_for_HML.out &




Best Regards,
Paulo Portugal

ASM Best Practice - Add and drop ASM Disks in one command - Solving ORA-15032 and ORA-15054

If you are planing to change some discs in your disk group, the best planing is below:

1-Before definitively adding new disks to your ASM disk group in production, test these disks create a new disk group with them (It's good to check them with KFOD utility)

2-Create a tablespace on this new disk group

3-Create a table on this tablespace and do some DML. Take a look at all your alerts log files (ASM and RDBMS instances)

4-If everything is ok, go ahead. Drop table, tablespace and disk group that you created for test purpose.

5-Finnaly, add new disks and drop old ones with this procedure below:


5.1 Set power limit to 0:
SQL> alter system set asm_power_limit=0 scope=memory sid='*';

5.2 Add disks and drop old ones like below:

SQL> alter diskgroup DG_EBS_DATA add disk '/dev/rhdisk157','/dev/rhdisk158','/dev/rhdisk159','/dev/rhdisk160','/dev/rhdisk161' drop disk 'DG_EBS_DATA_0002','DG_EBS_DATA_0003','DG_EBS_DATA_0004','DG_EBS_DATA_0005','DG_EBS_DATA_0006';


PS: Pay attention to the dropping disks. They are the name os the disk in dictionary of ASM and NOT the path in OS. If you try to drop using the path you will get errors like below:

01:01:54 SQL> alter diskgroup DG_EBS_DATA add disk '/dev/rhdisk157','/dev/rhdisk158','/dev/rhdisk159','/dev/rhdisk160','/dev/rhdisk161' drop disk '/dev/rhdisk12','/dev/rhdisk13','/dev/rhdisk14','/dev/rhdisk15','/dev/rhdisk16' rebalance power 10;
alter diskgroup DG_EBS_DATA add disk '/dev/rhdisk157','/dev/rhdisk158','/dev/rhdisk159','/dev/rhdisk160','/dev/rhdisk161'       drop disk '/dev/rhdisk12','/dev/rhdisk13','/dev/rhdisk14','/dev/rhdisk15','/dev/rhdisk16' rebalance power 10
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15054: disk "/DEV/RHDISK16" does not exist in diskgroup "DG_EBS_DATA"
ORA-15054: disk "/DEV/RHDISK15" does not exist in diskgroup "DG_EBS_DATA"
ORA-15054: disk "/DEV/RHDISK14" does not exist in diskgroup "DG_EBS_DATA"
ORA-15054: disk "/DEV/RHDISK13" does not exist in diskgroup "DG_EBS_DATA"
ORA-15054: disk "/DEV/RHDISK12" does not exist in diskgroup "DG_EBS_DATA"


Elapsed: 00:00:00.74
01:01:58 SQL>



Best Regards,
Paulo Portugal

How to Clean Up ALL Backups from your control file

This command is simple and can help when you are trying to restore a database on another server.

Best Regards,
Paulo Portugal

RMAN> run{

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

delete noprompt backup;

 };

Drop Diskgroup ORA-02000

Solution:

Use FORCE INCLUDING CONTENTS keywords as below:

SQL> drop diskgroup dg_otm_data force including contents;

Diskgroup dropped.

SQL>



Best Regards,
Paulo Portugal

Copying files from one ASM Instance to another ASM Instance using CP

On this example below, I'm copying some backup files from one Diskgroup in ASM instance from one cluster ware to another. Doing like that you can put many commands in a .sh file and use NOHUP.

Example:

asmcmd cp +DG_XXX_BACKUP/XXXPR/BACKUPSET/2014_02_08/nnndn0_BKP_FULL_DISCO_0.278.838976253 sys/XXXX@10.1.171.252.+ASM1:+DG_OTM_DATA/nnndn0_BKP_FULL_DISCO_0.278


As simple as that.

Best Regards,
Paulo Portugal

Checking patch Conflicts OPATCH

This command below is used to check , before applying the patch, if you will find any conflict with others patches already on your system.

cd XXXXX --> (PATCH DIRECTORY)

bwdh0309:oracle:/home/oracle/14275605/14275605> opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /opt/oracle/products/11.2.0/grid
Central Inventory : /opt/oracle/products/11.2.0/oraInventory
   from           : /opt/oracle/products/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /opt/oracle/products/11.2.0/grid/cfgtoollogs/opatch/opatch2014-02-08_10-46-39AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
bwdh0309:oracle:/home/oracle/14275605/14275605>

Best Regards,
Paulo Portugal

Check and Disable SQL Profile in Oracle

1-Check enabled profiles

select NAME,CREATED,STATUS from dba_sql_profiles;

2-Disable the required profile

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('coe_7by859jup2hvt_1730647264','STATUS','DISABLED');

Best Regards,
Paulo Portugal