bits
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| bits [2019/04/01 08:49] – stuart | bits [2025/03/11 11:34] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ==== Clean up RMAN catalog | + | * [[https:// |
| - | Query to find the oldest records in the catalog. Nothing should be more than a day older than the control_file_record_keep_time parameter. | + | ==== How to Create a Telegram Bot and Send Message via bot API ==== |
| + | * [[How to Create | ||
| + | * [[https:// | ||
| + | |||
| + | ==== Example of using pivot ==== | ||
| < | < | ||
| + | create table tt as (select/*+ materialize */ decode(owner,' | ||
| + | |||
| + | SYS INDEX | ||
| + | SYS INDEX | ||
| + | SYS TABLE | ||
| + | SYS INDEX | ||
| + | SYS TABLE | ||
| + | SYS INDEX | ||
| + | SYS VIEW | ||
| + | SYS VIEW | ||
| + | OTHERS PROCEDURE | ||
| + | SYS INDEX PARTITION | ||
| + | SYS INDEX PARTITION | ||
| + | SYS INDEX PARTITION | ||
| + | SYS INDEX PARTITION | ||
| + | SYS INDEX PARTITION | ||
| + | SYS INDEX PARTITION | ||
| + | ... | ||
| + | OTHERS SYNONYM | ||
| + | SYS VIEW | ||
| + | OTHERS SYNONYM | ||
| + | SYS VIEW | ||
| + | OTHERS SYNONYM | ||
| + | SYS VIEW | ||
| + | SYS INDEX PARTITION | ||
| + | SYS INDEX PARTITION | ||
| + | SYS LOB PARTITION | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS JOB | ||
| + | OTHERS SEQUENCE | ||
| + | OTHERS TABLE | ||
| + | OTHERS INDEX | ||
| + | OTHERS LOB | ||
| + | SYSTEM TABLE | ||
| + | SYSTEM INDEX | ||
| + | SYSTEM LOB | ||
| + | SYSTEM INDEX | ||
| + | SYSTEM INDEX | ||
| + | SYSTEM INDEX | ||
| + | SYSTEM TABLE PARTITION | ||
| + | SYSTEM TABLE | ||
| + | SYSTEM TABLE | ||
| + | SYSTEM TABLE | ||
| + | SYSTEM TABLE | ||
| + | SYSTEM INDEX | ||
| + | |||
| + | </ | ||
| + | < | ||
| + | select * from tt pivot ( count(*) for owner in (' | ||
| + | |||
| + | OBJECT_TYPE | ||
| + | ----------------------- ---------- ---------- ---------- | ||
| + | SEQUENCE | ||
| + | PACKAGE BODY | ||
| + | PROCEDURE | ||
| + | CONTEXT | ||
| + | TABLE SUBPARTITION | ||
| + | RULE SET 17 0 4 | ||
| + | RULE | ||
| + | FUNCTION | ||
| + | INDEX PARTITION | ||
| + | UNIFIED AUDIT POLICY | ||
| + | WINDOW | ||
| + | JOB | ||
| + | JOB CLASS | ||
| + | EVALUATION CONTEXT | ||
| + | PACKAGE | ||
| + | VIEW 6910 | ||
| + | LOB PARTITION | ||
| + | XML SCHEMA | ||
| + | TRIGGER | ||
| + | DIRECTORY | ||
| + | PROGRAM | ||
| + | QUEUE | ||
| + | INDEXTYPE | ||
| + | TABLE | ||
| + | EDITION | ||
| + | SYNONYM | ||
| + | CONTAINER | ||
| + | TYPE 1817 | ||
| + | RESOURCE PLAN | ||
| + | SCHEDULE | ||
| + | SCHEDULER GROUP 4 0 0 | ||
| + | CLUSTER | ||
| + | UNDEFINED | ||
| + | DATABASE LINK 1 0 16 | ||
| + | LIBRARY | ||
| + | OPERATOR | ||
| + | TABLE PARTITION | ||
| + | INDEX | ||
| + | LOB 279 | ||
| + | TYPE BODY 178 0 9 | ||
| + | CONSUMER GROUP 18 0 0 | ||
| + | DESTINATION | ||
| + | |||
| + | 42 rows selected. | ||
| + | </ | ||
| + | |||
| + | ==== Finding and Resetting User Passwords That Use the 10G Password Version ==== | ||
| + | When upgrading to 18c, passwords that only use version 10g will have to be changed. This is done by expiring the passwords for those users.\\ | ||
| + | Find the relevant usernames | ||
| + | < | ||
| + | SELECT USERNAME FROM DBA_USERS | ||
| + | WHERE ( PASSWORD_VERSIONS = '10G ' | ||
| + | OR PASSWORD_VERSIONS = '10G HTTP ') | ||
| + | AND USERNAME <> ' | ||
| + | </ | ||
| + | Set database access to be less restrictive than the default. Put this in sqlnet.ora | ||
| + | < | ||
| + | SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 | ||
| + | </ | ||
| + | Expire the passwords | ||
| + | < | ||
| + | ALTER USER username PASSWORD EXPIRE; | ||
| + | </ | ||
| + | |||
| + | ==== Export a subset (sample) of database rows using a package and datapump ==== | ||
| + | Using straight data pump to export a sample does not take referential constraints into account.\\ | ||
| + | Using this method will fix that oversight.\\ | ||
| + | [[http:// | ||
| + | |||
| + | ==== Change start time and duration of maintenance windows at the weekend ==== | ||
| + | < | ||
| + | col window_name | ||
| + | col resource_plan | ||
| + | col repeat_interval for a80 | ||
| + | col duration | ||
| set lines 1000 | set lines 1000 | ||
| - | select | + | select |
| - | , | + | / |
| - | , min(start_time) | + | |
| + | begin | ||
| + | dbms_scheduler.disable(name => ' | ||
| + | dbms_scheduler.disable(name => ' | ||
| + | |||
| + | dbms_scheduler.set_attribute ( name => ' | ||
| + | | ||
| + | | ||
| + | ); | ||
| + | dbms_scheduler.set_attribute ( name => ' | ||
| + | | ||
| + | | ||
| + | ); | ||
| + | |||
| + | dbms_scheduler.set_attribute ( name => ' | ||
| + | | ||
| + | | ||
| + | ); | ||
| + | dbms_scheduler.set_attribute ( name => ' | ||
| + | | ||
| + | | ||
| + | ); | ||
| + | dbms_scheduler.enable(name => ' | ||
| + | dbms_scheduler.enable(name => ' | ||
| + | end; | ||
| + | / | ||
| + | |||
| + | select window_name, | ||
| + | / | ||
| + | |||
| + | </ | ||
| + | |||
| + | ==== Show non default configuration settings in RMAN ==== | ||
| + | < | ||
| + | set lines 1000 | ||
| + | col name for a30 | ||
| + | col value for a100 | ||
| + | col con_id nopri | ||
| + | select * from v$rman_configuration; | ||
| + | </ | ||
| + | |||
| + | ==== How to set RMAN configure options using package DBMS_BACKUP_RESTORE ==== | ||
| + | Either use rman command line and specify: | ||
| + | < | ||
| + | configure controlfile autobackup on; | ||
| + | </ | ||
| + | or use SQL*Plus | ||
| + | < | ||
| + | var a number | ||
| + | exec :a := dbms_backup_restore.setconfig(' | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | print :a | ||
| + | |||
| + | A | ||
| + | ---------- | ||
| + | 1 | ||
| + | </ | ||
| + | Verify by either 'show all' in rman or as above selecting from v$rman_configuration. | ||
| + | < | ||
| + | CONF# NAME | ||
| + | ---------- ------------------------------ ---------------------------------------------------------------------------------------------------- | ||
| + | 1 CONTROLFILE AUTOBACKUP | ||
| + | 2 RETENTION POLICY | ||
| + | |||
| + | </ | ||
| + | |||
| + | Other options are dbms_backup_restore.deleteconfig(< | ||
| + | < | ||
| + | SQL> exec dbms_backup_restore.deleteconfig(1); | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | SQL> print a; | ||
| + | |||
| + | A | ||
| + | ---------- | ||
| + | |||
| + | |||
| + | SQL> select * from v$rman_configuration; | ||
| + | |||
| + | CONF# NAME | ||
| + | ---------- ------------------------------ ---------------------------------------------------------------------------------------------------- | ||
| + | 2 RETENTION POLICY | ||
| + | |||
| + | </ | ||
| + | or dbms_backup_restore.resetconfig to remove all customisations. | ||
| + | < | ||
| + | SQL> exec dbms_backup_restore.resetconfig; | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | SQL> select * from v$rman_configuration; | ||
| + | |||
| + | no rows selected | ||
| + | |||
| + | SQL> | ||
| + | </ | ||
| + | |||
| + | ==== Clean up RMAN catalog ==== | ||
| + | Show how far back the RMAN catalog goes for each instance | ||
| + | < | ||
| + | select db_name | ||
| + | , min(start_time) | ||
| + | , input_type | ||
| from | from | ||
| - | where 1=1 | + | group by db_name, |
| - | group by db_name | + | order by 2; |
| - | , | + | </ |
| - | order by 3 | + | produces something like |
| + | < | ||
| + | DB_NAME | ||
| + | -------- --------------------------- ------------- | ||
| + | EBSM | ||
| + | CRELSASP 25-JUN-16 18: | ||
| + | CRELSASP 29-JUN-16 18: | ||
| + | DOCT | ||
| + | CLNP2 08-JUL-16 12: | ||
| + | BILP2 08-JUL-16 12: | ||
| + | CRELSASD 08-JUL-16 16: | ||
| + | CLNP2 11-JUL-16 19: | ||
| + | CRELSASD 13-JUL-16 18: | ||
| + | BILP2 20-JUL-16 18: | ||
| + | BILF | ||
| + | FORMN 18-AUG-16 14: | ||
| + | CLNF 19-AUG-16 14: | ||
| + | LOANF 19-AUG-16 14: | ||
| + | FORMN 19-AUG-16 15: | ||
| + | LOANF 19-AUG-16 19: | ||
| + | BILF | ||
| + | FORMN 23-AUG-16 19: | ||
| + | CLNF 25-AUG-16 19: | ||
| + | ADSNA 27-AUG-16 12: | ||
| + | ... | ||
| + | </ | ||
| + | If these dates go back further than the retention period in the RMAN configuration then these entries need clearing out. This script will do it if the controlfile_record_keep_time is set correctly on all databases. | ||
| + | < | ||
| + | RMANUSR=" | ||
| + | RMANPWD=" | ||
| + | RMANCAT=${1: | ||
| + | |||
| + | # ======================================= | ||
| + | # thats it, nothing to change after here! | ||
| + | # ======================================= | ||
| + | export PATH="/ | ||
| + | ORAENV_ASK=NO | ||
| + | SIDLIST=$(ps -ef | grep [o]ra_pmon | awk ' | ||
| + | for i in ${SIDLIST} | ||
| + | do | ||
| + | export ORACLE_SID=" | ||
| + | unset ORACLE_HOME | ||
| + | . oraenv | ||
| + | if [[ " | ||
| + | echo " | ||
| + | continue | ||
| + | fi | ||
| + | |||
| + | # find out if the database is in archive log mode | ||
| + | LOG_TEXT=$(echo ' | ||
| + | LOG_MODE=$(echo $LOG_TEXT | perl -ne 'print " | ||
| + | if [[ " | ||
| + | echo "INFO: Instance ${i} is not archiving so will not be in catalog ${RMANCAT}, skipping." | ||
| + | continue | ||
| + | fi | ||
| + | |||
| + | # test we can see the database from the rman instance | ||
| + | # if this errors out, the instance probably is not in this catalog | ||
| + | RMANTEST=$(echo " | ||
| + | echo " | ||
| + | if [[ $? -eq 0 ]]; then | ||
| + | echo "INFO: Instance ${i} is not accessible from or not in catalog ${RMANCAT}, skipping." | ||
| + | continue | ||
| + | fi | ||
| + | echo | ||
| + | echo " | ||
| + | " | ||
| + | connect target / | ||
| + | connect catalog ${RMANUSR}/ | ||
| + | unregister database noprompt; | ||
| + | register database; | ||
| + | EORMAN | ||
| + | done | ||
| + | </ | ||
| + | ==== Unregister a database from RMAN catalog using PL/SQL procedure ==== | ||
| + | The UNREGISTER verb does not exist in 9i so have to use PL/SQL method. | ||
| + | < | ||
| + | Recovery Manager: Release 9.2.0.8.0 - 64bit Production | ||
| + | |||
| + | Copyright (c) 1995, 2002, Oracle Corporation. | ||
| + | |||
| + | connected to target database: PVCS (DBID=2561628519) | ||
| + | connected to recovery catalog database | ||
| + | |||
| + | RMAN> unregister database; | ||
| + | |||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== | ||
| + | RMAN-00571: =========================================================== | ||
| + | RMAN-00558: error encountered while parsing input commands | ||
| + | RMAN-01005: syntax error: found " | ||
| + | RMAN-01008: the bad identifier was: unregister | ||
| + | RMAN-01007: at line 1 column 1 file: standard input | ||
| + | </ | ||
| + | From SQL*Plus, get the db_key and dbid from the catalog | ||
| + | < | ||
| + | select db_key, dbid, name from rc_database where name = ' | ||
| + | </ | ||
| + | < | ||
| + | DB_KEY | ||
| + | ---------- ---------- -------- | ||
| + | | ||
| + | </ | ||
| + | Use these values as parameters to the packaged procedure | ||
| + | < | ||
| + | SQL> execute dbms_rcvcat.unregisterdatabase (5102549, 2561628519); | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | </ | ||
| + | |||
| + | ==== Install Oracle 18c database software from command line with silent option ==== | ||
| + | === Create a response file db_18c_sw_only.rsp === | ||
| + | Templates are normally in $ORACLE_HOME/ | ||
| + | < | ||
| + | oracle.install.responseFileVersion=/ | ||
| + | oracle.install.option=INSTALL_DB_SWONLY | ||
| + | UNIX_GROUP_NAME=oinstall | ||
| + | INVENTORY_LOCATION=/ | ||
| + | ORACLE_BASE=/ | ||
| + | oracle.install.db.InstallEdition=EE | ||
| + | oracle.install.db.OSDBA_GROUP=dba | ||
| + | oracle.install.db.OSOPER_GROUP= | ||
| + | oracle.install.db.OSBACKUPDBA_GROUP=dba | ||
| + | oracle.install.db.OSDGDBA_GROUP=dba | ||
| + | oracle.install.db.OSKMDBA_GROUP=dba | ||
| + | oracle.install.db.OSRACDBA_GROUP=dba | ||
| + | </ | ||
| + | === Run the installer === | ||
| + | < | ||
| + | cd $ORACLE_HOME | ||
| + | ./ | ||
| + | </ | ||
| + | ==== Create an Oracle 18c database from command line with silent option ==== | ||
| + | === Cut and paste this template into db_18c_no_options.dbt === | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | <option name=" | ||
| + | <option name=" | ||
| + | <option name=" | ||
| + | <option name=" | ||
| + | <option name=" | ||
| + | < | ||
| + | </ | ||
| + | <option name=" | ||
| + | <option name=" | ||
| + | < | ||
| + | </ | ||
| + | <option name=" | ||
| + | <option name=" | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | <SPfile useSPFile=" | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | === Run dbca to create the database === | ||
| + | < | ||
| + | $ORACLE_HOME/ | ||
| + | -createDatabase | ||
| + | -templateName $ORACLE_HOME/ | ||
| + | -gdbName oem \ | ||
| + | -Sid oem \ | ||
| + | -sysPassword qwertyuiop | ||
| + | -systemPassword asdfghjkl | ||
| + | -sysmanPassword qazwsxedc | ||
| + | </ | ||
| + | === To see what options are available or to create a responsefile with the appropriate answers... === | ||
| + | < | ||
| + | perl -ne 'print unless m/^\s*#/ or m/ | ||
| + | </ | ||
| + | ==== High TEMP tablespace usage by Enterprise Manager agent / dbsnmp user ==== | ||
| + | Stop the gathering of stats for the TEMP tablespace\\ Suggested by Oracle in | ||
| + | Use the following workaround to reduce the temp usage by emagent / dbsnmp: | ||
| + | < | ||
| + | |||
| + | exec dbms_stats.DELETE_TABLE_STATS(' | ||
| + | |||
| + | exec dbms_stats.LOCK_TABLE_STATS(' | ||
| + | |||
| + | alter system flush shared_pool; | ||
| + | </ | ||
| + | ==== Create a job to run every 15 minutes ==== | ||
| + | < | ||
| + | BEGIN | ||
| + | DBMS_SCHEDULER.create_job ( | ||
| + | job_name | ||
| + | comments | ||
| + | job_type | ||
| + | job_action | ||
| + | start_date | ||
| + | repeat_interval => ' | ||
| + | enabled | ||
| + | END; | ||
| / | / | ||
| </ | </ | ||
| + | ==== EM Agent DBSNMP Using Up Excessive Temp Space In Database ==== | ||
| + | Stop collecting stats on the TEMP tablespace\\ | ||
| + | Oracle recommends the following: | ||
| + | < | ||
| + | exec dbms_stats.delete_table_stats(' | ||
| + | exec dbms_stats.lock_table_stats(' | ||
| + | alter system flush shared_pool; | ||
| + | </ | ||
| + | ==== Shows Trace Filename for Current Session ==== | ||
| + | < | ||
| + | COLUMN value FORMAT A100 | ||
| + | |||
| + | SELECT value | ||
| + | FROM | ||
| + | WHERE name = ' | ||
| + | / | ||
| + | </ | ||
| + | ==== Fixed Objects Statistics and Why They are Important ==== | ||
| + | Reproduced from [[https:// | ||
| + | Fixed objects are the " | ||
| + | The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN).\\ | ||
| + | Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically used for SQL statement involving X$ tables when optimizer statistics are missing.\\ | ||
| + | The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a sub-optimal execution plan, which could cause severe performance problems in your system.\\ | ||
| + | It is for this reason that we strong recommend you gather fixed objects statistics. | ||
| + | |||
| + | Prior to Oracle Database 12c Release 1 fixed object statistics are not created or maintained by the automatic statistics gathering job.\\ | ||
| + | You can collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. | ||
| + | < | ||
| + | BEGIN | ||
| + | | ||
| + | END; | ||
| + | / | ||
| + | </ | ||
| + | The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. Blocks is always set to 0 since the X$ tables are in memory structures only and are not stored on disk.\\ | ||
| + | You must have the ANALYZE ANY DICTIONARY or SYSDBA privilege or the DBA role to update fixed object statistics. | ||
| + | |||
| + | Because of the transient nature of the X$ tables it is important that you gather fixed object statistics when there is a representative workload on the system. This may not always be feasible on large system due to additional resource needed to gather the statistics.\\ | ||
| + | If you can’t do it during peak load you should do it after the system has warmed up and the three key types of fixed object tables have been populated: | ||
| + | |||
| + | * Structural Data For example, views covering datafiles, controlfile contents, etc. | ||
| + | * Session-based Data For example, v$session, v$access, etc. | ||
| + | * Workload Data For example, v$sql, v$sql_plan etc. | ||
| + | |||
| + | It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module or make changes to the database configuration.\\ | ||
| + | For example if you increase the SGA size then all of the X$ tables that contain information about the buffer cache and shared pool may change significantly, | ||
| + | |||
| + | From Oracle Database 12c Release 1 the automatic statistics gathering job will gather statistics for fixed tables that have missing stats. For this to happen, there will need to be some time available inside the batch window after statistics for the other tables in the system have been gathered.\\ | ||
| + | Even with this new functionality, | ||
| + | |||
| + | ==== Show Undo Information ==== | ||
| + | < | ||
| + | SET PAGESIZE 60 | ||
| + | SET LINESIZE 300 | ||
| + | |||
| + | COLUMN username FORMAT A20 | ||
| + | COLUMN sid FORMAT 9999 | ||
| + | COLUMN serial# FORMAT 99999 | ||
| + | |||
| + | SELECT s.username, | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | FROM | ||
| + | | ||
| + | | ||
| + | | ||
| + | WHERE s.saddr = t.ses_addr | ||
| + | AND t.xidusn = r.usn | ||
| + | AND rs.segment_id = t.xidusn | ||
| + | ORDER BY t.used_ublk DESC | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== How much time is wasted waiting for redologs to be written ==== | ||
| + | An annoying message in the alertlog is | ||
| + | < | ||
| + | Thread 1 cannot allocate new log, sequence 170734 | ||
| + | Private strand flush not complete | ||
| + | </ | ||
| + | More info [[https:// | ||
| + | < | ||
| + | with my_data as (select sample_id, | ||
| + | cast(sample_time as date) as sample_time, | ||
| + | max(time_waited) / 1000000 as time_waited_s, | ||
| + | min(event) as event | ||
| + | from v$active_session_history | ||
| + | where event like '%log file switch%' | ||
| + | and session_state=' | ||
| + | group by sample_id, sample_time), | ||
| + | my_data2 AS (select row_number() over (order by sample_id) r, | ||
| + | sample_id, | ||
| + | sample_time, | ||
| + | decode(sample_id - lag(sample_id) over (order by sample_id), | ||
| + | time_waited_s, | ||
| + | event | ||
| + | from my_data) | ||
| + | select d.sample_time, | ||
| + | | ||
| + | | ||
| + | from (select d.*, | ||
| + | | ||
| + | | ||
| + | from my_data2 d | ||
| + | ) d | ||
| + | where diff is null | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | SAMPLE_TIME | ||
| + | --------------------------- ---------------------------------------------------------------- ------------- | ||
| + | 20-NOV-19 17: | ||
| + | 20-NOV-19 21: | ||
| + | 20-NOV-19 22: | ||
| + | 20-NOV-19 22: | ||
| + | 20-NOV-19 23: | ||
| + | 21-NOV-19 02: | ||
| + | 21-NOV-19 06: | ||
| + | 21-NOV-19 09: | ||
| + | 21-NOV-19 10: | ||
| + | | ||
| + | sum .297439 | ||
| + | </ | ||
| + | so only a third of a second lost since yesterday | ||
| + | |||
| + | ==== Set / change init.ora parameters (well the memory version) for all sessions ==== | ||
| + | SQL*Plus and anonymous PL/SQL blocks cannot avoid soft parsing the way stored procedures can. session_cached_cursors can be set to help this situation by caching the DML and reusing it.\\ | ||
| + | See also [[https:// | ||
| + | and [[https:// | ||
| + | In case you need to set a parameter that is only modifiable in the spfile, it can still be changed at session level. This changes it for all current sessions.\\ | ||
| + | For example the session_cached_cursors is not modifiable with " | ||
| + | |||
| + | < | ||
| + | begin | ||
| + | for c in (select sid, serial# from v$session) loop | ||
| + | | ||
| + | end loop; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Check if PGA_AGGREGATE_TARGET value is set optimally ==== | ||
| + | * [[https:// | ||
| + | This will return the percentage hit ratio for the current setting of pga_aggregate_target | ||
| + | < | ||
| + | select percs.cache_hit_perc | ||
| + | from ( | ||
| + | | ||
| + | | ||
| + | , | ||
| + | | ||
| + | | ||
| + | | ||
| + | ) percs | ||
| + | where rownum = 1 | ||
| + | / | ||
| + | </ | ||
| + | if this value is deemed to be a little off, use this to check what the best value would be | ||
| + | < | ||
| + | select round(pga_target_for_estimate/ | ||
| + | | ||
| + | | ||
| + | from | ||
| + | </ | ||
| + | < | ||
| + | | ||
| + | ---------- -------------- -------------------- | ||
| + | | ||
| + | | ||
| + | 1536 | ||
| + | 2304 | ||
| + | 3072 | ||
| + | 3686 | ||
| + | 4301 | ||
| + | 4915 | ||
| + | 5530 | ||
| + | 6144 | ||
| + | 9216 100 89848 | ||
| + | | ||
| + | | ||
| + | | ||
| + | </ | ||
| + | so here, setting pga_aggregate_target to 3G would give 99% hit rate. | ||
| + | |||
| + | ==== The secrets of NLS_LANG ==== | ||
| + | * [[https:// | ||
| + | |||
| + | Shows: | ||
| + | * Character Set of Database | ||
| + | * Some Facts of NLS_LANG | ||
| + | * Definition of NLS_LANG | ||
| + | * Proper Value of NLS_LANG | ||
| + | * NLS_LANG with SQL*Plus | ||
| + | * NLS_LANG with .sql Files | ||
| + | |||
| + | |||
| + | ==== Formatting results from an SQL*Plus query in shell ==== | ||
| + | * [[http:// | ||
| + | Trick: Using grep on SQL result set to filter and format lines | ||
| + | < | ||
| + | F_list_OH () { | ||
| + | |||
| + | F_colordef | ||
| + | echo | ||
| + | echo " | ||
| + | |||
| + | RESULT=`$SQLPLUS -S -L ${REPO_CREDENTIALS} << | ||
| + | set line 200 pages 1000 | ||
| + | set feed off head off | ||
| + | col name format a32 | ||
| + | alter session set nls_timestamp_format=' | ||
| + | select name||';' | ||
| + | EOF | ||
| + | ` | ||
| + | echo | ||
| + | printf "%-35s %-10s %-18s\n" | ||
| + | echo " | ||
| + | |||
| + | for line in $RESULT ; do | ||
| + | L_GI_Name=`echo $line | awk -F\; ' | ||
| + | L_GI_Date=`echo $line | awk -F\; ' | ||
| + | L_GI_Path=`echo $line | awk -F\; ' | ||
| + | L_Installed=`F_OH_Installed " | ||
| + | printf "%-35s %-10s %-18s\n" | ||
| + | done | ||
| + | } | ||
| + | </ | ||
| + | ==== Investigate High Number of Hard Parses in Oracle ==== | ||
| + | * [[https:// | ||
| + | |||
| + | ==== Solving Oracle Log File Sync Waits Caused by High Commit Frequency ==== | ||
| + | * [[https:// | ||
| + | |||
| + | |||
| + | |||
bits.1554108569.txt.gz · Last modified: 2019/04/01 08:49 by stuart