RMAN FILE LOCATION--------------------------------------------------------- show parameter recover; connect RMAN adwith catalog--------------------------------------------------- rman TARGET / CATALOG rman/cat@catdb rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb // rman target / CONFIGURE CONTROLFILE AUTOBACKUP ON restore controlfile from autobackup --- in case u loose control file -- will be helpful in catalog mode //RMAN backup //startup mount , archive log enabled rman target / //rman backup backup format '/u02/backup/%T%U' database plus archivelog;- hot backup rman target / restore datafile 9;- to restore particular corrupted file recover database; //in case of full restore startup mount restore database; recover database; ---------------------------------------------------------------------------- ------------------------- enable backup mode--------------------------------------------------------- alter database begin backup select * from v$backup; backup control file------------------------------------------------------ alter database backup control file to trace as '/u01/backup/ctrl%U' noreset logs; database version--------------------------------------------------------- select * from v$version; tablespace names----------------------------------------------------------- select * from dba_tablespace mount ---------------------------------------------------------------------- mount xx.xx.xx.xx:/u04 /smb mount xx.xx.xx.xx:/root/Server /root/Server unzip .tar file------------------------------------------------------------- tar -xvf archive.tar -C /location rman backup database-------------------------------------------------------- rman>backup database; crontab--------------------------------------------------------------------- crontab min hours date month_count day of week(0-6) command location manual apply log file-------------------------------------------------------- alter database register logfile '/location of log file'; secure copy file --------------------------------------------------------------------- scp -r /location apps2:/target location scp -r /root/Server x.x.x.x:/root/Server account unlock---------------------------------------------------------------------- ------- alter user user_name account unlock; alter user apps account unlock; apache restart--------------------------------------------------------------------- ---------- cd /usr/local/apache-tomcat/bin/ ./startup.sh ---------------------------------------------------------------------------- ------------------------- $APPL_TOP /u01/oracle/TEST/apps/apps_st/appl $INST_TOP /u01/oracle/TEST/inst/apps/TEST_training $AD_TOP /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0 adautoconfig adcmctl.sh adapcctl.sh adalnctl.sh adstrtal.sh adstrtal.sh adfrmctl.sh adfrmsrvctl.sh /u01/oracle/TEST/inst/apps/TEST_training/admin/scripts environment files .env adovars.env fndenv.env devenv.env appsMIND.env(consolidated env files) cd $APPL_TOP/admin ---------------------------------------------------------------------------- ------------------------- NLS parameters SELECT * FROM nls_session_parameters; Instance parameters (change with ALTER SYSTEM): SELECT * FROM nls_instance_parameters; Database parameters (set during database creation): SELECT * FROM nls_database_parameters; cd $TECH_ST pwd cd /u01/oracle/TEST/apps/tech_st //Active Session History select * from v$active_session_history adadmin./////////////maintenance mode cd $AD_TOP AD ADMINISTRATION LOG FILES cd $APPL_TOP/admin/TEST/log/adadmin.log adadmin log location//// /u01/oracle/TEST/apps/apps_st/appl/admin/TEST/log (cd $APPL_TOP) ---------------------------------------------------------------------------- -------------------------patch check version adident Header (name of patch) // apply patch (no maintenance mode enabled as well as the application is running) adpatch option=hot patch //bugs select * from ad_bugs; //version information (patches) select * from AD_CHECKS_FILE //defaults file of adadmin // non interactive mode pwd cd $APPL_TOP/admin/<SID>/-----.txt adadmin defaultsfile=$APPL_TOP/admin/SID/defs.txt logfile=-----.log \ workers=5 interactive=n ---------------------------------------------------------------------------- ------------------------- //DB size growth in a year select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month'); ---------------------------------------------------------------------------- ------------------------- //ad controller cd $AD_TOP/bin ./adctrl //worker log file adctrl cd $APPL_TOP/admin/sid/log //adworker status ps -a |grep adworker // adrelink logs location cd $APPL_TOP/admin/logs //applications context file cd $INST_TOP/appl/admin/...xml //rollback autoconfig (./restore.sh) /u01/oracle/TEST/inst/apps/DB_training/admin/out/ (cd $INST_TOP) //autoconfig test mode ./adchkcfg.sh db tier-- cd $ORACLE_HOME/appsutil/bin apps tier-- cd $AD_TOP/bin //AD configuration sqlplus apps/apps_PASSWORD @adutconf.sql pwd /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0/sql ---------------------------------------------------------------------------- ------------------------- //find command find . -name ("TEST3") ---------------------------------------------------------------------------- ------------------------- root@host1:/u01/app/product/grid/11.2.0/crs/install> ./rootcrs.pl -verbose -deconfig -force ---------------------------------------------------------------------------- ------------------------- //job timing report sqlplus apps/apps @adtimrpt.sql /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0/sql ($AD_TOP) ---------------------------------------------------------------------------- ------------------------- //character set conversion create a copy of the file example cp xyz.sql xyz.old adcnv afcmstat.old xyz \ afcmstat.sql UTF8 ---------------------------------------------------------------------------- ------------------------- //adsplice (off-cycle products) ---------------------------------------------------------------------------- ------------------------- //patch history file applptch.txt cd $APPL_TOP/admin/SID ---------------------------------------------------------------------------- ------------------------- //version information (patches) select * from AD_CHECKS_FILE ---------------------------------------------------------------------------- ------------------------- //autopatch log files $APPL_TOP/admin/SID/log/adpatch.log ---------------------------------------------------------------------------- ------------------------- //patch information files $APPL_TOP/admin/SID/log/adpatch.lgi ---------------------------------------------------------------------------- ------------------------- //autopath test mode adpatch apply=n // merge patch cd $AD_TOP/bin ./admerge.pl admrgpch <source_dir> <dest_dir> ---------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------- ------------------------- //Rapid Clone ----------------source location-------------------------------------(/* PRECLONE STEPS */) cd $ORACLE_HOME/appsutil/scripts/context_name ./ adpreclone.pl dbTier------------------------db tier cd $INST_TOP/admin/scripts ./adpreclone.pl appsTier-----------------------application Tier copy all the application tier file system to the target system (appl_top,common_top,technology stack 10.1.2 ,10.1.3) ----------------target location---------------------------------------(/* POSTCLONE STEPS */) cd $ORACLE_HOME/appsutil/clone/bin ./adcfgclone.pl dbTier----------------------------db Tier cd $COMMON_TOP/clone/bin ./adcfgclone.pl appsTier---------------------------application Tier //enable archive log mode SQL> alter database archivelog; archive log list; select log_mode from v$database; //disable archive log Shutdown edit pfile / spfile (spfile when instance is up of course) to say log_archive_start = false. startup mount alter database noarchivelog; alter database open; ---------------------------------------------------------------------------- ------------------------- //chmod chmod -R 777 /u02 //chown chown -R applprod.dba /u03/oracle... ---------------------------------------------------------------------------- ------------------------- //add datafile alter tablespace system add datafile '/u01/oracle/oradata/prodtest1/system02.dbf' size 50G; //add log file to existing group sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO GROUP 2; //add log file to the new group alter database add logfile group 5 ('/u01/oracle/oradata/prod/redologt1.dbf','/u01/oracle/oradata/prod/logt2.db f') size 50m; //control file creation "CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log'," ---------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------- ------------------------- // Data dump CONN / AS SYSDBA ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK; CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';---------------------create a directory where the dump file will be placed GRANT READ, WRITE ON DIRECTORY test_dir TO scott; Table Exports/Imports expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log---export impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log---import (/* IN THIS CASE THE TARGET DATABASE IS SAME BUT SUPPOSE WE NEED TO TRANSFER IT TO DIFFERENT DATABASE , WE HAVE TO CREATE A SEPARATE DIRECTORY THERE AND MOVE THE DUMP FILE TO THAT LOCATION) */ Schema Exports/Imports expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log ---------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------- ------------------------- //kill process pkill -u applprod RMAN FILE LOCATION--------------------------------------------------------- show parameter recover; connect RMAN with catalog--------------------------------------------------- rman TARGET / CATALOG rman/cat@catdb rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb // rman target / //RMAN backup rman target / //rman backup backup format '/u01/backup/%U' database plus archivelog; restore controlfile from autobackup rman restore database enable backup mode--------------------------------------------------------- alter database begin backup select * from v$backup; backup control file------------------------------------------------------ alter database backup control file to trace as '/u01/backup/ctrl%U' noreset logs; linux version cat /proc/version uname -m or a //oracle ebs version select release_name from apps.fnd_product_groups; database version--------------------------------------------------------- select * from v$version; tablespace names----------------------------------------------------------- select * from dba_tablespace mount ---------------------------------------------------------------------- mount X.X.X.X:/u04 /smb mount X.X.X.X:/root/Server /root/Server unzip .tar file------------------------------------------------------------- tar -xvf archive.tar -C /location rman backup database-------------------------------------------------------- rman>backup database; crontab--------------------------------------------------------------------- crontab min hours date month_count day of week(0-6) command location manual apply log file-------------------------------------------------------- alter database register logfile '/location of log file'; secure copy file --------------------------------------------------------------------- scp -rfv /location apps2:/target location account unlock---------------------------------------------------------------------- ------- alter user user_name account unlock; alter user apps account unlock; apache restart--------------------------------------------------------------------- ---------- cd /usr/local/apache-tomcat/bin/ ./startup.sh ---------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------- ------------------------- /// web service listener in oracle apps A. Script to start/stop OPMN in R12 - $ADMIN_SCRIPTS_HOME/ adopmnctl.sh B. Script to start/stop HTTP Server in R12 - $ADMIN_SCRIPTS_HOME/ adapcctl.sh C. Script to start/stop oacore OC4J in R12 - $ADMIN_SCRIPTS_HOME/ adoacorectl.sh D. Script to start/stop oafm OC4J in R12 - $ADMIN_SCRIPTS_HOME/ adoafmctl.sh E. Script to start/stop forms OC4J in R12 - $ADMIN_SCRIPTS_HOME/ adformsctl.sh ---------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------- ------------------------- $APPL_TOP /u01/oracle/TEST/apps/apps_st/appl $INST_TOP /u01/oracle/TEST/inst/apps/TEST_training $AD_TOP /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0 adautoconfig adcmctl.sh adapcctl.sh adalnctl.sh adstrtal.sh adstrtal.sh adfrmctl.sh adfrmsrvctl.sh /u01/oracle/TEST/inst/apps/TEST_training/admin/scripts environment files .env adovars.env fndenv.env devenv.env appsMIND.env(consolidated env files) cd $APPL_TOP/admin //concurrent manager restart cd $INST_TOP/admin/scripts adcmctl.sh start apps/appsPWD //oracle server process opmnctl cd $INST_TOP/admin/scripts ./adopmnctl.sh apps/appsPWD ---------------------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------- ------------------------- NLS parameters SELECT * FROM nls_session_parameters; Instance parameters (change with ALTER SYSTEM): SELECT * FROM nls_instance_parameters; Database parameters (set during database creation): SELECT * FROM nls_database_parameters; //Active Session History select * from v$active_session_history adadmin./////////////maintenance mode cd $AD_TOP AD ADMINISTRATION LOG FILES cd $APPL_TOP/admin/TEST/log/adadmin.log adadmin log location//// /u01/oracle/TEST/apps/apps_st/appl/admin/MIND/log (cd $APPL_TOP) check version adident Header (name of patch) // apply patch (no maintenance mode enabled as well as the application is running) adpatch option=hotpatch //bugs select * from ad_bugs; //defaults file of adadmin // non interactive mode pwd cd $APPL_TOP/admin/<SID>/-----.txt adadmin defaultsfile=$APPL_TOP/admin/SID/defs.txt logfile=-----.log \ workers=5 interactive=n //DB size growth in a year select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month'); //ad controller cd $AD_TOP/bin ./adctrl //worker log file adctrl cd $APPL_TOP/admin/sid/log //adworker status ps -a |grep adworker // adrelink logs location cd $APPL_TOP/admin/logs //applications context file cd $INST_TOP/appl/admin/...xml //rollback autoconfig (./restore.sh) /u01/oracle/TEST/inst/apps/TEST_training/admin/out/ (cd $INST_TOP) //autoconfig test mode ./adchkcfg.sh db tier-- cd $ORACLE_HOME/appsutil/bin //logs-appsutil/log/date/adconfig.log apps tier-- cd $AD_TOP/bin //logs-INST_TOP/sid/admin/log //job timing report sqlplus apps/apps @adtimrpt.sql /u01/oracle/TEST/apps/apps_st/appl/ad/12.0.0/sql ($AD_TOP) //character set conversion create a copy of the file example cp xyz.sql xyz.old adcnv afcmstat.old xyz \ afcmstat.sql UTF8 //adsplice (off-cycle products) //patch history file applptch.txt cd $APPL_TOP/admin/SID //version information (patches) select * from AD_CHECKS_FILE //autopatch log files $APPL_TOP/admin/SID/log/adpatch.log //patch information files $APPL_TOP/admin/SID/log/adpatch.lgi //autopath test mode adpatch apply=n // merge patch cd $AD_TOP/bin ./admerge.pl admrgpch <source_dir> <dest_dir> //Rapid Clone ----------------source location-------------------------------------(/* PRECLONE STEPS */) cd $ORACLE_HOME/appsutil/scripts/context_name ./ adpreclone.pl dbTier------------------------db tier cd $INST_TOP/admin/scripts ./adpreclone.pl appsTier-----------------------application Tier copy all the application tier file system to the target system (appl_top,common_top,technology stack 10.1.2 ,10.1.3) ----------------target location---------------------------------------(/* POSTCLONE STEPS */) cd $ORACLE_HOME/appsutil/clone/bin ./adcfgclone.pl dbTier----------------------------db Tier cd $COMMON_TOP/clone/bin ./adcfgclone.pl appsTier---------------------------application Tier //enable archive log mode SQL> alter database archivelog; //chmod chmod -R 777 /u02 //chown chown -R applprod.dba /u03/oracle... //add datafile alter tablespace system add datafile '/u01/oracle/oradata/prodtest1/system02.dbf' size 50G; //add log file to existing group sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO GROUP 2; //add log file to the new group alter database add logfile group 5 ('/u01/oracle/oradata/prod/redologt1.dbf','/u01/oracle/oradata/prod/logt2.db f') size 50m; //control file creation "CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log'," // Data Pump CONN / AS SYSDBA ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK; CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';---------------------create a directory where the dump file will be placed GRANT READ, WRITE ON DIRECTORY test_dir TO scott; Table Exports/Imports expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log---export impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log---import (/* IN THIS CASE THE TARGET DATABASE IS SAME BUT SUPPOSE WE NEED TO TRANSFER IT TO DIFFERENT DATABASE , WE HAVE TO CREATE A SEPARATE DIRECTORY THERE AND MOVE THE DUMP FILE TO THAT LOCATION) */ Schema Exports/Imports expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log //kill process pkill -u applprod //concurrent manager logs// $FND_TOP/log //port change OAM ->autoconfig->oa ports change the values and run autoconfig (with application down) //FNDCPASS SYSTEM ,It is used to change APPS/APPLSYS password USER , It is used to change oracle applications user like SYSADMIN ORACLE, It is used to change the password of oracle schema like GL,AP ALLORACLE , It is used to change all passwords of all schema. FNDCPASS apps/APPS_PWD 0 Y system/manager SYSTEM APPLSYS apps update FND_PROFILE_OPTION_VALUES set PROFILE_OPTION_VALUE = 'R12 INSTANCE' where PROFILE_OPTION_ID = '125' ; update FND_PROFILE_OPTION_VALUES set PROFILE_OPTION_VALUE = 'RED' where PROFILE_OPTION_ID = '5785' ; /red hat release vi /etc/redhat-release frd profile-> ICX forms launcher //last refresh of database created date cd $INST_TOP/appl/admin ls -ltr select created, sysdate from v$database; //create user directory CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name'; //bash profile export ORACLE_HOME=/u02/oracle/11.2.0 export ORACLE_SID=database2 export PATH=$PATH:$ORACLE_HOME/bin //environment file /u01/oracle/TEST/apps/apps_st/appl/APPSTEST_training.env //ad controller as a applprod user first run the environment file then run adctrl //block change tracking SELECT filename, status, bytes FROM v$block_change_tracking;---- status ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'c:\oracle\product lash_recovery_area\ORABASE\bctf01.log'; ----- enable ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; --- disable // listener.ora tnsname.ora location cd $ORACLE_HOME/network/admin/sid/ //gather schema statistics run the rquest as %GATHER SCHEMA STATISTICS% //multi organization select multi_org_flag from fnd_product_groups; //top ten process using memory ps aux|head -1;ps aux|sort -m //file system cat /etc/fstab //users in linux cat /etc/passwd //mount cd writer mount /dev/cdwriter /media/ //restart X server from gui ctrl+alt+f7 //edit .sh file substitute :%s/target1 /target2 /g //user add useradd -g dba oraprod //group add groupadd dba alter system switch logfile archive log list; export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin \\remove and keep last 5 days log files find . -mtime +10 -exec rm -f {} \; -print after this alter system switch logfile \\lock and unlock alter table myTable read only; alter table myTable read write; revoke the INSERT, UPDATE, DELETE grants from that table. SQL> revoke UNLIMITED TABLESPACE from KAMRAN; lock table <table_name> in exclusive mode modes <ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, NOWAIT and WAIT> \\DBlink create database link arobase connect to "mm@oracle" identified by "mypassword" using 'dg112odbc'; CREATE Public Database Link SS64 Connect To scott Identified By demo Using test; select * from dba_db_links; \\SGA (DDLJ - SR) select * from v$sgainfo Data buffer cache - cache data and index blocks for faster access. Shared pool - cache parsed SQL and PL/SQL statements. Dictionary Cache - information about data dictionary objects. Redo Log Buffer - committed transactions that are not yet written to the redo log files. JAVA pool - caching parsed Java programs. Streams pool - cache Oracle Streams objects. Large pool - used for backups, UGAs, etc. \\audit sign-on in oracle ebiz suite sign on audit users sign on audit forms sign on audit resposibilities sign on concuurent performance \\invalid objects select owner,object_name,object_type from dba_objects where status='INVALID'; \\recompile alter procedure procedur_name compile; alter view view_name compile; dbTechStack cd /u03/oracle/PROD/db/tech_st/11.2.0/appsutil/clone/bin/ \\unusable indexes select owner,index_name from dba_indexes where status='UNUSABLE'; SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions. skip unusable indexes alter system set SKIP_UNUSABLE_INDEXES=FALSE; rman target sys/PWD@prod auxiliary sys/PWD@TEST run { DUPLICATE TARGET DATABASE TO 'TEST' FROM ACTIVE DATABASE nofilenamecheck; } exit rman target sys/PWD@prod auxiliary / LOG $outputfile <<EOF run { DUPLICATE TARGET DATABASE TO 'TESTR' FROM ACTIVE DATABASE; } exit clearcache #!/bin/sh sync; echo 3 > /proc/sys/vm/drop_caches //DR setup httphttp://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php space check SELECT a.tablespace_name, b.size_mb, a.free_mb, Trunc((a.free_mb/b.size_mb) * 100) "FREE_%" FROM (SELECT tablespace_name, Trunc(Sum(bytes)/(1024*1024)) FREE_mb FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, Trunc(Sum(bytes)/(1024*1024)) SIZE_mb FROM dba_data_files GROUP BY tablespace_name ) b WHERE a.tablespace_name = b.tablespace_name //Flash Recovery area rman backups control file logs online redo logs archived logs flashback logs //recycle bin oracle (to recover drop tables) SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime 2 from recyclebin //to recover data from recycle bin SQL> flashback table tst to before drop; Flashback complete. AFTER THIS PURGE TABLE FROM RECYCLE BIN SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ; //clear cache in oracle apps either from functional administrator responsibility (core services->framework) or bring down the apache services clearing file $COMMON_TOP/_pages will clear cache and recompile all jsp files. In R12 if you clear cache $COMMON_TOP/_pages then you will have to recompile the jsp files manually perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2 // ash (active session history) for last 5 days SELECT NVL(a.event, 'ON CPU') AS event, COUNT(*) AS total_wait_time FROM v$active_session_history a WHERE a.sample_time > SYSDATE - 5/(24*60) -- 5 mins GROUP BY a.event ORDER BY total_wait_time DESC; select * from FND_LOGS; -- details of all application user login as apps ///////////////////alter the archive_dest_2 enable and disable ALTER SYSTEM SET log_archive_dest_state_2 = DEFER; ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE; /////node error on oracle apps SQL> conn apps/apps_PWD Connected. exec fnd_net_services.remove_system ('DB_NAME'); SQL> commit; exec fnd_conc_clone.setup_clean ; $ cd <RDBMS ORACLE_HOME>/appsutil/scripts/<context> $ ./adautocfg.sh //alert file location /u03/oracle/TEST/db/tech_st/11.2.0/admin/PROD_appstest1/diag/rdbms/prods/PRO D/trace select * fron FND_LOGS; -- details of all appl user login as apps ///////////////////////DATAGUARD PHYSICAL STANDBY CONFIGURATION///////////////////////////// http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php //AUTOMATIC WORKLOAD REPOSITORY V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second. V$METRIC - Displays metric information. V$METRICNAME - Displays the metrics associated with each metric group. V$METRIC_HISTORY - Displays historical metrics. V$METRICGROUP - Displays all metrics groups. DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history. DBA_HIST_BASELINE - Displays baseline information. DBA_HIST_DATABASE_INSTANCE - Displays database environment information. DBA_HIST_SNAPSHOT - Displays snapshot information. DBA_HIST_SQL_PLAN - Displays SQL execution plans. DBA_HIST_WR_CONTROL - Displays AWR settings @$ORACLE_HOME/rdbms/admin/awrrpt.sql @$ORACLE_HOME/rdbms/admin/awrrpti.sql //ADDM : automatic database diagnostic monitor analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.and we use it for the following cases : CPU bottlenecks Undersized memory structures I/O capacity issues High load SQL statements RAC specific issues Database configuration issues Also provides recommendations on hardware changes, database configuration & schema changes. Generate ADDM : Login to SQL @$ORACLE_HOME/rdbms/admin/addmrpt.sql enter system password when you asked for . Specify a begin_snap from the list and press Enter. Specify the end_snap from the list and press Enter. Report Name //////////// ASH : Active Session History statistics from the in-memory performance monitoring tables also used to track session activity and simplify performance tuning. ASH reports Give the following information : Top User Events (frequent wait events) Details to the wait events Top Queries Top Sessions Top Blocking Sessions Top DB Object. Activity Over Time Generate ASH reports : The Best way to do that using OEM. oraInventory all the oracle software products installed on all ORACLE_HOME other non - oracle products such as JRE select * from v$standby_log //tape backup commands backup tar -czf /dev/st0 * List tar -tzf /dev/st0 Erase the tape mt -f /dev/st0 erase Rewind Tape mt -f /dev/st0 rewind //logs location $INST_TOP-- apache logs , start and stop, concurrent manager, forms report . cd $INST_TOP/logs/ appl, ora [appltest@TEST PROD]$ cd inst/apps/TESTR_TEST/logs/appl/admin/log/-------------------------------- ----- cd $INST_TOP adalnctl.txt adcmctl.txt adoacorectl.txt adopmnctl.txt adstrtal.log jtffmctl.txt adapcctl.txt adformsctl.txt adoafmctl.txt adstpall.log javacache.log [appltest@TEST PROD]$ cd inst/apps/TESTR_TEST/logs/appl/admin/log/ [appltest@TEST PROD]$ cd inst/apps/TESTR_TEST/logs/ora/10.1.2 ------------- forms reports network [appltest@TEST PROD]$ cd inst/apps/TESTR_TEST/logs/ora/10.1.3-----------apache http autoconfig cd $INST_TOP/apps/TESTR/admin/log ---------------------------------------------------------------------------- -------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------------------------------------- $APPL_TOP adpatch logs , adadmin logs /u06/oracle/PROD/apps/apps_st/appl/admin/TESTR/log adadmin.log adpatch.log adwork002.log adwork004.log adwork006.log adwork008.log adpatch.lgi adwork001.log adwork003.log adwork005.log adwork007.log ____________________________________________________________________________ _________________________________________________________________ database autoconfig appsutil/logs The log file for this session is located at: /u06/oracle/PROD/db/tech_st/11.2.0/appsutil/log/TESTR_TEST/02120254/adcon fig.log opatch logs (before applying OPatch , you need to export the path export PATH=$PATH:$ORACLE_HOME/OPatch) cd $ORACLE_HOME/cfgtools/logs database trace file location /u06/oracle/PROD/db/tech_st/11.2.0/admin/TESTR_TEST/diag/rdbms/testr/TEST R/trace //.dbc file /u03/oracle/PROD/inst/apps/PRODR_TEST/appl/fnd/12.0.0/secure will hold all the database connect parameters //adbuild in R12 Generating a new Context file on the database tier perl $ORACLE_HOME/appsutil/bin/adbldxml.pl template=[contextfile_template] out=[contextfile_name] (Where [contextfile_template] is the location of the context template (default is $ORACLE_HOME/appsutil/template/adxdbctx.tmp) and [contextfile_name] is the absolute Path of the context file to be generated (the default is $ORACLE_HOME/appsutil/[context_name].xml).) Generating a new Context file on the Applications - Tier : run the adclonectx.pl script from. perl [COMMON_TOP]/clone/bin/adclonectx To retrieve the applications tier context file ( if it is lost or deleted accidentally) , execute the following command on the applications tier : perl [COMMON_TOP]/clone/bin/adclonectx.pl retrieve //concurrent enable trace file profile->system->concurrent:allow debugging Concurrent: Allow Debuggin enable trace location trace SQL> SELECT oracle_process_id FROM fnd_concurrent_requests fcr WHERE fcr.request_id = &req_id; //tkprof The TKPROF program converts Oracle trace files into a more readable form tkprof orcl102_ora_3064.trc output.prf EXPLAIN=scott/tiger SYS=NO TKPROF <trace-file> <output-file> explain=user/password@service table=sys.plan_table //concurrent programs running http://onlineappsdba.com/index.php/2008/11/20/troubleshoot-long-running-conc urrent-request-in-apps-11ir12/ http://onlineappsdba.com/index.php/2008/11/20/troubleshoot-long-running-conc urrent-request-in-apps-11ir12/ oradebug setospid spid //locate trace file //locate trace file sql>oradebug tracefile_name //You can see the request id and other relevant information from the result. Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend. SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine, b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr AND b.sql_hash_value = c.hash_value AND b.STATUS = 'ACTIVE' AND a.spid = '11696' ORDER BY a.spid, c.piece (Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session. //Concurrent manager status for a given sid? col MODULE for a20 col OSUSER for a10 col USERNAME for a10 set num 10 col MACHINE for a20 set lines 200 col SCHEMANAME for a10 select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME, s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
Wednesday, 1 July 2015
Commands and guide APPS DBA
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment