ORA-01110 When trying to Open Database

ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 5: ‘ /u01/test/users02.dbf’

This Error Maybe Appear On Different Database Files Because DBF Not Exists .

Solution
1-Restore From Rman if its Not Found .

Or

SQL> select file#,name, status from v$datafile where file#=5;
FILE# NAME STATUS
———- ————————————————- ———–
5 /u01/test/users02.dbf ONLINE

SQL>
SQL> alter database datafile ‘/u01/test/users02.dbf’ offline drop;
Database altered.

SQL> alter database open;
Database altered.
SQL>

Thank you
Osama Mustafa

Steps To Clone Oracle EBS R12

There’s Two major Parts In This Topics :
1- Pre Clone Steps .
2 – Post Clone Steps.

LETS START :

1- Pre Clone Steps : 

On Application Tier : 

1-    Execute Apps environment file
2-    cd $ADMIN_SCRIPTS_HOME
3-    ./adautocfg.sh
4-    Perl adpreclone.pl appsTier

Database Tier : 

1-    Execute Database environment ( under ORACLE_HOME)
2-    cd $ORACLE_HOME/appsutil/scripts/(CONTEXT_NAME)
3-    ./adautocfg.sh
4-    perl adpreclone.pl dbTier

shutdown application and database Copy your virtual nodes on new servers .

Post clone Steps:

Database Server

1-    cd $ORACLE_HOME/appsutil/clone/bin
2-    perl adcfgclone.pl dbTier

Application Server:

1-    cd $COMMON_TOP/clone/bin
2-    perl adcfgclone.pl appsTier

Notes:
$COMMON_TOP: APPLICATION_BASE/apps/apps_st/comn
CONTEXT_NAME: SERVICE_NAME_HOSTNAME

Thank you
Osama mustafa

ORA-16038 ORA-19504

Archiver Hung in Oracle database 
Summary
1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log
1. check how much space is used by archiver
  Sql>  select count(*),sum(blocks*block_size) from v$archived_log where    backup_count=0 and      deleted=’NO’;
   Sql> select * from v$flash_recovery_area_usage;
2. check the last good backup of the database

  set pages 999 lines 120

   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       INPUT_TYPE, STATUS,
       to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
       to_char(END_TIME,’mm/dd/yy hh24:mi’)   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

   3. delete archive log files older than last good backup
rman target / nocatalog
 allocate channel for maintenance device type disk;
 crosscheck archivelog all;
 delete noprompt archivelog until time ‘sysdate – 1’;
 delete noprompt expired archivelog all;
 delete noprompt obsolete device type disk;   

4. crosscheck archive log
crosscheck archivelog all;
 release channel;
 exit;  
Thank you 
Osama mustafa

ORA-19566: exceeded limit of 0 corrupt blocks for file

I already Talk about this Error but this Topics Share Another Solution For it :

RMAN> backup database;
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/22/2011 11:45:40
ORA-19566: exceeded limit of 0 corrupt blocks for file /oracle/oradata/orcl/system01.dbf

 

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
 
     FILE#     BLOCK#   BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 2684 1 0 CHECKSUM

RMAN> blockrecover datafile 5 block 2684;

Thank you
Osama mustafa

ORA-19566 exceeded limit of %s corrupt blocks for file %s

Cause: The user specified limit of allowable corrupt blocks was exceeded 
while reading the specified datafile for a datafile copy or backup.
Action: None. The copy or backup operation fails. The session trace file
contains detailed information about which blocks were corrupt.
 
 
But The Below is Work around : 
 
  
 1)Run DBVerify utility. Have a look at Verify Physical Data Structure



For example,
 
 
 $ dbv file=/oradata2/data1/dbase1/system01.dbf 
 
 Also run this operation for against the all datafiles you suspect which has corrupt block.



2)While taking backup within RUN block specify SET MAXCORRUPT in order
to say how many corruption it can handle. I ran dbverify utility and I
saw 1 block corrupt each in SYSTEM(1) and SYSAUX(3) datafile.



3)Now backup database with SET MAXCORRUPT option.

 
Syntax : 

 
SET MAXCORRUPT FOR DATAFILE File_id For DatabaseFiles to ;
 


SET MAXCORRUPT FOR DATAFILE to ;


RMAN> RUN{

2> SET MAXCORRUPT FOR DATAFILE 1,3 to 1;

3> BACKUP DATABASE;

4> }




For datafile 1 and 3 maximum 1 block corruption can be tolerated.
 

 
Thank you 
Osama Mustafa 

 

Check RMAN Backup Status

The Below Scripts To Check Backup Status Via Sqlplus , Maybe you will find Same Or Another Scripts In the Same way : 

Sqlplus / as sysdba

This script will report on all backups – full, incremental and archivelog backups –

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
 

This script will report all on full and incremental backups, not archivelog backups –

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

I would Love to Thank gavin soorma – OCM For His Amazing Effort . Share it For Knowledge .

Thank you
Osama Mustafa

Working with RMAN Stored Scripts

Creating Stored Scripts: CREATE SCRIPT

 

Make sure RMAN is connected to the right target database and the recovery catalog. Then run the CREATE SCRIPT command, as shown in this example:

 
 
CREATE SCRIPT full_backup 
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
 
 

 you can also provide a COMMENT with descriptive information:

CREATE GLOBAL SCRIPT global_full_backup 
COMMENT 'use only with ARCHIVELOG mode databases'
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

Running Stored Scripts: EXECUTE SCRIPT

 

RUN { EXECUTE SCRIPT full_backup; }

Displaying a Stored Script: PRINT SCRIPT

PRINT SCRIPT full_backup;
PRINT SCRIPT full_backup TO FILE 'my_backup.txt'; 

Listing Stored Scripts: LIST SCRIPT NAMES

LIST SCRIPT NAMES;

LIST ALL SCRIPT NAMES;

Updating Stored Scripts: REPLACE SCRIPT

REPLACE SCRIPT full_backup 
{
BACKUP DATABASE PLUS ARCHIVELOG;
}

Deleting Stored Scripts: DELETE SCRIPT

 

DELETE SCRIPT 'full_backup';
DELETE GLOBAL SCRIPT 'global_full_backup'; 

 

 

 

 Thank you 

Osama mustafa

 

 

 

Automatic Storage Management/ASM Part 2

Templates:

Templates are named groups of attributes that can be applied to the files within a disk group

Examples :

 

-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;

Available attributes include:

  • UNPROTECTED – No mirroring or striping regardless of the redundancy setting.
  • MIRROR – Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
  • COARSE – Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
  • FINE – Specifies higher granularity for striping. This attribute cannot be set for external redundancy.

 

 

Directories

A directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing

Examples :

-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';

-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';

-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;

 

Aliases

Aliases allow you to reference ASM files using user-friendly names

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1/mydb/datafile/my_ts.342.3';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1.342.3';

-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS

Files

Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.

 

-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';

-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';

ASM Views:

Migrating to ASM Using RMAN:

  • Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
  • Shutdown the database.
    SQL> SHUTDOWN IMMEDIATE 
    • Modify the parameter file of the target database as follows:
      Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
      Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
  • Start the database in nomount mode.

                            RMAN> STARTUP NOMOUNT

 

  • Restore the controlfile into the new location from the old location.

 RMAN> RESTORE CONTROLFILE FROM ‘old_control_file_name’;

  • Mount the database.
  • Copy the database into the ASM disk group.
    RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';

  • Switch all datafile to the new ASM location.

    RMAN> SWITCH DATABASE TO COPY;
  • Open the database.

    RMAN> ALTER DATABASE OPEN;
  • Create new redo logs in ASM and delete the old ones.
  • Enable change tracking if it was being used.

    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; 

RAC TO SINGLE NODE STEPS

Take RMAN backup of the production RAC database

1-
RMAN> run{
allocate channel c1 type disk format ‘/tmp/%U’;
backup database;
backup archivelog all;

backup controlfile ;
}

2-Create Pfile from RAC for single Node Using SQLPLUS :
 SQL>create pfile ” from spfile ;

3-Open pfile that you create it and modify the following parameters manually:
%dest, control_files
log_archive_dest_1
cluster_database_instances

4-Use the pfile created above to STARTUP NOMOUNT the database on the new host
$ sqlplus / as sysdba
SQL> startup nomount;

5-rman target /
A-restore controlfile from ‘/tmp/’;
OR
  restore controlfile from ‘/media/moh_1509/MEPSLIVE_20110919_762231895_3885.ctl’




B-ALTER DATABASE MOUNT ;
C.catalog backuppiece  ‘.bkp’ repeat it for all of backupset .
D.catalog backuppiece  ‘.arch’ repeat it for all of ARCHIVELOG.

E.
run
{
set newname for datafile 1 to ”;
set newname for datafile 2 to  ”;
set newname for datafile 3 to ”;
set newname for datafile 4 to  ”;
set newname for datafile 5 to  ”;
set newname for datafile 6 to  ”;
     restore database;
     switch datafile all;
}

NOTE : datafile number must be taken from the production system .

F.
sqlplus / as sysdba

alter database rename file ” to ”;
alter database rename file ” to ”;
alter database rename file ” to ”;

G.
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

Then Delete unnecessary Redolog .

6.RMAN TARGET /
RMAN>RECOVER DATABASE ;

7.SQL> alter database open resetlogs;

If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:

_no_recovery_through_resetlogs=TRUE

8. 
SQL> select THREAD#, STATUS, ENABLED 2 from v$thread;

SQL> select group# from v$log where THREAD#=2;
SQL> alter database disable thread 2;

9.DROP REDO LOG FROM NEW SINGLE NODE BY :
 SQL> alter database clear unarchived logfile group ;  
SQL>alter database drop logfile group ;

After doing this you can remove undo tablespace for other instance 

10.To Remove it :
 SQL> show parameter undo;
SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;
SQL> drop tablespace UNDOTBS2 including contents and datafiles; 

Now you have single node instance 🙂

Thank you
Osama Mustafa

Basic Rman (Recovery manager) Guide

Today we are gonna talk about Oracle Rman ( Recovery Manager) This tools from oracle makes backup and restore database looks easily , If you lose you data it will be problem so oracle provide solution for this issue
Rman is Command line tools that gives you lot of benefits for backup
such as backup database, control file , archive log , create scripts … etc

you can connect to the following types of databases.

Target database : 
RMAN connects you to the target database with the SYSDBA privilege. If you do not have this privilege, then the connection fails.
Recovery catalog database: 
This database is optional, you can also use RMAN with the default NOCATALOG option.
Auxiliary database :
You can connect to a standby database, duplicate database, or auxiliary instance (standby instance or tablespace point-in-time recovery instance.

Connecting to the Target Database

rman target /

 Information About your Schema And display datafiles Currently In the target database .

RMAN>REPORT SCHEMA;

To list the backup sets and image copies that you have created, run the list command as follows:

 RMAN> LIST BACKUP;

To list image copies, run the following command:

RMAN> LIST COPY;

 Show Rman Confurgation :

RMAN>Show all ;

List All the backup has been taken by Rman :

RMAN>list backupset ;

Validate Your backup that has been taken  By Rman , to check there’s no corrputed file in it :
RMAN>validate backupset

Take anote that I mention Rman basic command in earlier Blog , Please take a look at it .
And ENJOY .

Thank you
Osama Mustafa