How to check the UTL_FILE_DIR parameter?

CREATE OR REPLACE procedure utl_file_test_write1 ( path in varchar2, filename in varchar2, firstline in varchar2, secondline in varchar2) is output_file utl_file.file_type; begin output_file := utl_file.fopen (path,filename, ‘W’); utl_file.put_line (output_file, firstline); utl_file.put_line (output_file, secondline); utl_file.fclose(output_file); –exception — when others then null; end; / begin utl_file_test_write1 ( ‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test’, ‘first line’, ‘second line’ ); end; / This […]

Troubleshooting Agent Startup and Upload

If the agent in GRID control in not uploading to the OMS – cd $AGENT_HOME/bin oracle(DATABASE)@prdu030:./emctl start agent Oracle Enterprise Manager 10g Release 4 Grid Control Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved. Starting agent ………. started. oracle(DATABASE)@prdu030:./emctl status agent Oracle Enterprise Manager 10g Release 4 Grid Control Copyright (c) 1996, […]


FLASHBACK DATABASE TO SCN Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July. Get oldest flashback time – SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG; If there are logs up until 06:30 on 31 Jul, you can flashback. Get the SCN number to flashback to – col first_change# format 99999999999 select first_change# ,to_char(first_time,’DD-MON-YYYY HH24:MI:SS’),sequence# from […]

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups – This script will be run in the database, not the catalog. Login as sysdba – This script will report on all backups – full, incremental and archivelog backups – col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time, […]

RMAN Validate Backup

The validate command for backup and restore is useful to confirm that backup and restoration is posssible and valid without actually backing up or restoring datafiles. We can validate the restore of a spfile,controlfile,archivelog or even the whole database backup. The following example enables us to specify a point in time upto which we need […]

Unix – tar and gzip commands

tar and compress a bunch of datafiles and then untar and uncompress them cd /u02/oradata/test– (area of database files ) Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup and name the file as test.tar.gz tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz – cd /u02/oradata/test_bkup > ls -lrt -rw-r–r– 1 ofsad1 […]

TSM (Tivoli Storage Manager) – Using the dsmc command

Many DBA’s have to be familiar with TSM ( Tivoli Storage Manager) commands to backup files to tape or restore backups from tape. To query backups – for example all compressed files with ‘.Z’ extension. oracle:/u05/backup/test > dsmc q b -inactive ‘*.Z’ File /u05/backup/TEST/20090702_0841.test_tables.dmp.Z To Restore the file above – dsmc restore -inactive /u05/backup/TEST/20090702_0841.ofsap_tables.dmp.Z To […]

Change DBID of database

After a clone of a database to another machine with the same database name or if a new database name is created by recreating the controlfile, there is a need to recreate the dbid especially if the new database is to be registered in the same RMAN catalog. This ensures especially if an RMAN catalog […]

ORA-19755 error using block change tracking file.

While restoring and recovering a database UNTIL TIME which uses the block change tracking file, the recovery may be interrupted. This was the error – released channel: ch1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/18/2009 09:33:29 ORA-00283: recovery session canceled due to errors RMAN-11003: […]

Compress and uncompress Files

To tar and compress files, backup the files and also untar and uncompress the same files. cd /u01/oradata/test – area of files . Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup named as test.tar.gz tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz – Check for compressed files – cd /u02/oradata/test_bkup test:/u02/oradata/test_bkup> […]

RMAN – Create Catalog

CREATING NEW RMAN CATALOG Database name to store catalog – RMAN10CAT 1. Create Tablespace for catalog owner in catalog database Login to RMAN10CAT database as sysdba SQL> create tablespace rman10 datafile ‘/u03/oradata/rman10cat/rman10.dbf’ size 200m autoextend on; Create catalog owner in Catalog database create user rman10 identified by rman default tablespace rman10 temporary tablespace temp; Issue […]

Using Log Miner

Steps to take to use logminer to check for changes made to a certain table on a certain date. 1. Restore the archive log files from backup pertaining to archivelogs generated on the day and approximate time if you are not certain of the exact time – $ rman target / catalog rman/rmann@rcat Recovery Manager: […]

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?