shivenracdba

[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]$

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. OR...

 for i in `ls *.*`  do  gzip $i  done

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>/o...

 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 stor...

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...

[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                  13...

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;

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

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.

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'

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 ...

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=...

 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 a...

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 = bo...

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_...

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>;

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                   ...

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 t...

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 incor...