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 […]
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 […]
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> […]
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 […]
