Wednesday, February 10, 2016

ORA-01114: IO error writing block to file

ORA-01114 error indicates that the physical space where the datafile or tempfile exist has been exhausted. To resolve this issue you have to identify the mount point for which this error is coming.

whenever there is any ORA-01114 error in your database, then you first have to check the file number associated with this error. The file number may be datafile number or tempfile number.
If it is a datafile then following query will give you the mount point where the datafile exist.

SQL> select file_id, file_name from dba_data_files where file_id=;

If there is no output coming in the above query then the file number will be tempfile. 
Whenever the file mentioned in errors like ‘ORA-01114’ is a tempfile the file id is `file_id` + `the value of the DB_FILES of init parameter`.
Or;
IF file_id > DB_FILE THEN
    file_type := 'tempfile';
    file_id := DB_FILES - file_id ;

Thursday, December 25, 2014

Restore database to another host using RMAN


This document will detail the steps to restore a database on a new host using RMAN. For this demonstration we will be using Oracle Database 11gR2 on solaris and disk based backup.
One of the first things we want to do is get the Database ID (DBID) from the database in which we will be restoring its backup. You can find the DBID by connecting to the database with RMAN as shown below.










[oracle@ora1 ~]$ rman
 Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 27 09:40:34 2014
 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 RMAN> connect target /
 connected to target database: ORCL (DBID=1201234856)
 RMAN>
If you are unable to connect to the database you can also find the DBID by inspecting the file name of the control file autobackup. For example, c-1201234856-20140427-00, is a control file autobackup. The first group of numbers appearing after the c-123915029, is the DBID.
When we restore the database we are not going to have the active redo logs from the original database so we want to stop the recovery at the last SCN in the archive logs. We can find the last SCN by querying V$ARCHIVED_LOG.










SQL> select max(next_change#)
  2  from v$archived_log
  3  where archived = 'YES'
  4  group by thread#;

MAX(NEXT_CHANGE#)
-----------------
          1275229

Here we see that the archive logs contain log information up to SCN 1275229. That is our stopping SCN. We can also find the last SCN in our archive logs through RMAN using LIST BACKUP OF ARCHIVELOG ALL.










































RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54      2.09M      DISK        00:00:01     27-APR-14     
        BP Key: 54   Status: AVAILABLE  Compressed: NO  Tag: TAG20140427T094350
        Piece Name: /u03/app/oracle/oradata/orcl/backup/1vlc5nqn_1_1

  List of Archived Logs in backup set 54
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    12      1259005    26-APR-14 1259528    26-APR-14
  1    13      1259528    26-APR-14 1259609    26-APR-14
  1    14      1259609    26-APR-14 1262018    26-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
55      13.82M     DISK        00:00:02     27-APR-14     
        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: TAG20140427T094350
        Piece Name: /u03/app/oracle/oradata/orcl/backup/20lc5nqr_1_1

  List of Archived Logs in backup set 55
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       1262019    26-APR-14 1274991    27-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
60      136.00K    DISK        00:00:00     27-APR-14     
        BP Key: 60   Status: AVAILABLE  Compressed: NO  Tag: TAG20140427T094652
        Piece Name: /u03/app/oracle/oradata/orcl/backup/24lc5o0c_1_1

  List of Archived Logs in backup set 60
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       1274991    27-APR-14 1275229    27-APR-14

RMAN>
Looking through the output above we see that highest Next SCN is 1275229 which is part of backup set 60.
Next we need to make the backup available to the new host. 
Set the ORACLE_SID to the SID of the database that made the backup and start up RMAN and connect to the target.












[oracle@ora2 backup]$ export ORACLE_SID=orcl
[oracle@ora2 backup]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 27 11:27:23 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN>
We now need to set the DBID and bring the database in mount mode. When we bring the database up in mount mode we will receive errors because the parameter file is not found. This is to be expected as we are restoring to a new host. Oracle will use a “dummy” parameter file for the time being.




















RMAN> set dbid 1201234856

executing command: SET DBID

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

RMAN>
Now with the database in mount mode we need to restore the SPFILE. We are going to need to make some changes to the SPFILE so we will restore SPFILE to a PFILE. The SPFILE is stored in the control file autobackup. All of the RMAN configuration parameters values are at their defaults so we will need to set the location for the control file autobackup.






RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name DUMMY are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

RMAN>
Below we will set the location for the control file autobackup and restore the SPFILE to a PFILE and then we shutdown the database.























RMAN> set controlfile autobackup format for device type disk to '/u01/app/oracle/oradata/orcl/backup/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore spfile
2>    to pfile '/u01/app/oracle/oradata/orcl/initorcl.ora'
3>    from autobackup;

Starting restore at 27-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140427
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 27-APR-14

RMAN> shutdown abort;

Oracle instance shut down

RMAN> 
Now that we have the SPFILE we need to edit the some of the parameter to reflect the new host’s file system were appropriate. Below is a list of the some of the parameter you might need to change in your environment. These directories must exist on the new host.







*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.control_files='/u02/app/oracle/oradata/orcl/ctl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl','/u03/app/oracle/oradata/orcl/ctl/control03.ctl'#Restore Controlfile
*.db_recovery_file_dest_size=4039114752
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch'
*.local_listener='LISTENER_ORCL'

After making the necessary changes to the PFILE we now will bring the database back up in NOMOUNT mode so we can restore the control files. When bringing the database up in NOMOUNT mode we will use the PFILE we edited earlier.













RMAN> startup nomount pfile='/u01/app/oracle/oradata/orcl/initorcl.ora';
 connected to target database (not started)
Oracle instance started
 Total System Global Area     849530880 bytes
 Fixed Size                     1339824 bytes
Variable Size                528485968 bytes
Database Buffers             314572800 bytes
Redo Buffers                   5132288 bytes

RMAN>
Just like the SPFILE we will use the autobackup to restore the control file after setting the control file autobackup format. After restoring the control files we mount the database.



























RMAN> set controlfile autobackup format for device type disk to '/u01/app/oracle/oradata/orcl/backup/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 27-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140427
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u02/oradata/orcl/ctl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
output file name=/u03/app/oracle/oradata/orcl/ctl/control03.ctl
Finished restore at 27-APR-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>
Now that the control files have been restored and mounted, all of the RMAN configuration parameters have been set. You should verify the paths to make sure they are appropriate for this host.




















RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/oradata/orcl/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u03/app/oracle/oradata/orcl/backup/%U' MAXPIECESIZE 2G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>
Given the current settings RMAN is going to look in /u03/app/oracle/oradata/orcl/backup for both the control file autobackups and database/archive log backups. The backup we copied over is located in/u01/app/oracle/oradata/orcl/backup. There are two ways to resolve this issue. Either move the backup to the location were RMAN is configured or change the configuration. It may be that moving the backups may not be an option so we document what needs to be done next.
RMAN has record of a backup in /u03/app/oracle/oradata/orcl/backup but has no record of the backup we have in/u01/app/oracle/oradata/orcl/backup/. We need remove the record of the old location and the backup of the new location.
In order to let RMAN know about the backup in /u01/app/oracle/oradata/orcl/backup we use the CATALOG command.














































RMAN> catalog start with '/u01/app/oracle/oradata/orcl/backup';

Starting implicit crosscheck backup at 27-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK
Crosschecked 32 objects
Finished implicit crosscheck backup at 27-APR-14

Starting implicit crosscheck copy at 27-APR-14
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 27-APR-14

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /u01/app/oracle/oradata/orcl/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/orcl/backup/20lc5nqr_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/23lc5ns4_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
File Name: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140426-00
File Name: /u01/app/oracle/oradata/orcl/backup/21lc5nqv_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/1vlc5nqn_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/24lc5o0c_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/22lc5nqv_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/orcl/backup/20lc5nqr_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/23lc5ns4_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140427-00
File Name: /u01/app/oracle/oradata/orcl/backup/c-1201234856-20140426-00
File Name: /u01/app/oracle/oradata/orcl/backup/21lc5nqv_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/1vlc5nqn_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/24lc5o0c_1_1
File Name: /u01/app/oracle/oradata/orcl/backup/22lc5nqv_1_1

RMAN>
As a result of the CATALOG command RMAN performed a CROSSCHECK. The backup registered at/u03/app/oracle/oradata/orcl/backup/ was marked expired as a result of the CROSSCHECK because they were not present.
We are almost ready to restore the database the last thing to be done is to build the restore script. During the restore we can change the location of the data files by using SET NEWNAME FOR DATAFILE. The data files in the backup are on various locations but we would like to restore them to only one.
Also we would like to change the location of the redo logs as well. In order to change the redo log locations the ALTER DATABASE RENAME FILE command has to be issued. Below are examples of both.

























SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';

SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo01.log''
    TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/red01_b.log''
    TO ''/u02/app/oracle/oradata/orcl/redo/red01_b.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo02.log''
    TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02_b.log''
    TO ''/u02/app/oracle/oradata/orcl/redo/redo02_b.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo03.log''        
    TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03_b.log''
    TO ''/u02/app/oracle/oradata/orcl/redo/redo03_b.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo04.log''        
    TO ''/u01/app/oracle/oradata/orcl/redo/redo04.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo04_b.log''
    TO ''/u02/app/oracle/oradata/orcl/redo/redo04_b.log'' ";
If you remember we stated that we wanted the restore to stop at SCN 1275229 because that was the latest SCN in which we have archive logs. In order to stop at a specific SCN we will use the SET UNTIL SCN 1275229.
After all of that we just need to restore the database, switch the data files so the control files have the new path and recover the database. Not much at all. Description: ;)
Below is the entire script to do just that.


































RUN {
  SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/orcl/test.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/orcl/dbfs01.dbf';
   
   
  SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo01.log''
      TO ''/u01/app/oracle/oradata/orcl/redo/redo01.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/red01_b.log''
      TO ''/u02/app/oracle/oradata/orcl/redo/red01_b.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo02.log''
      TO ''/u01/app/oracle/oradata/orcl/redo/redo02.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo02_b.log''
      TO ''/u02/app/oracle/oradata/orcl/redo/redo02_b.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo03.log''        
      TO ''/u01/app/oracle/oradata/orcl/redo/redo03.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo03_b.log''
      TO ''/u02/app/oracle/oradata/orcl/redo/redo03_b.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u02/app/oracle/oradata/orcl/redo/redo04.log''        
      TO ''/u01/app/oracle/oradata/orcl/redo/redo04.log'' ";
  SQL "ALTER DATABASE RENAME FILE ''/u03/app/oracle/oradata/orcl/redo/redo04_b.log''
      TO ''/u02/app/oracle/oradata/orcl/redo/redo04_b.log'' ";
       
  SET UNTIL SCN 1275229;
   
  RESTORE DATABASE;
  SWITCH DATAFILE ALL;
   
  RECOVER DATABASE;
}

The script above was saved to the file restore.rman. Below is the output.

RMAN> @restore.rman
 RMAN>
RMAN> **end-of-file**

RMAN>

All that is left is to open the database with the RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

RMAN>



Followers