Minimize Downtime During Upgrade 

Solution:


1. Migration in NOARCHIVELOG mode will reduce the database upgrade time and hence the downtime.

NOTE: NOARCHIVELOG should NOT be used if any log-based replication technology is used. Log-based technologies include Active Data Guard, Oracle GoldenGate, XStream, Oracle Streams, Data Guard (Redo Apply and SQL Apply), Asynchronous Change Data Capture(CDC) and Audit Vault redo collector.


2. As the main function of a database upgrade is to create a new data dictionary, the upgrade can be tested for most of the upgrade functionality by using a copy of the database's SYSTEM tablespace and ROLLBACK SEGMENT tablespace and marking all other tablespaces OFFLINE. This allows realistic timings to be obtained without having to copy an entire database.


3. Make all tablespaces OFFLINE NORMAL or make READ ONLY  except for SYSTEM, SYSAUX (When migrating to 10gR2 or higher) and those containing rollback segments prior to upgrade. This way if upgrade fails, only the SYSTEM and rollback datafiles need to be restored rather than the entire database.

Note: You must OFFLINE the TABLESPACE as upgrade does not allow OFFLINE files in an ONLINE tablespace.

4. When upgrading to Oracle Database 10g, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables that lack statistics or are significantly changed during the upgrade.

To decrease the amount of downtime incurred when collecting statistics during upgrade, you must gather statistics prior to performing the actual database upgrade. DBMS_STATS.GATHER_SCHEMA_STATS is the procedure to be used to gather statistics for your database.

To verify the schema with stale statistics, refer Doc ID 560336.1 : Script to Check Schemas with Stale Statistics


5. (Applicable for DBUA only)

DBUA has an option to use flashback database feature for restoring and recovering the Database in case of upgrade failure. This can be done by selecting the option "Use Flashback and Guaranteed Restore Point".

In DBUA silent mode, below option/flag needs to be chosen :

-createGRP - When true, specifies that DBUA creates a guaranteed restore point when the database is in archive log mode and flashback mode



Note : Please test before upgrading production database by upgrading a test database.


Reference metalink Doc ID 455744.1

Not able to connect as rman catalog "RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified"

If you have created catalog user and you want to connect to rman with that user and you are getting the following error-

RMAN-04004: error from recovery catalog database:
ORA-12154: TNS:could not resolve the connect identifier specified

see below errors--

rman catalog = catalog/metalog@catdb
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 23 15:02:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified

You are trying with connect identifier on same machine on which you have database like @catdb in this command.

Action - Don't use @catdb because if you are using same server to connect there is no need to give connect identifier.

export ORACLE_SID=catdb

-bash-4.1$ rman catalog = catalog/metalog

by giving this you will easily connect to your rman.

If you are trying to connect to catalog database on different host then you have to create tns entry in tnsnames.ora file .

like  ----

catdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = DB_NAME)

Clusterware Processes In RAC Environment

Clusterware Processes In RAC Environment

Cluster Ready Services (CRS)
------------------------------------

$ ps-ef | grep crs | grep -v grep

root 25863 1 1 Oct27 ? 11:37:32
/opt/oracle/grid/product/11.2.0/bin/crsd.bin
reboot

crsd.bin => The above process is responsible for start, stop, monitor and failover of resource. It maintains OCR and also restarts the resources when the failure occurs.

This is applicable for RAC systems. For Oracle Restart and ASM ohasd is used.


Cluster Synchronization Service (CSSD)
-------------------------------------------------

$ ps-ef | grep -v grep | grep css

root 19541 1 0 Oct27 ? 00:05:55
/opt/oracle/grid/product/11.2.0/bin/cssdmonitor

root 19558 1 0 Oct27 ? 00:05:45
/opt/oracle/grid/product/11.2.0/bin/cssdagent

oragrid 19576 1 6 Oct27 ? 2-19:13:56
/opt/oracle/grid/product/11.2.0/bin/ocssd.bin

cssdmonitor => Monitors node hangs(via oprocd functionality) and monitors OCCSD process hangs (via oclsomon functionality) and monitors vendor clusterware(via vmon functionality).This is the multi threaded process that runs with elavated priority.

Startup sequence: INIT --> init.ohasd --> ohasd
--> ohasd.bin --> cssdmonitor

cssdagent => Spawned by OHASD
process.Previously(10g) oprocd, responsible for I/O fencing.Killing this process would cause node reboot.Stops,start checks the status of occsd.bin daemon

Startup sequence: INIT --> init.ohasd --> ohasd
--> ohasd.bin --> cssdagent

occsd.bin => Manages cluster node
membership runs as oragrid user.Failure of this process results in node restart.

Startup sequence: INIT --> init.ohasd --> ohasd
--> ohasd.bin --> cssdagent --> ocssd --> ocssd.bin


Event Management Daemon (EVMD)
----------------------------------------------

$ ps-ef | grep evm | grep -v grep

oragrid 24623 1 0 Oct27 ? 00:30:25 /opt/oracle/grid/product/11.2.0/bin/evmd.bin

oragrid 25934
24623 0 Oct27 ? 00:00:00
/opt/oracle/grid/product/11.2.0/bin/evmlogger.bin -o
/opt/oracle/grid/product/11.2.0/evm/log/evmlogger.info -l /opt/oracle/grid/product/11.2.0/evm/log/evmlogger.log

evmd.bin => Distributes and communicates some cluster events to all of the cluster members so that they are aware of the cluster changes.

evmlogger.bin => Started by EVMD.bin reads the configuration files and determines what events to subscribe to from EVMD and it runs user defined actions for those events.

Oracle Root Agent
-----------------------

$ ps-ef | grep -v grep | grep orarootagent


root 19395 1 0 Oct17 ? 12:06:57
/opt/oracle/grid/product/11.2.0/bin/orarootagent.bin
root 25853 1 1 Oct17 ? 16:30:45 /opt/oracle/grid/product/11.2.0/bin/orarootagent.bin

orarootagent.bin => A specialized oraagent process that helps crsd manages resources owned by root, such as the network, and the Grid virtual IP address.

The above 2 process are actually threads which looks like processes. This is a Linux specific


Cluster Time Synchronization Service (CTSSD)
----------------------------------------------------------

$ ps-ef | grep ctss | grep -v grep

root 24600 1 0 Oct27 ? 00:38:10
/opt/oracle/grid/product/11.2.0/bin/octssd.bin
reboot

octssd.bin => Provides Time Management in a cluster for Oracle Clusterware


Oracle Agent
----------------

$ ps-ef | grep -v grep | grep oraagent


oragrid 5337 1 0 Nov14 ? 00:35:47
/opt/oracle/grid/product/11.2.0/bin/oraagent.bin

oracle 8886 1 1 10:25 ? 00:00:05
/opt/oracle/grid/product/11.2.0/bin/oraagent.bin

oragrid 19481 1 0 Oct27 ? 01:45:19
/opt/oracle/grid/product/11.2.0/bin/oraagent.bin

oraagent.bin => Extends clusterware to support Oracle-specific requirements and complex resources. This process runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1).


ORACLE HIGH AVAILABILITY SERVICES STACK
-------------------------------------------------------------------

Cluster Logger Service
----------------------------

$ ps-ef | grep -v grep | grep ologgerd

root 24856 1 0
Oct27 ? 01:43:48
/opt/oracle/grid/product/11.2.0/bin/ologgerd -m mg5hfmr02a -r -d /opt/oracle/grid/product/11.2.0/crf/db/mg5hfmr01a

ologgerd => Receives information from all the nodes in the cluster and persists in a CHM repository-based database. This service runs on only two nodes in a cluster.


System Monitor Service (osysmond)
--------------------------------------------

$ ps-ef | grep -v grep | grep osysmond

root 19528 1 0 Oct27 ? 09:42:16
/opt/oracle/grid/product/11.2.0/bin/osysmond

osysmond => The monitoring and operating system metric collection service that sends the data to the cluster logger service. This service runs on every node in a cluster.


Grid Plug and Play (GPNPD)
-----------------------------------

$ ps-ef | grep gpn

oragrid 19502 1 0 Oct27 ? 00:21:13 /opt/oracle/grid/product/11.2.0/bin/gpnpd.bin

gpnpd.bin => Provides access to the Grid Plug and Play profile, and coordinates updates to the profile among the nodes of the cluster to ensure that all of the nodes have the most recent profile.


Grid Interprocess Communication (GIPC)
--------------------------------------------------

$ ps-ef | grep -v grep | grep gipc

oragrid 19516 1 0 Oct27 ? 01:51:41
/opt/oracle/grid/product/11.2.0/bin/gipcd.bin

gipcd.bin => A support daemon that enables Redundant Interconnect Usage.


Multicast Domain Name Service (mDNS)
---------------------------------------------------

$ ps-ef | grep -v grep | grep dns


oragrid 19493 1 0 Oct27 ? 00:01:18
/opt/oracle/grid/product/11.2.0/bin/mdnsd.bin

mdnsd.bin => Used by Grid Plug and Play to locate profiles in the cluster, as well as by GNS to perform name resolution. The mDNS process is a background process on Linux and UNIX and on Windows.


Oracle Grid Naming Service (GNS)
-------------------------------------------

$ ps-ef | grep -v grep | grep gns

gnsd.bin => Handles requests sent by external DNS servers, performing name resolution for names defined by the cluster.

Cache Fusion In Oracle Rac

cache fusion is disk less cache coherency mechanism in Oracle RAC that provides copies of data blocks directly from one instance’s memory cache (in which that block is available) to other instance (instance which is request for specific data block).  Cache Fusion provides single buffer cache (for all instances in cluster) through interconnect.

In Single Node oracle database, an instance looking for data block first checks in cache, if block is not in cache then goes to disk to pull block from disk to cache and return block to client.

In RAC Database there is remote cache so instance should look not only in local cache (cache local to instance) but on remote cache (cache on remote instance). If cache is available in local cache then it should return data block from local cache; if data block is not in local cache, instead of going to disk it should first go to remote cache (remote instance) to check if block is available in local cache (via interconnect)

This is because of accessing data block from remote cache is faster than accessing it from disk.

Terminating Instance Due To Error 472/ORA-00472

Terminating Instance Due To Error 472/ORA-00472

Cause:

When  user killed pmon process using kill -9 <pid> you will get this error in the alert log file.

MMAN: terminating instance due to error 472
ORA-00472: PMON process terminated with error
Instance terminated by MMAN, pid = 26700.

Terminating Instance Due To Error 474/ORA-00474

Terminating Instance Due To Error 474/ORA-00474

Cause:

When user killed smon process using kill -9 <pid> you will get this error in the alert log file.

Errors in file /u01/home/oracle/product/10.2.0/db_1/admin/PROD/bdump/prod_pmon_26800.trc:
ORA-00474: SMON process terminated with error
Mon Feb 25 15:51:26 2013
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 26800

How To Find Whether The Parameter Is Static or Dynamic

How To Find Whether The Parameter Is Static or Dynamic?

ISSYS_MODIFIABLE column display the values FALSE and IMMEDIATE.

FALSE: it means database bounce is require.
IMMEDIATE: it means database bounce not require.

Example:

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%control_files%';

ISSYS_MOD
---------
FALSE

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%open_cursors%';

ISSYS_MOD
---------
IMMEDIATE

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%undo_tablespace%';

ISSYS_MOD
---------
IMMEDIATE

SQL> select ISSYS_MODIFIABLE from v$parameter where name like '%log_archive_dest%';

ISSYS_MOD
---------
IMMEDIATE