Skip to main content

Posts

SQL Plan Management [ SPM ]

============================================= SQL> SHOW parameter baselines NAME                                 TYPE        VALUE ------------------------------------ ----------- ------- optimizer_capture_sql_plan_baselines boolean     FALSE optimizer_use_sql_plan_baselines     boolean     TRUE SQL> ============================================= Connect as SPM user and create a table as follows. -------------------------------------------------- SQL> CREATE TABLE EMPLOYEE (code number,dept char(100),address char(1000)); Table created. SQL> ============================================= Populate some rows in the table =================================== declare c1 number; begin for c1 IN 1..10000 ...
Post a Comment
Read more
Recent posts

Create ==> Gather ==> Export ==> Delete ==> Import Schema Statistics

================================================================================== create a table to hold the statistics ---------------------------------------- SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'stats'); PL/SQL procedure successfully completed. SQL> ================================================================================== SQL> DESC SYS.stats  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  STATID                                     ...
Post a Comment
Read more

CLSRSC-430: Failed to start rolling patch mode

############################################################ GRID PSU Failed on Node2: =========================== [root@server1 tmp]# /oracrs/oracle/product/12102/OPatch/opatchauto apply /path1/patches/23273629 -oh /oracrs/oracle/product/12102 -ocmrf /tmp/ocm.rsp OPatch Automation Tool Copyright (c)2014, Oracle Corporation. All rights reserved. OPatchauto Version : 12.1.0.1.10 OUI Version        : 12.1.0.2.0 Running from       : /oracrs/oracle/product/12102 opatchauto log file: /oracrs/oracle/product/12102/cfgtoollogs/opatchauto/23273629/opatch_gi_2016-11-25_06-13-44_deploy.log Parameter Validation: Successful Configuration Validation: Successful Patch Location: /path1/patches/23273629 Grid Infrastructure Patch(es): 21436941 23054246 23054327 23054341 DB Patch(es): 23054246 23054327 The following patch(es) are duplicate patches with patches installed in the Oracle Home.  [ 20299023] You hav...
2 comments
Read more

12c GRID PSU Patch Apply

################################################### GRID PSU Patch Analyze: Checks for Conflicts ============================================= [root@server1 cfgtoollogs]# /oracrs/oracle/product/12102/OPatch/opatchauto apply /path1/patches/23273629 -analyze OPatch Automation Tool Copyright (c)2014, Oracle Corporation. All rights reserved. OPatchauto Version : 12.1.0.1.10 OUI Version        : 12.1.0.2.0 Running from       : /oracrs/oracle/product/12102 opatchauto log file: /oracrs/oracle/product/12102/cfgtoollogs/opatchauto/23273629/opatch_gi_2016-11-25_21-57-51_analyze.log NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system. Parameter Validation: Successful Configuration Validation: Successful Patch Location: /path1/patches/23273629 Grid Infrastructure Patch(es): 21436941 23054246 23054327 23054341 DB Patch(es): 23054246 23054327 Patch Validation: Successful Grid Infrastructure home: /oracrs...
Post a Comment
Read more

Dataguard Recovery with RMAN SCN

On Standby: ############ SQL> SELECT CHECKPOINT_CHANGE# FROM v$datafile_header; CHECKPOINT_CHANGE# ==================            7691499            7691499            7691499            7691499            7691499 SQL> select current_scn from v$database; CURRENT_SCN ===========     7691498 SQL> SQL> SELECT ROUND((SYSDATE-MAX(FIRST_TIME))*24,2) "HOURS BEHIND" FROM V$LOG_HISTORY; HOURS BEHIND ============         5.5 SQL> On Prod: ######### SQL>  SELECT CHECKPOINT_CHANGE# FROM v$datafile_header; CHECKPOINT_CHANGE# ------------------            7699591            7699591 ...
Post a Comment
Read more

ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device (Doc ID 301830.1) Issue: ====== ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device Solution: ========= 1. Ask SA to increase "kernel.shmall" in /etc/sysctl.conf     Formula:     RAM * 1024 * 1024 * 1024 / page_size     Ex:-     RAM = 50GB     $getconf PAGE_SIZE ==> 4096     50*1024*1024*1024/4096 = 13107200 ==> kernel.shmall 2. After updating kernel.shmall, ask SA to do "sysctl -p" to activate changes. 3. Start the DB Note: ==== Kernel values will be set in below files 1. Updating below files doesn't require system reboot.     /proc/sys/kernel/shmmax     /proc/sys/kernel/shmall     /proc/sys/kernel/shmmni     Usage:  # echo "536870912" > /proc/sys/kernel/shmmax       ...
Post a Comment
Read more