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;

Click here to learn How to delete archive logs from ASM.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Download Oracle Grid Control and ASMLib

Oracle Enterprise Manager Grid Control is used for is a centralized administration and using this tool you can monitor and manage databases.

Download Oracle Enterprise Manager 10g Grid Control
http://www.oracle.com/technology/software/products/oem/index.html

Download Oracle Enterprise Manager Cloud Control 12c
Includes
- Agent
- Plug-in's
- Pre-upgrade Console
- Connectors for Heterogeneous Mgmt
- Installation Guide

Click here for steps to Install Oracle 10g Grid Control

Automatic Storage Management (ASM) is a new feature in Oracle Database 10g/11g and this feature simplifies the storage of Oracle data files, control files and log files...etc. It provides integration of the file system and the volume manager.

Download Oracle ASMLib using below link, follow the link for your platform.
http://www.oracle.com/technology/tech/linux/asmlib/index.html

Click here for steps to Install Oracle ASM on Linux in 10g/11g.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Download oracle 8i, 9i, 10g and 11g Database

Please find the direct links to download oracle 8i, 9i, 10g and 11g Database

Download Oracle 8i Database
http://www.oracle.com/technology/software/products/oracle8i/index.html

Download Oracle 9i Databsae
Includes
- Client
- Enterprise Edition/Standard Edition
http://www.oracle.com/technology/software/products/oracle9i/index.html

Download Oracle 10g Database
Includes
- Client
- Clusterware
- Examples
- Gateways
http://www.oracle.com/technology/software/products/database/oracle10g/index.html

Download Oracle 11gIncludes
- Client
- Clusterware
- Examples
- Gateways
http://www.oracle.com/technology/products/database/oracle11g/index.html

Download Oracle Enterprise Manager Cloud Control 12c
Includes
- Agent
- Plug-in's
- Pre-upgrade Console
- Connectors for Heterogeneous Mgmt
- Installation Guide

Download Oracle GoldenGate
Includes
- Management Pack
- Veridata

Click here for all Oracle software downloads
http://www.oracle.com/technology/software/index.html

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Download SQL Developer, Migration Workbench

Oracle SQL Developer:- Use Oracle SQL Developer Migrations to migrate Microsoft Access, MySQL, Sybase databases and Microsoft SQL Server to Oracle Database

Download Oracle SQL Developer Tool: http://www.oracle.com/technology/tech/migration/workbench/index_sqldev_omwb.html

Oracle Migration Workbench:- The Oracle Migration Workbench tool simplifying the migration from non-Oracle databases to Oracle. This tool now entered a desupport cycle.Now the existing functionality has been incorporated into Oracle SQL Developer.

Download Oracle Migration Workbench Tool: http://www.oracle.com/technology/tech/migration/workbench/index.html

See the Oracle Migration Technology Center in making decisions and migrating your databases and applications to the Oracle platform. http://www.oracle.com/technology/tech/migration/index.html

Please see the SQL Developer Forum for any question/issues
http://forums.oracle.com/forums/forum.jspa?forumID=260

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Download Oracle Client, Instant Client

Oracle Client:
The Oracle client is required software if you wish to access an Oracle database.

To download oracle client choose the Oracle version (8i/9i/10g/11) and select the Oracle release for Operating system that you want to download and it will take you to next page, where you will find the Oracle client.

Oracle Instant Client:
Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME

Click here to download Oracle Instant Client for all O/S platforms.
http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

For more information on Instant Client, see the official Instant Client site

Please see the Oracle Instant Client discussion forum for any questions/issues.
http://forums.oracle.com/forums/forum.jspa?forumID=190

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Transportable tablespace export/Import on same endian platforms

Transportable tablespaces export 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/national char 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.

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.

Follow the below steps

Step1: 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

Step 2: Make the tablespace “READ ONLY”
SQL> alter tablespace test_user_tbs read only;

Step3: 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 data pump export 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.

Step5: Run the below import command to plug the tablespace into the database.

Click here to see the Instructions to create Directory and grant privileges.

(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 data pump import utility
$ impdp test_user/test123 transport_datafiles='test_user_tbs01.dbf' directory=import_dir dumpfile=expdp_test_user_tbs.dmp log= impdp_test_user_tbs.

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

Step7: 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.

Please see the Data pump Export/Import related documents:
Click here for Data Pump Export modes and Interfaces.
Click here for Data Pump Export/Import with Examples.
Click here for Transportable Tablespace across different endian Platforms.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle Data Pump Export/Import

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.

Oracle Datapump utility introduced in Oracle 10g Release1 and this utility can be invoked using expdp(export) and impdb(import) commands. User need to specify the export/import parameters to determine the operation and you can specify the parameters on the command line or in a parameter file.

The expdp and impdp uses the procedures provided in the DBMS_DATAPUMP package to execute the commands and DBMS_METADATA package is used to move the data.

Please note that it is not possible to start or restart data pump jobs on one instance in Oracle RAC if jobs currently running on other instances.

Oracle Data Pump Export :-

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

Click here to see Roles/privileges required for Export modes.

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

Click here to learn more on Transportable Tablespace with examples.

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

From 11g, you can reaname a table during the import
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
$ impdp test_user/test123 remap_table=test_user.emp:emp1 directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

Tables will not be remapped if they already exist even if the TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND

(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.

Click here to learn Roles/ privileges required for Data pump Export and Import.

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

5. Import failed with below errors
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
Cause: The user that you are importing does not have privileages on CTXSYS.DRIIMP package or CTXSYS user does not exists
Action: Create CTXSYS user or grant required permissions

Please see the Data pump Export and Import related documents:
Click here for Data Pump Export modes and Interfaces.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Data Pump Export modes and Interfaces

Oracle Data Pump provides 5 Export modes:-

1. Full Export Mode: A full export is specified using the FULL parameter and entire database is exported using this mode. EXP_FULL_DATABASE role required for this mode.
2. Schema Mode: A Schema export is specified using the SCHEMAS parameter and you can specify more than one schema separated by comma. EXP_FULL_DATABASE role is required if you want to export other schemas.
3. Table Mode: A Table export is specified using the TABLES parameter and you can export specified set of tables and dependency objects. EXP_FULL_DATABASE role is required to export objects to that are not in your own schema.
4. Tablespace Mode: A tablespace export is specified using the TABLESPACES parameter and you can export full/part tables contained in a specified set of tablespaces including dependent objects. Privileged users get all tables and Nonprivileged users get only the tables in their own schemas.
5. Transportable Tablespace Mode: A transportable tablespace mode is specified using the TRANSPORT_TABLESPACES parameter. EXP_FULL_DATABASE role is required for this mode.

You can interact with Data Pump Export using below interfaces
1. Command line – specify all parameters directly on command line.
2. Parameter file – Specify all command-line parameters in a file i.e. PARFILE
3. Interactive – This mode is enabled by pressing Ctrl+C during an export and you can run various commands.

Oracle Data pump uses four methods for moving data in and out.
1. Datafile copy - The copying the datafiles is the fatest method of moving data and this method uses TRANSPORT_TABLESPACE,TRANSPORTABLE parameters
2. Direct Path - This is the second fatest method and data will be moved with minimal interpretation and in this method the SQL layer of the database is bypassed.
3. External Tables - This method creates external table mapping to a datafile dump
4. Network Link import - This methold is used to retrive data from a remote database over the network link and no dump files involved in this method.

Please see the Data pump Export/Import related documents:
Click here to learn Oracle Data Pump Export/Import with examples.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Delete archive logs using RMAN.

You can delete the archive logs using RMAN with or with out Recovery Catalog.
Use the below command to delete archive logs from ASM Disk Group or other file systems.

Method 1: Delete archive logs using RMAN with Recovery Catalog

RMAN> connect catalog userid/password@connection_string
RMAN> connect target
RMAN> delete force archivelog like '’;
For ex: -
RMAN> delete force archivelog like '+FLASH/testdb/archivelog/2009_08_11/thread_1_seq_366.663.1;

To delete multiple files just place them after the LIKE clause and use wildcard for file names.
RMAN> delete force archivelog like '+FLASH/asm/archivelog/2009_08_11/thread_1_*;

Method 2: Delete archive logs using RMAN with out Recovery Catalog.

RMAN> connect target
RMAN> delete force archivelog like '+FLASH/asm/archivelog/2009_08_11/thread_1_seq_366.663.1;

RMAN will update the controlfile/recovery catalog that the files have been removed.

Click here to learn How to delete the database files and archive logs without using RMAN.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

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

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

ORA-07445 Error

The ORA-07445 is very common error in many versions, but there are different reasons for it.

Here are some reasons for this error:-
1. Software Bugs
2. High RAM Usage.
3. OS Limits
4. Shared pool corruptions due to I/o slaves error (Refer Metalink note:1404681)
5. SGA corruption (Refer Metalink note: 5202899/5736850)
6. Library cache lock by query coordinator (Refer Metalink note: 3271112)
7. Hardware Errors
8.Oracle Block Corruptions
9. Program Errors

Oracle Metalink provided a Look up tool to search ORA-00600/ORA-07445 errors, look for workaround or bugs.

Error: ORA-07445: exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]"
Cause: An OS exception occurred which should result in the creation of a core file. This is an internal error.
Action: Contact your customer support representative.

The ORA-07445 has a multitude of causes and contacting Oracle technical support on Metalink is always required.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Voting disk Backup and Recovery

Voting disk manages node membership information and it is used by Cluster synchronization services demon (CSSD).

Backing up Voting Disks:-
Run the below command to back up the voting disk.
$ dd if=voting_disk_name of=backup_file_name
or
$ dd if=voting_disk_name of=backup_file_name bs=4k

Recovering Voting Disks:-
Run the below command to recover a voting disk
$ dd if=backup_file_name of=voting_disk_name

You can change the Voting Disk Configuration dynamically after the installation.
Please note that you need to run the command as “root”.

Run the below command to add a voting disk:
# crsctl add css votedisk_path

You can have upto 32 Voting disks.

Run the following command to remove a voting disk:
# crsctl delete css votedisk_path

Click here to learn Backup and Recovery of OCR

Regards,
Satishbabu Gunukua
http://www.oracleracexpert.com/

Restore and Recover OCR from backup (Oracle Cluster Registry)

Step 1: Locate physical the OCR backups using –showbackup command.
#ocrconfig – showbackup

Step 2: Review the contents
#ocrdump –backupfile backup_file_name

Step 3: Stop the Oracle clusterware on all the nodes.
#crsctl stop crs

Step 4: Restore the OCR backup
# ocrconfig –restore $CRS_HOME/cdata/crs/day.ocr
OR
Restore the OCR from export/logical backup.
# ocrconfig –import export_file_name
For ex: - # ocrconfig –import /backup/oracle/exp_ocrbackup.dmp

Step 5: Restart the Clusterware on all nodes.
#crsctl start crs

Step 6: Check the OCR integrity
# cluvfy comp ocr –n all

Click here to learn How to backup and recover OCR.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Backup and Recovery of OCR - Oracle Cluster Registry

Oracle Cluster Registry (OCR) file is a key component of the Cluster. It maintains the information about cluster node list, instance to node mapping and application resources profiles such as VIP address, services...Etc.

There are two methods for OCR Backup (Oracle Cluster Registry)

1. Automatically generated OCR files under $CRS_HOME/cdata/crs
2. OCR export/logical backup

The Oracle Clusterware automatically creates OCR backups
-Every four hours: last three copies
-At the End of the Day: last two copies
-At the end of the week: last two copies.

To backup OCR file, copy the generated file from $CRS_HOME/cdata/crs to your backup directory (/backup/oracle).

You must run the backup as “root”.

Run the below command to take OCR export backup.
# ocrconfig -export export_file_name

Click here to learn Restore and Recovery OCR from Backup.

Click here to learn Voting disk Backup and Recovery.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Restore validate using RMAN

Use RESTORE VALIDATE commands to validate RMAN backups to be sure they can be used for a restore.

Use below command to test most recent RMAN backup:
RMAN> restore validate database;

Use below command to test the most recent RMAN spfile backup:
RMAN> restore validate spfile to '/backup/testdb/spfile.ora';

Use below command to test the most recent RMAN controlfile backup:
RMAN> restore validate controlfile to '/backup/testdb/control01.ctl';

Use below command to test the most recent RMAN archivelog file(s) backup:
Find which archivelog sequences are in the last backup:
RMAN> list backup of archivelog all;
or
RMAN> list backup of archivelog all completed after 'sysdate -1';

RMAN> restore validate archivelog from sequence xxxxx until sequence XXXXX;
Where xxxxx is the sequence number.

Click here to lean How to validate RMAN backup.

Click here for Block recovery using RMAN.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

How to validate RMAN backup

The main purpose is to check for corrupt blocks and missing files.

There are two types of corruptions
1. Physical corruption
2. Logical corruption

In physical corruption/media corruption, the database does not recognize the block at all and the checksum is invalid.

In a logical corruption, the contents of the block are logically inconsistent. By default RMAN doesn’t check for logical corruption. Use “CHECK LOGICAL” with BACKUP or RESTORE command to check for logical and physical corruption using RMAN.

It’s advised to set DB_BLOCK_CHECKSUM=typical in the initialization parameter file of a database, set so that the database calculates datafile checksums automatically (not for backups). The BACKUP command computes checksum for each block and stores it in the backup. If you specify NOCHECKSUM while creating backup then RMAN does not perform a checksum of the blocks.

Note that Automatic Diagnostic Repository (ADR) tracks all types of corruptions.

VALIDATE:-
Run below command to validate Database
RMAN> VALIDATE DATABASE;

Run below command to validate Backup set
RMAN> VALIDATE BACKUPSET 10;

Run below command to validate individual data blocks within a data file for block corruption
RMAN> VALIDATE DATAFILE 1 BLOCK 10;

Click here for Block recovery using RMAN.

BACKUP VALIDATE:-
Use BACKUP VALIDATE to check datafile logical/physical corruptions and check all database files exists in correct location. Do not use the BACKUPSET, MAXCORRUPT, or PROXY parameters with BACKUP VALIDATE.

Run below command to validate all database files and archived logs. This command checks for physical corruptions only.
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Run below command to check for logical corruptions and physical corruptions.
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Click here to Validate restore using RMAN.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Block Recovery Using RMAN

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.

Click here to lean How to validate RMAN backup.

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_CORRUPTIONRMAN
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.

Click here to Validate restore using RMAN.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

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;

Use below query to find the object name and object type
SQL> SELECT owner,segment_type, segment_name FROM dba_extents
WHERE file_id = &file_no
AND &block_no BETWEEN block_id AND block_id + blocks -1;

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

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

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.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Connect by nocycle Query - Wrong results

The queries using “Connect by nocycle” may give different results with different versions.
Please try below workaround.

Alter the session and run the query.

alter session set "_optimizer_connect_by_cost_based"=false;
OR
alter session set optimizer_features_enable='10.2.0.1'

You can see this issue on 10.2.0.4,11.1.0.6,11.1.0.7.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

ORA-0431 error, when increasing SGA

High CPU_COUNT and increased granule size can cause ORA-0431 error.

Memory sizing depends on CPU_COUNT (No of processor groups).
Please use below formulas to calculate min buffer cache size

--Minimum Buffer Cache Size
10g : max(CPU_COUNT) * max(Granule size)
11g : max(4MB * CPU_COUNT)

Please note that If SGA_MAX_SIZE < 1GB then use Granule size = 4mb, SGA_MAX_SIZE > 1G then use Granule size = 8MB.

-- _PARALLEL_MIN_MESSAGE_POOL Size
If PARALLEL_AUTOMATIC_TUNING =TRUE then large pool is used for this area otherwise shared pool is used.

CPU_COUNT*PARALLEL_MAX_SERVERS*1.5*(OS msg bufferr size) OR CPU_COUNT*5*1.5*(OS message size)

-- Add extra 2MB per CPU_COUNT for shared pool.

Here is the example:-

Sun Solaris server has threaded CPUs. 2 physical CPUs has 8 cores, and each core has 8 threads, then Oracle evaluates CPU_COUNT = 2*8*8=128.

When SGA_MAX_SIZE=900MB,
Minimum Buffer Cache = CPU_COUNT *Granule size = 128*4M = 512MB
Shared Pool can use 338MB

When SGA_MAX_SIZE=1200MB,
Minimum Buffer Cache = CPU_COUNT *Granule size = 128*8M = 1024MB
Shared Pool can use 176 MB, so ORA-4031 occurs despite larger SGA_MAX_SIZE.

You need to manually tune CPU_COUNT parameter to resolve this error.

Please see the below link on CPU_COUNT
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams032.htm#sthref147

Thanks
Satishbabu Gunukula
http://www.oracleracexpert.com/

ORA-27102: out of memory on Solaris, database creation failed

Workaround 1:-This error indicates that the SGA is large for the amount of shared memory you have allocated in /etc/system file.

While creating database you may have to decrease the db_block_buffers. Please use the below formula to calculate SGA size.

sga size = db_block_buffers* block_size + shared_pool * log_buffer

Please note that when you are increasing your block size, try to reduce the value of db_block_buffers in your init.ora file.

This is just a work around and you may need to increase the max shared memory limit on the server. If this won’t work try "workaround 2".

Workaround 2:- Increase the kernel parameter shell limits.

Please see the below link for recommended values and make sure that the kernel parameter values should be greater than or equal to recommended values. http://download.oracle.com/docs/cd/B19306_01/install.102/b15704/pre_install.htm#sthref258

Click here to see How to resolve ORA-27102 error on Linux.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

ORA-27102: out of memory on Linux, unable to startup instance

Lower value of “kernel.shmall” in /etc/sysctl.conf will result “ORA—27102: out of memory” error on Linux x86_64, set the value using below Formula.

kernel.shmall = kernel.shmmax/PAGE_SIZE

Run the below command to get the value of PAGE_SIZE.
$getconf PAGE_SIZE

The kernel.shmmax parameter should be greater than or equal to the Half the size of physical memory (recommended value) of the machine. If the amount of Physical memory is ever changed then the parameter should be adjusted accordingly.

To make the new kernel settings take effect immediately, run this command:$/sbin/sysctl -p

Click here to see How to resolve ORA-27102 error on Solaris.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Upgrade Oracle 8i to 10g

Here are the simple steps to upgrade your oracle 8i database to 10g.

Please see the below guidelines:-

. Direct upgrade to 10g supported from 9.2.0,9.0.1,8.1.7,8.0.6.
. Make sure you take a cold backup of the database before upgradation.
. Make sure that required oracle version already installed on the system.
. Make sure you backup configuration files before you update for ex: - init.ora.
. Apply security patches after database upgraded to higher version, if necessary

Step:-1 Pre-upgradation check
Login to the Oracle 8i Database and run the utlu102i.sql from <10g_oracle_home>/rdbms/admin/utlu102i.sql.

Spool the contents of the file.
SQL> spool pre_check.log
SQL> @<10g_oracle_home>/rdbms/admin/utlu102i.sql
SQL> spool off

Review the spool file and make the necessary Database/parameter changes based on the recommendation.

Step:-2 Take cold backup of the database
Take backup using RMAN or command line file level backup

Step:-3 update oratab (/etc/oratab) and copy the init.ora from oracle 8i to 10g
Change the oracle home from 8i to 10g home to which it is being upgraded to.

For ex:-testdb:/oracle/v8174:N:UTF8 <-- 8i entry testdb:/oracle/v1020:N:UTF8 < --10g entry Copy init.ora from 8i to 10g oracle home. For ex:- $cp <8i_oracle_home>/dbs/init.ora <10g_oracle_home>/dbs/init.ora

If you are upgrading a RAC database, then set CLUSTER_DATABASE to “FALSE” during the upgrade.

Step:-4 Set the environment and Start the upgrade
Set the ORACLE SID and ORACLE_HOME

$ORACLE_SID=; export ORACLE_SID
$ORACLE_HOME=<10g_oracle_home>; export ORACLE_HOME

Run the upgrade script
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup upgrade

Create a SYSAUX tablespace.
SQL> CREATE TABLESPACE sysaux DATAFILE ‘’ SIZE 512M REUSEEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SQL> spool upgrade_10g.log
SQL> @<10g_oracle_home>/rdbms/admin/catupgrd.sql
SQL> spool off

Step:-5 Post upgradationCheck weather all components successfully upgraded or not.

SQL> @<10g_oracle_home>/rdbms/admin/utlu102s.sql
Run the script to recompile invalid objects.
SQL> @<10g_oracle_home>/rdbms/admin/utlrp.sql

Step:-6 make the database security compliant.
Check the current CPU Version.
SQL> select * from registry$history;

Please see the below link to check available Critical Patch Updates to your 10g Oracle version.http://www.oracle.com/technology/deploy/security/alerts.htm

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle OCP Logo Download

If you want to make use of OCA/OCP/OCM logs on your resumes or business cards or blogs, I would suggest them to download and use the registered logo from Oracle Website.

The people who are looking for logos apart from suggested and registered, searching for images can download from Google Images Oracle OCP Logo.

Download OCA/OCP/OCM Logo from Oracle Website:-

You will receive a Certification Success kit with welcome letter upon completion of your Oracle Certification. In welcome letter you will find Username and Password to download the logo from Oracle Certification member site.

Click here for Oracle Certification member's website

If you lost or forgot your username and password then contact ocpexam_ww@oracle.com with your Prometric ID, they will provide the username and password to download the log.

Click here for OCP logo Guidelines document.

Click here to learn on Oracle Certification Programs.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle Certification

Hi Everyone,

This is my first post in this blog and let’s starts with Oracle Certification.

In many of the forums I have seen a question that weather to go for Oracle 10g OCP/OCA or 11g OCP/OCA Certification.Everyone wants to go for latest version certification but I advise to go Oracle 10g and then upgrade to 11g for DBA’s. This way you will have both Oracle 10g and 11g Certifications and you can show the same on your resume. Also this fulfills the requirement of the companies those are looking for candidates with good experience with Oracle 10g and 11g and holding multiple certifications.

Please Check the below Links for more information:-

Click here for all Oracle Certification Programs
Please navigate yourself to know more about Upgrade Exams, Beta Exams, and Certification Retirements and Hands on Course Requirement etc.,

Click here for Oracle Certification Titles

Click here for OTN Discussion Forum for faq’s on Oracle Certification.

Click here for Oracle recommended Practice exams from Self Test and Transcender

Click here for to Schedule your test with new test delivery vendor -Pearson VUE .
Oracle exams through Pearson VUE will begin on September 14, 2009

Candidates may still take their exam at Prometric through September 25.

Click here to see the guidelines to Download oracle Logo.

I wish you best of luck for your Oracle Certification Exams..!

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/