Windows Oracle Batch Files

Category : Home > Databases > Oracle

Scripts to backup Oracle database on Windows NT

Here's a set of scripts to do database export, full database backup (putting tablespaces in

backup mode, not using RMAN) and scan the backup logs on Windows NT. The datafiles are first

copied to another filesystem using ocopy and then backuped with ntbackup. You can also see

some examples of using DOS variables in sqlplus. These scripts are examples only, obviously

you would have to adjust them for your environment.

REM ==========================================================================

REM Script# 1

REM This is the main backup script to run with Windows Scheduler / at command

REM ==========================================================================

set dbconn=system/manager@SAMPLEDB

set admdir=c:\admin\daily\

set logdir=c:\admin\daily\out\

set oradir1=c:\oracle\oradata\SAMPLEDB\

set oradir2=d:\oracle\oradata\SAMPLEDB\

set oradir3=e:\oracle\oradata\SAMPLEDB\

set orabckdir=e:\backup\SAMPLEDB\

set orabckdir1=e:\backup\SAMPLEDB\c\

set orabckdir2=e:\backup\SAMPLEDB\d\

set orabckdir3=e:\backup\SAMPLEDB\e\

REM

REM save previous copies of backup logs

REM

del %logdir%backup2.txt

ren %logdir%backup1.txt backup2.txt

ren %logdir%backup.txt backup1.txt

echo "SAMPLEHOST overnight.bat backup commenced ..." >>%logdir%backup.txt

date /t >>%logdir%backup.txt

time /t >>%logdir%backup.txt

REM

REM Run full export of the database

REM

echo "Exporting SAMPLEDB" ... >> %logdir%backup.txt

d:\oracle\ora81\bin\exp.exe parfile=d:\oracle\admin\SAMPLEDB\exp\SAMPLEDBexp.par >>

%logdir%backup.txt

REM

REM Compress the export file with winzip command line utility

REM May also put a line to remove the old dump files here

REM

c:\progra~1\winzip\wzzip.exe e:\oracle\oradata\SAMPLEDB\export\SAMPLEDB_full.zip

e:\oracle\oradata\SAMPLEDB\export\SAMPLEDB_full.dmp

REM

REM Append the database export log to backup.txt

REM

type d:\oracle\admin\SAMPLEDB\exp\logs\SAMPLEDB_full.log >> %logdir%backup.txt

echo "Finished export of SAMPLEDB" >> %logdir%backup.txt

echo "Generating sql scripts for hot SAMPLEDB backup ..." >> %logdir%backup.txt

sqlplus -s %dbconn% @%admdir%sql\backup.sql >> %logdir%backup.txt

echo "Putting SAMPLEDB in backup mode ..." >> %logdir%backup.txt

REM

REM Put all tablespaces in backup mode

REM

sqlplus -s %dbconn% @%admdir%sql\BEGIN_SAMPLEDB_BACKUP.sql >> %logdir%backup.txt

REM

REM Save the database files' location in SAMPLEDB_files.txt

REM

dir %oradir1% > %orabckdir%SAMPLEDB_files.txt

dir %oradir2% >> %orabckdir%SAMPLEDB_files.txt

dir %oradir3% >> %orabckdir%SAMPLEDB_files.txt

REM

REM File listing from sqlplus

REM

sqlplus -s %dbconn% @%admdir%sql\dbfiles.sql >> %orabckdir%SAMPLEDB_files.txt

REM

REM Copy Oracle files to backup directory on disk

REM

D:\oracle\ora81\bin\ocopy %oradir1%* %orabckdir1% >> %logdir%backup.txt

D:\oracle\ora81\bin\ocopy %oradir2%* %orabckdir2% >> %logdir%backup.txt

D:\oracle\ora81\bin\ocopy %oradir3%* %orabckdir3% >> %logdir%backup.txt

REM

REM backup filesystems

REM

REM D: drive is backed up last because it has the archived redo logs

REM

ntbackup backup c:\ e:\ d:\ /v /e /b /hc:on /d "SAMPLEHOST Overnight backup" /t normal /l

"%logdir%backup.txt"

ntbackup eject

date /T >> %logdir%backup.txt

time /T >> %logdir%backup.txt

echo "Putting SAMPLEDB in normal mode" >> %logdir%backup.txt

sqlplus -s %dbconn% @%admdir%sql\END_SAMPLEDB_BACKUP.sql >> %logdir%backup.txt

echo "SAMPLEHOST backup finished ..." >>%logdir%backup.txt

date /t >>%logdir%backup.txt

time /t >>%logdir%backup.txt

REM

REM Scan the logs for errors and produce the summary log file

REM

%admdir%SearchForErr.Bat %logdir%backup.txt

REM ==========================================================================

REM Script# 2

REM Script to sumbit the backup job using at command

REM ==========================================================================

@echo off

at \\SAMPLEHOST 23:30 /every:M,T,W,Th,F c:\admin\daily\overnight.bat

REM ==========================================================================

REM Script# 3

REM clean_archive.bat

REM Remove Oracle archived logs older than seven days

REM

REM NOTE: you need nt resource kit for this script to work

REM

REM ==========================================================================

C:\ntreskit\forfiles -pE:\oracle\oradata\SAMPLEDB\archive -d-7 -c"CMD /C del @FILE"

REM ==========================================================================

REM Script# 4

REM zip_archive.bat

REM Compress archived logs older than 2 days

REM

REM ==========================================================================

C:\ntreskit\forfiles -pD:\oracle\oradata\SAMPLEDB\archive -d-2 -c"CMD /C

c:\progra~1\winzip\wzzip.exe -amex -k @FILE.zip @FILE"

REM ==========================================================================

REM Script# 5

REM

REM SearchForErr.Bat

REM

REM This job will search the supplied file for error messages produce

REM a summary file if any are found.

REM

REM %1 - Supplied Output File to be checked.

REM c:\admin\daily\search.txt - Strings of text to be searched for.

REM c:\admin\daily\out\Error.txt - Errorlog of this job.

REM

REM ==========================================================================

@echo off

IF EXIST DEL c:\admin\daily\out\Summary.txt

echo . > c:\admin\daily\out\Summary.txt

echo Checking file - %1 >> c:\admin\daily\out\Summary.txt

echo . >> c:\admin\daily\out\Summary.txt

echo . >> c:\admin\daily\out\Summary.txt

Findstr /i /g:\admin\daily\search.txt %1 >> c:\admin\daily\out\Summary.txt

REM ==========================================================================

REM

REM search.txt

REM File containing possible error messages to search for in the backup logs

REM Do not put this comments in search.txt file

REM

REM ==========================================================================

/* Server : SG_Q1 Date :

backup commenced

backup finished

Backup of

Backup started

Backup completed

\<* error *

^error *

\<* abort *

^abort *

\<* failed *

^failed *

\<* command *

^command *

Process Aborted

Attempt to fetch logical page

not to object

is already hashed.

was expected to be the first page of

Table Corrupt: Extent

Data size mismatch occurred

bytes found,

expected data length

Could not find row in Sysindexes

CHECKTABLE

Descriptor for system table

hash table.

via RID failed

the last RID

Could not retrieve row

less than or equal

is not correctly

Sort failed because dpages

an incorrect value.

to correct the value

your command.

Table Corrupt: Offset table is incorrect

correct offset

is=

Table Corrupt: The row number and offset

check this page

number table=

Table Corrupt: Object id wrong;

in ext=

(name =

Allocation Discrepancy: Page

pg#=

on extent=

that has an illegal

The non_clustered leaf row

indexid

tried to remove resource lock

Unable to find buffer holding

dbid =

Table corrupt:

Table Corrupt: object id does not match

Table Corrupt: Extent id

but used bit off

is empty but

Status =

is not marked suspect.

I/O error detected

is incorrectly trying

lockid=

not found;

Table Corrupt: The index id

Table Corrupt: Keys

check row

page number

Table Corrupt: Page is linked but not

extent id=

Extent not within segment

is not in segment

uninitialized pages encountered

Could not find virtual page

System error detected

error log

Memmove() was called with

Table Corrupt: A page is linked

page:page number=

Table Corrupt: Page linkage

pointing to this page=

this page=

Table Corrupt: Object id

Database Corrupt: The last checkpoint

different checkpoint

Table Corrupt: Extent structures

extent#=

be=

cannot be opened

The SA can drop

Cannot deallocate extent

does not match

deallocated.

Could not find leaf row

data row from logical data page

page split.

There is insufficient system memory

Table Corrupt: Type id

Could not find row in Sysdatabases

CHECKTABLE

Your server command (process

deadlocked

\<command\>

not match

following page:

should be

Table Corrupt: The values in adjust table

starting from the end of the table

table in this row

Verify started

Verify completed

ORA-

-- ==========================================================================

--

-- dbfiles.sql

-- SQL script to get list of all database files, redo log members

-- and control files

--

-- ==========================================================================

SELECT 'Data files' FROM DUAL;

SELECT name FROM v$datafile;

SELECT 'Redo logs' FROM DUAL;

SELECT member FROM v$logfile;

SELECT 'Control files' FROM DUAL;

SELECT name FROM v$controlfile;

EXIT;

-- ==========================================================================

--

-- backup.sql

-- SQL script to generate scripts to start and end database backup

--

-- ==========================================================================

set pagesize 0 echo off feedback off verify off

spool C:\admin\daily\sql\BEGIN_SAMPLEDB_BACKUP.sql

SELECT 'ALTER SYSTEM SWITCH LOGFILE;'

FROM dual;

SELECT 'ALTER TABLESPACE ' || tablespace_name || ' BEGIN BACKUP;'

FROM dba_tablespaces;

SELECT 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;'

FROM dual;

SELECT 'EXIT;'

FROM dual;

spool off

spool C:\admin\daily\sql\END_SAMPLEDB_BACKUP.sql

SELECT 'ALTER TABLESPACE ' || tablespace_name || ' END BACKUP;'

FROM dba_tablespaces;

SELECT 'ALTER SYSTEM SWITCH LOGFILE;'

FROM dual;

SELECT 'EXIT;'

FROM dual;

spool off

EXIT;

 

Clone an Oracle database using an online/hot backup

Clone an Oracle database using an online/hot backup



This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:
When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups.
While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance.
Ok, lets get started...
1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:
select max(first_change#) chng
from v$archived_log
/
2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

cr_hot_backup.sql

cr_hot_backup.sql

set lines 999 pages 999

set verify off

set feedback off

set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb

from dba_tablespaces

where contents != 'TEMPORARY'

order by tablespace_name

/

spool off

spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb

from dba_tablespaces

where contents != 'TEMPORARY'

order by tablespace_name

/

spool off

3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:
@begin_backup
This will put all of the databases tablespaces into backup mode.

4. Copy the files to the new location
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

5. Take the source database out of backup mode
Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus:
@end_backup

6. Copy archive logs
It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:
alter system archive log current;
Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.
select name
from v$archived_log
where first_change# >= &change_no
order by name
/
Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

7. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:
create pfile='init.ora' from spfile;
This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Ensure that the archive log destination is pointing to the directory created in step 6.

8. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

Remove any lines that start with --

Remove any lines that start with a #

Remove any blank lines in the 'CREATE CONTROLFILE' section.

Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

Remove the line 'ALTER DATABASE OPEN RESETLOGS;'

Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.

Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

If the file paths are being changed, alter the file to reflect the changes.

Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/dg9a/system01.dbf',
'/u03/oradata/dg9a/undotbs01.dbf',
'/u03/oradata/dg9a/cwmlite01.dbf',
'/u03/oradata/dg9a/drsys01.dbf',
'/u03/oradata/dg9a/example01.dbf',
'/u03/oradata/dg9a/indx01.dbf',
'/u03/oradata/dg9a/odm01.dbf',
'/u03/oradata/dg9a/tools01.dbf',
'/u03/oradata/dg9a/users01.dbf',
'/u03/oradata/dg9a/xdb01.dbf',
'/u03/oradata/dg9a/andy01.dbf',
'/u03/oradata/dg9a/psstats01.dbf',
'/u03/oradata/dg9a/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;
9. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate.

10. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

11. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
sqlplus "/ as sysdba"

@/home/oracle/cr_
If all goes to plan you will see the instance start and then the message 'Control file created'.

12. Recover and open the database
The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:
recover database using backup controlfile until cancel
When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:
alter database open resetlogs;

13. Create temp files
Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

14. Perform a few checks
If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

Check that the database has opened with:
select status from v$instance;
The status should be 'OPEN'

Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.

Take a quick look at the alert log too.

15. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
alter database rename global_name to
/
Note. no quotes!

16. Create a spfile
From sqlplus:
create spfile from pfile;

17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:
shutdown immediate
startup mount
exit
From unix:
nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
shutdown immediate
startup mount
alter database open resetlogs
/

18. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.

19. Finished
That's it!

Restore and Recover Controlfile

Restore and Recover Controlfile ( Recovery Without Catalog database )

Last Controlfile Backup

------- ---- -- ---------- ----------- ------------ ---------------

44      Full    7.02M      DISK        00:00:01     22-FEB-12

        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20120222T160900

        Piece Name: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

  Control File Included: Ckp SCN: 10880516     Ckp time: 22-FEB-12

Simulate Loss of Controlfile

 [oracle@testbox:fwlive]$ pwd

/u01/app/oracle/oradata/fwlive

[oracle@testbox:fwlive]$ ls

control01.ctl  control02.ctl  control03.ctl  indx.dbf  paf_data.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@testbox:fwlive]$ mkdir test

[oracle@testbox:fwlive]$ mv *.ctl test/

[oracle@testbox:fwlive]$ ls -lrt test/

total 21396

-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control03.ctl

-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control02.ctl

-rw-r----- 1 oracle oinstall 7290880 Feb 22 16:12 control01.ctl

[oracle@testbox:fwlive]$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 22 16:12:51 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@fwlive >> select * from v$Database;

select * from v$Database

              *

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/fwlive/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@fwlive >> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testbox:fwlive]$

[oracle@testbox:fwlive]$

[oracle@testbox:fwlive]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 22 16:13:10 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06003: ORACLE error from target database:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/fwlive/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

[oracle@testbox:fwlive]$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 22 16:13:38 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@fwlive >> shutdown abort;

ORACLE instance shut down.

SYS@fwlive >> exit

SYS@fwlive >> startup

ORACLE instance started.

Total System Global Area  851443712 bytes

Fixed Size      1270316 bytes

Variable Size    432016852 bytes

Database Buffers   415236096 bytes

Redo Buffers      2920448 bytes

ORA-00205: error in identifying control file, check alert log for more info

SYS@fwlive >> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 [oracle@testbox:fwlive]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 22 16:16:24 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: fwlive (not mounted)

RMAN> set DBID 2568429818;

executing command: SET DBID

RMAN>  restore controlfile from autobackup;   -- only works if your controlfile autobackup  in flash recovery area

Otherwise

RMAN>  restore controlfile from '/u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp'; )

Starting restore at 22-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area

database name (or database unique name) used for search: FWLIVE

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u01/app/oracle/oradata/fwlive/control01.ctl

output filename=/u01/app/oracle/oradata/fwlive/control02.ctl

output filename=/u01/app/oracle/oradata/fwlive/control03.ctl

Finished restore at 22-FEB-12

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/22/2012 16:17:54

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/22/2012 16:18:00

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/fwlive/system01.dbf'

RMAN> recover database;

Starting recover at 22-FEB-12

Starting implicit crosscheck backup at 22-FEB-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 11 objects

Finished implicit crosscheck backup at 22-FEB-12

Starting implicit crosscheck copy at 22-FEB-12

using channel ORA_DISK_1

Crosschecked 14 objects

Finished implicit crosscheck copy at 22-FEB-12

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_7_7lj7nyfj_.arc

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_8_7lj7nznk_.arc

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/archivelog/2012_01_31/o1_mf_1_6_7lj7nzwb_.arc

File Name: /u01/app/oracle/flash_recovery_area/FWLIVE/autobackup/2012_02_22/o1_mf_s_775930140_7nb4rx18_.bkp

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 43 is already on disk as file /u01/app/oracle/oradata/fwlive/redo01.log

archive log filename=/u01/app/oracle/oradata/fwlive/redo01.log thread=1 sequence=43

media recovery complete, elapsed time: 00:00:01

Finished recover at 22-FEB-12

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 02/22/2012 16:18:26

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>

11gR2 Database Installation with ASM on OEL5

11gR2 Database Installation with ASM on OEL5

Oracle Latch

Oracle Latch

What is Latch ?

A mechanism to protect shared data structures in the System Global Area.
For Example: latches protect the list of users currently accessing the database
and protect the data structures describing the blocks in the buffer cache.

A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.


During DB performance we will see LATCH event ...so what is latch event and how many types of latch events ?

A latch is a low-level internal lock used by Oracle to protect memory structures.

The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.

Most Popular latch wait event are ...





1. Latch: library cache or Latch: shared pool

Below is Possible causes for above both latch events.

1. Lack of statement reuse
2. Statements not using bind variables
3. Insufficient size of application cursor cache
4. Cursors closed explicitly after each execution
5. Frequent logon/logoffs
6. Underlying object structure being modified (for example truncate)
7. Shared pool too small

Below is Possible suggestion for aviod above both latch events.

1. Increase SHARED_POOL_SIZE parameter value.
2. Modify Frontend application to use BIND VARIABLE
3. Use CURSOR_SHARING='force' (for temporary basis)


2. Latch: cache buffers lru chain

Possible Causes

1. Inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans.
2. DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer
3. Cache may be too small

Possible Suggestion

1. Look for: Statements with very high logical I/O or physical I/O, using unselective indexes
2. Increase DB_CACHE_SIZE parameter value.
3. The cache buffers lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained.

For symmetric multiprocessor (SMP) systems, Oracle automatically sets the number of LRU latches to a value equal to one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient.

Contention for the LRU latch can impede performance on SMP machines with a large number of CPUs. LRU latch contention is detected by querying V$LATCH, V$SESSION_EVENT, and V$SYSTEM_EVENT. To avoid contention, consider tuning the application, bypassing the buffer cache for DSS jobs, or redesigning the application.


Latch: cache buffers chains

Possible Causes

1. Repeated access to a block (or small number of blocks), known as a hot block
2. From AskTom:

Contention for these latches can be caused by:

- Very long buffer chains.
- very very heavy access to the same blocks.

Possible Suggestion

1. From AskTom:
When I see this, I try to see what SQL the waiters are trying to execute. Many times,
what I find, is they are all running the same query for the same data (hot blocks). If
you find such a query -- typically it indicates a query that might need to be tuned (to
access less blocks hence avoiding the collisions).

If it is long buffer chains, you can use multiple buffer pools to spread things out. You
can use DB_BLOCK_LRU_LATCHES to increase the number of latches. You can use both
together.

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:

SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR = 'address of latch'
ORDER BY TCH;

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.

Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment.

After you have identified the hot block, you can identify the segment it belongs to with the following query:

SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &obj;

In the query, &obj is the value of the OBJ column in the previous query on X$BH.


5. Latch: row cache objects

The row cache objects latches protect the data dictionary.
Suggestion: Increase SHARED_POOL_SIZE parameter to avoid this latch.