RMAN backup failing with RMAN-03009 / RMAN-10038

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later

Symptoms:

Database backup with rman and NetBackup/Tape backup failing with errors:

input datafile file number=00010 name=E:\ORADATA\P0003W\SYMYXAUD01.DBF
channel t1: starting piece 1 at 15-MAY-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 05/15/2012 07:50:04
RMAN-10038: database session for channel t1 terminated unexpectedly

Recovery Manager complete.

Cause:

Incompatible tape software being used to backup Oracle 10.2.0.1.0.
Solution:
 
There was an issue with the NetBackup/Tape Backup versions and Oracle database 10.2.0.1.

After upgrading to NetBackup 7.1, the backup is working with Oracle 10.2.0.1.
Contact the tape software vendor for further questions about compatibility with different Oracle versions,
the certification of Oracle database with third party tape software is performed by the tape software vendor.

Query to find the SCHEMA Size in oracle database

Here the query,


SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB

FROM dba_segments s
GROUP BY s.owner;

To find the DDL of schema's in Oracle Database

Command to find the DDL of Schema's:

select dbms_metadata.get_ddl( 'USER','username' ) from dual
     UNION ALL
     select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','username') from dual
          UNION ALL
          select dbms_metadata.get_granted_ddl('OBJECT_GRANT','username') from dual
               UNION ALL
               select dbms_metadata.get_granted_ddl('ROLE_GRANT','username') from dual;

How To Find Out The Size Of Database

How to find the size of database?



In general the size of the database is defined as total size of the physical datafiles.
The following query will help you for the space management of your database.

Code:

clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size (Mb) ' justify left format 9,999,999.99
column tbused heading 'Used (Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free (Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name,  (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/

ORA-01940: cannot drop a user that is currently connected

ORA-01940: cannot drop a user that is currently connected

SQL>drop user username cascade;
drop user username cascade

*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'ISL_DF' and p.addr (+) = s.paddr;

SID           SERIAL# STATUS    SPID

----------   ----------    --------       ------------
159               29          INACTIVE 12349

SQL>alter system kill session '159,29';

System altered.


SQL>!kill -9 12349            - (kill the process in OS level)


SQL>drop user username cascade;


User dropped.

ORA-12705: Cannot access NLS data files or invalid environment specified

ORA-12705: Cannot access NLS data files or invalid environment specified:

Question: I an getting the error "Resolving ORA-12705: Cannot access NLS data files or invalid environment specified" when I change my NLS_LANG setting.

The developers need to experiment with different character sets but when they issue export NLS_LANG and try to start SQLPLUS after that they get the ORA-12705 error.

Answer: First, The ORA-12705 error is indeed controlled by NLS_LANG, and the ORA-12705 commonly happens when Oracle does an automatic "alter session" at login time and discovers an invalid NLS_LANG setting. The Oracle documentation notes are not very helpful:

ORA-12705: "invalid or unknown NLS parameter value specified"

Cause: There are two possible causes:
- An attempt was made to issue an ALTER SESSION statement with an invalid NLS parameter or value.
- The NLS_LANG environment variable contains an invalid language, territory, or character set.

Action: Check the syntax of the ALTER SESSION command and the NLS parameter, correct the syntax and retry the statement, or specify correct values in the NLS_LANG environment variable.

Oracle also says that the ORA-12705 can happen under these conditions and MOSC has resolutions for these ORA-12705 conditions:

1. ORA-12705 with incorrect NLS_LANG parameter
2. ORA-12705 with incorrectly specified ORA_NLSx
3. ORA-12705 from incorrect Oracle install or configuration
4. ORA-12705 when using Special Character sets
5. ORA-12705 when connecting with SQL*Net
6. ORA-12705 during migrations
7. ORA-12705 when connecting with SQL*Plus
8. ORA-12705 when connecting with language pre-compilers
9. ORA-12705 during Export/Import

For ORA-12705 errors caused by invalid NLS_LANG settings, you need to verify that it is "unset" at the system-level:


Windows - The NLS_LANG must be unset in the Windows registry (re-named is best). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it.

Linux/UNIX - Here you simply issue the Linux command "unset NLS_LANG"

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

ORA-00230: operation disallowed: snapshot control file enqueue unavailable:
if Linux :-

find the Enqueue locks:

SELECT s.sid, username AS "User", program, module, action, logon_time "Logon", l.*
     FROM v$session s, v$enqueue_lock l

     WHERE l.sid = s.sid and l.type = 'CF' AND l.id1 = 0 and l.id2 = 2;

then kill the session with sid and Process# from v$session;

alter system kill session 'SID, Process#' immediately;

if Windows :-

Bug 7450366 RMAN Autobackup fails with unable to obtain snapshot enqueue (ORA-230)


This note gives a brief overview of bug 7450366.
The content was last updated on: 17-OCT-2011
Click here for details of each of the sections below.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 11.2
Versions confirmed as being affected • 11.1.0.7
• 10.2.0.5
• 10.2.0.4
• 10.2.0.3
• 10.2.0.2
• 9.2.0.1

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in • 11.2.0.1 (Base Release)
• 10.2.0.5.5 Patch Set Update

RMAN-08040: full resync skipped, control file is not current or backup

RMAN> register database;


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 02/02/2012 23:14:17
RMAN-08040: full resync skipped, control file is not current or backup

RMAN>
 
 
Cause:  trying you register the standby database to perform/configure backup?
 
SQL> select open_mode,controlfile_type from v$database;

OPEN_MODE CONTROL
______________________________________________
MOUNTED      STANDBY


Solution:

If yes then register the primary database instead of a standby database. Once you register the primary database, you can use the same catalog schema for the backup of physical standby database since the DBID of both the databases is same.

ORA-19554: ORA-27211: Failed to load Media Management Library

While taking rman backups may get error like this,

run {

allocate channel c1 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/......./....../....../oracle/bin64/tdpo.opt)'
format '%d_controlfile_%U.rbck';
backup current controlfile tag CONTROLFILE_DAY;
release channel c1;}
released channel: ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on a1 channel at 01/31/2012 16:37:38
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

if target servers already registered with preferred TSM servers,


Cause: More then one oracle_home without proper library soft link,

Actions: Create soft link for all oracle_home
Ex:
ln -sf /usr/lib/libobk64.a /u001/app/oracle/product/10.2.0/db_1/lib/libobk.a

ln -sf /usr/lib/libobk64.a /u001/app/oracle/product/11.1.0/db_1/lib/libobk.a

ln -sf /usr/lib/libobk64.a /u001/app/oracle/product/11.1.0/db_2/lib/libobk.a

ln -sf /usr/lib/libobk64.a /u001/app/oracle/product/11.2.0/db_1/lib/libobk.a

........
......
....

User export failed with error EXP-00008/ORA-00904: "MAXSIZE"/"OLEVEL": invalid identifier

User export failed with error EXP-00008/ORA-00904: "MAXSIZE"/"OLEVEL": invalid identifier

EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table DMI_DIST_COMP_RECORD_R

EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table DMI_DIST_COMP_RECORD_S

EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table DMI_DUMP_OBJECT_RECORD_S

EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table DMI_EXPR_CODE_S

EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table DMI_INDEX_R

Cause:
Depends upon the version mismatch.
like if you trying to take a export from 11.x.x.x.x home to 10.x.x.x.x dump file, you will get this error.

Solution:
If you want to take exp from db version 10.x.x.x.x serious, try it from some other database with same version.


example:
cd /opt/oracle/product/11.1.0.7/db_1/bin
./exp system/password@10gdb(tnshostname) full=y file=/oradata/export/prod007.dmp log==/oradata/export/prod007.log

you will get below error...
User export failed with error EXP-00008/ORA-00904: "MAXSIZE": invalid identifier




see Metalink ID :-741984.1

RMAN-03002, RMAN-06403, ORA-01034, ORA-27101: shared memory realm does not exist

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 01/04/2012 13:25:07
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Check your database is running or down by

$ps -ef | grep pmon

sekiazubck05.rman06> ps -ef | grep pmon
oracle 352464           1 0     Jun 16 - 42:56 ora_pmon_dbname
oracle 688150 962658 0 18:17:10 pts/0 0:00 grep pmon

it shows like this, your db is running. otherwise check your instance

ORA-00059: maximum number of DB_FILES exceeded

SQL> create tablespace 0362_363 DATAFILE '/oradata/rman/data/0362_01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 150M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

SQL> show parameter db_files
db_file_name_convert                string                             0
db_files                                      integer                          200

Cause: You are trying add more datafile then limit. the limit is 200 only.
Solution:
SQL>alter system set db_files = 256 scope = spfile;
 
SQL>shutdown immediate;
 
SQL> startup
 
SQL> sho parameter db_files
 db_files                               integer                             256

now ...
SQL>create spfile from pfile [path];

ORA-01092, ORA-01501, ORA-00450, ORA-00444, ORA-00020: maximum number of processes () exceeded

CREATE DATABASE D0308W

*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-00450: background process 'QMNC' did not start
ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
Process ID: 2192
Session ID: 27 Serial number: 3

Cause : Minimum default process required.
Solutiion: Increase process values to 150(default)

ORA-12560: TNS Protocol adapter error on Windows

ORA-12560: TNS Protocol adapter error

if you tried you connect database, it seems to be create a service,
open command prompt and run "oradim"

if you are going to create new db, service should be in started.
orelse
create new service like
C:\> oradim   -NEW -SID [dbname]-SYSPWD [syspassword] -STARTMODE auto
Instance started.


Now you can connect.