Monthly Archives: June 2014

Finding Redo log Gap


On primary database: select thread#,max(sequence#) from v$archived_log group by thread#; On standby database: select thread#,max(sequence#),applied from v$archived_log where applied=’YES’ group by thread#,applied; An alternative to running the two commands above is to run the following command on the standby database: select * from v$dataguard_stats where name = ‘apply lag’; Advertisements

WARNING archived log not deleted, needed for standby or upstream capture process


Solution  1  Removed archive log from filesystem using OS commands  2 delete noprompt force archivelog all completed before ‘<condition>’; Example  RMAN>delete noprompt force archivelog all completed before ‘SYSDATE-2’;

Move table to another tablespace


bash-3.2$ sqlplus “/ as sysdba”; SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 19 14:46:48 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real […]

Subroutine for wait events


sub collect_wait_events { my $sth2; my $event; my @value = (); foreach $event (@$all_wait_events) { $sth2 = $dbh_client_db->prepare(” select count(*) value from v\$session_wait where event=’$event->[0]’ “); $sth2 -> execute; @value = $sth2 -> fetchrow_array; $sth2 -> finish; $wait_values{$event->[0]} = $value[0]; } }

Subroutine for values collected as delta but not from v$sysstat


# Fills the hash %nonstandard_values   sub collect_nonstandard_delta_values { my $sth2; $sth2 = $dbh_client_db->prepare(” select sum(reloads) value from v\$librarycache ” ); $sth2 -> execute; my $value = $sth2 -> fetchrow_array; $sth2 -> finish; $nonstandard_values{‘shared pool reloads’} = $value; $sth2 = $dbh_client_db->prepare(” select request_failures FROM v\$shared_pool_reserved ” ); $sth2 -> execute; $value = $sth2 -> fetchrow_array; […]

Subroutine for collecting average wait times for wait events


sub collect_wait_event_timings { my $sth2; my $event; my @value = (); foreach $event (@$all_wait_events) { $sth2 = $dbh_client_db->prepare(” select total_waits,time_waited_micro from systemevent where event=’$event->[0]’ “) ; $sth2 -> execute; @value = $sth2 -> fetchrow_array; $sth2 -> finish; if (@value) { # if the wait event is in v$system_event $wait_event_counts{$event->[0]} = $value[0]; $wait_event_timings{$event->[0]} = $value[1]; } […]

Show the Number of Redo Log Switches Per Hour in RAC


SET PAUSE ON SET PAUSE ‘Press Return to Continue’ SET PAGESIZE 60 SET LINESIZE 300 SELECT to_char(first_time, ‘yyyy – mm – dd’) aday, to_char(first_time, ‘hh24’) hour, count(*) total FROM gv$log_history WHERE thread#=&EnterThreadId GROUP BY to_char(first_time, ‘yyyy – mm – dd’), to_char(first_time, ‘hh24’) ORDER BY to_char(first_time, ‘yyyy – mm – dd’), to_char(first_time, ‘hh24’) asc

ASM Startup


SQL> startup restrict ASM instance started Total System Global Area 283930624 bytes Fixed Size 2212656 bytes Variable Size 256552144 bytes ASM Cache 25165824 bytes ASM diskgroups mounted SQL> SELECT NAME, STATE FROM V$ASM_DISKGROUP; NAME STATE —————————— ———– FRA RESTRICTED   I am trying to start: SQL> startup ORA-01078: failure in processing system parameters ORA-01565: error […]

ORA-17502: ksfdcre:5 Failed to create file +data


RMAN> duplicate target database to aux nofilenamecheck; Starting Duplicate Db at 19-JAN-2014 13:12:37 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=23 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 01/19/2014 13:12:38 RMAN-06136: ORACLE error from auxiliary database: ORA-00200: control file could not be created ORA-00202: […]

EXPDP/IMPDP WITHOUT TABLE DATA /META DATA ONLY


expdp system/s directory=backup_dir CONTENT=METADATA_ONLY dumpfile=testmetaddl.dmp schemas=testmeta logfile=testmetalog.$Date.log impdp system/s directory=backup_dir dumpfile=testmetaddl.dmp sqlfile=metaddl.sql logfile=imp_log_of_meta.log    

Private strand flush not complete


Today I got a message in the alert log which is new to me: Fri Jun 13 00:00:24 2014 Thread 1 cannot allocate new log, sequence 514 Private strand flush not complete Private strand flush not complete… sounds similar to checkpoint not complete. So the next step brought me to Metalink and there I found […]

ORACLE’S OSWATCHER UTILITY


Time drift detected. Please check VKTM trace file for more details


  Fatal NI connect error 12170. VERSION INFORMATION: TNS for Solaris: Version 11.2.0.3.0 – Production Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.3.0 – Production TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.3.0 – Production Time: 11-JUN-2014 16:30:37 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out […]

check RECOVER MANAGED STANDBY DATABASE real time apply


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 60 disconnect; — apply delayed for 60 minutes. Database altered. SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; RECOVERY_MODE ———————– MANAGED IDLE IDLE IDLE … SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect; — Realtime apply Database altered. SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; RECOVERY_MODE ———————– MANAGED REAL […]

How to check if the running distribution is Linux


[anar@bakudcststapp boot]$ cat /etc/issue.net | head -1 Oracle Linux Server release 6.0 [anar@bakudcststapp boot]$ rpm -qf /etc/redhat-release oraclelinux-release-6Server-0.0.5.x86_64   [anar@bakudcststapp boot]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.0 (Santiago)  

ORA-65096: invalid common user or role name ( Oracle database 12c)


SQL> conn / as sysdba Connected. SQL> create user anar identified by anar; create user anar identified by anar * ERROR at line 1: ORA-65096: invalid common user or role name SQL> SHOW CON_NAME CON_NAME —————————— CDB$ROOT SQL> SELECT PDB FROM V$SERVICES; PDB —————————— PDBORCL CDB$ROOT CDB$ROOT CDB$ROOT CDB$ROOT SQL> alter session set container=PDBORCL; Session […]

Session info at Oracle


SELECT ‘USERNAME : ‘ || s.username || CHR (10) || ‘SCHEMA : ‘ || s.schemaname || CHR (10) || ‘OSUSER : ‘ || s.osuser || CHR (10) || ‘PROGRAM : ‘ || s.program || CHR (10) || ‘MACHINE : ‘ || s.machine || CHR (10) || ‘TERMINAL : ‘ || s.terminal || CHR (10) || […]

ORA-06512: at “ORACLE_OCM.MGMT_DB_LL_METRICS”


On a new install 11.2.0.3 following could be observed on the alert log Errors in file /export/home0/oracle/diag/rdbms/kbazer/KBAZER1 /trace/KBAZER1_j002_8409.trc: ORA-12012: error on auto execute of job “ORACLE_OCM”.”MGMT_CONFIG_JOB_2_1″ ORA-29280: invalid directory path ORA-06512: at “ORACLE_OCM.MGMT_DB_LL_METRICS”, line 2436 ORA-06512: at line 1 Solution: bash-3.2$ sqlplus “/ as sysdba”; SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 6 22:49:35 2014 […]

Oracle’s OSWatcher Utility


OSWatcher is a tool for the analysis of the data produced  such linux utilities as top, iostat, vmstat, netstat commands. It provides the analysed data as a graphic with OSWg tool . Tool developed by Oracle and can be downloaded via metalink (support.oracle.com). 1- Download the file called oswbb402.tar  from this metalink note (https://support.oracle.com) “OS Watcher Black Box User […]

Get Oracle DataFile List Script


SET pages 99 col file_name format a45 col tablespace_name format a13 col tablespace_name heading ts_name col blocks format 999,999 SELECT file_name,tablespace_name,bytes,blocks FROM dba_data_files ORDER BY tablespace_name,file_name /