Monthly Archives: December 2013

ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service

-bash-3.2$ sqlplus / as sysdba SQL*Plus: Release Production on Mon Dec 30 11:22:15 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service 1.First of all check services by command crs_stat –t Than try […]

RMAN-06059: expected archived log not found

trying to fix it by crosscheck:run {allocate channel c1 type disk ;crosscheck archivelog all ;release channel c1 ;}

Creating Procedures/Dbms_sql/Dbms_job/Triggers

Creating Procedures/Dbms_sql/Dbms_job/Triggers from Anar Godjaev

Oracle SQL

Oracle SQL from Anar Godjaev

Stale Statitics

DECLARE  ObjList dbms_stats.ObjectTab;  BEGIN  dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);  FOR i in ObjList.FIRST..ObjList.LAST  LOOP  dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);  END LOOP;  END;  /

Gather Histogram statistics

declare olist dbms_stats.objectTab; begin dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> ‘FOR ALL INDEXED COLUMNS’,options=>’GATHER’,objlist=>olist);  dbms_output.put_line(‘Objects Analyzed: ‘ || olist.COUNT );  if olist.COUNT > 0 then  FOR x in 1..olist.COUNT LOOP  dbms_output.put_line(‘Object Name: ‘ || olist(x).objname );  END LOOP;  end if;  end; / declare olist dbms_stats.objectTab; begin dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> ‘FOR ALL INDEXED COLUMNS’,options=>’GATHER AUTO’,objlist=>olist); dbms_output.put_line(‘Objects Analyzed: ‘ || olist.COUNT ); if olist.COUNT […]

history of your BACKUP and RESTORE operations

SQL> select to_char(start_time, ‘dd-mon-yyyy@hh24:mi:ss’) “Date”, 2 status, 3 operation, 4 mbytes_processed 5 from v$rman_status vs 6 where start_time > sysdate -1 7 order by start_time 8 /

Check status of rman backups

col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time, to_char(END_TIME,’mm/dd/yy hh24:mi’)   end_time, elapsed_seconds/3600                   hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;


Contraints from Anar Godjaev

Table Partitions

Table Partitions from Anar Godjaev

12c Cloud Control: How to Modify the Password for SYSMAN

1. Stop all the OMS: cd <OMS_HOME>/bin emctl stop oms Execute the same command on all the OMS machines including the primary OMS machine. Do not include ‘-all’ as the Admin Server needs to be up during this operation. 2. Modify the SYSMAN password: cd <OMS_HOME>/bin emctl config oms -change_repos_pwd 3. Stop the Admin server […]

downgrading a 11g database to a previous version

1. Shut down the database and start it up in the downgrade mode. SQL> startup downgrade 2. Spool the results of the downgrade script. SQL> spool downgrade.log 3. Execute the downgrade script, called catdwgrd.sql. SQL> @catdwgrd.sql 4. After the catdwgrd.sql script finished executing, shut down the database cleanly. SQL> shutdown immediate 5. Start up the […]

User and Password Management

User and Password Management from Anar Godjaev

Parallel Server

Parallel Server from Anar Godjaev

OMS Startup

$ORA_GRID_HOME/opmn/bin/opmnctl startall $ORA_GRID_HOME/opmn/bin/opmnctl stopall

11gR2 Grid Infrastructure Offline Targets

Installation of Oracle 11gR2 Grid Infrastructure (formally clusterware) installation, will notice that serveral resources are offline. expected behavior refer to metalink 1068835.1.

multiplexing oracle ASM control files

SQL> show parameter control_files; NAME TYPE VALUE ————— ———– —————————— control_files string +FLASH/ANARDB/controlfile/current.256.710173383 SQL> alter system set control_files=’+DATA’,’+FLASH’ scope=spfile ; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup nomount ; ORACLE instance started. Total System Global Area 267227136 bytes Fixed Size 2212496 bytes Variable Size 222301552 bytes […]

Performance Tuning

Performance Tuning from Anar Godjaev

Tuning SGA

Tuning SGA from Anar Godjaev

ORA-12012: error on auto execute of job “ORACLE_OCM”.”MGMT_CONFIG_JOB_2_1″

Following errors in the alert.log file: ORA-12012: error on auto execute of job “ORACLE_OCM”.”MGMT_CONFIG_JOB_2_1″ ORA-29280: invalid directory path This messages happen because of the OCM collection database job is unable to access the directory location where the OCM data is written . you do not use OCM delete the OCM configuration from the database: Log […]