Learn Oracle from Oracle: July 2010

Saturday, July 31, 2010

RMAN, Transportable Tablespace and Flashback

Controlfile and Server parameter file (spfile) autobackup:

RMAN can be configured to automatically backup the control file and server parameter file (spfile) whenever the database structure in the control file changes and whenever a backup record is added. The autobackup feature enables RMAN to recover the database even if the current control file, catalog, and server parameter file are lost.

The RMAN can search the autobackup path and restore the server parameter file from backup. Once the instance is started with restored spfile, use RMAN to restore the controlfile from autobackup. After you mount the DB using restored control file, use RMAN repository information from control file to restore and recover the data files.

You can turn ON or OFF the autobackup feature by using the following commands:
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP OFF;

If control file autobackups is ON and the backup includes datafile 1, RMAN writes the control file and SPFILE to a separate backup set. If autobackup is OFF and the backup includes datafile 1, then RMAN includes the current control file and SPFILE in the same backup set as the datafiles. After backup completion the database writes a message containing the complete path of the backup piece and the device type to the alert log.

The control file autobackup filename has a default format of %F for all device types, so that the RMAN can guess the file location and restore the controlfile without a repository. All autobackup formats must include the %F variable.

The format %F translates into c-IIIIIIIIII-YYYYMMDD-QQ, where
IIIIIIIIII - stands for DBID.
YYYYMMDD - Time stamp of the day the backup is generated
QQ is the hex sequence starts with 00 and has a maximum of FF

Use the following command to configure the Control file Autobackup format
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/cf _spfile_%F';

Use following command to write to an Automatic Storage Management disk group
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK TO '+DGROUP1/%F';

Use the following commands to clear control file autobackup formats for a device:
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt CLEAR;

You can use SET CONTROLFILE AUTOBACKUP FORMAT command to override the configured autobackup format at session level

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/cf_spfile_%F';

RMAN> BACKUP DATABASE;

If you have configured control file autobackup, you do not need a recovery catalog or target database control file to restore the control file in case if you lost all your control files.

Use the below command to restore the control file autobackup.
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

******************************************

Oracle Flashback Drop and Recycle bin:

The Oracle 10g provides the ability to reinstating an accidentally dropped table, which is called Flashback Drop.

When a table is dropped, the database does not immediately remove the space associated with that table. Instead, the table is renamed to a system-defined name and placed in the Recycle bin. The flashback drop operations recovers from recycle bin.

SQL> DROP TABLE employee_tbl;

You can see the system-defined name of the table.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE

You can see the dropped table in the recycle bin using
SQL> show Recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------------------ ------------ ----------------
EMPLOYEE_TBL BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE 2010-03-01:09:10:00

Recover the dropped table from Recycle bin using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP;

Recover the dropped table from Recycle bin with new name using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP RENAME TO employee_tmp;

You can also drop a table completely with out needing a flashback using
SQL> DROP TABLE employee_tbl PURGE;

The tablespace will not free up space until the table will remain until recycle bin is purged. You can purge table explicitly from recycle bin using
SQL> PURGE TABLE employee_tbl;

Purge all tables in recycle bin using
SQL> PURGE RECYCLEBIN;

As a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

Purge all the objects in recycle bin in a tablespace using
PURGE TABLESPACE employee_tbs;

Purge all the objects in recycle bin in a tablespace specific to a user only using
PURGE TABLESPACE employee_tbs USER emp_user;

**********************************************

Flashback Table Feature in Oracle 10g:

In Oracle 9i Database, we have concept of Flashback Query option to retrieve data from a point in time in the past. The Oracle 10g provides the ability to recover a table or set of tables to a specified point in time in the past, this concept is called “Flashback table”.

Oracle Flashback Table operation is very quick and you do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.

The Flashback table depends on Undo information retained in the undo tablespace. If you set UNDO_RETENTION=1 hr, Oracle will not overwritten the data in undo tablespace until 1 hr. User can recover from their mistakes until specified time only.

Flashback table feature has some prerequisites:
•Row movement must be enabled on the table.

SQL> ALTER TABLE table_name ENABLE ROW MOVEMENT;

•You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
•You must have FLASHBACK ANY TABLE privilege or the FLASHBACK object privilege on the table.

Use below commands to restore the table to its state when the database was at the time specified by SCN or point in time.

SQL> FLASHBACK TABLE employee_tbl TO SCN 786;

or
SQL> FLASHBACK TABLE employee_tbl TO TIMESTAMP TO_TIMESTAMP ('2010-03-01 09:00:00', 'YYYY-MM-DD HH:MI:SS')

******************************************************

Transportable tablespace export and Import:

Transportable tablespaces export and import is manageable across platforms and only Meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.

This mode requires that you have the EXP_FULL_DATABASE role.

Please note that:

1. source and target database must use the same character set/national character set

2. You cannot transport a tablespace to a target database which already exists.

3. Transportable tablespace exports cannot be restarted once stopped

4. Target database must at same or higher release level as the source database.

Transportable tablespace export and import on same endian platforms.

Step 1: Find the Operating system byte order on Source and Target Database

SQL > select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------------- ---------------------- ------- ----------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little

For example, if you want to transport a tablespace “test_user_tbs” from a Linux 64 bit(Little endian) machine TESTLINUX to Microsoft Windows 64 bit (Little endian) machine TESTWIN. Both the source and target platforms are of LITTLE endian type. The data file for the tablespace “test_user_tbs” is “test_user_tbs01.dbff.

Step 2:- Make the tablespace “READ ONLY”

SQL> alter tablespace test_user_tbs read only;

Step 3: Export metadata

(i) Using export utility$ exp testuser/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp log=exp_test_user_tbs.log

(ii) Using Export data pump utility$ expdp system/password TRANSPORT_TABLESPACES=test_user_tbs TRANSPORT_FULL_CHECK=Y DIRECTORY=export_dir DUMPFILE=expdp_test_user_tbs.dmp logfile= expdp_test_user_tbs.log

TRANSPORT_FULL_CHECK= Y Specifies that check for dependencies between those objects inside the transportable Tablespace and those outside the transportable Tablespace.

The file “exp_test_user_tbs.dmp” or ““expdp_test_user_tbs.dmp” contains only metadata.

Step 4: Copy the files to Target system
If you are using FTP use binary option.

Step 5: Initiate Import to plug the tablespace into the database.
(i) Using import utility $ imp test_user/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp datafiles='test_user_tbs01.dbf' log=imp_test_user_tbs.log

(ii) Using impdp utility
Click here to see the Instructions to create Directory and grant privileges.

$ impdp test_user/test123 TRANSPORT_DATAFILES='test_user_tbs01.dbf' DIRECTORY=import_dir DUMPFILE=expdp_test_user_tbs.dmp log= impdp_test_user_tbs.log

You can use REMAP_SCHEMA= (source: target), if you want to import into another schema.

Step6: - Put the tablespace in read/write mode.
SQL> ALTER TABLESPACE TEST_USER_TBS READ WRITE;

Now the database has a tablespace named “test_user_tbs” and the objects of the tablespace will be available.

Transport Tablespace Import Common Errors:-

1. Oracle Error : EXP-00044: must be connected "AS SYSDBA" to do Point-in-time Recovery or Transportable Tablespace import
Cause: The user must log in "as SYSDBA" to perform transportable tablespace imports or Point-In-Time Recovery imports.
Action: Ask your database administrator to perform the Transportable Tablespace import or
the Tablespace Point-in-time Recovery import.

2. IMP-00017: following statement failed with ORACLE error
19721:IMP-00003: ORACLE error 19721 encountered
ORA-06512: at "SYS.DBMS_PLUGTS", line 2065
ORA-06512: at line 1
Cause: A duplicated data file name in the import parameters file was causing the issue
Action: Modify the import parameters file with the right datafile name

************************************************************

Oracle Data Pump Export/Import :

Oracle Data Pump Export :-
Oracle Data Pump utility is used for exporting data and metadata into set of operating system files and it is newer, faster and flexible alternative to “export/import” utilities.

1. Create directory object as SYS user.
SQL> create or replace directory export_dir as '/oradata/export’;

2. Grant Read/Write privilege on the directory to the user, who invokes the Data pump export.
SQL> grant read,write on directory export_dir to test_user;

3. Take Data Pump Export

Oracle data pump export examples for all 5 modes.

(i) Full Database Export
$ expdp test_user/test123 full=y directory=export_dir dumpfile=expdp_fulldb.dmp logfile=expdp_fulldb.log

(ii) Schema Export
$expdp test_user/test123 schemas=test_user directory= export _dir dumpfile=expdp_test_user.dmp logfile=expdp_test_user.log

If you want to export more than one schema then specify the schema names separated by comma.

(iii)Table Export
$ expdp test_user/test123 tables=emp,dept directory= export _dir dumpfile=expdp_tables.dmp logfile=expdp_tables.log

You can specify more than one table.

(iv) Tablespace Export
$ expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log

You can specify more than one tablespace.

(v) Transportable tablespace
$ expdp test_user/test123 transport_tablespaces=test_user_tbs transport_full_check=y directory= export _dir dumpfile=expdp_trans_tbs.dmp logfile=expdp_trans_tbs.log

Oracle Data Pump Import :-
Data Pump Import utility is used for loading an export dump files into a target system and we can load one or more files.

Copy the dump file to the target system where you to import.

1. Create directory object as SYS user.
SQL> create directory import_dir as '/oradata/import';

2. Grant Read/Write privilege on the Directory to the user, who invokes the Data Pump import.
SQL> grant read,write on directory import_dir to test_user;

3. Import the data using Data Pump Import.

Oracle data pump import examples for all 5 modes.

(i) Full Database Import
$ impdp test_user/test123 full=Y directory=imp_dir dumpfile=expdp_fulldb.dmp logfile=imp_fulldb.log

(ii) Schema Import
$impdp test_user/test123 schemas=test_user directory=imp_dir dumpfile=expdp_test_user.dmp Logfile=impdp_test_user.log

(iii) Table Import
$ impdp test_user/test123 tables=emp,dept directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

(iv) Tablespace Import
$ impdp test_user/test123 tablespaces=test_user_tbs directory=imp_dir dumpfile=expdp_tbs.dmp logfile=impdp_tbs.log

Above example imports all tables that have data in tablespaces test_user_tbs and it assumes that the tablespaces already exist.

(v) Transportable Tablespace
Click here to to import data using Transportable Tablespace method.

Common Errors with Data pump import (impdp) utility:-

1. ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping
Cause: A user attempted to remap objects during an import but lacked the IMPORT_FULL_DATABASE privilege.
Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

2. ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
Cause: Either an attempt to perform a full database export without the EXP_FULL_DATABASE role or an attempt to perform a full database import over a network link without the IMP_FULL_DATABASE role.
Action: Retry the operation in a schema that has the required roles.

3. ORA-01950: no privileges on tablespace "string"
Cause: User does not have privileges to allocate an extent in the specified tablespace.
Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

4. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
IMP-00017: following statement failed with ORACLE error 3113:
"BEGIN "
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE SYS.DBMS_RULE_ADM.CREATE_EVALUATIO" "N_CONTEXT_OBJ, 'SYS',TRUE);"

Cause: Import fails while executing the following command.
Action: Login as sys and run the following scripts
$ORACLE_HOME/rdbms/admin/dbmsread.sql
$ORACLE_HOME/rdbms/admin/prvtread.plb

************************************************************************

Redo log corruption and Recovery:

You will see any of these errors, in case of redo log corruption

ORA-16038 log %s sequence# %s cannot be archived
ORA-367 checksum error in log file header
ORA-368 checksum error in redo log block
ORA-354 corrupt redo log block header
ORA-353 log corruption near block change time

Solution:- Try to clear the log file without shutdown the database.

You have to be careful when using 'alter database clear logfile', because the command erases all data in the logfile.

eg: alter database clear logfile group 1;
alter database clear unarchived logfile group 1;

Dropping/clearing the redo logs is not possible, if there are only two log groups and the corrupt logfile belongs to CURRENT/ACTIVE, it may be needed for instance recovery. You may receive ORA-1624 error.

If you receive ORA-1624 then you have to perform incomplete recovery stopping just before the redo log file which was corrupted.

ASM Concept With Troubleshoot steps

ORA-600 [kfcNullConvert20] error on ASM instance:

I have experienced ORA-600 error on ASM instance (10.2.0.2), which caused ASM instance restart on that particular node. Due to which all databases instances on that node are restarted, because they lost connectivity with ASM.

This issue is a known Bug 4682861 in Oracle and the effected version are 10.1.0.4 ,10.1.0.5 ,10.2.0.1 ,10.2.0.2. This BUG is fixed in 10.2.0.3 Server patch set and 11.1.0.6 Base release.

Check for Possible Error messages in ASM/Database alert.log and trace files.
If you find below SYMPTOMS then you are hitting a BUG 4682861.

ASM alert. log errors:
ORA-00600: internal error code, arguments: [kfcNullConvert20], [], [], [], [], [], [], []
DBW0: terminating instance due to error 471

Trace file (ASM DBWR trace file contains Stack Trace: kfcNullConvert and errors):-
error 600 detected in background process
ORA-00600: internal error code, arguments: [kfcNullConvert20], [], [], [], [], [], [], []
ksuitm: waiting for [5] seconds before killing DIAG

Database alert log errors:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
ASMB: terminating instance due to error 15064

Cause:
Due to Bug 4682861, ASM instance restarted.

Solution: ***************

Option 1:- Upgrade to 10.2.0.3 or higher release

Option 2:-Download the patch from MetaLink:

i) Click on Patches & Updates Link.

ii) Enter patch number: 4682861 and Select your O/S

iii) Click Go.

iv) Download the patch for the Oracle release that you experienced this issue.

Please note that ANY ORA-600 error indicates Oracle has detected an internal inconsistency or a problem which it doesn’t know how best to address. These are *NOT* necessarily bugs and can occur for reasons such as running out of some resource, Operating System IO problems.. etc.

************************************************

Create, Drop and Alter ASM disk groups
Crete Disk Group:
Create Disk groups using the CREATE DISKGROUP statement and specify the level of redundancy.

Disk group redundancy types:-
NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware RAID or mirroring.

SQL> CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK '/dev/sda1' NAME dataa1,'/dev/sda2' NAME dataa2,
FAILGROUP failure_group_2 DISK '/dev/sdb1' NAME datab1,'/dev/sdb2' NAME datab2;

Drop Disk Group:
Drop disk group using DROP DISKGROUP statement.
SQL> DROP DISKGROUP data INCLUDING CONTENTS;

Alter Disk Group:
Add or remove disks from disk groups Using ALTER DISKGROUP statement. You can also use wildcard "*" to reference disks.

Add a disk.
SQL> ALTER DISKGROUP data ADD DISK '/dev/datac1', '/dev/datac2';

Add all reference disks
SQL> ALTER DISKGROUP data ADD DISK '/dev/datad*;

Drop/remove a disk.
SQL> ALTER DISKGROUP data DROP DISK datab2;

The UNDROP command used to undo only pending drop of disks. After you drop the disks you cannot revert.
SQL> ALTER DISKGROUP data UNDROP DISKS;

Diskgroup Rebalance:
Disk groups can be rebalanced manually Using REBALANCE clause and you can modify the POWER clause default value.
SQL> ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are mounted at ASM instance startup and dismounted at shutdown. Using MOUNT and DISMOUNT options you can make one or more Disk Groups available or unavailable.
SQL> ALTER DISKGROUP data MOUNT;
SQL> ALTER DISKGROUP data DISMOUNT;
SQL> ALTER DISKGROUP ALL MOUNT;
SQL> ALTER DISKGROUP ALL DISMOUNT;

DiskGroup Check:
Use CHECK ALL to verify the internal consistency of disk group metadata and repair in case of any error.
SQL> ALTER DISKGROUP data CHECK ALL;

DiskGroup resize:
Resize the one or all disks in the Diskgroup.

Resize all disks in a failure group.
SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 1024G;

Resize a specific disk.
SQL> ALTER DISKGROUP data RESIZE DISK dataa1 SIZE 1024G;

Resize all disks in a disk group.
SQL> ALTER DISKGROUP data RESIZE ALL SIZE 1024

***************************************************

Migrate Database to ASM Using RMAN: *******

We are Using RMAN to relocate non-ASM files to ASM files. The ASM files cannot be accessed through normal OS interfaces.

Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.
SQL> select * from V$CONTROLFILE;
SQL> select * from V$LOGFILE;

Step 2: Shutdown the database.
SQL> SHUTDOWN IMMEDIATE;

Step3: Modify the target database parameter file:

(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.

Step 4: Startup nomount mode and Restore control file
RMAN> STARTUP NOMOUNT;

Restore the control file into the new location.
RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';

Step 5: Startup mount mode and backup the Database.
RMAN> ALTER DATABASE MOUNT;

Backup database into ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';

Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;
or
Create new redo logs in ASM Disk group and delete the old redo log files.

Step 7: Open Database and create temporary tablespace.
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

Create temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;

Step 8: Drop old database files.
1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;
2. Remove all remaining Non-ASM database files using OS commands

***************************************

Createdisk, Deletedisk and Querydisk in ASM :

The /etc/init.d/oracleasm script is used to create, delete and query ASM disks and make disks available.

Create and Delete ASM disk:

Run below command to Create ASM disks.

# /etc/init.d/oracleasm createdisk DATA1 /dev/sdc
Creating Oracle ASM disk "DATA1" [ OK ]

Run below command to Delete ASM disks.
# /etc/init.d/oracleasm deletedisk DATA1
Deleting Oracle ASM disk "DATA1" [ OK ]

Query/List/Scan ASM disk:
Run the below querydisk command to see if the Disk/Device is used by ASM:
# /etc/init.d/oracleasm querydisk /dev/sdc
Checking if device "/dev/sdc" is an Oracle ASM disk [ OK ]

# /etc/init.d/oracleasm querydisk DATA1
Checking for ASM disk "DATA1" [ OK ]

Run below command to list Existing disks
# /etc/init.d/oracleasm listdisks
DATA1

Run the below command to scan the disks created on another node.
# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks [ OK ]

********************************************

Delete Archivelog files without using RMAN :

Here I am explaining two methods to delete archive logs and other database files with out using RMAN from ASM Disk Group in Oracle 10g/11g.

Method 1: asmcmd - ASM command-line utility

ASMCMD> rm file_name

For ex:-ASMCMD> rm ‘+dgroup2/testdb/archivelogs/thread_1_seq_363.510.1’
Or
ASMCMD> rm ‘+dgroup2/testdb/datafile/USERS.250.5334166963’

If you use a wildcard, rm deletes all matches except non-empty directories (unless the -r flag is used). The rm command can delete the file or alias only if the file is not currently in use by a client database.

Method 2: SQLPLUS utility

SQL> ALTER DISKGROUP DROP file

For ex:- SQL> ALTER DISKGROUP FLASH DROP FILE ‘+FLASH/testdb/archivelog/2009_08_11/thread_1_seq_363.510.1';

The “asmcmd” and “sqlplus” commands will not update the database views (V$ARCHIVED_LOG, V$FLASH_RECOVERY_AREA_USAGE), controlfile, Recovery Catalog that the files have been removed.

To update the Database views, control file or RMAN Catalog about deleted files you need to run the below command from RMAN.

RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

*****************************************

Install and Configure ASMLib in 10g (Automatic Storage Management):

The Oracle ASM feature was introduced in Oracle 10g Release 1.

There are two methods to configure ASM on Linux.

1. Configure ASM with ASMLib I/O: This method creates all Oracle database files on raw block devices, which are managed by ASM using ASMLib calls. ASMLib works with block devices and raw devices are not required with this method.

2. Configure ASM with Standard Linux I/O: This method creates Oracle database files on raw character devices, which are managed by ASM using standard Linux I/O system calls. It requires creating RAW devices for all disk partitions used by the ASM.

Here we will “Configure ASM with ASMLib I/O” method.

Step 1: Download and Install ASMLib

Download Oracle “ASMLib” software from below link and follow the link for your platform.
http://www.oracle.com/technology/tech/linux/asmlib/index.html

You must install all three packages for the kernel you are running. Use “uname –r “command to determine the version of your kernel.

oracleasm-support-version.cpu_type.rpm
oracleasm-kernel-version.cpu_type.rpm
oracleasmlib-version.cpu_type.rpm

See the below example to install the packages and run the command as root.

# rpm -ivh oracleasm-support-2.0.3-1.x86_64.rpm \
> oracleasm-2.6.9-67.ELsmp-2.0.3-1.x86_64.rpm \
> oracleasmlib-2.0.2-1.x86_64.rpm

Step 2: Configure and Enable Oracle ASM

Run the below command to configure the Oracle ASM and it will ask for the user and group that default to owing the ASM drivers access point.

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration [ OK ]
Creating /dev/oracleasm mount point [ OK ]
Loading module "oracleasm" [ OK ]
Mounting ASMlib driver filesystem [ OK ]
Scanning system for ASM disks [ OK ]

This command will load the ASM driver and mount the ASM driver filesystem. By selecting “y” during the configuration, the system will always load the module and mount the file system on system boot.

Run the below command to enable automatic start
#/etc/init.d/oracleasm enable

Oracle Database Block corruption

Oracle Database Block corruption : ******************

“Block corruption is rare but it does happen. As databases get larger and larger – the probability of it happening at some point nears 100%.”--Mr. Tom Kyte Oracle Expert

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed

ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.

NOTE: We can find detail information about block corruption in alert.log file

Two types of block corruption can happens

- Physical corruption (media corrupt)
- Logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

Difference between logical and physical corruption

Logical corruption is header - footer - that is one of the checks, yes (it is looking for fractured blocks and when it hits one, it'll re-read it, that is why there is no need for "alter tablespace begin backup" with rman)

Physical corruption is "we cannot read the block from disk, something is physically preventing us from doing so”.

How to detect block corruption?

1. DBVERIFY utility

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

http://dbataj.blogspot.com/2007/04/offline-database-verification-utility.html

2. Block checking parameters

There are two initialization parameters for dealing with block corruption:- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)causes 1-2% performance overhead- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)causes 1-10% performance overhead

Note: In10g db_block_checksum value TYPICAL is implying TRUE and db_block_checking value FULL implying TRUE.

DB_BLOCK_CHECKING Initialization Parameter
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3176

3. ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement

Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

ANALYZE: Reporting Corruption
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3173

4. RMAN BACKUP command with THE VALIDATE option

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

Detection of Logical Block Corruption : *******************

Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used, the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.
For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.
RMAN found any block corruption in database then following Data Dictionary view populated.

V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION

Using RMAN to Validate Database Files
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup005.htm#i1006673

5. EXPORT/IMPORT command line utility

Full database EXPORT/IMPORT show=y is another method.

. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: 'C:\ORA10GHOME\ORADATA\ORCL10G\USERS01.DBF'

6. DBMS_REPAIR package

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.

http://www.oracleutilities.com/Packages/dbms_repair.html

How to Repair & Fix block corruption?

We can recover everything but we have valid database backup.
Whenever we found block corruption then first need to find out which type of block corruption occurred because block corruption recovery depends on block corruption type.

Like Corrupted block related to TABLE segment, INDEX segment, TABLE
PARTITION segment, INDEX PARTITION segment, ROLLBACK segment, LOB segment.

Through below query we can find out corrupted block type

select segment_type,owner'.'segment_name
from dba_extents
where file_id = [&file_id] and [&block] between block_id and block_id+blocks -1;

Below is example with RMAN BLOCK MEDIA RECOVERY.

SQL> conn scott/tiger
Connected.

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 11)
ORA-01110: data file 5: 'C:\INDEXDATA01.DBF'

First check which type of block corruption happened through above mentioned query.

RMAN> blockrecover datafile 5 block 11;
Starting recover at 29-APR-08using channel
ORA_DISK_1 starting media recoverymedia recovery complete,
elapsed time: 00:00:00
Finished recover at 29-APR-08

If you are not using rman then applying below procedure
- if it is index then drop and recreate index
- if it is table and you have backup of that table then restore backup on another database and exp/imp the table.

Same Reference:
http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

NOTE: Find more information “Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g” Metalink Note: 28814.1

How to corrupt database block for practice purpose?
On Unix:
Use dd command
$man dd

On Windows:
Use Editor and open datafile write some junk character at middle of file and save it.

****************************************

Another live example :

Block Corruption and Recovery

Step 1: Identify the corrupt blocks
-----------------------------------
1. Run below command to populate v$database_block_corruption view with information of all the corrupted blocks.

RMAN> backup validate check logical database;

“CHECK LOGICAL" option is used to identify both Physical and Logical Block Corruptions.

Select the view to identify the corrupted blocks detected by RMAN.

SQL> select * from v$database_block_corruption;

Please note, After a corrupt block is repaired, the row identifying the block is deleted from the view.

2. Check alert. log file for corrupted blocks, data file list.
For ex:- ORA-01578: ORACLE data block corrupted (file # 5, block # 15)
ORA-01110: data file 5: '/oracle/oradata/trgt/users01.dbf'

3. You can also use dbverify utility to identify Physical and Logical Intra Block Corruptions.

dbv file=datafile_name blocksize=datafile_block_size

Step 2: Recovering Data blocks
-------------------------------

1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15;

Recover multiple blocks in single command

RMAN>BLOCKRECOVER DATAFILE 5 BLOCK 15 DATAFILE 2 BLOCK 10;

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM BACKUPSET;

# restore from datafile image copy
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM DATAFILECOPY;

# restore from backup set with tag "Sunday"
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 FROM TAG = Sunday;

# restore using backups created before log sequence 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SEQUENCE 100;

# restore using one week ago backups
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL 'SYSDATE-7';

# restore using backups until SCN 100
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 15 RESTORE UNTIL SCN 100;

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION.

RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago
RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

Note:-Block corruptions in RMAN backups and copies is kept in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION

Step 3: Allow Recovery to corrupt blocks
-----------------------------------------
During recovery database finds corrupt blocks then recovery stops. Run the below command in order to skip the corrupt blocks and proceed with recovery.

SQL>RECOVER DATABASE ALLOW n CORRUPTION;

Where n is the number of allowable corrupt blocks

**************************************

Another Live example :********

Problem: the application encounters an ORA-01578 runtime error because there are one or more corrupt blocks in a table it is reading.

How can corrupt blocks be caused?

First of all we have two diffent kinds of block corruption:

- physical corruption (media corrupt)

- logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

Logical corrution can amoung other reasons be caused by an attempt to recover through a NOLOGGING action.

There are two initialization parameters for dealing with block corruption:

- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)

causes 1-2% performance overhead

- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)

causes 1-10% performance overhead

If performance is not a big issue then you should use these!

Normally RMAN checks only for physically corrupt blocks
with every backup it takes and every image copy it makes.
This is a common misunderstanding amoung a lot of DBAs.
RMAN doesn not automatically detect logical corruption by default!
We have to tell it to do so by using CHECK LOGICAL!
The info about corruptions can be found in the following views:

SYS @ orcl AS SYSDBA SQL > select * from v$backup_corruption;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#
———- ———- ———- ———- ———- ———- ———-
BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
———- —————— — ———
1 586945441 586945402 3 1 5 81
4 0 YES CORRUPT

– SYS @ orcl AS SYSDBA SQL > select * from v$copy_corruption;

Here is a case study:

HR @ orcl SQL > select last_name, salary
2 from employees;

ERROR at line 2:
ORA-01578: ORACLE data block corrupted (file # 5, block # 83)
# this could be an ORA-26040 in Oracle 8i! and before
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/orcl/
example01.dbf’

This is what you find in the alert_.log:
Wed Apr 5 08:17:40 2006
Hex dump of (file 5, block 83) in trace file
/u01/app/oracle/admin/orcl/udump/orcl_ora_14669.trc
Corrupt block relative dba: 0×01400053 (file 5, block 83)
Bad header found during buffer read
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0×0000.0006d162 seq: 0×1 flg: 0×04
spare1: 0×52 spare2: 0×52 spare3: 0×0
consistency value in tail: 0xd1622301
check value in block header: 0x63be
computed block checksum: 0xe420
Reread of rdba: 0×01400053 (file 5, block 83)
found same corrupted data
Wed Apr 5 08:17:41 2006
Corrupt Block Found
TSN = 6, TSNAME = EXAMPLE
RFN = 5, BLK = 83, RDBA = 20971603
OBJN = 51857, OBJD = 51255, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =

Starting with Oracle 9i we can use RMAN

to check a database for both physically and logically corrupt blocks.
Here is the syntax:

RMAN> backup validate check logical database;

Starting backup at 05-04-2006:08:23:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/
system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/
sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/
example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/
undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/
users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-04-2006:08:24:10

RMAN does not physically backup the database with this command

but it reads all blocks and checks for corruptions.

If it finds corrupted blocks it will place the information about the corruption into a view:

SYS @ orcl AS SYSDBA SQL > select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 81 4 0 CORRUPT

this is what we find in the alert_.log:

Corrupt block relative dba: 0x014000b1 (file 5, block 177)
Bad header found during backing up datafile
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04
spare1: 0×52 spare2: 0×52 spare3: 0×0
consistency value in tail: 0xbc772003
check value in block header: 0xb32
computed block checksum: 0xe4c1
Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/
example01.dbf.
found same corrupt data

Now we can tell RMAN to recover all the blocks
which it has found as being corrupt:

RMAN> blockrecover corruption list;

# (all blocks from v$database_block_corruption)

Starting blockrecover at 05-04-2006:10:09:15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/
oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/
flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc
archive log thread 1 sequence 1 is already on disk as file
/u01/app/oracle/oradata/
orcl/redo01.log
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 05-04-2006:10:09:24

this is in the alert_.log:

Starting block media recovery

Wed Apr 5 10:09:22 2006
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/
archivelog/2006_04_05/o1_mf_1_2_%u_.arc
Wed Apr 5 10:09:23 2006
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/
archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc ( restored)
Wed Apr 5 10:09:23 2006
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Wed Apr 5 10:09:23 2006
Completed block media recovery

I recommend you to check your database for corrupt blocks
with RMAN on a regular basis, proactively.
If you do so you RMAN finds out about block corruptions
before your application runs into an ORA-01578 and
before you find out that you have backed up the corrupt blocks again and again.

There have been incidents when DBAs found out
that they did not have a backup with the un-corruted block any more,
because you have deleted the last one with a not corrupted version.
They could not recover the block any more!

For more detailed info about recovering corrupt blocks
(without and with RMAN, releases 7-10g)

pls see metalink also:
Subject: Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g
Doc ID: Note:28814.1 Type: BULLETIN

Troubleshooting: ORA -01114 or datafile access or block curruption

Troubleshooting All related to block curruption or datafile access issue : ===>>

Query to understand the root cause of the issue , you can take the query as given format as below ....

***************************************************************************

ACTION PLAN
===========

1) Please provide a DBV on datafile 208.

2) Please provide:

RMAN> backup validate check logical datafile 208;

when this completes provide:

SQL> select * from v$database_block_corruption;

3) Please also provide:

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
archive log list;
spool off
exit

4) Upload alert.log

*****************************************************

1>>>

ORA -01114 ORA-27069 Errors In Alert Log [ID 433499.1]

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process,
and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.4.0
This problem can occur on any platform.
Symptoms
Following errors are seen when using RAC in the alert log

ORA-01114: IO error writing block to file 82 (block # 526666)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 526666
Additional information: 1
Additional information: 526657
Mon Apr 23 17:00:01 2007
Trace dumping is performing id=[cdmp_20070423170001]
Mon Apr 23 17:00:02 2007
Warning: The file system may not be configured correctly.
The file size returned by file system may be obsolete after a file has been
resized [obsolete size: 526656] [up-to-date size: 526672].
Mon Apr 23 17:16:14 2007
Private_strands 0 at log switch
Thread 1 advanced to log sequence 8483
Current log# 4 seq# 8483 mem# 0: /db/S0LLP0/redoa/s0llp0/log4a.log

Cause
The warning itself reveals the error. Error ORA - 1114 shows that the we are trying to write to a block ( 526666 ) after where
the file system thinks the file ends ( 526656 ). However , Oracle thinks the file should be 526672 bytes.

Looking at the code this suggests that the file may have changed size in another instance, hence on another node,
but this change is not propagated to the node on where the error is reported. The file information is in effect, stale.

Solution
These errors appear transitory and look like a configuration issue in the storage. Check with your
Storage Admin/Vendor to find the cause for this and make appropriate changes

==============>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<============

2>>>

SELECT or ANALYZE returns ORA-1114, ORA-27072, "No space left on device" [ID 121072.1]
Modified 29-AUG-2002 Type PROBLEM Status PUBLISHED

Problem Description
-------------------

You have created a tablespace of type TEMPORARY. The tablespace was created
successfully.

You run a SELECT statement or ANALYZE TABLE statement. You are getting
the following errors:

ORA-1114: IO error writing block to file 102(block # 57333)
ORA-27072:skgfdisp: I/O error
SVR4 Error: 28: No space left on device

(The exact errors may vary depending on the platform / operating system.)

Solution Description
--------------------

Check the space left on the device. Re-create the temporary tablespace with
an appropriate file size.

Explanation
-----------

Your SELECT or ANALYZE statement requires a lot of room for sorting in the
temporary tablespace.

The temporary tablespace was created successfully. You can see the file at
the operating system level. The operating system command (for example Unix
command ls -l) shows that the datafile exists and has the appropriate size.

However, the space for that file has not been really allocated by the
operating system yet (this can be confirmed by another Unix command
df -k ).

Only when the temporary tablespace is first used, Oracle will attempt to take
the space required by the temporary datafile. If there is insufficient space
on the device, Oracle will return the errors.

===========>>>>>>>>>>>>>>>>><=======================

3>>>

Avoiding ORA-01114 after using transportable tablespace feature. [ID 69373.1]
Modified 07-AUG-2003 Type BULLETIN Status PUBLISHED

PURPOSE
-------
When using the transportable tablespace feature of Oracle8i, if the file
permissions are not set correctly then it is possible to encounter ORA-1114,
ORA-1110, ORA-27091 when attempting to set the tablespace to READ WRITE.

SCOPE
-----
The article is intended to assist analysts and customers who might encounter
the errors.

The scenario is described below:

Source site has Unix user "oracle8" belonging to the dba group.
Target site has Unix users "oracle8" and "oracle8i" as members of the
"dba" group. However, "oracle8i" owns the ORACLE_HOME and the oracle
executables.

You have done the following steps:

1. Logged on to Unix as an user belonging to the dba group (say oracle8).
2. Created a tablespace TEST.
3. Alter tablespace TEST read only.
4. Exported the metadata pertaining to the tablespace.
5. Physically copied the datafile across to the target location using ftp as
user "oracle8".
6. Imported the metadata into the target location.

In the target location when you try to change the tablespace status, you
get errors similar to the following :

SQL> alter tablespace test read write;
alter tablespace test read write
*
ERROR at line 1:
ORA-01114: IO error writing block to file 8 (block # 1)
ORA-01110: data file 8: '/u02/app/oracle/product/8.1.5/oradata/V815/test.dbf'
ORA-27091: skgfqio: unable to queue I/O
SVR4 Error: 9: Bad file number
Additional information: 1

Explanation:
Apparently this might look to be something wrong with the Input/Output
operations of the Operating System. Though it is not the case. When the file
was copied across from the source to the destination, it typically preserves
the ownership of the creator. In the above example, if the ftp from the source
to the destination is done as the "oracle8" Unix user the file permissions
would look like :

-rw-r----- 1 oracle8 dba 2099200 Mar 31 04:39 test.dbf

ie.the mode is 640.

So when the server process (which is not owned by "oracle8") tries to put it
back to READ WRITE mode, it generates the error.

*Important Note* We would expect the operating system to generate EPERM (Error
number 13) instead of EBADF(Error number 9). But this is because there was no
error reported by the open( ) system call. The process has already opened the
file in read only mode (O_RDONLY flag)and hence an error has been reported by
the write( ) system call.

The solution could be:
Either change the permissions of the file in the target location to have
write permission for the "dba" group or ftp the file as the user who owns
the ORACLE_HOME and the executables, in this example "oracle8i".

KEYWORDS
--------
transportable tablespace ORA-27091 ORA-01114

******************************************

4>>>>

Ensure that other tools/products are not running against the Oracle
database files when an Instance startup is being performed.

ORA-1114 and/or ORA-27091 during Instance Startup on OpenVMS [ID 260868.1]
Modified 26-MAR-2010 Type BULLETIN Status PUBLISHED

Checked for relevance on 25-Mar-2010.

PURPOSE
-------

To discuss one possible cause of the following errors during
Instance startup :-

ORA-01114: IO error writing block to file n (block # n)

ORA-27091: skgfqio: unable to queue I/O

SCOPE & APPLICATION
-------------------

This note will be of interest to OpenVMS customers and Oracle Support
Analysts.

ORA-1114 and/or ORA-27091 during Instance Startup on OpenVMS
------------------------------------------------------------

During instance startup, the following errors may be reported in the
ALERT log by the LGWR process.

ORA-01114: IO error writing block to file x (block # y)
ORA-01110: data file x: ''
ORA-27091: skgfqio: unable to queue I/O
ORA-27041: unable to open file
%RMS-E-FLK, file currently locked by another user
LGWR: terminating instance due to error 1114

Or...

ORA-01110: data file x: ''
ORA-01114: IO error writing block to file x (block # y)
ORA-27070: skgfdisp: async read/write failed
%SYSTEM-F-NOPRIV, insufficient privilege or object protection violation -
%SYSTEM-S-NORMAL, normal successful completion

These errors can be reported if something external to Oracle has
taken a VMS lock out on one or more of the database files.

Please check to see if any third party products were being run
that could take out locks on the Oracle database files.
($ SHOW DEV/FILES may help to view open files on each disk)

For example, were VMS backups active at the time of the instance
startup attempt. If so, could the backup have been dealing with the
database files.

Solution:

Ensure that other tools/products are not running against the Oracle
database files when an Instance startup is being performed.

Show Related Information Related
Products

* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Errors
ORA-1114; ORA-27070; ORA-27091

*****************************************************

5 >>>>

ORA-01114 ORA-27063 SVR4 Error: 28: No space left on device,temporary tablespace [ID 121732.1]
Modified 25-JUL-2010 Type PROBLEM Status PUBLISHED

***Checked for relevance on 25-Jul-2010***

Problem Description
-------------------
You have a temporary tablespace of type TEMPORARY.

You are running a SQL statement using temporary storage (for example, a sort
segment to process a GROUP BY or ORDER BY ), and get these errors :

ORA-01114: IO error writing block to file 5015 (block # 199528)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 49152

Solution Description
--------------------
The files of the temporary tablespace of type TEMPORARY are in a full file
system (no free space available).

Make free space in this file system.

Explanation
-----------
On certain file systems, creation of tempfiles does not guarantee the allocation
of the actual disk space of the file size specified.
The disk space is allocated later when the tempfile blocks are accessed.
This has the advantage of fast tempfile creation (as compared to datafile
creation). However, the disk could run out of space when tempfiles are accessed
later. The same thing happens when tempfiles are resized (to a larger size:
either by ALTER DATABASE ... TEMPFILE ... RESIZE command or by autoextend) i.e.
resize does not guarantee the actual disk space allocation on certain file
systems.

Tempfiles are currently not initialized, and (at least on Unix) this means they
are not pre-allocated. So even if a file system has enough free space at the
time the tempfile is created, later on the user may get write errors due to the
file system being full while writing to the tempfile.

DATAFILE FOR TEMPORARY TABLESPACE IS CREATED AS A SPARSE FILE

References
----------

Note:6224.1 : ALERT: Sparse Files and Oracle
Note:160426.1 TEMPORARY Tablespaces : Tempfiles or Datafiles ?

**********************************************

6>>>>

ORA-1114 error accessing Temporary Tablespace on AIX [ID 1083829.6]
Modified 27-NOV-2002 Type PROBLEM Status PUBLISHED

Problem Summary
===============

ORA-1114 Accessing Temporary Tablespace on AIX 4.3.2
RDBMS: 8.1.5 Not Using Transportable Tablespaces

Problem Description
===================

Using Tuxedo and clients are receiving ORA-1114 accessing the temporary
tablespace and receive the following error messages:

ORA-1114: IO error writing block to file #3 block#497707
IBM AIX RISC system/6000: error 27: File too large.

Problem Explanation
===================

You are either on a platform and database version combination that requires
an Oracle patch to access datafiles over 2Gb. With 8.1.5 however, there
are no Oracle patches that are needed to access or create datafiles over 2Gb

AIX 4.2.1 and 4.3 allows datafiles up to 32Gb.

Release AIX Platform Required Patch FS/RAW AIO>2 GB
====================================================================
8.1.3 + >=4.2.1 RS/6000 & SP none both yes

Problem References
==================

But you are still getting that error messages and don't know why?

When you select using SQL*Plus you do not receive the error messages.
Generally when you see this error message it is related to something in
the OS such as large datafiles not being supported.

Solution Summary
================

Check to see if there is enough space in /VAR to even VI a log file.

Solution Description
====================

If you are unable to VI a log file and also receive the ORA-1114 error it
is not related to the datafile size but is most likely related to space
issue in /VAR.

If you clean out /VAR and still receive the error message you might also
need to alter the tablespace to 1Gb and then back to 2Gb or whatever the
size of the file is.

Solution Explanation
====================

If resolve space problems in /VAR allowing the temp tablespace to do
its sorting.

***************************************

7>>>>

Sporadic Export Failure with Exp-00008: Oracle Error 1114 Encountered, ORA-1114 [ID 390605.1]
Modified 07-SEP-2006 Type PROBLEM Status MODERATED

In this Document
Symptoms
Cause
Solution

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6.0
This problem can occur on any platform.
Symptoms
Sporadic export failures with the following errors, yet all datafiles are online.

. exporting synonyms
Export fails with:
EXP-00008: ORACLE error 1114 encountered
ORA-01114: IO error writing block to file %s (block # %s)
EXP-00000: Export terminated unsuccessfully

Cause :

The filesystem where the tempfiles (used by the temp tablespace) are located is currently 100%
utilized.

Checking filesystem at OS level verifies this fact. The location of tempfiles can be found by
selecting from the v$tempfile view. ( i.e. select * from v$tempfile; )

Solution :

1. Add more free space to filesystem.
2. Rerun the export.

Show Related Information Related
Products

* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Errors
EXP-0; EXP-8; ORA-1114

Thursday, July 29, 2010

RMAN Backup and Restore

Performing Backups and Recovering your Database using Oracle9i Recovery Manager
Module Objectives
Purpose

In this module, you will learn how to use Oracle9i Recovery Manager to backup and restore a database.
Objectives

After completing this module, you should be able to:

Create a Recovery Manager Backup Configuration
Use Recovery Manager to Recover a database, tablespace, and datafile
Use Recovery Manager Advanced Features
Prerequisites

Before starting this module, you should have complete the following:

Preinstallation Tasks

Install the Oracle9i Database

Postinstallation Tasks
Review the Sample Schema
Enabling Archiving
Downloaded the rman.zip module files and unzipped them into your working directory.
Reference Material

The following is a list of useful reference material if you want additional information about the topics in this module:

Documentation: Oracle9i Recovery Manager User's Guide

Recovery Manager Overview

Recovery Manager is Oracle’s utility to manage the backup, and more importantly the recovery, of the database. It eliminates operational complexity while providing superior performance and availability of the database. Recovery Manager debuted with Oracle8 to provide DBAs an integrated backup and recovery solution.
Recovery Manager determines the most efficient method of executing the requested backup, restore, or recovery operation and then executes these operations in concert with the Oracle database server. Recovery Manager and the server automatically identify modifications to the structure of the database and dynamically adjust the required operation to adapt to the changes.
Enhanced Enterprise Manager Support

A Recovery Manager Graphical User Interface is built into Oracle's Enterprise Manager. Enterprise Manager is the central management framework providing
a robust console, a rich set of tools, and the extensibility to detect, solve, and simplify the problems of any managed environment. Enterprise Manager
is included as part of the Oracle Database Server.
Scheduling of backup jobs and RMAN-specific tasks via the Enterprise Manager Job System enhances the Backup and Recovery facility and provides the flexibility in scheduling the backup task at specified intervals, on specified day of the week, or on a specified day of the month. Oracle Enterprise Manager (EM) supports Backup and Recovery features required by users.

1. Backup Configurations to customize and save commonly used configurations for repeated use
2. Backup and Recovery wizards to walk the user through the steps of creating a backup script and submitting it as a scheduled job
3. Backup Job Library to save commonly used backup jobs that can be retrieved and applied to multiple targets
4. Backup Job Task to submit any RMAN job using a user-defined RMAN script.

Integrated with 3rd Party Media Management Vendors

Organizations rely on Oracle to provide solutions for very large critical systems. In addition to needing databases capable of handling large amounts of data and complex queries, these organizations also need robust backup and recovery technology. Recovery of data quickly and reliably is paramount should some aspect of the system fail. To address these needs, Oracle has created the Backup Solutions Program (BSP), a cooperative program designed to facilitate tighter integration between Oracle's backup products and those of third-party media management vendors.
Why learn two tools to backup your enterprise systems? Use your preferred third-party backup software to backup all of your system files and the Oracle database. Use a product that is integrated with Recovery Manger to ensure your database is precisely backed up; "One tool - One interface". Together, Oracle and media management vendors provide robust easy-to-use database backup and recovery solutions to customers with high-end requirements. To view the current members of the Backup Solutions Program, go to http://otn.oracle.com/deploy/availability.
Module Layout

The RMAN lessons are broken into three parts:

1. Configuration and Backup
2. Database Recovery
3. Advanced Features

Lesson 1 Configuration and Backup must be completed before starting Database Recovery or Advanced Features. After completing the Configuration and Backup lesson you can move to Database Recovery or Advanced Features.

1 Configuration and Backup

The Recovery Manager GUI interface is built into Oracle's Enterprise Manager. It provides the capability to backup, recover, and perform catalog maintenance on your Oracle database.

The first step in backing up your database is to create a backup configuration. The backup configuration is used at backup time to determine where the backup will be made.
1.

Open three windows, one for SQL*Plus, two command line windows of which one is for RMAN and the other is for scripts.

2.

Verify the ORCL instance is started. Click on the SQL*Plus icon and connect as SYS. If the instance is idle start it.

connect sys/oracle@orcl.world as sysdba

If SQL*Plus returns: 'Connect to an idle instance' then type

startup

Verify that the database is in archive log mode.

archive log list;

Starting RMAN

RMAN is a client executable that is installed with the Oracle database server. You can connect it to a target database, and then use server sessions on the target database to back up, restore, and recover files.

The following options can be used to start RMAN:

Specify the database connection information at the command line:

rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode

rman TARGET / CATALOG rman/rman@rcat

rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str

Omit the database connection at the command line, and use the CONNECT command in your
RMAN scripts:

rman

In both cases, RMAN displays an RMAN> prompt at which you can enter commands or run a command file.

RMAN can connect to the following types of databases:
Target database

The database that you will use RMAN to back up and restore: RMAN connects to the target database as SYSDBA. If you do not have this privilege, then the connection fails. You can connect with the SYSDBA privilege by using a password file or using operating system authentication.
Recovery catalog database

This database is optional: you can use RMAN with the default NOCATALOG option, in which case RMAN uses the control file as the exclusive repository of metadata. A recovery catalog is a set of tables containing RMAN metadata. RMAN obtains the metadata from the target database control file. If you choose to use a recovery catalog, refer to Oracle9i Recovery Manager User's Guide for complete instructions.
Auxiliary database

The auxiliary instance is only used for duplicate and standby database created with the DUPLICATE command and for tablespace point-in-time-recovery
3.

Connect to RMAN. From RMAN, type the following command to start the
RMAN client and create a server session on the target database ORCL.

rman target sys/oracle@orcl.world

Configuring the RMAN Environment

You can configure persistent settings in the RMAN environment. The configuration setting is done once, and used by RMAN to perform all subsequent operations. Display the preconfigured settings by typing the command SHOW ALL.
There are various parameters that can be used to configure RMAN operations to suit your needs. Some of the things that you can configure are:

* Required number of backups of each datafile
* Number of server processes that will do backup/restore operations in parallel
* Directory where on-disk backups will be stored

You can return any CONFIGURE command to its default setting by running the command with the CLEAR option, as in:

CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;

CONFIGURE RETENTION POLICY CLEAR;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

You will configure the following parameters:

* 3 backups of each datafile
* Backups to be stored on disk in the D:\ORACLE\ORCLBACKUP directory
* 2 server processes to do backup/restore operations in parallel
* Use the new controlfile autobackup feature
* Set backup optimization on

4.

If D:\ORACL\ORCLBACKUP does not exist please create is now. From the command prompt, in a windows where you are not connected to RMAN:

D:
cd\ORALCE
mkdir ORCLBACKUP
cd\wkdir

5.

Configure the RMAN environment by typing the following commands from RMAN.

CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'd:\oracle\orclbackup\ora_df%t_s%s_s%p';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'd:\oracle\orclbackup\ora_cf%F';
CONFIGURE BACKUP OPTIMIZATION ON;

6.

To view the current configuration settings, type the following command.

SHOW ALL;

Backup the Database

Use the BACKUP command to back up files to the configured default device by using the configured channels. For example, use this command to back up the database and all archived logs:

BACKUP DATABASE PLUS ARCHIVELOG;

RMAN stores its backups in backup sets. A backup set is a logical structure that contains one or more backup pieces, which are the physical files containing the data. A backup set usually contains only one backup piece. Only RMAN can create and restore backup sets. You can also back up individual tablespaces, database files, and backup sets.
Incremental Backups

In an incremental backup strategy, you first create a level 0 backup, which is a whole backup of the database. For example:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

Later, you can create backups at a higher "level." In a cumulative incremental backup, RMAN only backs up those blocks that are different between the specified level n and the most recent level n-1.
Restarting Backups

If a backup fails for any reason, backing up some but not all specified files, then you can use the NOT BACKED UP SINCE option on the BACKUP command to continue from the point of failure. This example backs up all files not backed up within the last day:

BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1';

Testing Backups

You can run a test RMAN backup that does not generate any output. The test checks datafiles for physical and logical corruption and that all database files exist and are in the correct locations. For example:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

7.

Generate archive logs to backup. From the command prompt execute the batch file switch5.bat, location in D:\wkdir, by typing the following:

switch5 orcl.world

8.

Backup database files and archive logs. While the backup is going on, we can also monitor the progress.

In RMAN type:

BACKUP DATABASE PLUS ARCHIVELOG;

In SQL*Plus where you have already connected to the ORCL database, type the following command to view the progress of the RMAN job. At first you may receive 'no row returned.'

@monitorjob

The columns from the resulting SQL statement which is run in MONITORJOB, refer to RMAN jobs in this manner:

SOFAR – number of blocks read/or put into the buffer by

the RMAN process

TOTALWORK – total number of blocks to be read and/or put into

the buffer

%COMPLETE – percentage of SOFAR/TOTALWORK * 100

9.

Backup individual datafiles. First, let’s take a look at the schema via RMAN. From RMAN, type the following commands. What does the database schema look like?

REPORT SCHEMA;

10.

RMAN can backup at the database, tablespace, and datafile level. First you will backup a datafile. Backup the database file which corresponds to the Example tablespace. In the example below the Example tablespace is datafile 5.

BACKUP DATAFILE 5;

11.

Backup at the Tablespace Level. From RMAN, type the following
commands.

BACKUP TABLESPACE EXAMPLE;

12.

Incremental backups can be made by RMAN. From RMAN, type the following command to make an incremental level 0 backup of the database.

BACKUP INCREMENTAL LEVEL 0 DATABASE;

In SQL*Plus, you can run the following SQL statement to monitor the work being processed by RMAN.

@monitorjob

13.

Not only can you perform an incremental backup at the database level, but you can perform incremental backups on datafiles and tablespaces as well. Let’s perform an incremental backup on a datafile.

Use the same datafile as used previously, the datafile for the Example tablespace.

BACKUP INCREMENTAL LEVEL 1 DATAFILE 5;

14.

RMAN can backup the archive logs and database at the same time or separately. Let’s backup just the archive logs. First, generate some logs. From the command prompt type the following:

switch5 orcl.world

Now, backup the archive logs. From RMAN type:

BACKUP ARCHIVELOG ALL;

15.

At one time or another, a backup may terminate due to an error or be killed due to system resources. RMAN offers the capability to restart a backup. With the RMAN command NOT BACKED UP SINCE, files that have not been backed up since a specified time will then be backed up. In the following command, we tell RMAN to backup the database files that have not been backed up since yesterday.
From RMAN, type the following command:

BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1';

Administrative Commands
The following commands can be used in RMAN to find out if files require a backup, delete backups that are no longer needed, and view backups of files. We refer to this set of commands as RMAN maintenance. From RMAN, type the following commands.

What files need to be backed up based on the configured retention policy?

REPORT NEED BACKUP;

The following 2 commands allow you to view what backups are obsolete and delete them.

REPORT OBSOLETE;

DELETE OBSOLETE;

The validate command can be used to check for corruption or verify that database files are in the correct location.

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

If you run any batch jobs with the nologging option, the following command will report any files that have not been backed up since the last nolog operation.

REPORT UNRECOVERABLE;

To fee up disk space of archive logs that you know have already been backed up, run this command to delete the archive logs that are older then yesterday.

DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';

The crosscheck command is used to verify that backups of the database are in the location known by the repository and to verify that archive logs are still on disk and have not been deleted outside of RMAN.

CROSSCHECK BACKUP OF DATABASE;

The LIST commands allow you to view the backup information of the database, datafile, tablespaces, and controlfiles. Lets view the backups for datafile 4 and the control file.

LIST BACKUP OF DATAFILE 4;

LIST BACKUP OF CONTROLFILE;

16.

Enter the following commands in RMAN:

REPORT NEED BACKUP;

REPORT OBSOLETE;

DELETE OBSOLETE;

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

REPORT UNRECOVERABLE;

DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';

CROSSCHECK BACKUP OF DATABASE;

LIST BACKUP OF DATAFILE 5;

LIST BACKUP OF CONTROLFILE;

17.

Clean up.

From RMAN, type the following command to exit RMAN.

Exit

In SQL*Plus, type the following command to exit from SQL*Plus

Exit

2 Database Recovery

The RESTORE and RECOVER commands manage restore and recovery of the database from RMAN.
Complete Recovery

When you perform complete recovery, you recover the backups to the current SCN. You can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later.

Note: Lesson 1 Configuration and Backup, must be completed prior to starting this lesson.
Recovering Datafiles

Take the datafile that needs recovery offline, restore the datafile, recover the datafile, and bring the datafile online.
1.

In SQL*Plus connect as HR and select from the EMPLOYEES table to verify that a table exist.

connect hr/hr@orcl.world

set pagesize 50000

SELECT first_name
FROM employees;

2.

Simulate the loss of employee01.dbf. Run the following script from the command line window.

rmdf orcl.world orcl

3.

In SQL*Plus as HR, verify that the file has been deleted.

connect hr/hr@orcl.world;
SELECT *
FROM employees;

4.

Recover example01.dbf. In RMAN, type the following commands.

RMAN TARGET SYS/ORACLE@orcl.world

RESTORE DATAFILE 5;
RECOVER DATAFILE 5;

SQL 'alter tablespace example online';

5.

In SQL*Plus as HR, verify the data is there.

SELECT first_name
FROM employees;

Recovering the Database

The database must be in MOUNT mode to restore and recover with RMAN.
6.

As HR, select from the EMPLOYEES table to verify that the database is up and running. In SQL*Plus, type the following:

SELECT first_name
FROM employees;

7.

Simulate the loss of all datafiles. (This does not include the control files or online logs) From the command prompt, type the following

rmdb orcl.world orcl

8.

Recover DATABASE. In RMAN, type the following commands.

RMAN TARGET SYS/ORACLE@orcl.world
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

9.

In SQL*Plus as HR, type the following command to show that the datafile has been successfully restored and recovered.

CONNECT hr/hr@orcl.world
SELECT first_name
FROM employees;

Recovering Tablespaces

Take the tablespace that needs recovery offline, restore the tablespace, recover the tablespace, and bring the recovered tablespace online.
10.

Select from the EMPLOYEES table to verify that a table exist. In SQL*Plus as HR, type the following command .

CONNECT hr/hr@orcl.world
SELECT first_name
FROM employees;

11.

Simulate the loss of the EXAMPLE tablespace. Run the following command from the command prompt.

rmts orcl.world orcl

12.

Verify that the file has been deleted.

Connect hr/hr@orcl.world
SELECT first_name
FROM EMPLOYEES;

13.

Recover tablespace EXAMPLE. In RMAN, run the following commands

RMAN TARGET SYS/ORACLE@ORCL.WORLD
RESTORE TABLESPACE EXAMPLE;
RECOVER TABLESPACE EXAMPLE;
SQL 'ALTER TABLESPACE EXAMPLE ONLINE';

14.

In SQL*Plus, type the following commands.

SELECT first_name
FROM employees;

Recovering the Database

The database must be in MOUNT mode to restore and recover with RMAN.
15.

In SQL*Plus as HR, select from the EMPLOYEES table to verify that the database is up and running.

SELECT first_name
FROM employees;

16.

Simulate the loss of all datafiles (this does not include the control files or online logs). From the command prompt, type the following command.

rmdb orcl.world orcl

17.

Verify that the database files have been deleted.

Connect hr/hr@orcl.world
SELECT *
FROM employees;

18.

Recover DATABASE. In RMAN, type the following commands. Connect to RMAN.

RMAN TARGET SYS/ORACLE@ORCL.WORLD
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

19.

From SQL*Plus are HR, type the following commands to verify that the database has been recovered.

Connect hr/hr@orcl.world
SELECT first_name
FROM employees;

Incomplete Recovery

Incomplete recovery uses a backup to produce a noncurrent version of the database. In other words, you
do not apply all of the redo records generated after the most recent backup. You usually perform
incomplete recovery of the whole database in the following situations:

* Media failure destroys some or all of the online redo logs.
* A user error causes data loss, for example, a user inadvertently drops a table.
* You cannot perform complete recovery because an archived redo log is missing.
* You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database�in other words, a database with a new stream of log sequence numbers starting with log sequence 1.
Incomplete Recovery of the Database
20.

In SQL*Plus as HR, select from the EMPLOYEES table to verify that the database is up and running.

Connect hr/hr@orcl
SELECT *
FROM employees;

21.

Connect as SYS and select the database id from V$DATABASE. Write down the value returned.

Connect SYS/ORACLE@orcl.world as sysdba
SELECT dbid
FROM v$database;

22.

Simulate the loss of all datafiles, control files, online logs and archive logs. Run the following script from the command line.

rmall orcl.world orcl

23.

Verify that the database files have been deleted.

Connect hr/hr@orcl.world
SELECT first_name
FROM employees;

24.

Recover DATABASE. In RMAN, type the following commands. Connect to RMAN without any connect strings

RMAN

25.

The following commands are issued within RMAN, using the database id(DBID) returned from the query. You will receive an error message at the end of recover due to the loss of the online redo logs.

SET DBID 1003121815;
CONNECT TARGET SYS/ORACLE@ORCL.WORLD;
STARTUP NOMOUNT;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'D:\ORACLE\ORCLBACKUP\ora_cf%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;

26.

Since the online logs were lost, complete recovery is not possible. Open the database with resetlogs to continue.

ALTER DATABASE OPEN RESETLOGS;

27.

To view the successful recovery of the database, in SQL*Plus type the following commands.

Connect hr/hr@orcl.world
SELECT first_name
FROM employees;

28.

Make a backup of new database incarnation version!! Let's generate some archive logs. From the command prompt, type the following command.

swtich5 orcl.world

29.

In RMAN, type the following commands to make a backup and remove obsolete backups and archive logs. What files require a backup now?

RMAN TARGET SYS/ORACLE@ORCL.WORLD
REPORT NEED BACKUP;

30.

Make a backup of the database and archive logs.

BACKUP DATABASE PLUS ARCHIVELOG;

31.

Remove obsolete backups and archive logs.

DELETE OBSOLETE;

32.

Crosscheck Backups.

CROSSCHECK BACKUP OF DATABASE;

33.

Delete obsolete again.

DELETE OBSOLETE;

3 RMAN Advanced Features
Duplicate Database

You can use the RMAN DUPLICATE command create a duplicate database from target database backups while still retaining the original target database. A duplicate database is a copy of the target database that you can run independently for a variety of purposes. For example, you can use it to:

* Test backup and recovery procedures
* Export data such as a table that was inadvertently dropped from the production database, and then importing it back into the production database.

A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.

Note: Lesson 1 Configuration and Backup, must be completed prior to starting this lesson.
01.

From the command prompt create a password file for the AUX database.

orapwd file=d:\oracle\ora92\database\PWDaux.ora password=oracle entries=5

2.

In SQL*Plus as SYS create a parameter file from the spfile for the AUX database.

connect sys/oracle@orcl.world as sysdba
create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;

3.

Edit the file INITaux.ORA, found in D:\ORALCE\ORA92\DATABASE; change all occurrences of \orcl\ to \auxorcl\. Next change db_name and instance_name to aux. Then add the following two lines to the parameter file:

db_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
log_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')

4.

Run script cre_dir from the command prompt to create the subdirectories for the duplicate database.

cre_dir

5.

Add an entry in the tnsnames.ora and listener.ora for the AUX database. Use Net Manager.

Start > Programs > Oracle - OraHome92 > Configuration and Migration Tools > Net Manager
TNSNAMES.ORA

Select Oracle Net Configuration > Local > Service Name. Then from the menu select Edit > Create. Use the following values to create the entry.

Net Service Name: aux.world

Protocol: TCP/IP (Internet Protocol)

Host:

Port: 1521

Service Name: aux.world

Since the database has not been created you can not successfully test the configuration.
LISTENER.ORA

Select Oracle Net Configuration > Local > Listeners > LISTENER. From the drop down menu on the right and side change Listener Location to Database Services. Click Add Database at the bottom. Use the following values:

Global Database Name: aux.net

Oracle Home: D:\oracle\ora92

SID: aux

Select from the top menu: File > Save Network Configuration followed by File > Exit.

6.

Shutdown and startup the listener.ora. From the command prompt type the follow:

lsnrctl stop

lsnrctl start

7.

Create a service for the database. From the command prompt type:

oradim -new -sid AUX -startmode m -pfile d:\oracle\ora92\database\INITaux.ORA

8.

From RMAN, type the following commands.

RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;

9.

Connect to the new database

connect hr/hr@aux.world
SELECT first_name
FROM employees;

*CRS and SRVCTL Command *****

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 - Release: 10.1 to 11.1

Information in this document applies to any platform.

Purpose :

This document is to provide additional information on CRS (Cluster Ready Services) in 10g and 11.1 Real Application Clusters. For information on version 11.2 and above, see Note: 1053147.1 "11gR2 Clusterware and Grid Home - What You Need to Know"
Scope and Application

This document is intended for RAC Database Administrators and Oracle support
enginneers.

CRS and 10g/11.1 Real Application Clusters

CRS and 10g REAL APPLICATION CLUSTERS
-------------------------------------

CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters
that provides a standard cluster interface on all platforms and performs
new high availability operations not available in previous versions.

CRS KEY FACTS
-------------

Prior to installing CRS and 10g RAC, there are some key points to remember about
CRS and 10g RAC:

- CRS is REQUIRED to be installed and running prior to installing 10g RAC.

- CRS can either run on top of the vendor clusterware (such as Sun Cluster,
HP Serviceguard, IBM HACMP, TruCluster, Veritas Cluster, Fujitsu Primecluster,
etc...) or can run without the vendor clusterware. The vendor clusterware
was required in 9i RAC but is optional in 10g RAC.

- The CRS HOME and ORACLE_HOME must be installed in DIFFERENT locations.

- Shared Location(s) or devices for the Voting File and OCR (Oracle
Configuration Repository) file must be available PRIOR to installing CRS. The
voting file should be at least 20MB and the OCR file should be at least 100MB.

- CRS and RAC require that the following network interfaces be configured prior
to installing CRS or RAC:
- Public Interface
- Private Interface
- Virtual (Public) Interface
For more information on this, see Note 264847.1

- The root.sh script at the end of the CRS installation starts the CRS stack.
If your CRS stack does not start, see Note 240001.1

- Only one set of CRS daemons can be running per RAC node.

- On Unix, the CRS stack is run from entries in /etc/inittab with "respawn".

- If there is a network split (nodes lose communication with each other). One
or more nodes may reboot automatically to prevent data corruption.

- The supported method to start CRS is booting the machine or use "crsctl start crs"
or "init.crs start".

- The supported method to stop is shutdown the machine or use "crsctl stop crs" or
"init.crs stop".

- Killing CRS daemons is not supported unless you are removing the CRS
installation via Note 239998.1 because flag files can become mismatched.

- For maintenance, go to single user mode at the OS or shut down CRS.

Once the stack is started, you should be able to see all of the daemon processes
with a ps -ef command:

[rac1]/u01/home/beta> ps -ef | grep crs

oracle 1363 999 0 11:23:21 ? 0:00 /u01/crs_home/bin/evmlogger.bin -o /u01
oracle 999 1 0 11:21:39 ? 0:01 /u01/crs_home/bin/evmd.bin
root 1003 1 0 11:21:39 ? 0:01 /u01/crs_home/bin/crsd.bin
oracle 1002 1 0 11:21:39 ? 0:01 /u01/crs_home/bin/ocssd.bin

CRS DAEMON FUNCTIONALITY
------------------------

Here is a short description of each of the CRS daemon processes:

CRSD:
- Engine for HA operation
- Manages 'application resources'
- Starts, stops, and fails 'application resources' over
- Spawns separate 'actions' to start/stop/check application resources
- Maintains configuration profiles in the OCR (Oracle Configuration Repository)
- Stores current known state in the OCR.
- Runs as root
- Is restarted automatically on failure

OCSSD:
- OCSSD is part of RAC and Single Instance with ASM
- Provides access to node membership
- Provides group services
- Provides basic cluster locking
- Integrates with existing vendor clusteware, when present
- Can also runs without integration to vendor clustware
- Runs as Oracle.
- Failure exit causes machine reboot.
--- This is a feature to prevent data corruption in event of a split brain.

EVMD:
- Generates events when things happen
- Spawns a permanent child evmlogger
- Evmlogger, on demand, spawns children
- Scans callout directory and invokes callouts.
- Runs as Oracle.
- Restarted automatically on failure

CRS LOG DIRECTORIES
-------------------

When troubleshooting CRS problems, it is important to review the directories
under the CRS Home.

In 10gR2 and 11.1, almost all logfiles are under /log/. The following
is the directory structure under /log/

./admin:
./client:
./crsd:
./cssd:
./cssd/oclsmon:
./cssd/oclsomon:
./evmd:
./racg:
./racg/racgeut:
./racg/racgevtf:
./racg/racgmain:
./srvm:

In 10.1:

/crs/log - This directory includes traces for CRS resources that are
joining, leaving, restarting, and relocating as identified by CRS.

/crs/init - Any core dumps for the crsd.bin daemon should be written
here. Note 1812.1 could be used to debug these.

/css/log - The css logs indicate all actions such as
reconfigurations, missed checkins , connects, and disconnects from the client
CSS listener . In some cases the logger logs messages with the category of
(auth.crit) for the reboots done by oracle. This could be used for checking the
exact time when the reboot occured.

/css/init - Core dumps from the ocssd primarily and the pid for the
css daemon whose death is treated as fatal are located here. If there are
abnormal restarts for css then the core files will have the formats of
core.. Note 1812.1 could be used to debug these.

/evm/log - Log files for the evm and evmlogger daemons. Not used
as often for debugging as the CRS and CSS directories.

/evm/init - Pid and lock files for EVM. Core files for EVM should
also be written here. Note 1812.1 could be used to debug these.

/srvm/log - Log files for OCR.

STATUS FOR CRS RESOURCES
------------------------

After installing RAC and running the VIPCA (Virtual IP Configuration Assistant)
launched with the RAC root.sh, you should be able to see all of your CRS
resources with crs_stat. Example:

crsctl stat resource -t <<<< Its used in 11.2 CRS

cd $ORA_CRS_HOME/bin
./crs_stat

NAME=ora.rac1.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac1.oem
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac1.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac1.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.oem
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE

There is also a script available to view CRS resources in a format that is
easier to read. Just create a shell script with:

--------------------------- Begin Shell Script -------------------------------

#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
AWK=/usr/xpg4/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

--------------------------- End Shell Script -------------------------------

Example output:

[opcbsol1]/u01/home/usupport> ./crsstat
HA Resource Target State
----------- ------ -----
ora.V10SN.V10SN1.inst ONLINE ONLINE on opcbsol1
ora.V10SN.V10SN2.inst ONLINE ONLINE on opcbsol2
ora.V10SN.db ONLINE ONLINE on opcbsol2
ora.opcbsol1.ASM1.asm ONLINE ONLINE on opcbsol1
ora.opcbsol1.LISTENER_OPCBSOL1.lsnr ONLINE ONLINE on opcbsol1
ora.opcbsol1.gsd ONLINE ONLINE on opcbsol1
ora.opcbsol1.ons ONLINE ONLINE on opcbsol1
ora.opcbsol1.vip ONLINE ONLINE on opcbsol1
ora.opcbsol2.ASM2.asm ONLINE ONLINE on opcbsol2
ora.opcbsol2.LISTENER_OPCBSOL2.lsnr ONLINE ONLINE on opcbsol2
ora.opcbsol2.gsd ONLINE ONLINE on opcbsol2
ora.opcbsol2.ons ONLINE ONLINE on opcbsol2
ora.opcbsol2.vip ONLINE ONLINE on opcbsol2

CRS RESOURCE ADMINISTRATION
---------------------------

You can use srvctl to manage these resources. Below are syntax and examples.

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

CRS RESOURCE STATUS

srvctl status database -d [-f] [-v] [-S ]

srvctl status instance -d -i >[,]
[-f] [-v] [-S ]

srvctl status service -d -s [,]
[-f] [-v] [-S ]

srvctl status nodeapps [-n ]

srvctl status asm -n

EXAMPLES:

Status of the database, all instances and all services.
srvctl status database -d ORACLE -v

Status of named instances with their current services.
srvctl status instance -d ORACLE -i RAC01, RAC02 -v

Status of a named services.
srvctl status service -d ORACLE -s ERP -v

Status of all nodes supporting database applications.
srvctl status node

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

START CRS RESOURCES

srvctl start database -d [-o < start-options>]
[-c | -q]

srvctl start instance -d -i
[,] [-o ] [-c | -q]

srvctl start service -d [-s [,]]
[-i ] [-o ] [-c | -q]

srvctl start nodeapps -n
srvctl start asm -n [-i ] [-o ]

EXAMPLES:

Start the database with all enabled instances.
srvctl start database -d ORACLE

Start named instances.
srvctl start instance -d ORACLE -i RAC03, RAC04

Start named services. Dependent instances are started as needed.
srvctl start service -d ORACLE -s CRM

Start a service at the named instance.
srvctl start service -d ORACLE -s CRM -i RAC04

Start node applications.
srvctl start nodeapps -n myclust-4

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

STOP CRS RESOURCES

srvctl stop database -d [-o ]
[-c | -q]
srvctl stop instance -d -i [,]
[-o ][-c | -q]
srvctl stop service -d [-s [,]]
[-i ][-c | -q] [-f]
srvctl stop nodeapps -n
srvctl stop asm -n [-i ] [-o ]

EXAMPLES:

Stop the database, all instances and all services.
srvctl stop database -d ORACLE

Stop named instances, first relocating all existing services.
srvctl stop instance -d ORACLE -i RAC03,RAC04

Stop the service.
srvctl stop service -d ORACLE -s CRM

Stop the service at the named instances.
srvctl stop service -d ORACLE -s CRM -i RAC04

Stop node applications. Note that instances and services also stop.
srvctl stop nodeapps -n myclust-4

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

ADD CRS RESOURCES

srvctl add database -d -o [-m ] [-p ]
[-A /netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}]
[-s ] [-n ]

srvctl add instance -d -i -n

srvctl add service -d -s -r
[-a ] [-P ] [-u]

srvctl add nodeapps -n -o
[-A /netmask[/if1[|if2|...]]]

srvctl add asm -n -i -o

OPTIONS:

-A vip range, node, and database, address specification. The format of
address string is:
[]//[/] [,] []//
[/]
-a for services, list of available instances, this list cannot include
preferred instances
-m domain name with the format “us.mydomain.com”
-n node name that will support one or more instances
-o $ORACLE_HOME to locate Oracle binaries
-P for services, TAF preconnect policy - NONE, PRECONNECT
-r for services, list of preferred instances, this list cannot include
available instances.
-s spfile name
-u updates the preferred or available list for the service to support the
specified instance. Only one instance may be specified with the -u
switch. Instances that already support the service should not be
included.

EXAMPLES:

Add a new node:
srvctl add nodeapps -n myclust-1 -o $ORACLE_HOME –A
139.184.201.1/255.255.255.0/hme0

Add a new database.
srvctl add database -d ORACLE -o $ORACLE_HOME

Add named instances to an existing database.
srvctl add instance -d ORACLE -i RAC01 -n myclust-1
srvctl add instance -d ORACLE -i RAC02 -n myclust-2
srvctl add instance -d ORACLE -i RAC03 -n myclust-3

Add a service to an existing database with preferred instances (-r) and
available instances (-a). Use basic failover to the available instances.

srvctl add service -d ORACLE -s STD_BATCH -r RAC01,RAC02 -a RAC03,RAC04

Add a service to an existing database with preferred instances in list one and
available instances in list two. Use preconnect at the available instances.

srvctl add service -d ORACLE -s STD_BATCH -r RAC01,RAC02 -a RAC03,RAC04 -P PRECONNECT

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

REMOVE CRS RESOURCES

srvctl remove database -d
srvctl remove instance -d [-i ]
srvctl remove service -d -s [-i ]
srvctl remove nodeapps -n

EXAMPLES:

Remove the applications for a database.
srvctl remove database -d ORACLE
Remove the applications for named instances of an existing database.
srvctl remove instance -d ORACLE -i RAC03
srvctl remove instance -d ORACLE -i RAC04
Remove the service.
srvctl remove service -d ORACLE -s STD_BATCH
Remove the service from the instances.
srvctl remove service -d ORACLE -s STD_BATCH -i RAC03,RAC04
Remove all node applications from a node.
srvctl remove nodeapps -n myclust-4

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

MODIFY CRS RESOURCES

srvctl modify database -d [-n ] [-m ]
[-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}]
[-s ]
srvctl modify instance -d -i -n
srvctl modify instance -d -i {-s | -r}
srvctl modify service -d -s -i
-t [-f]
srvctl modify service -d -s -i
-r [-f]
srvctl modify nodeapps -n [-A ] [-x]

OPTIONS:

-i -t the instance name (-i) is replaced by the
instance name (-t)
-i -r the named instance is modified to be a preferred instance
-A address-list for VIP application, at node level
-s add or remove ASM dependency

EXAMPLES:

Modify an instance to execute on another node.
srvctl modify instance -d ORACLE -n myclust-4
Modify a service to execute on another node.
srvctl modify service -d ORACLE -s HOT_BATCH -i RAC01 -t RAC02
Modify an instance to be a preferred instance for a service.
srvctl modify service -d ORACLE -s HOT_BATCH -i RAC02 –r

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

RELOCATE SERVICES

srvctl relocate service -d -s [-i ]-t [-f]

EXAMPLES:

Relocate a service from one instance to another
srvctl relocate service -d ORACLE -s CRM -i RAC04 -t RAC01

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

ENABLE CRS RESOURCES (The resource may be up or down to use this function)

srvctl enable database -d
srvctl enable instance -d -i [,]
srvctl enable service -d -s ] [, ] [-i ]

EXAMPLES:

Enable the database.
srvctl enable database -d ORACLE
Enable the named instances.
srvctl enable instance -d ORACLE -i RAC01, RAC02
Enable the service.
srvctl enable service -d ORACLE -s ERP,CRM
Enable the service at the named instance.
srvctl enable service -d ORACLE -s CRM -i RAC03

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

DISABLE CRS RESOURCES (The resource must be down to use this function)

srvctl disable database -d
srvctl disable instance -d -i [,]
srvctl disable service -d -s ] [,] [-i ]

EXAMPLES:

Disable the database globally.
srvctl disable database -d ORACLE
Disable the named instances.
srvctl disable instance -d ORACLE -i RAC01, RAC02
Disable the service globally.
srvctl disable service -d ORACLE -s ERP,CRM
Disable the service at the named instance.
srvctl disable service -d ORACLE -s CRM -i RAC03,RAC04

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

For more information on this see the Oracle10g Real Application Clusters
Administrator’s Guide - Appendix B

RELATED DOCUMENTS
-----------------

Oracle10g Real Application Clusters Installation and Configuration
Oracle10g Real Application Clusters Administrator’s Guide

------------------------------------------------------------------------
To learn about Oracle University offerings related to Real Application Clusters,
read note:762187.1.