Automatic Diagnostic Repository ADR – Automatic Diagnostic Repository (introduced in Oracle 11g) is a system managed repository for storing database alert logs, trace files, and any other diagnostic data previously controlled by any other init.ora parameters. DIAGNOSTIC_DEST is the base location for all diagnostic directories. There… Read More
There is no excerpt because this is a protected post.
ORACLE ARCHITECTURE -4(PHYSICAL REPRESENTATION OF DATABASE ) In this article we will cover Physical representation of Database. Physical representation is nothing but Physical files(CRD). From Oracle version 6 to 12c every database has three files. They are : 1.Controlfile 2.Redolog files 3.Datafiles Please check below to… Read More
ORACLE OPTIONAL BACKGROUND PROCESSES Optional Background Processes Archiver Process Name: ARC0 through ARC9 Max Processes: 10 The ARCn process is responsible for writing the online redo log files to the mentioned archive log destination after a log switch has occurred. ARCn is present only if… Read More
ORACLE SERVER ARCHITECTURE What is Oracle Sever ? Oracle Server is a combination of Instance and database . What is an Oracle instance ? Instance is a gateway in-order to access the database. Without instance we can’t access database and its objects . Every database… Read More
ORACLE ARCHITECTURE -1 What is DATABASE ? A database is an organized collection of data, generally stored and accessed electronically from a computer system. As a database administrator we can say one more definition A Database is a collection of Logical level and Physical level In Logical level… Read More
Comparing parameter files from two different databases. In this article we will learn how to compare two parameter files from two different databases. AIM : To make changes on orcl to look like prod we need to follow below procedure Source : Server name … Read More
How to Deinstall Oracle software To Deinstall Oracle software we need to go first Oracle software location
|
1 |
[oracle@dba2 oracle]$ cd /u01/app/oracle/product/12.1.0/ |
List out the files
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
[oracle@dba2 12.1.0]$ ll total 288 drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:42 addnode drwxr-xr-x. 8 oracle dba 4096 Oct 11 16:42 apex drwxr-xr-x. 9 oracle dba 4096 Oct 11 16:40 assistants drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:44 bin drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:42 ccr drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:42 cdata drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:45 cfgtoollogs drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:42 clone drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:40 crs drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:42 css drwxr-xr-x. 11 oracle dba 4096 Oct 11 16:42 ctx drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:40 cv drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:40 dbs drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:42 dc_ocm drwxr-xr-x. 5 oracle dba 4096 Oct 11 16:44 deinstall drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 demo drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 diagnostics drwxr-xr-x. 8 oracle dba 4096 Oct 11 16:41 dmu drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 dv drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 has drwxr-xr-x. 5 oracle dba 4096 Oct 11 16:44 hs drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:45 install drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 instantclient drwxr-x---. 13 oracle dba 4096 Oct 11 16:44 inventory drwxr-xr-x. 8 oracle dba 4096 Oct 11 16:40 javavm drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:42 jdbc drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:42 jdk drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:42 jlib drwxr-xr-x. 12 oracle dba 4096 Oct 11 16:42 ldap drwxr-xr-x. 3 oracle dba 12288 Oct 11 16:44 lib drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:40 log drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:40 md drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:42 mgw drwxr-xr-x. 10 oracle dba 4096 Oct 11 16:44 network drwxr-xr-x. 5 oracle dba 4096 Oct 11 16:40 nls drwxr-xr-x. 19 oracle dba 4096 Oct 11 16:41 oc4j drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:40 odbc drwxr-xr-x. 5 oracle dba 4096 Oct 11 16:40 olap drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:42 OPatch drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:42 opmn drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:40 oracore -rw-r-----. 1 oracle dba 56 Oct 11 16:40 oraInst.loc drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:40 ord drwxr-xr-x. 8 oracle dba 4096 Oct 11 16:44 oui drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:40 owm drwxr-xr-x. 5 oracle dba 4096 Oct 11 16:40 perl drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:40 plsql drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 plugins drwxr-xr-x. 6 oracle dba 4096 Oct 11 16:40 precomp drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 QOpatch drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 R drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:42 racg drwxr-xr-x. 14 oracle dba 4096 Oct 11 16:44 rdbms drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 relnotes drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 rest -rwxr-x---. 1 oracle dba 409 Oct 11 16:44 root.sh drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:40 scheduler drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 slax drwxr-xr-x. 15 oracle dba 4096 Oct 11 16:42 sqldeveloper drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 sqlj drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:40 sqlpatch drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:43 sqlplus drwxr-xr-x. 9 oracle dba 4096 Oct 11 16:42 srvm drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:40 suptools drwxr-xr-x. 4 oracle dba 4096 Oct 11 16:40 sysman drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:42 ucp drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 usm drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 utl drwxr-xr-x. 3 oracle dba 4096 Oct 11 16:40 wwg drwxr-xr-x. 7 oracle dba 4096 Oct 11 16:41 xdk |
Go-to Deinstall directory
|
1 |
[oracle@dba2 12.1.0]$ cd deinstall/ |
List out the files unser deinstall directory
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[oracle@dba2 deinstall]$ ll total 348 -rw-r--r--. 1 oracle dba 240220 Jul 7 2014 bootstrap_files.lst -rwxr-xr-x. 1 oracle dba 11952 Mar 4 2014 bootstrap.pl -rwxr-xr-x. 1 oracle dba 10222 Oct 11 16:44 deinstall -rwxr-xr-x. 1 oracle dba 28591 May 19 2014 deinstall.pl -rw-r--r--. 1 oracle dba 8623 Apr 11 2014 deinstall.xml drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 jlib -rw-r--r--. 1 oracle dba 401 Feb 10 2012 readme.txt drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 response -rwxr-xr-x. 1 oracle dba 32334 Jun 7 2013 sshUserSetup.sh drwxr-xr-x. 2 oracle dba 4096 Oct 11 16:41 utl |
Run the file ./deinstall
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
[oracle@dba2 deinstall]$ ./deinstall Checking for required files and bootstrapping ... Please wait ... Location of logs /tmp/deinstall2019-01-10_07-43-23PM/logs/ ############ ORACLE DECONFIG TOOL START ############ ######################### DECONFIG CHECK OPERATION START ######################### ## [START] Install check configuration ## Checking for existence of the Oracle home location /u01/app/oracle/product/12.1.0 Oracle Home type selected for deinstall is: Oracle Single Instance Database Oracle Base selected for deinstall is: /u01/app/oracle Checking for existence of central inventory location /u01/app/oraInventory ## [END] Install check configuration ## Network Configuration check config START Network de-configuration trace file location: /tmp/deinstall2019-01-10_07-43-23PM/logs/netdc_check2019-01-10_07-46-36-PM.log Network Configuration check config END Database Check Configuration START Database de-configuration trace file location: /tmp/deinstall2019-01-10_07-43-23PM/logs/databasedc_check2019-01-10_07-46-36-PM.log Use comma as separator when specifying list of values as input Specify the list of database names that are configured in this Oracle home []: Database Check Configuration END Oracle Configuration Manager check START OCM check log file location : /tmp/deinstall2019-01-10_07-43-23PM/logs//ocm_check1526.log Oracle Configuration Manager check END ######################## DECONFIG CHECK OPERATION END ######################### ####################### DECONFIG CHECK OPERATION SUMMARY ####################### Oracle Home selected for deinstall is: /u01/app/oracle/product/12.1.0 Inventory Location where the Oracle home registered is: /u01/app/oraInventory Checking the config status for CCR Oracle Home exists with CCR directory, but CCR is not configured CCR check is finished Do you want to continue (y - yes, n - no)? [n]: y A log of this session will be written to: '/tmp/deinstall2019-01-10_07-43-23PM/logs/deinstall_deconfig2019-01-10_07-46-31-PM.out' Any error messages from this session will be written to: '/tmp/deinstall2019-01-10_07-43-23PM/logs/deinstall_deconfig2019-01-10_07-46-31-PM.err' ######################## DECONFIG CLEAN OPERATION START ######################## Database de-configuration trace file location: /tmp/deinstall2019-01-10_07-43-23PM/logs/databasedc_clean2019-01-10_07-47-04-PM.log Network Configuration clean config START Network de-configuration trace file location: /tmp/deinstall2019-01-10_07-43-23PM/logs/netdc_clean2019-01-10_07-47-04-PM.log De-configuring backup files... Backup files de-configured successfully. The network configuration has been cleaned up successfully. Network Configuration clean config END Oracle Configuration Manager clean START OCM clean log file location : /tmp/deinstall2019-01-10_07-43-23PM/logs//ocm_clean1526.log Oracle Configuration Manager clean END ######################### DECONFIG CLEAN OPERATION END ######################### ####################### DECONFIG CLEAN OPERATION SUMMARY ####################### Cleaning the config for CCR As CCR is not configured, so skipping the cleaning of CCR configuration CCR clean is finished ####################################################################### ############# ORACLE DECONFIG TOOL END ############# Using properties file /tmp/deinstall2019-01-10_07-43-23PM/response/deinstall_2019-01-10_07-46-31-PM.rsp Location of logs /tmp/deinstall2019-01-10_07-43-23PM/logs/ ############ ORACLE DEINSTALL TOOL START ############ ###################### DEINSTALL CHECK OPERATION SUMMARY ####################### A log of this session will be written to: '/tmp/deinstall2019-01-10_07-43-23PM/logs/deinstall_deconfig2019-01-10_07-46-31-PM.out' Any error messages from this session will be written to: '/tmp/deinstall2019-01-10_07-43-23PM/logs/deinstall_deconfig2019-01-10_07-46-31-PM.err' ######################## DEINSTALL CLEAN OPERATION START ######################## ## [START] Preparing for Deinstall ## Setting LOCAL_NODE to dba2 Setting CRS_HOME to false Setting oracle.installer.invPtrLoc to /tmp/deinstall2019-01-10_07-43-23PM/oraInst.loc Setting oracle.installer.local to false # [END] Preparing for Deinstall ## Setting the force flag to false Setting the force flag to cleanup the Oracle Base Oracle Universal Installer clean START Detach Oracle home '/u01/app/oracle/product/12.1.0' from the central inventory on the local node : Done Delete directory '/u01/app/oracle/product/12.1.0' on the local node : Done Delete directory '/u01/app/oraInventory' on the local node : Done Delete directory '/u01/app/oracle' on the local node : Done Oracle Universal Installer cleanup was successful. Oracle Universal Installer clean END ## [START] Oracle install clean ## Clean install operation removing temporary directory '/tmp/deinstall2019-01-10_07-43-23PM' on node 'dba2' ## [END] Oracle install clean ## ######################### DEINSTALL CLEAN OPERATION END ######################### ####################### DEINSTALL CLEAN OPERATION SUMMARY ####################### Successfully detached Oracle home '/u01/app/oracle/product/12.1.0' from the central inventory on the local node. Successfully deleted directory '/u01/app/oracle/product/12.1.0' on the local node. Successfully deleted directory '/u01/app/oraInventory' on the local node. Successfully deleted directory '/u01/app/oracle' on the local node. Oracle Universal Installer cleanup was successful. Run 'rm -r /etc/oraInst.loc' as root on node(s) 'dba2' at the end of the session. Run 'rm -r /opt/ORCLfmap' as root on node(s) 'dba2' at the end of the session. Run 'rm -r /etc/oratab' as root on node(s) 'dba2' at the end of the session. Oracle deinstall tool successfully cleaned up temporary directories. ####################################################################### ############# ORACLE DEINSTALL TOOL END ############# |
Installation of Oracle software… Read More
Oracle 12c installation on AWS EC2 Instance In this article we will come to know how to install Oracle Software on AWS cloud. I have an existing AWS Cloud account. or we can create a AWS account using below link https://portal.aws.amazon.com/gp/aws/developer/registration/index.html There I going to… Read More
Performance Troubleshooting Steps reference of this article from : dbaparadise.com
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
/* sessions.sql Example: @sessions.sql Copyright @2016 dbaparadise.com */ set linesize 200 set pagesize 100 clear columns col inst for 99999999 col sid for 9990 col serial# for 999990 col username for a12 col osuser for a16 col program for a10 trunc col Locked for a6 col status for a1 trunc print col "hh:mm:ss" for a8 col SQL_ID for a15 col seq# for 99990 col event heading 'Current/LastEvent' for a25 trunc col state head 'State (sec)' for a14 select inst_id inst, sid , serial# , username , ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser , substr(program,instr(program,'/',- 1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')- 1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status, to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , SQL_ID, seq# , event, decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6) ,'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state , substr(module,1,25) module, substr(action,1,20) action from GV$SESSION where type = 'USER' and audsid != 0 -- to exclude internal processess order by inst_id, status, last_call_et desc, sid / |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/* sqltext.sql Example @sqltext.sql 6rv5za4tfnjs8 Copyright dbaparadise.com */ set define '&' set verify off define sqlid=&1 col sql_text for a80 word_wrapped col inst_id for 9 break on inst_id set linesize 150 select inst_id, sql_text from gv$sqltext where sql_id = '&sqlid' order by inst_id,piece / |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
/* findsql.sql Example: @findsql.sql employee_contact Copyright dbaparadise.com */ set define '&' define sql_str=&1 col sql_id for A15 col sql_text for A150 word_wrapped set linesize 170 set pagesize 300 SELECT /* findsql */ sql_id, executions, sql_text FROM gv$sql WHERE command_type IN (2,3,6,7,189) AND UPPER(sql_text) LIKE UPPER('%&sql_str%') AND UPPER(sql_text) NOT LIKE UPPER('%findsql%') / undef sql_str |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* sqlplan.sql Copyright dbaparadise.com Example: @sqlplan.sql 1t8v91nxtxgjr */ set define '&' define sqlid=&1 col ELAPSED for 99,990.999 col CPU for 99,990.999 col ROWS_PROC for 999,999,990 col LIO for 9,999,999,990 col PIO for 99,999,990 col EXECS for 999,990 col sql_text for a40 trunc set lines 200 set pages 300 select inst_id,sql_id,child_number child_num ,plan_hash_value, round(ELAPSED_TIME/1000000/greatest(EXECUTIONS,1),3) ELAPSED, round(CPU_TIME/1000000/greatest(EXECUTIONS,1),3) CPU,EXECUTIONS EXECS, BUFFER_GETS/greatest(EXECUTIONS,1) lio, DISK_READS/greatest(EXECUTIONS,1) pio, ROWS_PROCESSED/greatest(EXECUTIONS,1) ROWS_PROC, sql_text from gv$sql where sql_id = '&sqlid' order by inst_id, sql_id, child_number / select plan_table_output from table(dbms_xplan.display_cursor('&sqlid',NULL,'ADVANCED -PROJECTION -BYTES RUNSTATS_LAST')); |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
/* sqlplan_hist.sql Copyright dbaparadise.com Example: @sqlplan_hist.sql 1t8v91nxtxgjr WARNING! Diagnistic and Tuning Pack licensing is required to run this script!!! */ set linesize 200 set pagesize 200 set verify off set define '&' define sqlid=&1 select plan_table_output from table(dbms_xplan.display_awr('&sqlid')) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/* historical_runs.sql Copyright dbaparadise.com Example @historical_runs.sql sql_id */ set linesize 200 set pagesize 200 set verify off set define '&' define sqlid=&1 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 9999 break on plan_hash_value skip 1 select sh.snap_id, sh.instance_number inst, sh.begin_interval_time, s.sql_id, s.plan_hash_value, nvl(s.executions_delta,0) execs, (s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime, (s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SH where s.sql_id = '&sqlid' and sh.snap_id = s.snap_id and sh.instance_number = s.instance_number order by 1, 2, 3 / |
