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