Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for the ‘Uncategorized’ Category

« Previous Entries
Next Entries »

How to monitor/troubleshoot RAC Stack … Answer is “ORAchk”

Posted by FatDBA on March 2, 2015

Below provided are steps to call and a sample health check report generated for a 2 node RAC system via ORAchk Tool.
link to download: https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=kfoe5ynno_4&_afrLoop=178033262862018
Note ID: 1268927.2

=======================================================================

[oracle@dixitdb12v dixit]$ ./orachk
This version of orachk was released on 09-Oct-2014 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.

Do you want to continue running this version? [y/n][y]y

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /opt/app/grid/11.2.0/grid?[y/n][y]y

Checking ssh user equivalency settings on all nodes in cluster

Node dixitdb13v is configured for ssh user equivalency for oracle user

Searching for running databases . . . . .

. .
List of running databases registered in OCR
1. TESTRAC
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].1
. .

Checking Status of Oracle Software Stack – Clusterware, ASM, RDBMS

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
——————————————————————————————————-
Oracle Stack Status
——————————————————————————————————-
Host Name CRS Installed RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
——————————————————————————————————-
dixitdb12v Yes Yes Yes Yes Yes TESTRAC1
dixitdb13v Yes Yes Yes Yes Yes TESTRAC2
——————————————————————————————————-

Copying plug-ins

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . .

18 of the included audit checks require root privileged data collection . If sudo is not configured or the root password is not available, audit checks which require root privileged data collection can be skipped.

1. Enter 1 if you will enter root password for each host when prompted

2. Enter 2 if you have sudo configured for oracle user to execute root_orachk.sh script

3. Enter 3 to skip the root privileged collections

4. Enter 4 to exit and work with the SA to configure sudo or to arrange for root access and run the tool later.

Please indicate your selection from one of the above options for root access[1-4][1]:- 1

*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***

Collections and audit checks log file is
/home/oracle/dixit/orachk_dixitdb12v_TESTRAC_022715_032812/log/orachk.log

Running orachk in serial mode because expect(/usr/bin/expect) is not available to supply root passwords on remote nodes

NOTICE: Installing the expect utility (/usr/bin/expect) will allow orachk to gather root passwords at the beginning of the process and execute orachk on all nodes in parallel speeding up the entire process. For more info – http://www.nist.gov/el/msid/expect.cfm. Expect is available for all major platforms. See User Guide for more details.

Checking for prompts in /home/oracle/.bash_profile on dixitdb12v for oracle user…

Checking for prompts in /home/oracle/.bash_profile on dixitdb13v for oracle user…

=============================================================
Node name – dixitdb12v
=============================================================

Collecting – ASM Disk Groups
Collecting – ASM Disk I/O stats
Collecting – ASM Diskgroup Attributes
Collecting – ASM disk partnership imbalance
Collecting – ASM diskgroup attributes
Collecting – ASM diskgroup usable free space
Collecting – ASM initialization parameters
Collecting – Active sessions load balance for TESTRAC database
Collecting – Archived Destination Status for TESTRAC database
Collecting – Cluster Interconnect Config for TESTRAC database
Collecting – Database Archive Destinations for TESTRAC database
Collecting – Database Files for TESTRAC database
Collecting – Database Instance Settings for TESTRAC database
Collecting – Database Parameters for TESTRAC database
Collecting – Database Parameters for TESTRAC database
Collecting – Database Properties for TESTRAC database
Collecting – Database Registry for TESTRAC database
Collecting – Database Sequences for TESTRAC database
Collecting – Database Undocumented Parameters for TESTRAC database
Collecting – Database Undocumented Parameters for TESTRAC database
Collecting – Database Workload Services for TESTRAC database
Collecting – Dataguard Status for TESTRAC database
Collecting – Files not opened by ASM
Collecting – Log Sequence Numbers for TESTRAC database
Collecting – Percentage of asm disk Imbalance
Collecting – Process for shipping Redo to standby for TESTRAC database
Collecting – RDBMS Feature Usage for TESTRAC database
Collecting – Redo Log information for TESTRAC database
Collecting – Standby redo log creation status before switchover for TESTRAC database
Collecting – /proc/cmdline
Collecting – /proc/modules
Collecting – CPU Information
Collecting – CRS active version
Collecting – CRS oifcfg
Collecting – CRS software version
Collecting – CSS Reboot time
Collecting – CSS disktimout
Collecting – Cluster interconnect (clusterware)
Collecting – Clusterware OCR healthcheck
Collecting – Clusterware Resource Status
Collecting – DiskFree Information
Collecting – DiskMount Information
Collecting – Huge pages configuration
Collecting – Interconnect network card speed
Collecting – Kernel parameters
Collecting – Maximum number of semaphore sets on system
Collecting – Maximum number of semaphores on system
Collecting – Maximum number of semaphores per semaphore set
Collecting – Memory Information
Collecting – NUMA Configuration
Collecting – Network Interface Configuration
Collecting – Network Performance
Collecting – Network Service Switch
Collecting – OS Packages
Collecting – OS version
Collecting – Operating system release information and kernel version
Collecting – Oracle Executable Attributes
Collecting – Patches for Grid Infrastructure
Collecting – Patches for RDBMS Home
Collecting – Shared memory segments
Collecting – Table of file system defaults
Collecting – Voting disks (clusterware)
Collecting – number of semaphore operations per semop system call
Preparing to run root privileged commands dixitdb12v. Please enter root password when prompted.
root@dixitdb12v’s password:
Collecting – ACFS Volumes status
Collecting – Broadcast Requirements for Networks
Collecting – CRS user time zone check
Collecting – Custom rc init scripts (rc.local)
Collecting – Disk Information
Collecting – Grid Infastructure user shell limits configuration
Collecting – Interconnect interface config
Collecting – Network interface stats
Collecting – Number of RDBMS LMS running in real time
Collecting – OLR Integrity
Collecting – Root user limits
Collecting – Verify no database server kernel out of memory errors
Collecting – root time zone check
Collecting – slabinfo

Data collections completed. Checking best practices on dixitdb12v.
————————————————————————————–

WARNING => Cluster Health Monitor (CHM) repository does not provide recommended level of retention
INFO => Important Automatic Storage Management (ASM) Notes and Technical White Papers
FAIL => Bash is vulnerable to code injection (CVE-2014-6271)
WARNING => ARCHIVELOG mode is disabled for TESTRAC
INFO => $CRS_HOME/log/hostname/client directory has too many older log files.
INFO => ORA-00600 errors found in alert log for TESTRAC
INFO => At some times checkpoints are not being completed for TESTRAC
INFO => Some data or temp files are not autoextensible for TESTRAC
INFO => oracleasm (asmlib) module is NOT loaded
WARNING => Shell limit soft nproc for DB is NOT configured according to recommendation
WARNING => kernel.shmmax parameter is NOT configured according to recommendation
WARNING => Database Parameter memory_target is not set to the recommended value on TESTRAC1 instance
FAIL => Operating system hugepages count does not satisfy total SGA requirements
WARNING => NIC bonding is not configured for interconnect
WARNING => NIC bonding is NOT configured for public network (VIP)
WARNING => OSWatcher is not running as is recommended.
INFO => Jumbo frames (MTU >= 8192) are not configured for interconnect
WARNING => NTP is not running with correct setting
WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for TESTRAC
FAIL => Flashback on PRIMARY is not configured for TESTRAC
INFO => Operational Best Practices
INFO => Database Consolidation Best Practices
INFO => Computer failure prevention best practices
INFO => Data corruption prevention best practices
INFO => Logical corruption prevention best practices
INFO => Database/Cluster/Site failure prevention best practices
INFO => Client failover operational best practices
WARNING => fast_start_mttr_target should be greater than or equal to 300. on TESTRAC1 instance

INFO => Information about hanganalyze and systemstate dump
WARNING => Package unixODBC-2.2.14-11.el6-i686 is recommended but NOT installed
WARNING => Package unixODBC-devel-2.2.14-11.el6-i686 is recommended but NOT installed
FAIL => Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for TESTRAC
INFO => Database failure prevention best practices
WARNING => Database Archivelog Mode should be set to ARCHIVELOG for TESTRAC
FAIL => Primary database is NOT protected with Data Guard (standby database) for real-time data protection and availability for TESTRAC
INFO => Parallel Execution Health-Checks and Diagnostics Reports for TESTRAC
WARNING => Package unixODBC-devel-2.2.14-11.el6-x86_64 is recommended but NOT installed
WARNING => Linux transparent huge pages are enabled
WARNING => vm.min_free_kbytes should be set as recommended.
INFO => Oracle recovery manager(rman) best practices
WARNING => RMAN controlfile autobackup should be set to ON for TESTRAC
INFO => Consider increasing the COREDUMPSIZE size
INFO => Consider investigating changes to the schema objects such as DDLs or new object creation for TESTRAC
INFO => Consider investigating the frequency of SGA resize operations and take corrective action for TESTRAC

Best Practice checking completed.Checking recommended patches on dixitdb12v.
———————————————————————————

Collecting patch inventory on CRS HOME /opt/app/grid/11.2.0/grid
Collecting patch inventory on ORACLE_HOME /opt/app/oracle/product/11.2.0/dbhome_1
———————————————————————————
1 Recommended CRS patches for 112040 from /opt/app/grid/11.2.0/grid on dixitdb12v
———————————————————————————
Patch# CRS ASM RDBMS RDBMS_HOME Patch-Description
———————————————————————————
19769489 no yes /opt/app/oracle/product/11.2.0/dbhome_1Patch description: “Database Patch Set Update : 11.2.0.4.5 (19769489)”
———————————————————————————

———————————————————————————
1 Recommended RDBMS patches for 112040 from /opt/app/oracle/product/11.2.0/dbhome_1 on dixitdb12v
———————————————————————————
Patch# RDBMS ASM type Patch-Description
———————————————————————————
19769489 yes merge Patch description: “Database Patch Set Update : 11.2.0.4.5 (19769489)”
———————————————————————————
———————————————————————————

———————————————————————————
Clusterware patches summary report
———————————————————————————
Total patches Applied on CRS Applied on RDBMS Applied on ASM
———————————————————————————
1 0 1 0
———————————————————————————

———————————————————————————
RDBMS homes patches summary report
———————————————————————————
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME
———————————————————————————
1 1 0 /opt/app/oracle/product/11.2.0/dbhome_1
———————————————————————————

=============================================================
Node name – dixitdb13v
=============================================================

Collecting – /proc/cmdline
Collecting – /proc/modules
Collecting – CPU Information
Collecting – CRS active version
Collecting – CRS oifcfg
Collecting – CRS software version
Collecting – Cluster interconnect (clusterware)
Collecting – DiskFree Information
Collecting – DiskMount Information
Collecting – Huge pages configuration
Collecting – Interconnect network card speed
Collecting – Kernel parameters
Collecting – Maximum number of semaphore sets on system
Collecting – Maximum number of semaphores on system
Collecting – Maximum number of semaphores per semaphore set
Collecting – Memory Information
Collecting – NUMA Configuration
Collecting – Network Interface Configuration
Collecting – Network Performance
Collecting – Network Service Switch
Collecting – OS Packages
Collecting – OS version
Collecting – Operating system release information and kernel version
Collecting – Oracle Executable Attributes
Collecting – Patches for Grid Infrastructure
Collecting – Patches for RDBMS Home
Collecting – Shared memory segments
Collecting – Table of file system defaults
Collecting – number of semaphore operations per semop system call
Preparing to run root privileged commands dixitdb13v. Please enter root password when prompted.
root@dixitdb13v’s password:

Data collections completed. Checking best practices on dixitdb13v.
————————————————————————————–

FAIL => Bash is vulnerable to code injection (CVE-2014-6271)
INFO => $CRS_HOME/log/hostname/client directory has too many older log files.
INFO => ORA-00600 errors found in alert log for TESTRAC
INFO => At some times checkpoints are not being completed for TESTRAC
INFO => oracleasm (asmlib) module is NOT loaded
WARNING => Shell limit soft nproc for DB is NOT configured according to recommendation
WARNING => kernel.shmmax parameter is NOT configured according to recommendation
WARNING => Database Parameter memory_target is not set to the recommended value on TESTRAC2 instance
FAIL => Operating system hugepages count does not satisfy total SGA requirements
WARNING => NIC bonding is not configured for interconnect
WARNING => NIC bonding is NOT configured for public network (VIP)
WARNING => OSWatcher is not running as is recommended.
INFO => Jumbo frames (MTU >= 8192) are not configured for interconnect
WARNING => NTP is not running with correct setting
WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for TESTRAC
WARNING => fast_start_mttr_target should be greater than or equal to 300. on TESTRAC2 instance

INFO => IMPORTANT: Oracle Database Patch 17478514 PSU is NOT applied to RDBMS Home /opt/app/oracle/product/11.2.0/dbhome_1
WARNING => Package unixODBC-2.2.14-11.el6-i686 is recommended but NOT installed
WARNING => Package unixODBC-devel-2.2.14-11.el6-i686 is recommended but NOT installed
WARNING => Package unixODBC-devel-2.2.14-11.el6-x86_64 is recommended but NOT installed
WARNING => Linux transparent huge pages are enabled
WARNING => vm.min_free_kbytes should be set as recommended.
INFO => Consider increasing the COREDUMPSIZE size

Best Practice checking completed.Checking recommended patches on dixitdb13v.
———————————————————————————

Collecting patch inventory on CRS HOME /opt/app/grid/11.2.0/grid
Collecting patch inventory on ORACLE_HOME /opt/app/oracle/product/11.2.0/dbhome_1
———————————————————————————
1 Recommended CRS patches for 112040 from /opt/app/grid/11.2.0/grid on dixitdb13v
———————————————————————————
Patch# CRS ASM RDBMS RDBMS_HOME Patch-Description
———————————————————————————
18706472 no no /opt/app/oracle/product/11.2.0/dbhome_1GRID INFRASTRUCTURE SYSTEM PATCH 11.2.0.4.3
———————————————————————————

———————————————————————————
1 Recommended RDBMS patches for 112040 from /opt/app/oracle/product/11.2.0/dbhome_1 on dixitdb13v
———————————————————————————
Patch# RDBMS ASM type Patch-Description
———————————————————————————
18706472 no merge GRID INFRASTRUCTURE SYSTEM PATCH 11.2.0.4.3
———————————————————————————
———————————————————————————

———————————————————————————
Clusterware patches summary report
———————————————————————————
Total patches Applied on CRS Applied on RDBMS Applied on ASM
———————————————————————————
1 0 0 0
———————————————————————————

———————————————————————————
RDBMS homes patches summary report
———————————————————————————
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME
———————————————————————————
1 0 0 /opt/app/oracle/product/11.2.0/dbhome_1
———————————————————————————

———————————————————————————
CLUSTERWIDE CHECKS
———————————————————————————
———————————————————————————

Detailed report (html) – /home/oracle/dixit/orachk_dixitdb12v_TESTRAC_022715_032812/orachk_dixitdb12v_TESTRAC_022715_032812.html

UPLOAD(if required) – /home/oracle/dixit/orachk_dixitdb12v_TESTRAC_022715_032812.zip

Thanks
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

Online Patching & Bug Fixing — > : Bug 16342845 : EXCESSIVE CPU IN DBW PROCESSES FOR FAST OBJECT CHECKPOINTSDB

Posted by FatDBA on March 2, 2015

During further investigation we found that all the Database Writers started consuming high resources and waits on ‘DB FILE ASYNC I/O SUBMIT’ event and all rest of the database sessions started waiting on ‘enq: KO – Fast Object Checkpoint’

We have one most critical production system running on oracle 11.2.0.3 on the top of RHEL.
Recently we started experiencing a very bizarre and weird issue where applications timeouts happens intermittently and during that specific period extremely slow IO operations happens at the database level.

While investigation found that during the issue DB started throwing huge and frequent waits on ‘enq: KO – fast object checkpoint’ and ‘db file async I/O submit’ events and all of the queries in the database during the period starts snailing.
At the same time all the 8 available DBWR processes started waiting on “db file async I/O Submit” wait event and seems takes time to write blocks to the disk and this leads to late check-pointing in the system which in turn slow down database.

DB_Waits_Stats-duringSlowness-onetmproduction___mmma

From AWR
—————
foreground wait event
enq: KO – fast object checkpoint — 70% of the DB time.

background wait event
db file async I/O submit — 90% of bg time

Wait Event Histogram
———————-
db file async I/O submit — 100% >1s (% of waits)
enq: CR – block range reuse ckpt — 100% >1s (% of waits)
enq: KO – fast object checkpoint — 100% >1s (% of waits)
enq: RO – fast object reuse — 100% >1s (% of waits)

After weeks of probing and hours of analyzing system state dumps, Hang analyze and writer process traces we reached to a conclusion that we have strike by a Bug 16342845 : EXCESSIVE CPU IN DBW PROCESSES FOR FAST OBJECT CHECKPOINTSDB

Below mentioned are steps performed to apply the recommended patch.
PRIMARY: disable log shipping
STANDBY: stop MRP

1. Disable Log Shipping on Primary Database.
2. Stop MRP process on Physical Standby Database.
3. Apply the patch first in to Physical Standby (online)
4. Monitor the performance on the Phy STDBY db.
5. Will apply the patch on Logical Standby Database.
6. Monitor the logical database performance.
7. Will apply the patch on Primary Database.
8. Monitor the system performance.
9. Start Log shipping on Primary DB end.
10.Start MRP process on Standby database.

Step 1.
Disable log shipping on Primary DB.
SQL> show parameter LOG_ARCHIVE_DEST_

NAME TYPE VALUE
———————————— ——————————– ——————————
log_archive_dest_1 string location=”/vol5/oracle/dixit_db_
arc/dixitdb/arch”, valid_for=(A
LL_LOGFILES,ALL_ROLES)

log_archive_dest_2 string service=”dixitdb_al”, LGWR ASYNC
NOAFFIRM delay=0 optional com
pression=disable max_failure=0
max_connections=1 reopen=300
db_unique_name=”dixitdb_al” net_
timeout=30, valid_for=(all_log
files,primary_role)

log_archive_dest_3 string service=”dixitdb_r”, NOAFFIRM AS
YNC VALID_FOR=(ALL_LOGFILES,PR
IMARY_ROLE) DB_UNIQUE_NAME=one
tm_r

alter system set log_archive_dest_state_2=defer scope=both;

Step 2:
Stop MRP process on Physical Standby Database.

Before
PROCESS STATUS DELAY_MINS
——— ———— ———-
MRP0 WAIT_FOR_LOG 0

SQL> alter database recover managed standby database cancel;

After cancelling recover process the MRP automatically stops.

PROCESS STATUS DELAY_MINS
——— ———— ———-

Step 3:
Opatch online apply – common to all environments.

Recommendations
1. Take binaries backup (ORACLE_HOME).
2. Take Full DB Backup.
3. Check Opatch Prerequisites in order to identify and resolve any patch conflicts.

-bash-3.2$ export PATH=$PATH:$ORACLE_HOME/OPatch

-bash-3.2$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /opt/app/oracle/product/11gR2
Central Inventory : /opt/app/oracle/oraInventory
from : /opt/app/oracle/product/11gR2/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/opatch2015-03-01_05-34-42AM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

-bash-3.2$ opatch apply online -connectString dixitdb_al:sys:xxxxxxx
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/app/oracle/product/11gR2
Central Inventory : /opt/app/oracle/oraInventory
from : /opt/app/oracle/product/11gR2/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/20505138_Mar_01_2015_05_35_29/apply2015-03-01_05-35-29AM_1.log

The patch should be applied/rolled back in ‘-all_nodes’ mode only.
Converting the RAC mode to ‘-all_nodes’ mode.
Applying interim patch ‘20505138’ to OH ‘/opt/app/oracle/product/11gR2’
Verifying environment and performing prerequisite checks…
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Backing up files…

Patching component oracle.rdbms, 11.2.0.3.0…
Installing and enabling the online patch ‘bug20505138.pch’, on database ‘dixitdb_al’.

Verifying the update…
Patch 20505138 successfully applied
Log file location: /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/20505138_Mar_01_2015_05_35_29/apply2015-03-01_05-35-29AM_1.log

OPatch succeeded.


From Alert log during the patch apply:

Sun Mar 01 06:17:13 2015
Patch file bug20505138.pch is out of sync with oracle binary; performing fixup
Patch file bug20505138.pch has been synced with oracle binary
Patch bug20505138.pch Installed – Update #1
Patch bug20505138.pch Enabled – Update #2
Sun Mar 01 06:17:15 2015
Online patch bug20505138.pch has been installed
Online patch bug20505138.pch has been enabled

-bash-3.2$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/app/oracle/product/11gR2
Central Inventory : /opt/app/oracle/oraInventory
from : /opt/app/oracle/product/11gR2/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/opatch2015-03-01_05-35-53AM_1.log

Lsinventory Output file location : /opt/app/oracle/product/11gR2/cfgtoollogs/opatch/lsinv/lsinventory2015-03-01_05-35-53AM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Interim patches (5) :

Patch (online) 20505138: applied on Sun Mar 01 05:35:42 CET 2015
Unique Patch ID: 18568953
Created on 9 Feb 2015, 19:34:29 hrs PST8PDT
Bugs fixed:
16367081, 16463153, 16342845


‘Hope That Helps’
Thanks
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

How to check/Identify archival gaps in Standby Environment.

Posted by FatDBA on March 2, 2015

ON PRIMARY DATABASE
=====================

SQL> SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;  2    3    4

Thread Last Sequence Generated
———- ———————–
1                  105334
1                  105334
1                  105334

SQL> set time on
06:26:03 SQL>

06:28:04 SQL> alter system switch logfile;

System altered.

06:28:15 SQL> /.

System altered.

06:28:17 SQL> /

System altered.

06:28:17 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vol5/oracle/dixit_db_arc/dixitdb/arch
Oldest online log sequence     105334
Next log sequence to archive   105338
Current log sequence           105338

ON STANDBY DATABASE
======================

SQL> select process, status, THREAD#,SEQUENCE#,DELAY_MINS from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105298          0
ARCH      CLOSING               1     105299          0
ARCH      CLOSING               1     105326          0
ARCH      CLOSING               1     105327          0
ARCH      CLOSING               1     105328          0
ARCH      CLOSING               1     105329          0
ARCH      CLOSING               1     105330          0
ARCH      CLOSING               1     105334          0
ARCH      CLOSING               1     105306          0
ARCH      CLOSING               1     105307          0
ARCH      CLOSING               1     105308          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105309          0
ARCH      CLOSING               1     105310          0
ARCH      CLOSING               1     105311          0
ARCH      CLOSING               1     105312          0
ARCH      CLOSING               1     105285          0
ARCH      CLOSING               1     105313          0
ARCH      CLOSING               1     105314          0
ARCH      CLOSING               1     105315          0
ARCH      CLOSING               1     105316          0
ARCH      CLOSING               1     105317          0
ARCH      CLOSING               1     105318          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105319          0
ARCH      CLOSING               1     105320          0
ARCH      CLOSING               1     105321          0
ARCH      CLOSING               1     105322          0
ARCH      CLOSING               1     105323          0
ARCH      CLOSING               1     105324          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               1     105325          0
RFS       IDLE                  1     105335          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG          1     105335          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

40 rows selected.

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                 105334                105334          0

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> set time on
06:26:06 SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
——— ——————– —————-
dixitdb     READ ONLY WITH APPLY PHYSICAL STANDBY

06:28:20 SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                 105337                105337          0

06:28:32 SQL>

06:29:35 SQL> select process, status, THREAD#,SEQUENCE#,DELAY_MINS from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105298          0
ARCH      CLOSING               1     105299          0
ARCH      CLOSING               1     105326          0
ARCH      CLOSING               1     105327          0
ARCH      CLOSING               1     105328          0
ARCH      CLOSING               1     105329          0
ARCH      CLOSING               1     105330          0
ARCH      CLOSING               1     105334          0
ARCH      CLOSING               1     105306          0
ARCH      CLOSING               1     105335          0
ARCH      CLOSING               1     105336          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105337          0
ARCH      CLOSING               1     105310          0
ARCH      CLOSING               1     105311          0
ARCH      CLOSING               1     105312          0
ARCH      CLOSING               1     105285          0
ARCH      CLOSING               1     105313          0
ARCH      CLOSING               1     105314          0
ARCH      CLOSING               1     105315          0
ARCH      CLOSING               1     105316          0
ARCH      CLOSING               1     105317          0
ARCH      CLOSING               1     105318          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
ARCH      CLOSING               1     105319          0
ARCH      CLOSING               1     105320          0
ARCH      CLOSING               1     105321          0
ARCH      CLOSING               1     105322          0
ARCH      CLOSING               1     105323          0
ARCH      CLOSING               1     105324          0
ARCH      CONNECTED             0          0          0
ARCH      CLOSING               1     105325          0
RFS       IDLE                  1     105338          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

PROCESS   STATUS          THREAD#  SEQUENCE# DELAY_MINS
——— ———— ———- ———- ———-
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0
MRP0      WAIT_FOR_LOG          1     105338          0
RFS       IDLE                  0          0          0
RFS       IDLE                  0          0          0

Thanks
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

EXECUTION PLAN: “automatic DOP: skipped because of IO calibrate statistics are missing”

Posted by FatDBA on September 23, 2014

Recently during one Performance Problem i have attached one better SQL Profile to the statement which includes to add DOP (Degree Of Parallelism) to reduce the impacts of a definite FTS (Full Table Scan) but found one NOTE coming during the execution plan generation which reads
“automatic DOP: skipped because of IO calibrate statistics are missing”

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_33935’,task_owner => ‘SYS’, replace => TRUE, profile_type =>DBMS_SQLTUNE.PX_PROFILE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.86
SQL> explain plan for select count(*) from DIXIT_EVW_ETAILQ;

Explained.

Elapsed: 00:00:00.03
SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————————————–

————————————————————————————————————————————-
Plan hash value: 584586630

—————————————————————————————————————-
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
—————————————————————————————————————-
|   0 | SELECT STATEMENT       |                  |     1 | 36776   (1)| 00:07:22 |        |      |            |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                  |   177M| 36776   (1)| 00:07:22 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| DIXIT_EVW_ETAILQ |   177M| 36776   (1)| 00:07:22 |  Q1,00 | PCWP |            |
—————————————————————————————————————-

Note
—–
   – automatic DOP: skipped because of IO calibrate statistics are missing
   – SQL profile “SYS_SQLPROF_0148a0b0821b0005” used for this statement

The ‘AUTOMATIC DOP’ is skipped because I/O calibration is not run to gather the required statistics. Required statistics can be collected using DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
——————————-
NOT AVAILABLE

DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
–DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/

max_iops = 5944
latency = 9
max_mbps = 75

18 rows selected.

Elapsed: 00:00:00.11

Issue:
If using DBMS_RESOURCE_MANAGER.CALIBRATE_IO there are times when you might recieve beloe error message
ORA-56708: Could not find any datafiles with asynchronous i/o capability

Resolution:
Then we need to enable asynch I/O, set below two values to mentioned settings in the init.ora file.

disk_asynch_io = true
filesystemio_options = asynch

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
——————–
AVAILABLE

Now you can implement the DOP for the query and this way you can reduce FTS impacts.

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Something Strikingly odd with Oracle: DB with Two ‘Current’ log-files!!

Posted by FatDBA on January 8, 2014

Something real uncanny happened with one of our databases recently: There were 2 log-files with CURRENT status: Strange right!!!

SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024 as SIZE_MB,MEMBERS,STATUS from v$log;
GROUP#  SEQUENCE#      SIZE_MB    MEMBERS STATUS
———- ———- ———- ———-   —————
1       15651       50          2 INACTIVE
3       15653       50          2 CURRENT
2       15652       50          2 CURRENT
4       15650       50          2 INACTIVE

Found the ‘Notorious’ Generic error ORA-600 in alert log :
ORA-00600: internal error code, arguments: [3705], [1], [3], [2], [8], [], [], []

We had to perform point-in-time recovery up to 15652 sequence and we were all good.

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

‘Weird’ Error with Oracle’s “Log Errors” potentiality: — PL/SQL: ORA-00972: identifier is too long

Posted by FatDBA on November 20, 2013

Going to discuss one of the uncanny situation faced by me couple of minutes ago on one of my Test Machine. (Yes!, work on Vacations as well 🙂 )

One of the coolest features introduced in Oracle 10gR2 is the ability to log errors in large DML commands such as
“INSERT INTO table_name SELECT ….”.

One silly scenario I just came across while implementing the following.
I wanted to create an error log on a table who’s name is 30 characters long

PRASHANT.DIXIT_DEV07> BEGIN
2        DBMS_ERRLOG.CREATE_ERROR_LOG(‘MYREALLY_LONG_TABLE_NAME_HERE’);
3  END;
4  /

PL/SQL procedure successfully completed.

This command created an error log table named “ERR$_MYREALLY_LONG_TABLE_NAME” (note that the “_HERE” has been removed).
I then tried to compile a stored procedure that had a command like the following

“INSERT INTO MYREALLY_LONG_TABLE_NAME_HERE (cola, colb)
SELECT a, b FROM ….
LOG ERRORS
REJECT LIMIT UNLIMITED”

No luck, I get a “PL/SQL: ORA-00972: identifier is too long” error when trying to compile.

Looking through metalink and Google didn’t help much, so I then tried supplying the name of the error log table to the DBMS_ERRLOG.CREATE_ERROR_LOG procedure, once I did that I had to modify the “INSERT INTO” command to tell it where to log the errors.

Note the “INTO [schema.]table” section below.

LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT  {integer|UNLIMITED} ]

In a nutshell, I guess Oracle is smart enough on the creation of the table to truncate the error log table at 30 characters but it’s not smart enough yet when actually using the “LOG ERRORS” feature.
I haven’t checked the same in 11g and until then I’m going consider using the “INTO” clause as a best practice when using the “LOG ERRORS” feature.

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

Viagra For SQL’s. DBMS_STATS (NO_INVALIDATE argument/parameter) ?

Posted by FatDBA on August 20, 2013

While trying to get any resolution for one of our old problem (Database Hangs while Gathering Stats – Schema Wise), one of the oracle support analyst recommends us to gather stats next time with no_invalidate parameter. We actually never tested/tried this option with DBMS_STATS procedure before.

Let me put some light over this argument/parameter and how it can act as a Viagra for your SQL’s. Fast, Instant …

DBMS_STATS provides us an argument to control invalidation of SQL plans available and are sitting in your Library Cache and are used by your CBO. This is quite decisive when used to control SQL executions and query optimizations and performance.

It has three arguments types.
dbms_stats.set_param (no_invalidate false):  
This option will orders that a change in statistics always invalidates the dependent sql cursors/plans immediately upon a change to the statistics. Very much similar to ‘ALTER SYSTEM FLUSH SHARED POOL’.
dbms_stats.set_param (no_invalidate true):  In this case, a change in statistics will never nullify or voids current SQL execution plans in Library Cache.
dbms_stats.set_param (no_invalidate dbms_stats.auto_invalidate):  This is the default (Since Oracle 10g). This default type says that Oracle will not invalidate the old plan immediately after new statistics are generated.

Now a question:
After how many seconds, minutes, hours or probably days, old plans gets invalidate in case of no_invalidate.auto_invalidate which is a Default ??
Ans: Many claims that a hidden parameter _optimizer_invalidation_period dictates the maximum time before invalidation. Which is default of 18000 seconds (5 hours)

SQL> select     rpad(i.ksppinm, 35) || ‘ = ‘ || v.ksppstvl parameter,
i.ksppdesc description,
v.ksppstdf dflt
from    x$ksppi         i,
x$ksppcv        v
where   v.indx = i.indx
and     v.inst_id = i.inst_id
and     i.ksppinm like ‘_optimizer_invalidation_period’
order by 1;

PARAMETER                                                                                DESCRIPTION                                                                             DFLT
—————————————————————–    ——————————————————————————————-    ———–
_optimizer_invalidation_period      = 18000            time window for invalidation of cursors of analyzed objects         TRUE

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Uncategorized | 1 Comment »

AMM (Automatic Memory Management) & ASMM (Automatic Shared MM)

Posted by FatDBA on December 31, 2012

Evolution of Memory Management Features:

Memory management has evolved with each database release:

Oracle Database 10g
Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g
Automatic Memory Management is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

Oracle Database 11g supports various memory management methods, which are chosen by initialization parameter
settings. Oracle recommends that you enable the automatic memory management method.
1. Automatic Memory Management – For Both the SGA and Instance PGA
2. Automatic Shared Memory Management – For the SGA
3. Manual Shared Memory Management – For the SGA
4. Automatic PGA Memory Management – For the Instance PGA
5. Manual PGA Memory Management – For the Instance PGA

Untitled

Automatic Memory Management – For Both the SGA and Instance PGA

Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands.
This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs. To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

Switching to Automatic Memory Management

1)Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

SQL>SHOW PARAMETER TARGET

NAME TYPE VALUE
—————————— ———– —————-
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 200M
sga_target big integer 500M

Add the values of pga_aggregate_target and sga_target. In our case it would sum to 700 M

2)Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 808M

3)Change the parameter in initialization parameter file.

Using Spfile
==============
SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

Using Pfile
==============
If you have started the instance with Pfile, then edit the pfile and set the parameters manually
MEMORY_MAX_TARGET = 808M
MEMORY_TARGET = 808M
SGA_TARGET =0
PGA_AGGREGATE_TARGET = 0

In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of
MEMORY_MAX_TARGET.
MEMORY_MAX_TARGET is a static parameter i.e it cannot be changed Dynamically and Instance has to be bounced for modifying the value. So ensure that you have set it to appropriate value.

4)Shutdown and startup the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 845348864 bytes
Fixed Size 1303188 bytes
Variable Size 469765484 bytes
Database Buffers 369098752 bytes
Redo Buffers 5181440 bytes

SQL> show parameter target

NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 808M
memory_target big integer 808M
pga_aggregate_target big integer 0
sga_target big integer 0

Automatic Shared Memory Management – For the SGA
If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management.This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration. Please refer to following document for setting SGA_TARGET

In case you have enabled Automatic Memory Management , then to switch to Automatic Shared Memory Management , please follow below procedure

SQL>Alter system set MEMORY_TARGET=0 scope=both;
SQL>Alter system set SGA_TARGET=500M scope=both;

Posted in Uncategorized | Tagged: , | 4 Comments »

Oracle Secure Backup: Configuration

Posted by FatDBA on October 31, 2012

  • Steps to Configure Oracle Secure Backup (OSB):

Step 1. As the root user, check if the uncompress utility is installed on the system. If it is not,
create a symbolic link pointing to the gunzip utility:

(This is an important per-requisite and this is needed by the installed to uncompress files to installation directory and Mostly this does not comes pre-installed with Linux OS, create symbolic link with the Gunzip utility or install an RPM for this)
[root@lin32 ~]# uncompress
-bash: uncompress: command not found
[root@lin32 ~]# ln -s /bin/gunzip /bin/uncompress
Step 2. Create a directory for the download, and then issue the change directory command to
that directory:
[root@lin32 ~]# mkdir download
[root@lin32 ~]# cd download/
Step 3. Download OSB into the download directory and then unzip the product:
[root@lin32 download]# ls –l
total 43864
-rw-r–r– 1 root root 44866571 Jan 19 20:31 osb-10.3.0.1.0_linux32_release.zip
[root@lin32 download]# unzip osb-10.3.0.1.0_linux32_release.zip
Archive: osb-10.3.0.1.0_linux32_release.zip
creating: osb-10.3.0.1.0_linux32_cdrom090504/
extracting: osb-10.3.0.1.0_linux32_cdrom090504/OSB.10.3.0.1.0_LINUX32.rel
creating: osb-10.3.0.1.0_linux32_cdrom090504/doc/
creating: osb-10.3.0.1.0_linux32_cdrom090504/doc/dcommon/
creating: osb-10.3.0.1.0_linux32_cdrom090504/doc/dcommon/css/
inflating: osb-10.3.0.1.0_linux32_cdrom090504/doc/dcommon/css/blafdoc.css
inflating: osb-10.3.0.1.0_linux32_cdrom090504/doc/dcommon/css/bp_layout.css
…
inflating: osb-10.3.0.1.0_linux32_cdrom090504/welcome.html
inflating: osb-10.3.0.1.0_linux32_cdrom090504/doc.tar
Step 4. Create the directory where the install will place OSB files:
[root@lin32 download]# mkdir -p /usr/local/oracle/backup

Step 5. Issue the change directory command to the OSB destination and run setup:
[root@lin32 download]# cd /usr/local/oracle/backup/
[root@lin32 backup]# /root/download/osb-10.3.0.1.0_linux32_cdrom090504/setup
The following output is returned:
Welcome to Oracle’s setup program for Oracle Secure Backup. This program loads
Oracle Secure Backup software from the CD-ROM to a filesystem directory of your
choosing.
This CD-ROM contains Oracle Secure Backup version 10.3.0.1.0_LINUX32.
Please wait a moment while I learn about this host… done.
– – – – – – – – – – – – – – – – – – – – – – – – – – –
1. linux32
administrative server, media server, client
– – – – – – – – – – – – – – – – – – – – – – – – – – –
Loading Oracle Secure Backup installation tools… done.
Loading linux32 administrative server, media server, client… done.
– – – – – – – – – – – – – – – – – – – – – – – – – – –
Oracle Secure Backup has installed a new obparameters file.
Your previous version has been saved as install/obparameters.savedbysetup.
Any changes you have made to the previous version must be made to the new obparameters file.
Would you like the opportunity to edit the obparameters file
Please answer ‘yes’ or ‘no’ [no]:
Step 6. Leaving the default parameters for now, press ENTER to choose the default answer. The
following output is returned:
– – – – – – – – – – – – – – – – – – – – – – – – – – –
Loading of Oracle Secure Backup software from CD-ROM is complete.
You may unmount and remove the CD-ROM.
Would you like to continue Oracle Secure Backup installation with ‘installob’ now?
(The Oracle Secure Backup Installation Guide contains complete information about
installob.)
Please answer ‘yes’ or ‘no’ [yes]:
Step 7. Again, press ENTER to choose the default answer. The following output is returned:
– – – – – – – – – – – – – – – – – – – – – – – – – – –
Welcome to installob, Oracle Secure Backup’s installation program.
For most questions, a default answer appears enclosed in square brackets.
Press Enter to select this answer.
Please wait a few seconds while I learn about this machine… done.
Have you already reviewed and customized install/obparameters for your Oracle
Secure Backup installation [yes]?
Step 8. Again, press ENTER to choose the default answer and to leave the default parameters. The
following output is returned:
– – – – – – – – – – – – – – – – – – – – – – – – – – –
Oracle Secure Backup is not yet installed on this machine.
Oracle Secure Backup’s Web server has been loaded, but is not yet configured.
Choose from one of the following options. The option you choose defines the
software components to be installed.

Configuration of this host is required after installation completes.
You can install the software on this host in one of the following ways:
(a) administrative server, media server and client
(b) media server and client
(c) client
If you are not sure which option to choose, please refer to the Oracle Secure
Backup Installation Guide. (a,b or c) [a]?
Step 9. You are going to install all three components of OSB on the same server, so again press
ENTER to choose the default answer. The following output is returned:
Beginning the installation. This will take just a minute and will produce
several lines of informational output.
Installing Oracle Secure Backup on lin32 (Linux version 2.6.18-53.el5)
You must now enter a password for the Oracle Secure Backup encryption key store.
Oracle suggests you choose a password of at least 8 characters in length,
containing a mixture of alphabetic and numeric characters.
Please enter the key store password:
Re-type password for verification:
Step 10. Enter the OSB encryption key twice. The key is not displayed. You will see the
following output:
You must now enter a password for the Oracle Secure Backup ‘admin’ user. Oracle
suggests you choose a password of at least 8 characters in length, containing a
mixture of alphabetic and numeric characters.
Please enter the admin password:
Re-type password for verification:
Step 11. Enter the admin password twice. The password is not displayed. You will see the
following output:
You should now enter an email address for the Oracle Secure Backup ‘admin’ user.
Oracle Secure Backup uses this email address to send job summary reports and to
notify the user when a job requires input. If you leave this blank, you can set it
later using the obtool’s ‘chuser’ command.
Please enter the admin email address:
Step 12. Leave the e-mail address blank for now. The following output is returned:
generating links for admin installation with Web server
updating /etc/ld.so.conf
checking Oracle Secure Backup’s configuration file (/etc/obconfig)
setting Oracle Secure Backup directory to /usr/local/oracle/backup in /etc/obconfig
setting local database directory to /usr/etc/ob in /etc/obconfig
setting temp directory to /usr/tmp in /etc/obconfig
setting administrative directory to /usr/local/oracle/backup/admin in /etc/obconfig
protecting the Oracle Secure Backup directory
creating /etc/rc.d/init.d/observiced
activating observiced via chkconfig
initializing the administrative domain
****************************** N O T E ******************************
On Linux systems Oracle recommends that you answer no to the next two questions.
The preferred mode of operation on Linux systems is to use the /dev/sg devices for

attach points as described in the ‘ReadMe’ and in the ‘Installation and
Configuration Guide’.
Is lin32 connected to any tape libraries that you’d like to use with Oracle Secure Backup [no]?
Is lin32 connected to any tape drives that you’d like to use with Oracle Secure
Backup [no]?
Step 13. Since, in this example, you use a Linux system, answer “no,” as recommended by
Oracle, and configure the media server later. The following summary is returned:
Installation summary:
Installation Host OS Driver OS Move Reboot
Mode Name Name Installed? Required? Required?
admin lin32 Linux no no no
Oracle Secure Backup is now ready for your use.
The OSB administrative server, media server, and client are now installed. The OSB Web tool
is used to configure the tape library and tape drives.

Once configured launch your Web browser and supply the URL of the host running Oracle Secure Backup. Use the following syntax, where hostname can be a fully qualified domain name:

https://hostname
https://localhost.localdomain

Posted in Uncategorized | Tagged: | Leave a Comment »

Re-Creating the Control File: RMAN Effects during the activity.

Posted by FatDBA on October 29, 2012

It used to be that certain conditions required the occasional rebuild of the database control
file. If you use RMAN and you do not use a recovery catalog, be very careful of the control file
rebuild. When you issue the command

alter database backup control file to trace;

the script that is generated does not include the information in the control file that identifies
your backups. Without these backup records, you cannot access the backups when they are
needed for recovery. All RMAN information is lost, and you cannot get it back. The only
RMAN information that gets rebuilt when you rebuild the control file is any permanent
configuration parameters you have set with RMAN.
If you back up the control file to a binary file, instead of to trace, then all backup
information is preserved. This command looks like the following:

alter database backup controlfile to ‘/u01/backup/bkup_cfile.ctl’;

Posted in Uncategorized | Tagged: | Leave a Comment »

« Previous Entries
Next Entries »