ON CALL DBA SUPPORT

— Database blog

Archive for June, 2015

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode(During RMAN COLD BACKUP)

Posted by ssgottik on 17/06/2015

RMAN-03009: failure of backup command on c3 channel at 05/15/2012 18:49:11
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel c4: starting compressed full datafile backupset
channel c4: specifying datafile(s) in backupset

There is at least one file that is in need of recovery.
A COLD backup for a database running in NOARCHIVELOG mode has to be a CONSISTENT backup as it cannot be recovered after restore.

To confirm, mount the database and run the following SQL:

SQL >select count(1),fuzzy from v$datafile_header group by fuzzy;

COUNT(1) FUZ
——————– —
92 YES
SQL>Recover database ;

SQL >select count(1),fuzzy from v$datafile_header group by fuzzy;

COUNT(1) FUZ
——————– —
92 NO

 

Now take the backup of the database (RMAN COLD BACKUP)

 

Reference : Doc ID 373828.1

Advertisements

Posted in ADMINISTRATION | Leave a Comment »

CATPROC Oracle Database Packages and Types INVALID After upgrade

Posted by ssgottik on 17/06/2015

STEP 01:

For validating the catproc the following steps needs to be executed:

Step 01 a  –
SQL> shutdown immediate;

Step 01 b –
SQL> startup upgrade;

Step 01 c  –

conn / as sysdba
spool catalogproc.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
Column comp_id format a10
Column comp_name format a35
Column version format a12
Column status format a10
select comp_id, comp_name, version, status from dba_registry;
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE STATUS<>’VALID’ ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS;
spool off

Step 01 d –
SQL> shutdown immediate;
SQL> startup;

STEP 02:

If step 01 doesn’t works :

Reference note:782735.1

Execute the following script repeatedly while connected as SYS AS SYSDBA and perform the indicated operation until it returns ‘CATPROC can be validated now’.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = ‘CATPROC’;
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line(‘Please compile Invalid object ‘||object_name||’
Object_id ‘||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘CATPROC can be validated now’ );
end;
/

Once all of the dependent objects have been recompiled, validate CATPROC by executing the following command:
execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

In my case even after running this CATPROC did not become VALID . So when I ran the below query

column object_name format a25

SQL > select object_name, object_type, owner from dba_objects
where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
—————————— ——————- ——————————
PLAN_TABLE                         TABLE                                            SYS
PLAN_TABLE                         SYNONYM                                     PUBLIC
TOAD_PLAN_TABLE          TABLE                                             DORIS
BMC_SQLEX_PLAN_TABLE_S02   TABLE                            SYSTEM
PLAN_TABLE                        TABLE                                             SYSTEM
SQL_PLAN_TABLE_TYPE   TYPE                                             SYS
SQL_PLAN_TABLE_TYPE   SYNONYM                                  PUBLIC
PLAN_TABLE$                           TABLE                                      SYS
TOAD_PLAN_TABLE             SYNONYM                                 PUBLIC

 

This issue is caused by existence of :
– table SYS.PLAN_TABLE$
– public synonym PLAN_TABLE for the SYS.PLAN_TABLE$.
– and possibly (not always present) table SYS.PLAN_TABLE

In order to prevent the issue from occurring it is necessary to drop before starting the upgrade process. :
– the SYS owned table PLAN_TABLE$
– the public synonym PLAN_TABLE
– the SYS owned table PLAN_TABLE if it exists

As the SYS user, drop the PLAN_TABLE$, the public synonym PLAN_TABLE and if it exists
the table PLAN_TABLE in SYS schema (SYS.PLAN_TABLE is not always there) :

If you receive the following error while attempting to drop the plan table:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

ensure that the cursor_sharing parameter is set to EXACT.

This can be done at the session level using ‘alter system set cursor_sharing=exact scope=memory;’
SQL> drop table plan_table$;

Table dropped.

SQL> drop public synonym plan_table;

Synonym dropped.

if SYS.PLAN_TABLE exists :

drop table plan_table;

SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS

Then re-create the PLAN_TABLE
$ sqlplus ‘/ as sysdba’
SQL> @?/rdbms/admin/catplan.sql — creates a public plan table as a global temporary table accessible from any schema
SQL> @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
SQL> @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
SQL> @?/rdbms/admin/prvtspa.plb — reload dbms_sqlpa (Note use prvtspao.plb if prvtspa.plb is not present).
SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
PLAN_TABLE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS
PLAN_TABLE$ TABLE SYS

Posted in ADMINISTRATION | Leave a Comment »

ORA-01157: cannot identify/lock data file 202 – see DBWR trace file

Posted by ssgottik on 17/06/2015

ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 – see DBWR trace file
ORA-01110: data file 202: ‘/u10/oradata/orcl01/temp_02.dbf’

STEP 01: Drop the particular datafile:

SQL>alter database tempfile ‘/u10/oradata/orcl01/temp_02.dbf’ drop;

STEP 02 : Add the temp file :

SQL>ALTER TABLESPACE TEMP1 add tempfile ‘/u10/oradata/orcl01/temp_02.dbf’ size 10G;

Posted in ADMINISTRATION | Leave a Comment »

FLUSING THE SINGLE SQL STATEMENT FROM THE SHARED POOL

Posted by ssgottik on 12/06/2015

FLUSING THE SINGLE SQL STATEMENT FROM THE SHARED POOL:

Till 10.2.0.2:
————–
SQL > ALTER SYSTEM FLUSH SHARED_POOL;

In 10.2.0.4:
————
The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

NOTE : RDBMS patch 5614566

FROM 11g:
———
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = ‘9k2thjfjykcb%’;

ADDRESS HASH_VALUE
—————- ———-
000000085FD77CF0 808321886

SQL> exec DBMS_SHARED_POOL.PURGE (‘0000000090DA1C68, 809121806’, ‘C’);

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘9k2thjfjykcb%’;

No rows selected

Posted in ADMINISTRATION | Leave a Comment »