ON CALL DBA SUPPORT

— Database blog

Archive for the ‘ADMINISTRATION’ Category

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

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 »

CLONE ORACLE 11g HOME

Posted by ssgottik on 09/04/2015

STEPS TO CLONE ORACLE 11g ORACLE_HOME  FROM ONE SERVER TO ANOTHER

 

STEP 01 : ZIP THE ORACLE HOME FILE FROM THE SOURCE

cd /u01/app/oracle/product

$tar  -pcvf   11204.tar   11.2.0.4

STEP 02 : SCP  .tar file to the target server

scp -p username@target.com:/u01/app/oracle/product/ .

STEP 03 : UNTAR THE .tar FILE

$tar -xvf 11204.tar

STEP04 : RUN CLONE.PL FROM ORACLE_HOME

vmlinux01::/u01/app/oracle/product/11.2.0.4/clone/bin> perl clone.pl ORACLE_BASE=’/u01/app/oracle/’
ORACLE_HOME=’/u01/app/oracle/product/11.2.0.4′ ORACLE_HOME_NAME=’11204_HOME’
./runInstaller -silent -clone -waitForCompletion “ORACLE_HOME=/u01/app/oracle/product/11.2.0”
“ORACLE_HOME_NAME=11204_HOME” -noConfig -nowait
Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-12-02_03-22-12PM. Please wait …Oracle
Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

You can find a log of this install session at:
/u01/app/oraInventory/logs/cloneActions2012-12-02_03-22-12PM.log
………………………………………………………………………………………. 100% Done.

Installation in progress (Tuesday, December 2, 2012 3:22:29 PM EST)
……………………………………………………………….
73% Done.
Install successful

Linking in progress (Tuesday, December 2, 2012 3:22:39 PM EST)
Link successful

Setup in progress (Tuesday, December 2, 2012 3:24:50 PM EST)
Setup successful

End of install phases.(Tuesday, December 2, 2012 3:24:56 PM EST)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central
inventory of this system.
To register the new inventory please run the script ‘/u01/app/oraInventory/orainstRoot.sh’ with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the “root” user.
#!/bin/sh
#Root script to run
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as “root”
3. Run the scripts

The cloning of 11204_HOME was successful.
Please check ‘/u01/app/oraInventory/logs/cloneActions2012-12-02_03-22-12PM.log’ for more details.
vmlinux01::/u01/app/oracle/product/11.2.0/clone/bin> cat /etc/oratab
#

STEP 05 : RUN orainstRoot.sh and root.sh as root user

#/u01/app/oraInventory/orainstRoot.sh
#/u01/app/oracle/product/11.2.0/root.sh

 

Thanks,

Satish.G.S

Posted in ADMINISTRATION | Leave a Comment »