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 11.2.0.1.0 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
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 […]