Sunday, December 18, 2016

ORA-01516: nonexistent log file, data file, or temporary file - Oracle while resizing tempfile

Got below error while resizing the tempfile. 


sql> alter database tempfile '/user/app/db_home1/data3/db_home1/temp1.dbf' resize 12001m;

alter database tempfile '/user/app/db_home1/data3/db_home1/temp1.dbf' resize 12001m
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/user/app/db_home1/data3/db_home1/temp1.dbf"


sql> alter tablespace temp shrink tempfile '/user/app/db_home1/data3/db_home1/temp1.dbf' keep 12G;

Tablespace altered.

Monday, September 19, 2016

ORA-00600: internal error code, arguments: [13013], Table/Index row count mismatch ; SQL error 600 occurred when updating duplicate row in table.

My Oracle database started reporting Ora-600 errors

Upon checking the alert log and Oracle Support (Doc ID 1438920.1) , 

Turns out the Index was corrupted.. Ran the Analyze table command to analyze the problem table

ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Trace file shows below

Table/Index row count mismatch
table 384001 : index 384028, 0
Index root = tsn: 7 rdba: 0x02400a5a

The error says the indexes are still bad.

If you run the query through SQL*PLUS it will ask for a rdba number. Enter the value from error message '0x02400a5a' (no quotes). This will return a file number and a block number.

SQL> SELECT dbms_utility.data_block_address_file(
  2           to_number(trim(leading '0' from
  3  replace('&&rdba','0x','')),'XXXXXXXX')
  4         ) AS rfile#,
  5         dbms_utility.data_block_address_block(
         to_number(trim(leading '0' from
  6    7  replace('&&rdba','0x','')),'XXXXXXXX')
  8         ) AS block#
  9  FROM dual;
Enter value for rdba: 0x02400a5a
old   3: replace('&&rdba','0x','')),'XXXXXXXX')
new   3: replace('0x02400a5a','0x','')),'XXXXXXXX')
old   7: replace('&&rdba','0x','')),'XXXXXXXX')
new   7: replace('0x02400a5a','0x','')),'XXXXXXXX')

    RFILE#     BLOCK#
---------- ----------
         9       2650

Next run the following query:

select owner, segment_name, segment_type 
from  dba_segments 
where header_file = <rfile#>
  and header_block = <block#>;

This will give you the offending index to be dropped and recreated.

Wednesday, September 7, 2016

ORA-00245: control file backup failed; target is likely on a local file system

Error : ORA-00245: control file backup failed; target is likely on a local file system

Cause : the control file is saved on local file system., in case of RAC databases the control file snapshot should be saved on the shared location.

Solution : Make change in RMAN to point the control file snapshot to be saved under shared location




RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 09/07/2016 08:41:04
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 09/07/2016 08:41:04
ORA-00234: error in identifying or opening snapshot or copy control file
ORA-00202: control file: '+DB4_ORADATA'
ORA-17503: ksfdopn:2 Failed to open file +DB4_ORADATA
ORA-15045: ASM file name '+DB4_ORADATA' is not in reference form

Everything I run on the catalog database gives me the above error

Solution :

Make the change as sys user


PL/SQL procedure successfully completed.

this resolved the rman issue.. 

Thursday, September 1, 2016

"E575: viminfo: Illegal starting char in line:" while opening a file on Linux

E575: viminfo: Illegal starting char in line: 2015-04-03 12:35:01:64263:1428089701:1428089701:0:ok
E575: viminfo: Illegal starting char in line: 2015-04-03 12:36:01:44948:cronmon.ksh starting as pid 44948 with threshold of 120 seconds.

Reason : 
viminfo file got corrupted

Solution :
viminfo file is present in the user home location, as its hidden file it starts with .viminfo. The solution for this error is deleting this .viminfo file or rename it. This file will get generated again once you open a file.

Wednesday, August 10, 2016

ORA-24324 & ORA-01041 During Database startup

SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

Problem : Database was being started with old Oracle Home ( Probably the old Oracle Home was not renamed after database was upgraded) 

Solution : Set the environment correct and start the database from correct Oracle Home

ORA-01092 & ORA-39701 during Startup upgrade

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size                  2261968 bytes
Variable Size            6845107248 bytes
Database Buffers         3.3554E+10 bytes
Redo Buffers               84606976 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 122696
Session ID: 267 Serial number: 3

Issue : The parameter cluster_database was set to true. We need to change it to false to start the RAC database in upgrade database

SQL> startup
ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size                  2261968 bytes
Variable Size            6845107248 bytes
Database Buffers         3.3554E+10 bytes
Redo Buffers               84606976 bytes
Database mounted.
Database opened.
SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade 
ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size                  2261968 bytes
Variable Size            6845107248 bytes
Database Buffers         3.3554E+10 bytes
Redo Buffers               84606976 bytes
Database mounted.
Database opened.

Tuesday, May 17, 2016

Delete two hours old files in Sun OS

In Sun OS mmin doesnt work to delete the older files..

To delete older files on SunOS use "newer" option

oracle@sev2(1125) db2 /opt/app/db2/oracle/admin/db2/udump
$ date
Tue May 17 18:33:22 EDT 2016
oracle@sev2(1126) db2 /opt/app/db2/oracle/admin/db2/udump
$ touch 05171633 /tmp/TIMESTAMP 
[05=month 17 = date 1633 = 16:33 pm ( 2 hours older than current time)]
oracle@sev2(1128) db2 /opt/app/db2/oracle/admin/db2/udump
$ nohup find . ! -newer /tmp/TIMESTAMP -exec rm {} \;

Tuesday, May 10, 2016

Linux : how to mail all files under a directory

the below command can help sending all the files under a directory

cd /location/where/files/are/present/
for file in .* *; do mailx -s "${file}" -a "${file}" <email@id> < ${file}; done

FYI : each file will be sent as separate email.

Monday, March 28, 2016

Oracle : Recreate AWR for 11gR2 databases

Steps to recreate awr

For complete steps please refer to : How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)

save a copy of spfile
db1@SEV3(!) db1 /opt/app/db1
$ cd $OH/dbs
db1@SEV3(!) db1 /opt/app/db1/oracle/product/
$ sql
SQL*Plus: Release Production on Mon Mar 28 12:11:57 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='spfile_beforeawr.txt' from spfile;
File created.

In 10g and 11g , if sga_target is not 0, then in pfile or spfile set the following parameters:
The example below refers to spfile:
alter system set shared_pool_size = 5000m scope = spfile;
// Oracle recommended 200M, but my database didnt start and failed with error ORA-00093 .. so changed the size to 5000m
alter system set db_cache_size = 300m scope = spfile;
alter system set java_pool_size = 100 scope = spfile;
alter system set large_pool_size = 50 scope = spfile;
alter system set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile;
alter system reset memory_max_target scope=spfile;
####alter system set statistics_level=basic scope=spfile; // Do not run this, I got below error if we change the statistics_level=basic
##SQL> startup restrict
##ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings

Setting the parameter cluster_database only applicable in RAC environment Check actual shared pool and buffer cache usage in AWR to make sure the settings are correct
alter system set cluster_database = false scope = spfile;

Shutdown database and startup in restrict mode so that no transactions will occur while dropping the AWR repository:
sqlplus /nolog
connect / as sysdba
shutdown immediate
startup restrict

Drop and recreate the AWR objects The following scripts drop AWR tables and then recreates them. 
After recreating ,utlrp is run in order to validate all views and objects dependent on the AWR tables. On both 10g, 11g, and 12c drop AWR
start ?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;

Check to see if all the objects are dropped :
SQL> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';

>>>> If there are objects after running catnoawr.sql, drop them manually:
drop table WRH$_MV_PARAMETER;
drop table WRH$_MV_PARAMETER_BL;
drop table WRH$_DISPATCHER;
drop table WRM$_WR_USAGE;

Now create AWR:
start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql
start ?/rdbms/admin/execsvrm.sql

Note: If you receive the following errors when executing "?/rdbms/admin/execsvrm.sql", as follows:
start ?/rdbms/admin/execsvrm.sql
Fails with the following errors :
ERROR at line 1:
existing state of packages has been discarded
existing state of package body "SYS.DBMS_SWRF_ INTERNAL" has been
then recompile the object(s).
alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;
It is important to do this even if the object(s) (dbms_swrf_internal in this case) appear valid. You will
then need to reexecute
the "?/rdbms/admin/execsvrm.sql" script.

Create spfile from pfile we created before
SQL> create spfile from pfile='spfile_beforeawr.txt';

File created.

Restart database in normal mode
shutdown immediate

Check invalid objects exists are not , if exists then please compile it manually. As we have run utlrp.sql, any
invalid objects should already have been reported there:
spool objects.lst
set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version
from dba_registry
order by comp_name ;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type
from dba_objects
where status='INVALID' order by owner,object_type;
select owner,object_type,count(*)
from dba_objects
where status='INVALID'
group by owner,object_type order by owner,object_type ;
spool off
alter package <schema name>.<package_name> compile;
alter package <schema name>.<package_name> compile b ody;
alter view <schema name>.<view_name> compile;
alter trigger <schema).<trigger_name> compile ;

To take the AWR snapshots:
exec dbms_workload_repository.create_snapshot;
wait for 5 min
exec dbms_workload_repository.create_snapshot;

To create AWR report run the script:
start $ORACLE_HOME/rdbms/admin/awrrpt.sql

Thursday, February 25, 2016

Oracle : Prerequisite check "CheckActiveFilesAndExecutables" failed ; OPatch failed with error code 73

While patching the oracle database, the patch apply failed with below error

Following executables are active :
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /opt/app/oracle_db/oracle/product/oracle_db/cfgtoollogs/opatch/opatch2016-02-25_11-13-41AM_1.log

OPatch failed with error code 73

Cause :
Something is using the library , we need to find what is using it and kill it.

find the culprit using fuser

Solution :

oracle_db@sev2(174) oracle_db /opt/app/patches
$ fuser /opt/app/oracle_db/oracle/product/oracle_db/lib/
/opt/app/oracle_db/oracle/product/oracle_db/lib/ 118942m
oracle_db@sev2(175) oracle_db /opt/app/patches
$ ps -ef | grep 118942
oracle_db  95662  57918  0 11:15 pts/0    00:00:00 grep 118942
oracle_db 118942 118378  0 Jan21 ?        00:00:00 sqlplus -s              @/opt/app/oracle_db/backup/rman/common/info.sql  oracle_db
oracle_db@sev2(176) oracle_db /opt/app/patches
$ kill -9 118942

And then start the patch apply. Hope this resolves your problem

Monday, February 1, 2016

Oracle : How to add comments to Alert Log

To add comments to Alert log of Oracle Database, you can run the below command

SQL> execute sys.dbms_system.ksdwrt(2,'Hi, Hello World');

PL/SQL procedure successfully completed.

Note that the first parameter MUST be a 2.