ORA-XXXXX

Oracle 11g Logo

RMAN-06026: some targets not found – aborting restore, RMAN-06023: no backup or copy of datafile X found to restore

Published by Jignesh Jethwa

RMAN restore would failed with RMAN-06026 and RMAN-06023 after successfully restoration of controlfile. Logs are as follows:

Error log:

RMAN> restore database;
Starting restore at 15-FEB-16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/15/2016 14:52:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

As per oracle error RMAN-06023 means RMAN cannot find a backup for that datafile in its repository, so I cross verified RMAN backup files physically on disk as well as into RMAN repository with following RMAN commands. The backups are marked as AVAILABLE. Everything was pretty perfect.

RMAN> list backup of database summary;

AND

RMAN> list backup of datafile 1;
RMAN> list backup of datafile 2;
RMAN> list backup of datafile 3;
RMAN> list backup of datafile 4;

OR

RMAN> list backup of archivelog sequence;

After drilled down everything, I came to know that there are some backup files in FRA that are belongs to OLD INCARNATION than available backup CURRENT INCARNATION.
The problem is here, while restoring RMAN backups, RMAN implicitly perform cross verification to FRA(If FRA defined in controlfile) and if new files encounter it will be catalog(registered) automatically. This prevents database from restoring backups that belong to old CURRENT incarnation. Resulting RMAN would not allowed to restored this case. Backup incarnation and the CURRENT incarnation in controlfile should be same.

There are 3 Solutions to this error:

First: You can move old RMAN backup files(that belongs to old incarnation) to outside FRA and begin your restoration again with restoration of controlfile.

 

Second: Disabled FRA for the temporary purpose while RMAN backup being restore.

SQL> shutdown immediate;
SQL> create pfile from spfile;
comment *.db_recovery_file_dest and *.db_recovery_file_dest_size in pfile.
SQL> startup nomount pfile='.... init.ora'

Restart the restore controlfile and then restore/recover database commands…

 

Third: Resetting incarnation to the older incarnation, as below. In my case, following work around works like a charm.

RMAN> reset database to incarnation 3;
database reset to incarnation 3

After resetting database incarnation, restore database successful.

Folks, Your valuable comments are highly appreciated.

Thank you,
Stay Tune. 🙂

Oracle 11g Logo

ORA-01031: insufficient privileges – “SQLPLUS / as sysdba” working, but “SQLPLUS sys/sys@YOUTH as sysdba” is not working.

Published by Jignesh Jethwa

I have faced ORA-01031 oracle error after migrating oracle database from oracle 10g (10.2.0.4.0) to Oracle 11g (11.2.0.1.0)
Error log is as follows:

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

But, I can connect the database with SYS user as follow, but not able to connect SYS user with credentials cum connection string(as mentioned above).

[oracle@newyouth dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:29 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
  • Oracle error itself pretty self explanatory, according to error I have cross checked all the permissions but it doesn’t work for me.
  • Listener.ora and tnsnames.ora files also verified and found correct.
  • Oracle environment variables (like ORACLE_SID,ORACLE_HOME and PATH) also set appropriately with no mistake.
  • REMOTE_LOGIN_PASSWORDFILE parameter set to EXCLUSIVE in pfile/spfile.

Password file also created with following command.

orapwd FILE=orapwblade1 PASSWORD=sys entries=30

Finally, I got the clue from v$pwfile_users dictionary view, This view lists users who have been granted SYSDBA and SYSOPER privileges, So following query must fetch something as I have already created password file. But no luck. Oops.
SQL> select * from v$pwfile_users;
no rows selected

I have double check “$ORACLE_HOME/dbs” directory and came to know there was naming convention mistake with password file(Case sensitivity with ORACLE SID).

[oracle@newyouth dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Feb 10 04:00 hc_BLADE1.dat
-rw-r--r--. 1 oracle oinstall 1018 Feb 10 03:58 initBLADE1.ora
-rw-r-----. 1 oracle oinstall 24 Feb 8 02:24 lkBLADE1
-rw-r-----. 1 oracle oinstall 5120 Feb 10 04:16 orapwblade1
-rw-r-----. 1 oracle oinstall 3584 Feb 10 04:00 spfileBLADE1.ora

So as a part of the solution to my scenario, I have dropped an existing password file and created new one by following command.

orapwd FILE=orapwBLADE1 PASSWORD=sys entries=30

Finally, I got access and v$pwfile_user dictionary view fetched SYS user entry.

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:18:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

Cheers!!
Stay Tune. 🙂

Oracle 11g Logo

ORA-01182: cannot create database file X – file is in use or recovery ORA-01111: name for data file X is unknown – rename to correct file

Published by Jignesh Jethwa

On Physical Standby Data Guard, Following command failed with ORA-01182. Error itself self explanatory.
As a part of solution, you just need to End up recovery sessions and bounce back your database to Mount mode and issue SQL command again.

Error Log:

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100' as '/u03/Oradata/Prod_OTHER_0072.DAT';
alter database create datafile '/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100' as '/u03/Oradata/Prod_OTHER_0072.DAT'
*
ERROR at line 1:
ORA-01182: cannot create database file 10 - file is in use or recovery
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10:
'/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100'

You just can’t create a datafile that already being online or is being recovered.

Solution:

SQL> shutdown immediate;
SQL> startup mount;
SQL>  < Issue above SQL Command >;

 

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device ORA-27048: skgfifi: file header information is invalid ORA-27072: File I/O error

Published by Jignesh Jethwa

While recovering my manual physical standby data guard configuration, Every time oracle would apply all archive logs to standby database and end up with following mentioned errors.

As a part of troubleshooting, I have Verified:

  • Archive log sequence number on both the side but that was in place.
  • Block corruptions on datafiles level on both sides. It was clean.
  • Physical storage blocks, It was also clean.
  • Almost verify everything, that i can.

After scratching my head for several hours, I found non oracle files situated on FRA. After moving it from FRA, Recovery process successfully carried out. Non oracle file was only culprit to the following mentioned errors. It was very strange case I have faced in my life.

Solution:

Remove all non oracle files from FRA.

Error logs:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15198931 generated at 07/23/2015 17:55:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc
ORA-00280: change 15198931 for thread 1 is in sequence #5180
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 15199107 generated at 07/23/2015 18:00:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5181_bv1qvq3n_.arc
ORA-00280: change 15199107 for thread 1 is in sequence #5181
ORA-00278: log file '/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc' no longer needed for this recovery
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropri

Thanks,

Stay Tune. 🙂

Oracle 12c Logo

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

Published by Jignesh Jethwa

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA error message itself is self explanatory, When undo tablespace is in use and we will try to drop that tablespace than oracle pop up with ORA-30013 error.

Solution:
Wait until active transactions are COMMITED or ROLLED BACK. If so, you can easily drop mentioned tablespace.
OR
You can kill that transaction if it unnecessary.

Consider following example:

SQL> create tablespace newundotbs datafile '/u01/app/oracle/oradata/prod/newundotbs1.dbf' size 200M autoextend on maxsize 500M;
Tablespace created.
SQL> alter system set UNDO_TABLESPACE=NEWUNDOTBS scope=spfile;
System altered.

Following command will failed because there is active transactions.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Query dba_rollback_segs to know list of online segments:

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

Retrive SID and SERIAL# from below mentioned query in order to kill session with ‘alter system kill session’ command.

SQL> select SID, substr(username,1,10) username,serial#,segment_name from v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and xidusn=segment_id;
SQL> alter system kill session 'SID,SERIAL#';
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.

Tablespace successfully dropped.

Thanks
Stay Tune 🙂

Oracle 12c Logo

ORA-03206: maximum file size of XXXX blocks in AUTOEXTEND clause is out of range

Published by Jignesh Jethwa

If you are creating SMALLFILE datafile (default) with 8k blocks with datafile size greater than 32 GB than oracle will pop up with ORA-03206 error.

Because oracle will allow only up to 32 GB of datafile size limit with 8k blocks, maximum data blocks per datafile : 2^22 -1 = 4194303. So oracle simply won’t allow you in this case.

If you wish more than 32 GB datafile with 8k blocks than your datafile should be create with BIGFILE keyword.

Calculations are as follows:

Oracle allows max number of datafile is 65533 and Maximum data blocks per datafile is 2^22 -1 = 4194303 blocks.

Formula to calculate max datafile size

max datafile size = db_block_size * maximum number of blocks

In our case:

max datafile size = 8 * 4194303 = 33554424/1024 = 32767.9921875 MB/1024 = 31.99999237060547 GB, i.e. 32 GB

Formula to calculate Max tablespace size:

Max tablespace size = 32 GB * max database file = 32 GB * 65533 = 2097056 GB/1024 = 2047.90625 TB

Error Log:

SQL> create temporary tablespace temp02
tempfile '/u02/oradata/db1/temp2_01.dbf' size 30G autoextend on maxsize 70G; 2
create temporary tablespace temp02
*
ERROR at line 1:
ORA-03206: maximum file size of (9175040) blocks in AUTOEXTEND clause is out of range

Solution:

Create tempfile with less than 32 GB size or use BIGFILE keyword.

In above example, I am facing ORA-03206 with tempfile creation, you may face the problem with datafile too. Same solution will be applicable.

Other area’s you may get same error:

If your existing datafile exceeds limit of 32 GB, than you will receive same error message, solution is to add new SMALLFILE datafile to the associated tablespace.

Stay Tune. 🙂

Oracle 12c Logo

ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Published by Jignesh Jethwa

Oracle Error:

ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Scenario:

Here, I was trying to access C##SCOTT user immediately after creation of it, Above mentioned oracle error itself self-explanatory. I had forgotten to grant CONNECT privilege to C##SCOTT user. After granting mentioned privileges, I was able to connect C##SCOTT user.

Error logs and solution:

SQL> create user C##SCOTT identified by tiger default tablespace users;
User created.
SQL> exit

Trying to connect C##SCOTT user:

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 17 05:13:19 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Connect as sysdba and grant required privileges:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> grant connect,resource to C##SCOTT;
Grant succeeded.
SQL> exit

Successfully connect C##SCOTT after assigning required privileges.

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL> show user;
USER is "C##SCOTT"

It was only problem with required privilege.

Oracle 12c Logo

ORA-01950: no privileges on tablespace ‘USERS’

Published by Jignesh Jethwa

Oracle Error:

ORA-01950: no privileges on tablespace ‘USERS’

Scenario:

I am trying to insert values in EMP table of C##SCOTT schema, but getting filled with above mentioned oracle error, Error itself self explanatory. I have forgotten to assign quota space for C##SCOTT on USERS tablespace. After assigning quota space on USERS, everything was smooth.

Error logs and solution:

On C##SCOTT schema:

SQL> show user;
USER is "C##SCOTT"
SQL> insert into EMP values(1,'scott',1234567898);
insert into EMP values(1,'scott',1234567898)
 *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Connect as sysdba and assign quota to C##SCOTT on USERS tablespace:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> show user;
USER is "SYS"
SQL> alter user c##scott quota unlimited on users;
User altered.

On C##SCOTT schema again:

SQL> show user;
USER is "C##SCOTT"
SQL> insert into EMP values(1,'scott',1234567898);
1 row created.

In my case, it was problem with quota allocation for C##SCOTT schema on USERS tablspace.

Oracle 11g Logo

ORA-01149:cannot shutdown-file 1 has online backup set OR ORA-10873:file 1 needs to be either taken out of backup mode or media recovered

Published by Jignesh Jethwa

Oracle Error: While shutdown your database you may encounter following oracle error.

SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/RTS/system01.dbf’

Cause:

If you try to shutdown database when database is in hot backup mode, you will encountered above mentioned error. Kindly verify with your team mates with hot backup activity, Oracle will not allow you to shutdown your database in above scenario, i.e. shutdown, shutdown immediate, shutdown transactional OR startup force will be failed with ORA-01149 oracle error.

Root cause detection:

Following query will help you to understand which oracle tablespace datafile is in hot backup mode.

SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;
TABLESPACE_NAME                STATUS
------------------------------ ------------------
SYSAUX                         ACTIVE
SYSTEM                         ACTIVE
UNDOTBS1                       ACTIVE
USERS                          ACTIVE

Status of the all datafile seems to be active. i.e. hot backup mode.

Solution:
Issue following SQL command in order to move out database from hot backup mode:

SQL> alter database end backup;
Database altered.

Now you can gracefully shutdown your database.

 

Cause: ORA-10873

In case you force fully trying to shutdown database with “shutdown abort” OR instance crashes for some reason OR database have not been shutdown gracefully than you would encountered “ORA-10873” error while next startup of your database.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 1040189872 bytes
Database Buffers 419430400 bytes
Redo Buffers 7958528 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS/system01.dbf'

Solution:

Bring out your database from hot backup mode with the help of following SQL:

SQL> alter database end backup;
Database altered.

OR, you can individually take out tablespaces from hot backup mode:

SQL> alter tablespace system end backup;
Tablespace altered.

Open database:

SQL> alter database open;
Database altered.

Your database opened in READ WRITE mode.

Stay Tune. 🙂

Oracle 11g Logo

ORA-16038-log 4 sequence# 10702 cannot be archived-ORA-19809: limit exceeded for recovery files-ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit

Published by Jignesh Jethwa

My one of the RAC RMAN backup failed with above mentioned oracle error:

RMAN script and its error logs are as follows:

RMAN> #Script to run Daily Incremental Backup...
2> #Created 17 June 2014
3> #Author : Jignesh Jethwa
4> #Version 1.0
5>
6> run
7> {
8> backup as compressed backupset incremental level 1 database plus archivelog;
9> delete noprompt archivelog until time 'sysdate -1' backed up 1 times to device type disk;
10>
11> #Delete Obsolte Backups
12> delete noprompt force obsolete;
13> }
14>
Starting backup at 13-SEP-14
ORACLE error from target database:
ORA-16038: log 4 sequence# 10702 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 4 thread 2: '+DATA/****/onlinelog/group_4.270.818354365'
ORA-00312: online log 4 thread 2: '+DATA/****/onlinelog/group_4.271.818354367'
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=953 instance=Instance_name1 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=10701 RECID=18396 STAMP=858207615
channel ORA_DISK_1: starting piece 1 at 13-SEP-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 09/13/2014 23:00:07
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit
Recovery Manager complete.

According to above error logs, Size allocated for database recovery file destination is exceed with its current limit.

Work around for the above mentioned error is very simple, Re-size ‘db_recovery_file_dest_size’ oracle parameter and instantiate backup again, It will work.

SQL> show parameter db_recovery_file_dest;
SQL> show parameter db_recovery_file_dest_size;

Note:

If you are on RAC environment, than you have to change same parameter on both the instances, otherwise another instance will not come up after next reboot.

Stay Tune. 🙂

Oracle 11g Logo

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

Published by Jignesh Jethwa

While RMAN full backup, today i have encountered “RMAN-06059: expected archived log not found, loss of archived log compromises recoverability”

RMAN error.
Solution for the mentioned error is to Crosscheck your archive logs from RMAN prompt and delete those expired logs who are not needed anymore.

While crosscheck archivelogs, RMAN will marked archives as Expired in his repository (i.e. controlfile or catalog) those who are not physically present there. So we can easily delete expired archives from controlfile or catalog repository.

Error log:

RMAN> RUN
 2> {
 3> ALLOCATE CHANNEL backup1 DEVICE TYPE DISK FORMAT 'Z:\RMAN\%U';
 4> BACKUP DATABASE PLUS ARCHIVELOG;
 5> }
using target database control file instead of recovery catalog
 allocated channel: backup1
 channel backup1: SID=70 device type=DISK
Starting backup at 10-SEP-14
 current log archived
 released channel: backup1
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of backup plus archivelog command at 09/10/2014 18:22:35
 RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
 ORA-19625: error identifying file E:\ORACLE\--ORACLE_SID--\ORAARCH\1_35338_778192583.DBF
 ORA-27041: unable to open file
 OSD-04002: unable to open file
 O/S-Error: (OS 2) The system cannot find the file specified.
RMAN> CROSSCHECK ARCHIVELOG ALL;

Above command will give information of expired RMAN repository after verification. (i.e. in catalog or controlfile)
Above will marked archives as Expired who are not available physically and who are not required for any kind of recovery..

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Above command will delete all archive logs who are marked as expired while crosscheck.

Oracle 11g Logo

ORA-01105: mount is incompatible with mounts by other instances – ORA-19808: recovery destination parameter mismatch

Published by Jignesh Jethwa

After reboot of both the RAC nodes, we encountered “ORA-01105: mount is incompatible with mounts by other instances” & “ORA-19808: recovery destination parameter  mismatch” oracle error, after diagnosis i came to know about the size of the db recovery parameter, i.e. db_recovery_file_dest_size is mismatch over the nodes.

After setting identical size of the db recovery file destination, infected node up and running smooth as expected.

Error Logs:

 SQL> startup;
 ORACLE instance started.
 Total System Global Area 4275781632 bytes
 Fixed Size 2235208 bytes
 Variable Size 1694500024 bytes
 Database Buffers 2566914048 bytes
 Redo Buffers 12132352 bytes
 ORA-01105: mount is incompatible with mounts by other instances
 ORA-19808: recovery destination parameter mismatch

DB recovery parameter ‘db_recovery_file_dest_size’ verification on 2nd instance by issuing the following command:

 SQL> show parameter recovery
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_recovery_file_dest string +DATA
 db_recovery_file_dest_size big integer 150G
 recovery_parallelism integer 0

DB recovery parameter ‘db_recovery_file_dest_size’ verification on 1st instance by issuing the following command:

 SQL> show parameter recovery
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_recovery_file_dest string +DATA
 db_recovery_file_dest_size big integer 100G
 recovery_parallelism integer 0

According to above verification logs, highlighted area mismatched over the RAC nodes.

Workaround: Set ‘db_recovery_file_dest_size’ at 2nd instance to 100GB, as follows:

SQL> alter system set db_recovery_file_dest_size = 100G scope=spfile;
 System altered.

Bounce 2nd instance by normal shutdown and restart, as follows:

SQL> shutdown

 ORA-01507: database not mounted
 
 ORACLE instance shut down.
SQL> startup
 ORACLE instance started.
 Total System Global Area 4275781632 bytes
 Fixed Size 2235208 bytes
 Variable Size 1694500024 bytes
 Database Buffers 2566914048 bytes
 Redo Buffers 12132352 bytes
 Database mounted.
 Database opened.

By above workaround, Infected node up and running smooth.

 SQL> select inst_name from v$active_instances;
 INST_NAME
 ------------------------------------------------------------
 database1:>>Instance_I<<
 database2:>>Instance_II<<

Stay Tune 🙂

Oracle 11g Logo

HP Data Protector Backup Job failed with ORA-01157: cannot identify/lock data file 516 – see DBWR trace file

Published by Jignesh Jethwa

HP data protector daily database backup job failed after restoring database, with “ORA-01157: cannot identify/lock data file 516 – see DBWR trace file” OR “ORA-01110: data file 516: ‘/oracle//sapdata1/temp_4/temp.data4′” oracle errors.

Solution: Drop and recreate specified tempfile at desired location mentioned in error.

HP data protector backup job logs are as follows:

 BR0051I BRBACKUP 7.20 (35)
 BR0055I Start of database backup: beomxhha.anf 2014-08-13 07.30.42
 BR0484I BRBACKUP log file: /oracle/ECP/sapbackup/beomxhha.anf
 BR0477I Oracle pfile /oracle//11203/dbs/init.ora created from spfile /oracle//11203/dbs/spfile.ora
 BR0280I BRBACKUP time stamp: 2014-08-13 07.30.47
 BR0301E SQL error -1157 at location BrDbfInfoGet-1, SQL statement:
 'OPEN curs_8 CURSOR FOR'
 'SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.BIGFILE, DF.FILE_NAME, DF.FILE_ID, NVL(DF.STATUS, ' '), NVL(DF.BYTES, 0), NVL(DF.AUTOEXTENSIBLE, 'NO'), NVL(DF.MAXBYTES, 0), NVL(DF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_DATA_FILES DF WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME UNION ALL SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.BIGFILE, TF.FILE_NAME, TF.FILE_ID * -1, NVL(TF.STATUS, ' '), NVL(TF.BYTES, 0), NVL(TF.AUTOEXTENSIBLE, 'NO'), NVL(TF.MAXBYTES, 0), NVL(TF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_TEMP_FILES TF WHERE TS.TABLESPACE_NAME = TF.TABLESPACE_NAME ORDER BY 1, 7'
 ORA-01157: cannot identify/lock data file 516 - see DBWR trace file
 ORA-01110: data file 516: '/oracle//sapdata1/temp_4/temp.data4'
 BR0314E Collection of information on database files failed
 BR0056I End of database backup: beomxhha.anf 2014-08-13 07.30.47
 BR0280I BRBACKUP time stamp: 2014-08-13 07.30.47
 BR0054I BRBACKUP terminated with errors

Work around for the mentioned error are as follows:

Note: I would request you to consider full backup of database before doing this activity, for future safety.

Verify ‘temp.data4’ tempfile available at specified location:

ll -l /oracle//sapdata1/temp_4/temp.data4

OR verify ‘temp_4’ directory exist or not. In case of ‘temp_4’ directory not exist recreate it with mkdir linux command from oracle user.

ll -l /oracle//sapdata1/temp_4/

Verify tempfile status with the help of following query:

 select file#,status,name from v$tempfile;
 OR
 select file_name,status from dba_temp_files;

Drop mentioned tempfile and recreate it, with the help of following SQL command:

alter database tempfile '/oracle//sapdata1/temp_4/temp.data4' drop;
 OR
 alter database datafile 516 OFFLINE DROP;
alter tablespace temp add tempfile '/oracle//sapdata1/temp_4/temp.data4' size 10G autoextend on maxsize unlimited;

After dropping and recreating tempfile, HP data protector test backup run successful.

Stay Tune. 🙂

Oracle 11g Logo

ORA-01113: file n needs media recovery- ORA-01110: data file n: ‘\SYSTEM01.data4′

Published by Jignesh Jethwa

After incomplete recovery of my one of the database, I have encounter “ORA-01113: file n needs media recovery” and “ORA-01110: data file n: ‘\SYSTEM01.data4′” while alter database to open mode with reset-logs. After diagnosis, I came to know that, while incomplete recovery oracle database ask for non existing archive log file, in this case provide current log file as an input to “RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;”.

In my case, Workaround for ORA-01113 & ORA-01110 are as follows:

Startup your database in mount state:

 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.
SQL> startup mount;
 ORACLE instance started.
 Total System Global Area 7917113344 bytes
 Fixed Size 2198064 bytes
 Variable Size 3976201680 bytes
 Database Buffers 3925868544 bytes
 Redo Buffers 12845056 bytes
 Database mounted.

Following query will help to retrieve name of the current redo log files, i.e. redo log belongs to active group.

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# AND G.STATUS = 'CURRENT';
 MEMBER
 --------------------------------------------------------------------------------
 /oracle/<oracle_sid>/origlogA/log_g17m1.dbf
 /oracle/<oracle_sid>/mirrlogA/log_g17m2.dbf

Above redo files will help to recover database.

Recover your database with the help of following command, when oracle suggested for non existing archive log with full path (i.e. invalid file) Ignore it and provide above redo log file as an input.
Note: There is no file available on specified path. i.e. <oracle_sid>arch1_66606_758161962.dbf

 SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
 ORA-00279: change 1041939961 generated at 08/11/2014 16:31:04 needed for thread 1
 ORA-00289: suggestion : /oracle/<oracle_sid>/oraarch/<oracle_sid>arch1_66606_758161962.dbf
 ORA-00280: change 1041939961 for thread 1 is in sequence #66606
 Specify log: {=suggested | filename | AUTO | CANCEL}
 /oracle/<oracle_sid>/origlogA/log_g17m1.dbf
 Log applied.
 Media recovery complete.

Media recovery completed successfully, While backup with controlfile OR in case of incomplete recovery, you need to open your database with reset-logs, its recommended.

 SQL> alter database open resetlogs;
 Database altered.
SQL> SQL> select open_mode from v$database;
 OPEN_MODE
 --------------------
 READ WRITE

Database open successfully.

After database recovery, Kindly consider full database backup for future safety.

Stay Tune. 🙂

Oracle 11g Logo

ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error & ORA-06512

Published by Jignesh Jethwa

After creating DBMS scheduler external job for Oracle RAC RMAN full database backup, I have encountered ORA-27369 & ORA-06512 oracle error while manual test run.

SQL> exec dbms_scheduler.run_job('RMAN_FULL_BACKUP');
BEGIN dbms_scheduler.run_job('RMAN_FULL_BACKUP'); END;
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

After diagnosing “ADDITIONAL_INFO” column from “ALL_SCHEDULER_JOB_RUN_DETAILS” dictionary view with the help of following query, Than i realize something went wrong with launching external job due to “login executable not setuid-root”.

SQL> select job_name, additional_info from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name='RMAN_FULL_BACKUP';
...
...
JOB_NAME
-----------------------------------------------------------------
ADDITIONAL_INFO
----------------------------------------------------------------------------------------------------
EXTERNAL_LOG_ID="job_173557_54754",
USERNAME="oracle"
RMAN_FULL_BACKUP
EXTERNAL_LOG_ID="job_197309_55531",
ORA-27369: job of type EXECUTABLE failed with exit code: Input/output error
STANDARD_ERROR="Launching external job failed: Login executable not setuid-root"

Solution:

Run root.sh script file on Database home with the help of root user, as follows:

-bash-4.1# whoami
root
-bash-4.1# cd /u01/app/oracle/product/11.2.0/db_1
-bash-4.1# ./root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
 ORACLE_OWNER= oracle
 ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
-bash-4.1#

After performing above solution, my DBMS scheduler external job ran successful.

Stay tune. 🙂

Oracle 11g Logo

ORA-00245: control file backup failed; target is likely on a local file system- RMAN-08132: WARNING: cannot update recovery area reclaimable file list

Published by Jignesh Jethwa

ORA-00245: control file backup failed; target is likely on a local file system — RMAN-08132: WARNING: cannot update recovery area reclaimable file list.

On Oracle 11gR2 RAC, I have encountered ORA-00245 & RMAN-08132 error while deleting archive logs manually from RMAN prompt, After diagnosis i realize snapshot control file backup location not being set to shared location. i.e. Shared Disk group.

In RAC environment, Snapshot control file backup location should be on shared disk group so that it can be accessible to all RAC nodes.

Work around for the mentioned error:

Verify snapshot control file location by RMAN prompt as with the help of below command:

RMAN> show snapshot controlfile name;

Configure it to the shared disk group as below:

[oracle@db2 bin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 29 01:30:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PDB (DBID=518531946)

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/snapcf.f';

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/snapcf_PROD021.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/snapcf.f';
new RMAN configuration parameters are successfully stored

Note:
+data is my shared Disk group.

After setting Snapshot controlfile backup location to shared disk group, deletion of archivelog from RMAN prompt is smooth.

Stay tune. 🙂

Oracle 12c Logo

ORA-19573: cannot obtain exclusive enqueue for datafile 3

Published by Jignesh Jethwa

While database restore, I got oracle error “ORA-19573: cannot obtain exclusive enqueue for datafile 3“. After diagnosis I came to know my database is in open mode (It was my mistake), and RMAN restore operation is trying to overwrite currently active version of datafile-3, so after database has been altered in mounted state, Restore database has been successful.
Note:

  • Also close the duplicate session from database where we trying to restore database.

Lets consider following hands on, In this I have tried to simulate same scenario:

// Trying to restore database while database is in open mode, It will throw an error: RMAN-03002, ORA-19870 & ORA-19573.
RMAN> restore database;

Starting restore at 18-JUN-14
using channel ORA_DISK_1

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9pgdjoln_.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9pgdjolj_.dbf
skipping datafile 13; already restored to file /u01/app/oracle/oradata/ORCL/FA0A923A8CCD0B2FE043CE6A7C736473/datafile/o1_mf_system_9qxm7bg1_.dbf
skipping datafile 14; already restored to file /u01/app/oracle/oradata/ORCL/FA0A923A8CCD0B2FE043CE6A7C736473/datafile/o1_mf_sysaux_9qxm7bmq_.dbf
skipping datafile 15; already restored to file /u01/app/oracle/oradata/ORCL/FA0A923A8CCD0B2FE043CE6A7C736473/datafile/o1_mf_users_9qxm8yd0_.dbf
skipping datafile 16; already restored to file /u01/app/oracle/oradata/ORCL/FA0AA072E8D30DA9E043CE6A7C73E432/datafile/o1_mf_system_9qxmgro6_.dbf
skipping datafile 17; already restored to file /u01/app/oracle/oradata/ORCL/FA0AA072E8D30DA9E043CE6A7C73E432/datafile/o1_mf_sysaux_9qxmgro0_.dbf
skipping datafile 18; already restored to file /u01/app/oracle/oradata/ORCL/FA0AA072E8D30DA9E043CE6A7C73E432/datafile/o1_mf_users_9qxmjvts_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9pgd6f0w_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9pgdh77f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9pgdh615_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/ORCL/datafile/undo2.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/jignesh/1mpb5857_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2014 12:54:43
ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/jignesh/1mpb5857_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 3
// Shutdown database & startup in mount state, as follows:  ( Note : We can startup and shutdown database from RMAN prompt )
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 3206836224 bytes
Fixed Size 2293496 bytes
Variable Size 1879048456 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16871424 bytes

// Now issue the following to restore database.
RMAN> restore database;
RMAN restoration continue…

// Open database after restore complete:
RMAN> alter database open;

Statement processed

RMAN> exit

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

 

Cheers!! Your database has been restored successfully and ready to use.

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

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M – Oracle 12c

Published by Jignesh Jethwa

Today morning, I got new excitement with oracle error: ORA-00838. Error message itself self-explanatory.

Cause: Existing MEMORY_TARGET value not enough to start database.

Solution: Increase MEMORY_TARGET value.

 

Consider following workaround:

[oracle@12c bin]$ sqlplus “/ as sysdba” Continue reading →

Oracle 12c Logo

ORA-38707: Media recovery is not enabled

Published by Jignesh Jethwa

Oracle Error: ORA-38707: Media recovery is not enabled

This error usually occurred when Media recovery is not enabled means database is not in archivelog mode. ( i.e. NoArchivelog mode ) After enabling it, you won’t face same error any more.

This tutorial will guide you to enable archivelog. Continue reading →

Oracle 12c Logo

ORA-65096: invalid common user or role name and ORA-65049: creation of local user or role is not allowed in CDB$ROOT

Published by Jignesh Jethwa

This error usually occurs due to we are trying to create user ( common user ) under root container. In oracle 12c there is two type of users: common user and local user.

Common users belongs to CBD’s as well as current and future PDB’s. It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

// Consider following example in which i am trying to create common user in container root. Continue reading →