Monitoring tool with graphs
AWR Report Analysis
AWR report analysis is important for Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Popular Posts
Goldengate 19c Installation with issue resolution
Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements: For installing OEL6.8 and Oracle DB 12c installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation. Step3: unzip GG software in folder. Here unzipped in gg_software folder. Folder fbo_ggs_Linux_x64_shiphome will create. Step4: Enable GUI by issuing x host + command in root user. and keep open this root terminal. Step5: Enable GUI by issuing x host + command in oracle user. Move to software path execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next provide GG home, oracle home and port number and click Next. After verifying paths and information, Click Install ...
OEM queries
col target_name for a60 col metric_name for a20 col metric_column for a20 col value for a20 set lines 300 select target_name,metric_name,metric_column,value from sysman.mgmt$metric_current where metric_label='Database Size' and metric_column='ALLOCATED_GB'; Database Options/Management Packs Usage Reporting for Oracle Databases 11.2 and later (Doc ID 1317265.1) ./emcli get_on_demand_metrics -target_type=oracle_database -target_name=<target_name> ./emcli modify_collection_schedule -targetType="oracle_database" -targetNames="<target_name>" -collectionName="ME\$database_size" -freqType="Minute" -freqValue=5 -preview="N" ./emcli modify_collection_schedule -targetType="oracle_database" -targetNames="<target_name>" -collectionName="ha_backup_last" -collectionStatus=Disabled -preview="N" ./emcli get_targets -target='oracle_database' | awk '{print $4}...
Oracle 11g Physical Standy Setup
Step1: take Binaries tar backup from Primary DB server tar -cvf /path/11.2.0.4.tar 11.2.0.4 Step2: untar Binaries tar back on Standby DB server tar -xvf /path/11.2.0.4.tar -C /path ###################### Binaries Cloning ################### Step3: run clone command on Standby DB server export ORACLE_HOME=<PATH> cd $ORACLE_HOME/clone/bin perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE='<PATH>' ORACLE_HOME='<PATH>' ORACLE_HOME_NAME='<ANY_NAME>' ###################### POST Binaries Cloning ################### Step4: run root scripts $ORACLE_BASE/oraInventory/orainstRoot.sh $ORACLE_HOME/root.sh ###################### Pre-requisites ################### Standby: Step5: update hostnames in listener and tnsnames files Step6: start the listener in standby Server Primary: Step7: check force logging enabled -- to make sys activities will logged in redo logs SQL> select force_logging from v$database; - NO ( default ) to enable force logging - S...
Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2 select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5 Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking Step9 Click Next Step10 Select langu...
Oracle to Postgres
Round 1: Created metadata for tables, sequences, packages Round 2: Created indexes Round 3: freeze column name to freezes to_date function updated GEOMETRY datatype , installed POSTGIS package to support this datatype Round 4: created triggers created Functions Migration: bash-4.2$ pwd /usr/edb/migrationtoolkit/etc bash-4.2$ cat toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl SRC_DB_USER=hr SRC_DB_PASSWORD=hr TARGET_DB_URL=jdbc:edb://postoffline:5444/dev TARGET_DB_USER=enterprisedb TARGET_DB_PASSWORD=password bash-4.2$ -bash-4.2$ pwd /usr/edb/migrationtoolkit/bin -bash-4.2$ ls -lrt total 724 -rwxr-xr-x. 1 root root 2439 Dec 11 13:29 runJavaApplication.sh -rwxr-xr-x. 1 root root 544 Dec 11 13:33 runMTK.sh -rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar -bash-4.2$ ./runMTK.sh HR OUTPUT: Running EnterpriseDB Migration Toolkit (Build 55.7.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@o...
Oracle 11.2.0.4 to 19c DB Upgrade
Step0: Linux should be minimum 7.9 , AIX should be minimum 7.1 Step 1 : Check archive log generation size and request enough space to for archive location to keep archives atleast 7 days Step2: request filesystem should be minimum 50GB Step3: Install 19c software only and apply recent PSU patches. Step4: Download preupgrade.jar from MOS 884522.1 @19C_HOME/jdk/bin/java -jar $19C_HOME/rdbms/admin/preupgrade.jar TERMINAL TEXT Log: @19C_HOME/cfgtoollogs/<ORACLE_SID>/preupgrade/preupgrade.log take actions on recommend: 1. parameters manaully set 2. tablespace size increase 3. remove EM DB console config 4. remove OLAP catalog - SQL> @19C_HOME/olap/admin/catnoamd.sql 5. ignore APEX upgrade if APEX is not using 6. SQL>execute dbms_stats.gather_fixed_objects_stats; SQL>execute dbms_stats.gather_dictionary_stats; 7. recompile invalid objects SQL>@19C_HOME/rdbms/admin/utlrp.sql SQL>set serveroutput on; SQL>execute dbms_preup.invalid_objec...
General Queries
Tablespace growth Size We can use view to check tablespace size greater than 80% RAC / NON-RAC / Cloud RDS: SELECT tbm.TABLESPACE_NAME, round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB, round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB, round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB, round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME w...
Execution_Explain Plan
Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Active Session History Analysis
ASH analysis is important for Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15. 16. 17. 18. 19.
Comments
Post a Comment