Monthly Archives: January 2014

ORA-39000, ORA-31641,ORA-27038


Error Description: ————————————— Data pump export fails with following error. Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production With the Partitioning, OLAP and Data Mining options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file “/backup1/d.dmp” ORA-27038: created […]

Restore SPfile from backup


How to perform a SCN based incomplete recovery


Recovery scenarios through rman


RMAN


RMAN command to create level 0 backup which is needed before running of incremental backup RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle […]

How to setup a database user to be identifed externally with OS authenticattion


Shutdown fails with ORA-24324, ORA-24323, ORA-01090


ORA-24324: service handle not initialized ORA-24323: value not allowed ORA-01090: shutdown in progress – connection is not permitted Cause: for this is that the Background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown. Solution 1. Verify […]

How to Restore the controlfile using RMAN


The following examples use Oracle Database 10g and make use of a Recovery Catalog and the Flash Recovery Area (FRA). 1. Restore controlfile from autobackup. RMAN> restore controlfile from autobackup; 2. Restore controlfile from a specific backup piece. RMAN> restore controlfile from ‘/backup_dir/piece_name’; 3. Restore controlfile from most recent available controlfile backup. RMAN> restore controlfile; […]

Drop the queues if you use advanced queuing


declare cursor active_queues is select OBJECT_NAME from user_objects where OBJECT_TYPE = ‘QUEUE’; cursor active_queue_tables is select OBJECT_NAME from user_objects where OBJECT_TYPE = ‘TABLE’; begin for v_queue in active_queues loop DBMS_AQADM.STOP_QUEUE (queue_name => v_queue.object_name); DBMS_AQADM.DROP_QUEUE (queue_name => v_queue.object_name); end loop; for v_table in active_queue_tables loop DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => v_table.object_name, force => TRUE); end loop; end; /

Script for dropping all objects


declare cursor c_get_objects is select object_type,'”‘||object_name||'”‘||decode(object_type,’TABLE’ ,’ cascade constraints’,null) obj_name from user_objects where object_type in (‘TABLE’,’VIEW’,’PACKAGE’,’SEQUENCE’,’PROCEDURE’,’FUNCTION’, ‘SYNONYM’, ‘MATERIALIZED VIEW’) order by object_type; cursor c_get_objects_type is select object_type, ‘”‘||object_name||'”‘ obj_name from user_objects where object_type in (‘TYPE’); begin for object_rec in c_get_objects loop execute immediate (‘drop ‘||object_rec.object_type||’ ‘ ||object_rec.obj_name); end loop; for object_rec in c_get_objects_type loop begin […]

ORA-01940: Cannot drop a user that is currently connected


SQL> drop user DCS_LIVE cascade; drop user DCS_LIVE cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected Solution of The Problem: Find out the connected user sid and serial# by, SQL> select sid, serial# from v$session where username = ‘DCS_LIVE’; SID SERIAL# ———- ———- 268 1268 315 1223 Before […]

Export tables using parfile only metadata having exclude parameter


expdp_parfile.par directory=data_pump_dir content=metadata_only tables=scott.emp, scott.dept exclude=statistics logfile=ocmdb_scott_tables_expdp_test.log dumpfile=ocmdb_scott_teable_test.dmp expdp_using_parfile.sh #!/bin/bash set -x dt=‘date=%y%m%d%H%M‘ export ORACLE_SID=anardb; export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1; echo ‘date’ echo $ORACLE_HOME expdp system PARFILE=expdp_parfile.par

Export schema both metadata and data


expdp_schema.sh #!/usr/bin/ksh set -x dt=‘date +%y%m%d%H%M‘ export ORACLE_SID=anardb export ORACLE_HOME=’/u01/app/oracle/product/10.2.0/db_1′ echo ‘date’ expdp system directory=DATA_PUMP_DIR schemas=scott logfile= scott_expdp_${dt}.log dumpfile=scott_${dt}.dmp

Modify rman configurations parameters


Configure default device type to disk RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK; Configure default device type to tape RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt; Configure channel device type disk RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/app/oracle/flash_recovery_area/ANARDB/DB_%U’; Configure Controlfile auto backup on and controlfile auto backup format RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> […]

Example rman_backup_script.rcv for cumulative incremental rman backup script to backup as copy


run { recover copy of database with tag ‘ INCR_DAILY_UPD’; backup incremental level 1 for recover of copy with tag ‘INCR_DAILY_UPD’ cumulative database plus archivelog; }

ORA-13717: Tuning Package License is needed for using this feature.


DECLARE * ERROR at line 1: ORA-13717: Tuning Package License is needed for using this feature. ORA-06512: at “SYS.PRVT_SMGUTIL”, line 52 ORA-06512: at “SYS.PRVT_SMGUTIL”, line 37 ORA-06512: at “SYS.DBMS_MANAGEMENT_PACKS”, line 26 ORA-06512: at “SYS.DBMS_SQLTUNE”, line 651 ORA-06512: at line 4 Solution: SQL> show parameter control_management_pack NAME                                 TYPE                             VALUE ———————————— ——————————– —————————— control_management_pack_access       string                           NONE SQL> […]

Recover of missing redlog


Simple RMAN script to RESTORE DATABASE


run { startup pfile=’/BACKUP/PROD/pfilexxxxxxx.ora’ nomount; restore controlfile from ‘/BACKUP/PROD/cntrl_xxxxxxx’; alter database mount; restore database; alter database open resetlogs; }

Simple RMAN script to take cold database backup


mkdir -p /BACKUP/PROD mkdir -p /BACKUP/PROD/log $cold_backup.sh export ORACLE_HOME=/u01/app/oracle/product/11.2.0 export ORACLE_SID=PROD export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH rman target / log=/BACKUP/PROD/log/PROD`date +%d%m%Y`.log <<EOF sql ‘alter system checkpoint’; shutdown immediate; startup mount; sql “create pfile=”/BACKUP/PROD/pfile`date +%d%m%Y`.ora” from spfile”; RUN { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/BACKUP/PROD/%U’; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/BACKUP/PROD/%U’; ALLOCATE CHANNEL […]

Recovery if all controlfiles are missing