Tuesday, 16 December 2014

opatch version /bin/java is not a valid executable for this platform. OPatch cannot proceed

[oracle@standby OPatch]$ opatch version
/bin/java is not a valid executable for this platform. OPatch cannot proceed!
OPatch returns with error code = 1

[oracle@standby OPatch]$ cd /usr/bin/j
jade         join         jpegicc      jpegtran     jscal        jv-convert   jwhois
java         jpeg2ktopam  jpegtopnm    jsattach     jstest       jvmjar

[oracle@standby OPatch]$ su -
Password:

[root@standby ~]# ln -s /usr/bin/java /bin/java

[root@standby ~]# exit
logout

[oracle@standby OPatch]$ opatch version
OPatch Version: 11.2.0.3.6

OPatch succeeded.

[oracle@standby OPatch]$

Wednesday, 24 September 2014

change oracle database character set

change oracle database character set from WE8MSWIN1252 to AL32UTF8 and from AL32UTF8 to WE8ISO8859P1 :-

[oracle@netmindtwcint ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 24 17:10:31 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';  2    3

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8MSWIN1252


SQL> UPDATE PROPS$
SET VALUE$ = 'AL32UTF8'
WHERE NAME = 'NLS_CHARACTERSET';  2    3

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             327157920 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> SELECT *
  2  FROM V$NLS_PARAMETERS
  3  WHERE PARAMETER = 'NLS_CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8


SQL> we8iso8859p1
SP2-0734: unknown command beginning "we8iso8859..." - rest of line ignored.
SQL> UPDATE PROPS$
  2  SET VALUE$ = 'WE8ISO8859P1'
  3  WHERE NAME = 'NLS_CHARACTERSET';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>  startup;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             327157920 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL>  SELECT *
  2  FROM V$NLS_PARAMETERS
  3  WHERE PARAMETER = 'NLS_CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1




Thursday, 24 July 2014

oracle emctl is not working

if oracle emctl is not working or not starting .Need to check down one by one :-

1. is emctl  start responds ?
2. if yes , then check 1158 port is open or not ?
3. if port is not opening then request to IT team to open it , if its opened then move to next step .
4.check the firewall at your end , some times firewall prevents to start the OracleDbConsoleorcl services .
5.check the dbsnmp account status ,it may be locked or its password has been expired, unlock it and if require change the password.
6. If everything is OK , then reconfigure the OEM.

Here i am sharing the steps to configre the EM from scratch , even if the database is running , please get approval to before run on Production :-

1. Please set the following environment variables
ORACLE_SID
ORACLE_HOSTNAME

2.Delete DB Control Configuration Files Manually:
- Remove the following directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

3. Delete DB Control Repository Objects Manually
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management
objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

The above will completely delete the DB Control repository from the database;
under certain circumstances (e.g. you want to recreate the repository later on)
the following statements may be sufficient to remove the repository:

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and
management objects:
SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

4. Create the DB Control Repository Objects and Configuration Files
<ORACLE_HOME>bin/emca -config dbcontrol db -repos drop
<ORACLE_HOME>bin/emca -config dbcontrol db -repos create

5. Then check the following:
5.1 emctl start dbconsole (any errors?)
5.2 emctl status dnconsole
5.3 emctl status agent
5.4 emctl upload

Monday, 21 July 2014

steps to minimize the archive log generations

 steps to minimize the archive log generations ,Few more recommendations, see below:- 

1) Regarding the tables that are involved in this data activity/loads. 
Please alter the DDL of the tables to avoid redo log generation using options like NOLOGGING. It should boost DB performance significantly.must use  append and nologging hint in the insert query.
However, with this kind of change, you should do RMAN database backups immediately after your db loads are finished (for disaster recovery in the future, if needed).

2) Use GLOBAL TEMPORARY tables to store temp activity instead of regular tables. It should cut down redo generation & improve DB performance.

3) Spread your data load & update jobs to different timings, particularly when working on the same tables. It should avoid queuing-up/wait events.

4) Again, ensure redo logs/temp files are stored under different mount points than the data files (basically different controller on the storage array). Otherwise, there would be lot of contention with the I/O activity resulting in slowness.


install/configure em for oracle database

Here i am sharing the steps to configre the EM from scratch , even if the database is running , please get approval to before run on Production :-

1. Please set the following environment variables
ORACLE_SID
ORACLE_HOSTNAME

2.Delete DB Control Configuration Files Manually:
- Remove the following directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

3. Delete DB Control Repository Objects Manually
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management
objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

The above will completely delete the DB Control repository from the database;
under certain circumstances (e.g. you want to recreate the repository later on)
the following statements may be sufficient to remove the repository:

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and
management objects:
SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

4. Create the DB Control Repository Objects and Configuration Files
<ORACLE_HOME>bin/emca -config dbcontrol db -repos drop
<ORACLE_HOME>bin/emca -config dbcontrol db -repos create

5. Then check the following:
5.1 emctl start dbconsole (any errors?)
5.2 emctl status dnconsole
5.3 emctl status agent
5.4 emctl upload

Tuesday, 3 June 2014

restore UNDO tablespace from RMAN

[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:01:08 2014


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data1/shiven01/dbs/arch
Oldest online log sequence     1
Current log sequence           3
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             163580064 bytes
Database Buffers          364904448 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> alter database archivelog;


Database altered.


SQL> alter database open;


Database altered.


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data1/shiven01/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 3 11:02:28 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: SHIVEN01 (DBID=900949679)


RMAN> backup database;


Starting backup at 03-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/data1/shiven01/dbs/sysaux01.dbf
input datafile file number=00001 name=/data1/shiven01/dbs/system01.dbf
input datafile file number=00003 name=/data1/shiven01/dbs/undotbs01.dbf
input datafile file number=00004 name=/data1/shiven01/dbs/users01.dbf
input datafile file number=00005 name=/data1/shiven01/dbs/indx01.dbf
input datafile file number=00006 name=/data1/shiven01/dbs/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-14
channel ORA_DISK_1: finished piece 1 at 03-JUN-14
piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp tag=TAG20140603T110238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-JUN-14
channel ORA_DISK_1: finished piece 1 at 03-JUN-14
piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_ncsnf_TAG20140603T110238_9rtqjkty_.bkp tag=TAG20140603T110238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JUN-14


RMAN> exit




Recovery Manager complete.
[oracle@shiven instance]$ rm -f /data1/shiven01/dbs/undotbs01.dbf
[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:03:26 2014


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/data1/shiven01/dbs/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             163580064 bytes
Database Buffers          364904448 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 3 11:04:25 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: SHIVEN01 (DBID=900949679, not open)


RMAN> restore tablespace UNDOTBS1;


Starting restore at 03-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK


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 00003 to /data1/shiven01/dbs/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp
channel ORA_DISK_1: piece handle=/data1/shiven01/flash_recovery_area/SHIVEN01/backupset/2014_06_03/o1_mf_nnndf_TAG20140603T110238_9rtqhqqc_.bkp tag=TAG20140603T110238
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 03-JUN-14


RMAN> recover tablespace UNDOTBS1;


Starting recover at 03-JUN-14
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01


Finished recover at 03-JUN-14


RMAN> exit




Recovery Manager complete.
You have new mail in /var/spool/mail/oracle
[oracle@shiven instance]$ sqlplus sys/sys as sysdba


SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 3 11:05:43 2014


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter database open;


Database altered.



Thursday, 1 May 2014

monitoring oracle datapump jobs

Scripts to monitoring oracle datapump jobs :-

sqlplus / as sysdba
SET lines 200
COL owner_name FORMAT a20;
COL job_name FORMAT a20
COL state FORMAT a15
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

SELECT * FROM DBA_DATAPUMP_SESSIONS;

select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

select 
   substr(sql_text,instr(sql_text,'into "'),30) table_name, 
   rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from   
   sys.v_$sqlarea 
where  
   sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;


Wednesday, 30 April 2014

renice oracle

bash-2.05# renice -n -19 -u oracle
bash-2.05# renice -n -19 -p 5022
renice -10 -u oracle

nls_date_format showing only DD-MON-RR

On a particular server , select sysdate from dual was showing only DD-MON-RR not full time stamp .

when i checked the nls_date_format , it was set as :-

nls_date_format                      string      DD-MON-RR HH.MI.SS AM

The issue was with parameter file in which the following parameters were set :-

*.fixed_date='2014-05-02'

and

*.nls_date_format='DD-MON-RR HH.MI.SS AM'

then , I commented the *.fixed_date='2014-05-02' and reset the nls_date_format and bounce the DB.


Wednesday, 9 April 2014

sqlplus: error cannot restore segment prot after reloc: Permission denied

Problem : sqlplus: error while loading shared libraries: /opt/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

The server is Suse Linux .

Solution:- [root@localhost ~]# chmod -R 777  /opt/oracle
[root@localhost ~]# chown -R oracle:oinstall /opt/oracle
[root@localhost ~]# chcon -t textrel_shlib_t '/opt/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1'

Wednesday, 12 March 2014

IO calibrate statistics are missing in oracle explain plan

IO calibrate statistics are missing  in explain plan, then below mentioned steps should be executed one by one :-

set these parameters

1. alter system set filesystemio_options=asynch scope=spfile
2. alter system set disk_asynch_io = true scope=spfile;
3. bounce the db
4. Ensure asynchronous I/O is enabled on all datafiles and tempfiles. The following query can be used to            verify asynchronous I/O for these files.

col name format a50
select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name='Data File' or filetype_name='Temp File');
/

5. run the calibration procedure :-

SET SERVEROUTPUT ON
DECLARE
  l_max_iops        PLS_INTEGER;
  l_max_mbps        PLS_INTEGER;
  l_actual_latency  PLS_INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.calibrate_io (
    num_physical_disks => 1,
    max_latency        => 20,
    max_iops           => l_max_iops,
    max_mbps           => l_max_mbps,
    actual_latency     => l_actual_latency);
   DBMS_OUTPUT.put_line ('l_max_iops       = ' || l_max_iops);
  DBMS_OUTPUT.put_line ('l_max_mbps       = ' || l_max_mbps);
  DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;

Note :- Here num_physical_disks should be confirmed from system admin and This should be set to the                     defined response time SLA for your application;
e.g., your 95th percentile response time SLA is 10secs.

6. then find the status of the calibration runs:-  select * from v$io_calibration_status;


   

Thursday, 6 March 2014

change oracle database name using nid

First of all start your database in mount state . Here I am changing the database name from orcl to orcl4. Then perform below steps :-

[oracle@host01 dbs]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 7 12:09:36 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@host01 dbs]$ nid target=sys/sys DBNAME=orcl4

DBNEWID: Release 11.2.0.1.0 - Production on Fri Mar 7 12:14:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database ORCL (DBID=1305795399)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/orcl/control01.ctl
    /u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database ID and database name ORCL to ORCL4? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1305795399 to 766881041
Changing database name from ORCL to ORCL4
    Control File /u01/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/orcl/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/tracetbs.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/tracetbs3.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/orcl/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to ORCL4.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL4 changed to 766881041.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@host01 dbs]$ ls -slhrt
total 36K
4.0K -rw-r--r-- 1 oracle oinstall 2.8K May 15  2009 init.ora
4.0K drwx------ 2 oracle oinstall 4.0K Mar 15  2012 peshm_DBUA0_0
4.0K -rw-rw---- 1 oracle oinstall 1.6K Mar 15  2012 hc_DBUA0.dat
4.0K drwx------ 2 oracle oinstall 4.0K Mar 15  2012 peshm_orcl_0
4.0K -rw-rw---- 1 oracle oinstall 1.6K Mar 15  2012 hc_orcl.dat
4.0K -rw-r----- 1 oracle oinstall   24 Mar 15  2012 lkORCL
4.0K -rw-r----- 1 oracle oinstall 2.0K Mar  2 17:17 orapworcl
4.0K -rw-r----- 1 oracle oinstall 2.5K Mar  7 12:05 spfileorcl.ora
4.0K -rw-r----- 1 oracle oinstall  947 Mar  7 12:06 initorcl.ora
[oracle@host01 dbs]$ rm -f orapworcl
[oracle@host01 dbs]$ cp initorcl.ora initorcl4.ora
[oracle@host01 dbs]$ rm -f spfileorcl.ora
[oracle@host01 dbs]$ vi initorcl4.ora ( Here change the dbname in pfile)
[oracle@host01 dbs]$ orapwd file=orapworcl4 entries=4 password=sys(create new password file)
[oracle@host01 dbs]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 7 12:16:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile=initorcl4.ora
ORACLE instance started.

Total System Global Area 1062965248 bytes
Fixed Size                  1341252 bytes
Variable Size             683673788 bytes
Database Buffers          373293056 bytes
Redo Buffers                4657152 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Alternatively , You can change the database name using alter database backup controlfile to trace command .For this step ,I will post again . 

Sunday, 2 March 2014

Things to consider before moving table

 The following thing can be potential problem for big size table move .

1. No transaction should access those tables which you are going to move otherwise there will be ORA error on application side .So better to use dbms_redifnation in this case.( dbms_redifnation is available only EE edition) .

2. Time estimation can be go wrong .It may be due to different storage type used ( some times in some scenario the moving table reside on attached hard disk level file system and target reside on NAS or SAN ). I have seen that even 100GB of table takes time around 4 hours although target tablesapce and source tablespace were on different mount point but on same storage ( it was SAN ) .

3. Always use DBverify command to check block level corruption on target datafile before moving large tables .Otherwise it stuck in the middle of operation and some times it gets failed .

4.you can not use index rebuild on BITMAP index on partition table , if it is invalid state , you have to drop and rebuild .

5. You can not move those table which LONG Rows .

6. Always take expdp of those tables which you going to move, so in the middle of operation , this activity failed , you can rolled back .

7. Always enable row movement for the table which is going to be move.

8. Move the table.

9. Disable row movement for the table .

configuring archive and backup

1. Log on the target server and find out database is in archive log or not :-

Export ORACLE_SID=<database_name>
Sqlplus / as sysdba
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled

2. Create respective directories for backup in the name of database on the backup location and grant permission on it .

mkdir –P /backup/$ORACLE_SID/flash_recovery_area

chmod –R 777 /backup/$ORACLE_SID/flash_recovery_area

chown –R oracle:oinstall /backup/$ORACLE_SID/flash_recovery_area


3. Configure database in archive log, recovery area ,recovery location  :-

SQL>create pfile from spfile;
(take backup of pfile)

SQL> select * from v$recovery_file;
(Here none of the datafile should be there)

SQL> show parameter recovery_file_dest;

SQL> alter system set log_archive_dest_1='LOCATION=/backup/$ORACLE_SID/flash_recovery_area' scope = both;

SQL> alter system set db_recovery_file_dest_size=(db_size+archivelog+20%);

SQL>shutdown immediate;

SQL> startup mount;

SQL>alter database archivelog;

SQL> alter database open;

check , your database is in archive log mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled

4.     Configure auto backup through crontab.

Sunday, 2 February 2014

automated statistics gathering failed in Oracle

Sometimes automated statistics job failed on large table or table partition segments. In that case , you have to find out the automatic stats job scheduling , its running time and failed times and how to resolve them .

By default , automatic stats job runs every weekday night between 22:00 hours and 06:00 hours.You can find out these information  by below queries :-

SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members;

SELECT window_name, start_time, duration FROM dba_autotask_schedule;

select * from dba_autotask_client;

SELECT client_name, job_status, job_start_time, job_duration, job_info
FROM dba_autotask_job_history
WHERE client_name like '%stats%'
ORDER BY job_start_time;


select Owner, job_name, program_name, schedule_name, schedule_type,
failure_count, last_start_date , comments
from dba_scheduler_jobs;

select window_name, repeat_interval,duration, window_priority,
next_start_date, last_start_date, Comments
from dba_scheduler_windows;

select operation||decode(target,null,null,'-'||target) operation
      ,to_char(start_time,'YY-MM-DD HH24:MI:SS.FF4') start_time
      ,to_char(end_time,'YY-MM-DD HH24:MI:SS.FF4') end_time
from dba_optstat_operations
order by start_time desc

If Automated Statistics Gathering working , then check :-

SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS LAST; --Tables.
SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.

Then check :-

select * from sys.dba_tab_modifications
where table_owner not in ('SYS','SYSTEM')
order by timestamp;

After that check the DBA_TAB_STATS_HIST table to see the history of stats stored  :-

select owner,table_name, partition_name,stats_update_time
from dba_tab_stats_history
where table_name='<tb_name>'  and owner=<user_name> ORDER BY stats_update_time DESC;

To resolve this issue , please  follow below instructions :-


sql> grant analyze any to <username> ;

sql> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;

sql> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

sql> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;

sql> EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'<username>', TabName=>'<tb_name>', Degree=>DBMS_STATS.AUTO_DEGREE, Granularity=>'AUTO') ;

sql> SELECT table_name object_name, partition_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics WHERE table_name = '<tb_name>';

Note :- Run the above steps before automated statistics gathering job .





Unable to recompile invalid package bodies after data pump import

Sometimes after IMPORT , the user is unable to recompile invalid package bodies after data pump import. This issue arises when sys objects has been also imported .

To solve this issue , Please see the below view :-

select * from user_errors ;

You may  found some privileges are missing . Then grant them :- 

grant execute on SYS.DBMS_LOCK to <usr_name>;

grant execute on SYS.DBMS_FLASHBACK to <usr_name>;

grant execute on SYS.DBMS_SYS_SQL to <usr_name>;


Sunday, 26 January 2014

configure DNS for Installtion of Oracle Grid Infrastructure RAC cluster

1.create these files :-  forward.zone,reverse.zone and named.rfc1912.zones into /var/named/chroot/var/named

2.content of forward.zone :-
$TTL 86400
@          IN     SOA    rac.  rac. (
                         42 ; serial (d. adams)
                         3H ; refresh
                        15M ; retry
                         1W ; expiry
                         1D ) ; minimum
@          IN   NS     rac.
rac       IN   A      192.168.72.128
localhost                     IN A    127.0.0.1
rac2                          IN A    192.168.72.129
rac-vip                       IN A    192.168.72.102
rac2-vip                      IN A    192.168.72.104
rac-cluster-scan              IN A    192.168.72.106
rac-cluster-scan              IN A    192.168.72.107
rac-cluster-scan              IN A    192.168.72.108

3.content of reverse.zone :-
$TTL 86400
@        IN     SOA    rac.  rac. (
                         42 ; serial (d. adams)
                         3H ; refresh
                        15M ; retry
                         1W ; expiry
                         1D ) ; minimum
@         IN   NS   rac.
128.72.168.192      IN   PTR  rac.
1.0.0.127           IN   PTR  localhost.
129.72.168.192      IN   PTR  rac2.
102.72.168.192      IN   PTR  rac-vip.
104.72.168.192      IN   PTR  rac2-vip.
106.72.168.192      IN   PTR  rac-cluster-scan.
107.72.168.192      IN   PTR  rac-cluster-scan.
108.72.168.192      IN   PTR  rac-cluster-scan.

4.content of named.rfc1912.zones :-
zone "rac" IN {
                 type master; 
                 file "forward.zone"; 
                  
};
zone "72.168.192.in-addr.arpa" IN {
                 type master;
                 file "reverse.zone";
                 };
5.change the ownership and owner of these files by below command :-

chown named:named *.*

6.create the named.conf into /var/named/chroot/etc

7.content of named.conf :-

options {
listen-on port 53 { 192.168.72.128; };
# listen-on-v6 port 53 { ::1; };
        directory "/var/named";
        recursion yes;
        };
logging {
        channel default_debug {
        file "data/named.run";
        severity dynamic;
        };
};
  include "named.rfc1912.zones";

8.change the ownership and owner of these files by below command :-

chown named:named *.*

9.config the file /etc/resolv.conf

10.content of /etc/resolv.conf

search rac
nameserver 192.168.72.128

11.change the ownership and owner of these files by below command :-

chown named:named *.*

12. start the named service :-

service named start

chkconfig named on

13.Test the DNS IP reachability :-

nslookup rac

nslookup rac2

nslookup rac-cluster-scan

14. contents of /etc/hosts file :-

cat /etc/hosts
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
#eth1 - PUBLIC
192.168.72.128 rac
192.168.72.129 rac2
#VIP
192.168.72.102 rac-vip
192.168.72.104 rac2-vip
#eth2 - PRIVATE
192.168.140.128 rac-pvt
192.168.140.129 rac2-pvt

Note :- Make all these changes to all node , by interchanging the node name .

VOILA ----> Now you ready to Oracle Grid Infrastructure Installation ;) Here I am sharing one snap :-


Thursday, 2 January 2014

direct path reads

To resolve the direct path reads wait event :-

Please check the below views :-

V$SESSION_EVENT ->  to identify sessions with high numbers of waits
V$SESSTAT ->  to identify sessions with high "physical reads direct" (statistic only present in newer Oracle releases)
V$FILESTAT ->  to see where the IO is occurring
V$SQLAREA ->  for statements with SORTS and high DISK_READS (which may or may not be due to direct reads)

Solution approcah :-

If the file indicates a temporary tablespace check for unexpected disk sort operations.
Ensure Parameter DISK_ASYNCH_IO -> is TRUE .
Ensure the OS asynchronous IO is configured correctly.
Check for IO heavy sessions / SQL and see if the amount of IO can be reduced.Better approach will be to look at the sql query that need to be tuned and need to run in parallel .
Please check with ASH reports to see the all the queries which have this particular wait event .
Ensure no disks are IO bound.

And in the last , Please tune the disk_asynch_io and filesystemio_options parameters .

Significant Virtual Memory Paging on Linux When None is Occurring in ADDM Reports

There will be  Significant Virtual Memory Paging on Linux When None is Occurring in ADDM Reports.

There may be following message in the ADDM report:

From  the ADDM report

FINDING 1: 100% impact (48628 seconds)
--------------------------------------
Significant virtual memory paging was detected on the host operating system.

RECOMMENDATION 1: Host Configuration, 100% benefit (32719 seconds)
ACTION: Host operating system was experiencing significant paging but
no particular root cause could be detected. Investigate processes that
do not belong to this instance running on the host that are
consuming significant amount of virtual memory. Also consider adding more
physical memory to the host.

First, review the following bugs which incorrectly show large swapping statistics that may cause incorrect ADDM message as per
above message.  And if applicable, apply the bugs.

For 10.2.0.5,  for Linux:

Note:12347332.8 Bug 12347332 - V$OSSTAT / ADDM incorrectly shows very large swapping/paging stats

This bug is specific to Linux.  So download the patch for Linux:
Patch:12347332

For 11.2, there is the following bug:      

Note:10220118.8 Bug 10220118 - Print warning to alert log when system is swapping

If available for your os, download following patch:
Patch:10220118