Monthly Archives: June 2015

Recording the Output CellCli


CellCLI> spool mycellcli.txt To append to a file you already have, use append CellCLI> spool mycellcli.txt append Or, if you want to overwrite the file, use replace CellCLI> spool mycellcli.txt replace To stop spooling, just issue SPOOL OFF. CellCLI> spool currently spooling to mycellcli.txt Advertisements

QUERY BASED EXPORT USING DATAPUMP – EXPDP


Run a query based export using expdp. Confirm rows to be exported by running a normal query. SQL> SELECT COUNT (*) FROM ANAR.EMP WHERE LOAD_DATE= TRUNC (SYSDATE -190); COUNT(*) ———- 42 vi expana.par directory=adhoc dumpfile=anartest1.dmp logfile=anartest1.log COMPRESSION=ALL CONTENT=ALL TABLES=”ANAR.EMP” QUERY= ANAR.EMP:”WHERE LOAD_DATE = TRUNC (SYSDATE -190)” nohup expdp anar/anar parfile=expana.par & Export: Release 11.2.0.3.0 – […]

Crash Recovery – Alert log


ALTER DATABASE MOUNT < – – – – – DATABASE MOUNT ISSUED Mon Sep 17 1938:28 2012 ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xxx.xx.xx)(PORT=1521))))’ SCOPE=MEMORY SID=’MYDB01′; NOTE: Loaded library: System SUCCESS: diskgroup REDO01 was mounted SUCCESS: diskgroup REDO02 was mounted NOTE: dependency between database MYDB01 and diskgroup resource ora.REDO01.dg is established NOTE: dependency between database MYDB01 and diskgroup resource ora.REDO02.dg […]

Data Pump


Refrences: 1. Oracle Database 10g: New Features for Administrators Chapter # 3: Load and Unload Data 2. http://orafaq.com/node/12 3. http://www.oracle.com/technology/pub/notes/technote_pump_restart.html Practical: D:\ORACLE_HOME\BIN> Impdp -help D:\ORACLE_HOME\BIN> Expdp -help Table Level: E:>EXPDP system/oracle TABLES=scott.emp,scott.dept DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_dept.dmp 1LOGFILE=emp_dept.log E:>EXPDP system/oracle TABLES=hr.emp DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_backup_%date:~7,2%%date:~4,2%%date:~10,4%_%time:~0,2%%time:~3,2%%time:~6,2%.dmp LOGFILE=emp_backup_logfile%date:~7,2%%date:~4,2%%date:~10,4%_%time:~0,2%%time:~3,2%%time:~6,2%.log Day (DD) = %date:~7,2% Month (MM)=%date:~4,2% Year (YY)=%date:~10,4% Hour (HH)=%time:~0.2% Minutes(MM)=%time:~3,2% Seconds(SS)=%time:~6,2% E:>IMPDP TABLES=scott.emp […]

How to change user to root when using oracle user on Exadata


[oracle@exadata ~]$ su – root Password: su: incorrect password [oracle@exadata ~]$ If we want to do su to “root” user, just try solution below: Add the oracle user to the wheel group. usermod -G wheel oracle

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue table


SQL> Drop user odb cascade; Eger useri drop edende bele Error vererse: SQL> Drop user odb cascade; ORA-00604: error occurred at recursive SQL level 1 ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue table Solution: alter session set events ‘10851 trace name context forever, level 2’; or alter session set events ‘25475 trace name context forever, […]

Movement Control file to new disk group ( for example +DATA02)


RMAN> backup as copy current controlfile format ‘+DATA02′; Starting backup at 17-JAN-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying current control file output file name=+DATA02/ANARDB/controlfile/backup.273.834409791 tag=TAG20131217T122951 RECID=43 STAMP=834409791 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 17-JAN-11 SQL> alter system set db_create_file_dest=’+DATA02′ scope=spfile; System altered. SQL> alter system set control_files=’+DATA02/ANARDB/controlfile/Backup.273.834409791’ […]

Movement DB file to new disk group (for example +DATA02) / Switch to New disk groups


Strategy 1. / Copy each db file RMAN> backup as copy datafile 19 format ‘+DATA02’; Starting backup at 17-JAN-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00019 name=+DATA01/ANARDB/datafile/system.281.801406799 output file name=+DATA02/ANARDB/datafile/system.256.834409133 tag=TAG20131217T121852 RECID=13 STAMP=834409138 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 17-JAN-11 […]

Create a New Disk Group (for example) DATA02


col NAME for a20 col PATH for a40 select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk; GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB TOTAL_MB FREE_MB NAME PATH ——- ——– ——- ——– ———- ———- ——– ————— —————————————- 1 2 CACHED NORMAL 51200 51200 43 DATA01_0002 /dev/rdsk/mpathc 1 1 CACHED NORMAL 51200 51200 41 DATA01_0001 /dev/rdsk/mpathb 1 3 CACHED NORMAL 51200 51200 45 […]

Upgrade OPACH version to 11.2.0.3.6


bash-3.2$ which opatch /export/home/oracle/product/FRHome_1/OPatch/opatch bash-3.2$ env HZ= TERM=vt100 SHELL=/bin/sh NLS_LANG=AMERICAN_AMERICA.AL32UTF8 OLDPWD=/export/home0 LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/ db_1/rdbms/lib:/u01/app/oracle/product/11.2.0/ db_1/lib:/u01/app/oracle/product/11.2.0/db_1/ jdk/fre/lib/i386:/u01/app/oracle/product/11.2.0/ db_1/jdk/jre/lib/i386/server::/usr/local/ssl/ lib:/usr/lib/dns:.:/etc/emc/rsa/cst/lib:/lib:/rdbms/lib ORACLE_SID=ANARDB LD_LIBRARY_PATH_64=/u01/app/oracle/product/11.2.0/db_1/lib ORACLE_BASE=/u01/app/oracle TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/export/ home/oracle/product/FRHome_1/OPatch:/export/home/oracle/ product/FRHome_1/bin:/bin:/opt/sfw/cups/bin:/usr/bin::/ usr/local/bin:.:/opt/EMCpower/bin:/etc/emc/bin:/etc:/ home/oracle:/u01/app/oracle/product/10.2.0/db_1/OPatch MAIL=/var/mail/oracle PWD=/export/home0/11837095 ORACKE_SID=ANARDB TZ=Asia/Baku SHLVL=1 HOME=/export/home/oracle LOGNAME=oracle EXTPROC_DLLS=ANY ORACLKE_SID=ANARDB ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 _=/bin/env OPatch succeeded.  

NETSTAT / Network Statistics


netstat displays the contents of various network-related data structures in depending on theoptions selected. Syntax netstat multiple options can be given at one time. Options -a – displays the state of all sockets. -r – shows the system routing tables -i – gives statistics on a per-interface basis. -m – displays information from the network […]

VMSTAT


vmstat – vmstat reports virtual memory statistics of process, virtual memory, disk, trap, and CPU activity. On multicpu systems , vmstat averages the number of CPUs into the output. For per-process statistics .Without options, vmstat displays a one-line summary of the virtual memory activity since the system was booted. syntax Basic synctax is vmstat interval […]

IOSTAT


iostat reports terminal and disk I/O activity and CPU utilization. The first line of output is for the time period since boot & each subsequent line is for the prior interval . Kernel maintains a number of counters to keep track of the values. iostat’s activity class options default to tdc (terminal, disk, and CPU). […]

Temp Tablespace WARNING- These used% > 85% (add temp file)


select tsh.tablespace_name, sum(tsh.bytes_used+tsh.bytes_free) allocated_space,round(sum( tsh.bytes_used)*100/( sum(tsh.bytes_used+tsh.bytes_free)),2) “USED %” from V$TEMP_SPACE_HEADER tsh group by tsh.tablespace_name;

LIST OF OBJECTS HAVING > 12 EXTENTS (DBA_EXTENTS)


column Tablespace_ext format a30 column Segment format a40 column Count format 9999 break on “Tablespace_ext” skip 1 select tablespace_name “Tablespace_ext” , owner “Owner”, segment_name “Segment”, count(*) “Count” from sys.dba_extents group by tablespace_name,owner,segment_name having count(*)>12 order by 1,3 desc;

ERROR- These segments will fail during NEXT EXTENT (DBA_SEGMENTS)


column Tablespaces format a30 column Segment format a40 column “NEXT Needed” format 999,999,999 column “MAX Available” format 999,999,999 select a.tablespace_name “Tablespaces”, a.owner “Owner”, a.segment_name “Segment”, a.next_extent “NEXT Needed”, b.next_ext “MAX Available” from sys.dba_segments a, (select tablespace_name,max(bytes) next_ext from sys.dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name(+) and b.next_ext < a.next_extent;

WARNING- These segments > 70% of MAX EXTENT (DBA_SEGMENTS)


column Tablespace format a30 column Segment format a40 column Used format 9999 column Max format 9999 select tablespace_name “Tablespace”, owner “Owner”, segment_name “Segment”, extents “Used”, max_extents “Max” from sys.dba_segments where (extents/decode(max_extents,0,1,max_extents))*100 > 70 and max_extents >0;

Full compile invalid obje by PL/SQL code


grant execute on SYS.FULL_COMPILE to ANAR; CREATE OR REPLACE PROCEDURE SYS.FULL_COMPILE(xfull IN BOOLEAN,xowner IN VARCHAR2,xoutput IN BOOLEAN) AS CURSOR c1 IS SELECT ‘ALTER ‘ || object_type || ‘ ‘ || owner || ‘.’ || object_name || ‘ COMPILE’ strsql FROM dba_objects WHERE status = ‘INVALID’ AND object_type IN (‘PROCEDURE’,’FUNCTION’,’VIEW’,’PACKAGE’,’TRIGGER’) AND owner like xowner UNION ALL SELECT ‘ALTER PACKAGE’ […]

How to Connect Oracle Database (sysdba) by Java code


public class ConnectSysdba { public static void main(String[] args) throws Exception { String driver = “oracle.jdbc.Driver”; String url = “jdbc:oracle:thin:@localhost:1521:XE”; Connection con = null; // load jdbc driver Class.forName(driver); // connection info Properties info = new Properties(); info.put(“user”, “sys”); info.put(“password”, “oracle”); info.put(“internal_logon”, “sysdba”); // connect as sysdba con = DriverManager.getConnection(url, info); // check username String […]

Online Patching


ORACLE_SID=CDB12C ORACLE_HOME=/opt/oracle/app/product/12.1.0/dbhome_1 PATH=/opt/oracle/app/product/12.1.0/dbhome_1/bin: /opt/oracle/app/product/12.1.0/dbhome_1/OPatch: /usr/local/bin:/usr/bin:/bin:/usr/local/sbin 1. Check actual patch state [bash]$ opatch lsinventory Oracle Interim Patch Installer version 12.1.0.1.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /opt/oracle/app/product/12.1.0/dbhome_1 Central Inventory : /opt/oracle/oraInventory from : /opt/oracle/app/product/12.1.0/dbhome_1/oraInst.loc OPatch version : 12.1.0.1.0 OUI version : 12.1.0.1.0 Log file location : /opt/oracle/app/product/12.1.0/dbhome_1/ cfgtoollogs/opatch/opatch2013-07-13_07-14-28AM_1.log Lsinventory Output file […]