How to Backup ORACLE_HOME binaries and Oracle INVENTORY ORACLE_HOME and INVENTORY need to backup before Database / OS level patching Table of Contents ______________________________________ Backup ORACLE_HOME and INVENTORY 1. Shutdown…
Read MoreStep 1: Identify OCR physical backup using the following command [root@rac1 ~]# ocrconfig -showbackup rac2 2020/04/03 16:57:56 /u01/app/12.1.0/grid/cdata/rac-scan/backup00.ocr 0 rac1 2020/04/01 17:34:19 /u01/app/12.1.0/grid/cdata/rac-scan/backup01.ocr 0 rac1 2020/04/31 13:57:45 /u01/app/12.1.0/grid/cdata/rac-scan/backup02.ocr 0 rac2…
Read MoreTransferring statistics between database In general, development DB usually will have only portion of the data when we compared to Production database. In such a scenario, when we fix any production issues, obviously we make the changes in Dev DB and test the code and move to Prod DB. While testing the code in Dev DB, if we want to compare the execution plan between Dev and Prod, then we can copy the Prod DB statistics into Dev DB and forcast the optimizer behaviour in development server. DBMS_STATS has an ability to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. Source database : orcl Source schema : sales Target database : oradev Target schema : sales Now our goal is to copy the statistics from sales@orcl to sales@ordev. step1. First create a stat table in the source database. The statistics table is created in SYSTEM schema. SQL> connect system/password@orcl Connected. SQL> EXEC DBMS_STATS.create_stat_table(‘SYSTEM’,’STATS_TABLE’); PL/SQL procedure successfully completed. SQL> Step2. Export the sales schema statistics. SQL> EXEC DBMS_STATS.export_schema_stats(‘SALES’,’STATS_TABLE’,NULL,’SYSTEM’); PL/SQL procedure successfully completed. SQL> Step3. Export the STATS_TABLE by using expdp or exp utility and move the dump file to target(ordev) server. Step4. Import the dump file into target database by using impdp or imp utility. Here i imported the dump file in system schema at target server. Step5. Import the statistics into application schema(sales@ordev). Please remember, previous step, we imported the stats_table content into system schema by using impdp method. But this step, we are importing the statistics into relevant data dictionary table by using dbms_stats pacakge. SQL> EXEC DBMS_STATS.import_schema_stats(‘SALES’,’STATS_TABLE’,NULL,’SYSTEM’); PL/SQL procedure successfully completed. SQL> Step6. Drop the stats_table in target server. SQL> EXEC DBMS_STATS.drop_stat_table(‘SYSTEM’,’STATS_TABLE’); PL/SQL procedure successfully completed.…
Read MoreHistogram Stats in Columns The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms Histogram is collecting statistics on columns for better query selectivity and optimal execution plan The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below. FOR ALL INDEXED COLUMNS limits base column gathering to only those columns that are included in an index. FOR ALL HIDDEN COLUMNS limits base column statistics gathering to only the virtual columns that have been created on a table. FOR ALL COLUMNS is column statistics gathering on all columns in the table The SIZE part of the METHOD_OPT syntax controls the creation of histograms AUTO means Oracle will automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$) SKEWONLY automatically creates a histogram on any column that shows a skew in its data distribution. An integer (SIZE) value indicates that a histogram will be created with at most the specified number of buckets. Must be in the range [1,254]. To force histogram creation it is recommend that the number of buckets be left at 254. Note SIZE 1 means no histogram will be created. Collecting histograms stats for only a set of columns Example: Below is the DBMS_STATS.GATHER_TABLE_STATS command in action. The only column that has a histogram created on it is the CUST_ID, even though several of the columns in the SALES table were used in the where clause of queries executed on the system. BEGIN dbms_stats.Gather_table_stats(‘SH’, ‘SALES’, – method_opt => ‘FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID’); END; / PL/SQL procedure successfully completed. SELECT column_name, num_distinct, histogram FROM user_tab_col_statistics WHERE table_name = ‘SALES’;
Read MoreHow to create SQL profiles using the SQLT utility. Oracle has a utility called SQLT to create profiles. To create profiles we do not need to install this utility it can be done by downloading and unzipping the utility. Refer to metalink note 215187.1. Once the utility is unzipped, you can call the profiler function by running this SQL. You need to be logged in as SYS. @sqlt/utl/coe_xfr_sql_profile.sql SQL> @sqlt/utl/coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) This will ask sqlid as input parameter. Once the sqlid is provided, it will list all the plan hash values and their elapsed times. Enter value for 1: crj0huynfjdz5 PLAN_HASH_VALUE AVG_ET_SECS ————— ———– 129850372 248.461 3675459451 2151.575 664690662 14074.004 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: Once the plan hash value is given, it creates the files necessary to create the profile. Enter value for 2: 129850372 Execute coe_xfr_sql_profile_crj0huynfjdz5_129850372.sql on TARGET system in order to create a custom SQL Profile with plan 129850372 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL> If you execute the file coe_xfr_sql_profile_crj0huynfjdz5_129850372.sql, the profile for that hash value is created.
Read MoreFind the memory on the host: [gg60@ut03 :+ASM] /home/dba/gg60 $ free -m total used free shared buffers cached Mem: 3820 3773 47 0 396 2045 [gg60@ut03 :+ASM] /home/dba/gg60 $ cat /proc/meminfo | grep Mem MemTotal: 3912580 kB MemFree: 64228 kB ********************************************************************************* Set the value for vm.nr_hugepages: 3820/1024 = 3.73 GB 1.5 GB is 40% (this will be the sga_target value 1528 MB ) (1.50 * 1024 * 1024/2048) + 5 = 773 vm.nr_hugepages=773 A) Edit /etc/sysctl.conf Add line: vm.nr_hugepages=773 ********************************************************************************* Set the value for Soft memlock and Hard memlock: 3912580 * 2 = 7825160 B) Edit: /etc/security/limits.conf Add lines: oracle soft memlock 7825160 oracle hard memlock 7825160 ************************************************************************************* .75 * 3264 * 1024 * 1024 = 3004170240 kernel.shmmax = 3004170240 C) Edit /etc/sysctl.conf Add line:…
Read MoreSnapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database. i. e we can convert the physical standby database to snapshot standby.…
Read MoreSQL PROFILE IN ORACLE SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query.…
Read MoreCreating sqlplan Baselines Create sql tuning set ————————- begin DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => ‘&&sql_id’, DESCRIPTION => ‘SQL TUNE SET2’); END; / ——-SELECT DBMS_SQLTUNE.report_sql_monitor(type => ‘TEXT’, report_level=>’ALL’, SQL_ID=>’f2nydgpwc252s’) AS REPORT FROM DUAL; —…
Read MoreCurrent Status Of Redo Logs Primary Side SQL> select group#,sum(bytes/1024/1024)”Size in MB” from v$log group by group#; GROUP# Size in MB ——- ———- 1 750 2 750 3 750 SQL>…
Read More