Oracle Administration

DBA Scripts

2014-11-18T22:57:00.002+05:30


db_info

SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF

SELECT *
FROM   v$database;

SELECT *
FROM   v$instance;

SELECT *
FROM   v$version;

SELECT a.name,
       a.value
FROM   v$sga a;

SELECT Substr(c.name,1,60) "Controlfile",
       NVL(c.status,'UNKNOWN') "Status"
FROM   v$controlfile c
ORDER BY 1;

SELECT Substr(d.name,1,60) "Datafile",
       NVL(d.status,'UNKNOWN') "Status",
       d.enabled "Enabled",
       LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM   v$datafile d
ORDER BY 1;

SELECT l.group# "Group",
       Substr(l.member,1,60) "Logfile",
       NVL(l.status,'UNKNOWN') "Status"
FROM   v$logfile l
ORDER BY 1,2;

PROMPT
SET PAGESIZE 14
SET FEEDBACK ON




sessions


SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A35
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.service_name,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

tablespace_full

SET PAGESIZE 100

PROMPT Tablespaces nearing 0% free
PROMPT ***************************
SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM   (
        SELECT a.tablespace_name,
               b.size_mb,
               a.free_mb,
               b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name
       )
WHERE  ROUND((max_free_mb/max_size_mb) * 100,2) < 10;

SET PAGESIZE 14

max_extents


SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
       e.segment_type,
       Substr(e.segment_name, 1, 30) segment_name,
       Trunc(s.initial_extent/1024) "INITIAL K",
       Trunc(s.next_extent/1024) "NEXT K",
       s.max_extents,
       Count(*) as extents
FROM   dba_extents e,
       dba_segments s
WHERE  e.owner        = s.owner
AND    e.segment_name = s.segment_name
AND    e.owner        NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;






object_status

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SET VERIFY OFF

SELECT Substr(object_name,1,30) object_name,
       object_type,
       status
FROM   all_objects
WHERE  owner = Upper('&&1');

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

min_datafile_size

COLUMN block_size NEW_VALUE v_block_size

SELECT TO_NUMBER(value) AS block_size
FROM   v$parameter
WHERE  name = 'db_block_size';

COLUMN tablespace_name FORMAT A20
COLUMN file_name FORMAT A50
COLUMN current_bytes FORMAT 999999999999999
COLUMN shrink_by_bytes FORMAT 999999999999999
COLUMN resize_to_bytes FORMAT 999999999999999
SET VERIFY OFF
SET LINESIZE 200

SELECT a.tablespace_name,
       a.file_name,
       a.bytes AS current_bytes,
       a.bytes - b.resize_to AS shrink_by_bytes,
       b.resize_to AS resize_to_bytes
FROM   dba_data_files a,
       (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
        FROM   dba_extents
        GROUP by file_id) b
WHERE  a.file_id = b.file_id
ORDER BY a.tablespace_name, a.file_name;

monitor_memory

SET LINESIZE 200

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT NVL(a.username,'(oracle)') AS username,
       a.module,
       a.program,
       Trunc(b.value/1024) AS memory_kb
FROM   v$session a,
       v$sesstat b,
       v$statname c
WHERE  a.sid = b.sid
AND    b.statistic# = c.statistic#
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC;

spfile_parameters

SET LINESIZE 500

COLUMN name  FORMAT A30
COLUMN value FORMAT A60
COLUMN displayvalue FORMAT A60

SELECT sp.sid,
       sp.name,
       sp.value,
       sp.display_value
FROM   v$spparameter sp
ORDER BY sp.name, sp.sid;

temp_io

SET PAGESIZE 1000

SELECT SUBSTR(t.name,1,50) AS file_name,
       f.phyblkrd AS blocks_read,
       f.phyblkwrt AS blocks_written,
       f.phyblkrd + f.phyblkwrt AS total_io
FROM   v$tempstat f,
       v$tempfile t
WHERE  t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

SET PAGESIZE 18

http://oracle-base.com/dba/scripts.php

RMAN Backup types in DIAGRAM

2014-09-16T11:50:00.003+05:30

Here i attached a clear diagram of Oracle RMAN backup types; Hopes it will clear you doubts.

the digram can help you see how Rman can perform backups and the types of backup that can be done using Rman tool.

Oracle Database Architecture

2014-08-26T16:49:00.001+05:30

Oracle Architecture :-

Oracle Architectural Components:

Objectives:

This is introduces the Oracle server architecture by examining the physical, memory,process, and logical structures involved in establishing a database connection, creating a session, and executing SQL commands.

Oracle is a Relational Database Management System (RDBMS), which uses Relational Data Model to store its database and SQL (commonly abbreviated as Structured Query Language) to process the stored data. The architecture of Oracle system can be best explained in terms of client/server paradigm. Thus, we will explain the architecture of Oracle server using the structure called instance..

An oracle instance is a complex set of memory structures and operating system processes. It is the Oracle instance, which manages all database activities, such as transaction processing, database recovery, form generation, and so on. The instance structure is loosely styled after UNIX's implementation of multitasking operating system. Discrete processes perform specialized tasks within the RDBMS that work together to accomplish the goals of the instance. Each process has a separate memory block that it uses to store local and private variables, address stacks and other runtime information. The processes use a common shared memory area for processing data concurrently. This memory block is called the System Global Area (SGA). Figure 1 illustrates the architecture of an instance.

System Global Area (SGA)

The SGA is the primary memory component of the instance. It provides memory structure necessary for data manipulation, SQL statement parsing, and redo caching. The SGA is shared, which means that the multiple processes can access and modify the data contained in it in a synchronized manner. The SGA consists of the following components:

Shared Pool
Database buffer cache
Redo Log Buffer

Multithread server(MTS) structures.

The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called the Shared Global Area).

o This information includes both organizational data and control information used by the Oracle Server.

o The SGA is allocated in memory and virtual memory.

o The size of the SGA can be established by a DBA by assigning a value to the parameter SGA_MAX_SIZE in the parameter file—this is an optional parameter.

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE).

The SGA has the following mandatory memory structures:

· Database Buffer Cache

· Redo Log Buffer

· Java Pool

· Streams Pool

· Shared Pool – includes two components:

o Library Cache

o Data Dictionary Cache

· Other structures (for example, lock and latch management, statistical data)

Additional optional memory structures in the SGA include:

· Large Pool

The SHOW SGA SQL command will show you the SGA memory allocations.

· This is a recent clip of the SGA for the DBORCL database at SIUE.

· In order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).

SQL> connect / as sysdba

Connected.

SQL> show sga

Total System Global Area 1610612736 bytes

Fixed Size 2084296 bytes

Variable Size 1006633528 bytes

Database Buffers 587202560 bytes

Redo Buffers 14692352 bytes

Early versions of Oracle used a Static SGA. This meant that if modifications to memory management were required, the database had to be shutdown, modifications were made to the init.ora parameter file, and then the database had to be restarted.

Oracle 11g uses a Dynamic SGA. Memory configurations for the system global area can be made without shutting down the database instance. The DBA can resize the Database Buffer Cache and Shared Pool dynamically.

Several initialization parameters are set that affect the amount of random access memory dedicated to the SGA of an Oracle Instance. These are:

· SGA_MAX_SIZE: This optional parameter is used to set a limit on the amount of virtual memory allocated to the SGA – a typical setting might be 1 GB; however, if the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE. For optimal performance, the entire SGA should fit in real memory to eliminate paging to/from disk by the operating system.

· DB_CACHE_SIZE: This optional parameter is used to tune the amount memory allocated to the Database Buffer Cache in standard database blocks. Block sizes vary among operating systems. The DBORCL database uses 8 KB blocks. The total blocks in the cache defaults to 48 MB on LINUX/UNIX and 52 MB on Windows operating systems.

· LOG_BUFFER: This optional parameter specifies the number of bytes allocated for the Redo Log Buffer.

· SHARED_POOL_SIZE: This optional parameter specifies the number of bytes of memory allocated to shared SQL and PL/SQL. The default is 16 MB. If the operating system is based on a 64 bit configuration, then the default size is 64 MB.

· LARGE_POOL_SIZE: This is an optional memory object – the size of the Large Pool defaults to zero. If the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default size is automatically calculated.

· JAVA_POOL_SIZE: This is another optional memory object. The default is 24 MB of memory.

The size of the SGA cannot exceed the parameter SGA_MAX_SIZE minus the combination of the size of the additional parameters, DB_CACHE_SIZE, LOG_BUFFER, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE.

Memory is allocated to the SGA as contiguous virtual memory in units termed granules. Granule size depends on the estimated total size of the SGA, which as was noted above, depends on the SGA_MAX_SIZE parameter. Granules are sized as follows:

· If the SGA is less than 1 GB in total, each granule is 4 MB.

· If the SGA is greater than 1 GB in total, each granule is 16 MB.

Granules are assigned to the Database Buffer Cache, Shared Pool, Java Pool, and other memory structures, and these memory components can dynamically grow and shrink. Using contiguous memory improves system performance. The actual number of granules assigned to one of these memory components can be determined by querying the database view named V$BUFFER_POOL.

Granules are allocated when the Oracle server starts a database instance in order to provide memory addressing space to meet the SGA_MAX_SIZE parameter. The minimum is 3 granules: one each for the fixed SGA, Database Buffer Cache, and Shared Pool. In practice, you'll find the SGA is allocated much more memory than this. The SELECT statement shown below shows a current_size of 1,152 granules.

SELECT name, block_size,current_size,prev_size,prev_buffers

FROM v$buffer_pool;

NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE PREV_BUFFERS

-------- ---------- ------------ ---------- ------------

DEFAULT 8192 560 576 71244

Shared pool

The Oracle shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Hence, the shared pool is a key component, so it's necessary for the Oracle database administrator to check for shared pool contention.

The shared pool is like a buffer for SQL statements. Oracle's parsing algorithm ensures that identical SQL statements do not have to be parsed each time they're executed. The shared pool is used to store SQL statements, and it includes the following components:

Since it is not possible to dedicate separate regions of memory for the shared pool components, the shared pool is usually the second-largest SGA memory area (depending on the size of the db_cache_size parameter). The shared pool contains RAM memory regions that serve the following purposes:

• Library cache – The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code.

• Dictionary cache - The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.

• Session information – Systems that use SQL*Net version 2 with a multi-threaded server need this area to store session information. Beginning with Oracle, the v$session view contains information related to Oracle*Net users.

The following table lists the different areas stored in the shared pool and their purpose:

* Shared SQL Area - The shared SQL area stores each SQL statement executed in the database. This area allows SQL execution plans to be reused by many users.

* Private SQL Area - Private SQL areas are non-shared memory areas assigned to unique user sessions.

* PL/SQL Area - Used to hold parsed and compiled PL/SQL program units, allowing the execution plans to be shared by many users.

* Control Structures - Common control structure information, for example, lock information.

Database Buffer Cache

The database buffer cache holds copies of data blocks read from the data files. The term data block is used to describe a block containing table data, index data, clustered data, and so on. Basically, it is a block that contains data. All user processes concurrently connected to the instance share access to the database buffer cache. The database buffer cache is logically segmented into multiple sets. This reduces contention on multiprocessor systems.

This area of the SGA contains only the buffers themselves and not their control structures. For each buffer, there is a corresponding buffer header in the variable area of the SGA.

Program Global Area (PGA)

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a non-shared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. It contains a private SQL area and a session memory area.

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

Buffer Cache Management

The database buffer cache is organized in two lists: the write list and the least-recently-used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are buffers that are currently being accessed.

When an Oracle process requires data, it searches the buffer cache, finds the data blocks, and then uses the data. This is known as a cache hit. If it cannot find the data, then it must be obtained from the data file. In this case, it finds a free buffer to accommodate the data block by scanning the LRU list, starting at the least-recently-used from the end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

When the user process is performing a full table scan, it reads the data blocks into buffers and places them on the LRU end instead of the MRU end of the LRU list. This is because a fully scanned table is usually needed only briefly and the blocks should be moved out quickly.

What Is a Dirty Block?

Whenever a server process changes or modifies a data block, it becomes a dirty block. Once a server process makes changes to the data block, the user may commit transactions, or transactions may not be committed for quite some time. In either case, the dirty block is not immediately written back to disk.

Writing dirty blocks to disk takes place under the following two conditions:

* When a server process cannot find a clean, reusable buffer after scanning a threshold number of buffers, then the database writer process writes the dirty blocks to disk.

* When the checkpoint takes place, the database writer process writes the dirty blocks to disk.

Large Pool

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.

1. Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved

2. I/O Server Processes

3. Parallel Query Buffers

4. Oracle Backup and Restore Operations using RMAN

Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead casued by shrinking the SQL Cache Area

JAVA Pool

The JAVA Pool holds the JAVA execution code in a similar manner to the PL/SQL cache in the shared pool. The JAVA pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other JAVA will be utilized in the user applications

The Redo Log Buffer

The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to save changes to data, in case something fails and Oracle has to put it back into its original state (a “rollback”). When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo images are created and stored in the redo log buffer. Since RAM is faster than disk, this makes the storage of redo very fast.

The Oracle redo log buffer provides the following functions within the Oracle SGA:

Serves for assistance with database recovery tasks
Records all changes made to database blocks
Places changes recorded to redo entries for redo logs

The database initialization parameter log_buffer defines the default size of the redo log buffer within Oracle 11g.

Redo log files:

Redo logs are transaction journals. Each transaction is recorded in the redo logs. Redo logs are used in a serial fashion with each transaction queuing up in the redo log buffers and being written one at a time into the redo logs. Redo logs as a general rule should switch about every thirty minutes. However, you may need to adjust the time up or down depending on the importance of your data. The rule of thumb is to size the redo logs such that you only loose the amount of data you can stand to loose should for some reason the online redo log become corrupt. With modern Oracle redo log mirroring and with disk array mirroring and various forms of online disk repair and replacement the occurrence of redo log corruptions has dropped to practically zero, so size based on the number of archive logs you want to apply should the database fail just before your next backup.

The LOG_BUFFER_SIZE and LOG_BUFFERS parameters control the redo log buffers. The LOG_BUFFER_SIZE should be set to reduce the number of writes required per redo log but not be so large that it results in an excessive IO wait time. Some studies have shown that sizing bigger than one megabyte rarely results in performance gains. Generally I size the LOG_BUFFER_SIZE such that it is equal to or results in an even divisor of the redo log size.

Monitor redo logs using the alert log, V$LOGHIST, V$LOGFILE, V$RECOVERY_LOG and V$LOG DPTs.

Control files

Control files are created when the database is created. Control file size is determined by a number of initialization parameters and really shouldn’t concern you. However, if you are using raw devices for control files make sure the raw partition is sized to allow the control file to grow in Oracle8 and Oracle8i databases as backup information is added. Each database instance must have at least one control file of it will not start. Loss of all control files will cause the database to crash. Have at least two copies on physically separate disk arrays or devices.

I suggest maintaining a script to rebuild your control files. The control file rebuild script should be recreated after each physical change to the database. The command CREATE CONTROLFILE is used to manually create a controlfile. However, the ALTER DATABASE BACKUP CONTROL FILE TO TRACE command is the preferred method to generate a script that can be used to rebuild the control file if it becomes damaged.

Initialization File

Although not normally discussed as a database file a database will not startup without a valid initialization parameter file. The file is only read at startup and contains the information required to set up the SGA, control file locations, trace and dump file locations and setup parameters for multi-threaded server, parallel query and Oracle Parallel Server. I suggest keeping a copy in a secure place incase yours ever becomes corrupted, deleted or damaged. The Oracle Reference, which is included in the Oracle8 and Oracle8i documentation set, provides descriptions of all initialization parameters. There are also undocumented initialization parameters that allow specialized behaviors to be configured. Both the documented and undocumented initialization parameters are covered in my book “ORACLE8i Administration and Management” from Wiley and Sons.

It should be obvious that the most important file regarding database setup and operation is probably the INIT.ORA, or initialization file. This file contains the assignments for the database initialization parameters.

NAME	VALUE	DESCRIPTION
O7_DICTIONARY_ACCESSIBILITY	TRUE	V7 Dictionary accessibility Support (Migration only)
ALWAYS_ANTI_JOIN	NESTED_LOOPS	Always use this anti-join when possible
ALWAYS_SEMI_JOIN	standard	Always use this semi-join when possible
AQ_TM_PROCESSES	0	Number of AQ Time Managers to start
AUDIT_FILE_DEST	(PD)	Destination for audit files
AUDIT_TRAIL	NONE	Enable system auditing
BACKGROUND_CORE_DUMP	PARTIAL	Sets whether SGA is dumped with core file dump, PARTIAL means don't dump SGA.
BACKGROUND_DUMP_DEST	(PD)	Detached process dump directory
BACKUP_TAPE_IO_SLAVES	FALSE	BACKUP Tape I/O slaves
BITMAP_MERGE_AREA_SIZE	1048576	Maximum memory allow for BITMAP MERGE
BLANK_TRIMMING	FALSE	Blank trimming semantics parameter
BUFFER_POOL_KEEP	0	Number of database blocks/latches in KEEP buffer pool
BUFFER_POOL_RECYCLE	0	Number of database blocks/latches in recycle buffer pool
COMMIT_POINT_STRENGTH	1	Bias this node has toward not preparing in a two-phase commit
COMPATIBLE	8.1.0	Database will be compatible with this software version
CONTROL_FILE_RECORD_KEEP_TIME	7	Control file record keep time in days
CONTROL_FILES	(PD)	Control file names list
CORE_DUMP_DEST	(PD)	Destination for core dump files.
CPU_COUNT	(PD)	Number of cpu's for this instance
CREATE_BITMAP_AREA_SIZE	8388608	Size of create bitmap buffer for bitmap index
CURSOR_SPACE_FOR_TIME	FALSE	Use more memory in order to get faster execution
DB_BLOCK_BUFFERS	8000	Number of database blocks cached in memory
DB_BLOCK_CHECKING	TRUE	Data and index block checking overrides events 10210 and 10211
DB_BLOCK_CHECKSUM	FALSE	Store checksum in db blocks and check during reads
DB_BLOCK_LRU_LATCHES	1\|CPU_COUNT/2	Number of lru latches
DB_BLOCK_MAX_DIRTY_TARGET	DB_BLOCK_BUFFERS	Upper bound on modified buffers/recovery reads
DB_BLOCK_SIZE	(PD)	Size of database block in bytes
DB_DOMAIN	WORLD	Directory part of global database name stored with CREATE DATABASE
DB_FILE_DIRECT_IO_COUNT	64	Sequential I/O block count
DB_FILE_MULTIBLOCK_READ_COUNT	8	Db blocks read for each IO
DB_FILE_NAME_CONVERT	NULL	Datafile name convert pattern and string for standby/clone database
DB_FILES	MAXDATAFILES	Max allowable # db files
DB_NAME	(PD)	Database name specified in CREATE DATABASE
DB_WRITER_PROCESSES	1	Number of background database writer processes to start
DBLINK_ENCRYPT_LOGIN	FALSE	Enforce password for distributed login always be encrypted
DBWR_IO_SLAVES	0	Number of DBWR I/O slaves
DELAYED_LOGGING_BLOCK_CLEANOUTS	TRUE	Turns delayed block cleanout on or off
DISK_ASYNCH_IO	TRUE	Use asynch I/O for random access devices
DISTRIBUTED_TRANSACTIONS	(PD)	Max. number of concurrent distributed transactions
DML_LOCKS	4*Trans.	Dml locks - one for each table modified in a transaction
ENQUEUE_RESOURCES	Derived	Resources for enqueues
EVENT	NULL	Debug event control
FIXED_DATE	NULL	Fixed SYSDATE value
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY	FALSE	Freeze database during instance recovery (OPS)
GC_DEFER_TIME	10	How long to defer down converts for hot buffers (DFS)(OPS)
GC_FILES_TO_LOCKS	NULL	Mapping between file numbers and lock buckets (DFS)(OPS)
GC_RELEASABLE_LOCKS	0	Number of releasable locks (DFS)(OPS)
GC_ROLLBACK_LOCKS	20	Locks for the rollback segments (DFS)(OPS)
GLOBAL_NAMES	TRUE	Enforce that database links have same name as remote database
HASH_AREA_SIZE	2*SORT	Size of in-memory hash work area
HASH_JOIN_ENABLED	TRUE	Enable/disable hash join
HASH_MULTIBLOCK_IO_COUNT	1	Number of blocks hash join will read/write at once
HI_SHARED_MEMORY_ADDRESS	0	SGA starting address (high order 32-bits on 64-bit platforms)
HS_AUTOREGISTER	TRUE	Enable automatic server DD updates in HS agent self-registration
IFILE	NULL	Include file in init.ora
INSTANCE_GROUPS	NULL	List of instance group names
INSTANCE_NAME	NULL	Instance name supported by the instance

INSTANCE_NUMBER	0	Instance number
JAVA_POOL_SIZE	10000K	Size in bytes of the Java pool
JOB_QUEUE_INTERVAL	60	Wakeup interval in seconds for job queue processes
JOB_QUEUE_KEEP_CONNECTIONS	FALSE	Keep network connections between execution of jobs
JOB_QUEUE_PROCESSES	0	Number of job queue processes to start
LARGE_POOL_SIZE	0	Size in bytes of the large allocation pool (auto set at 600k)
LICENSE_MAX_SESSIONS	0	Maximum number of non-system user sessions allowed
LICENSE_MAX_USERS	0	Maximum number of named users that can be created in the database
LICENSE_SESSIONS_WARNING	0	Warning level for number of non-system user sessions
LM_LOCKS	12000	Number of locks configured for the lock manager (OPS)
LM_PROCS	64	Number of client processes configured for the lock manager (OPS)
LM_RESS	6000	Number of resources configured for the lock manager (OPS)
LOCAL_LISTENER	NULL	Local listener
LOCK_NAME_SPACE	NULL	Lock name space used for generating lock names for standby/clone database
LOCK_SGA	FALSE	Lock entire SGA in physical memory

Oracle Database Structure:

• A database is collection of data files plus programs that manipulate those data files.

• Two types of information are stored in an Oracle database

• User data, relevant to a particular application and

• System data, that the database needs to manage itself

Components:

• Database files – contain all database data

• Control files – contain info to access and manipulate the data

• Redo Logs – record all the transactions on the database

• Tablespaces – logical divisions of a database

• Segments – set of database blocks

• Extents – allocation of contiguous space on disk exp in bytes.

Database files:

• An Oracle database consists of one or more database files.

• These files contain data that can be accessed with the help of SQL.

• A file can be associated with one and only one tablespace.

• The database when created initially, has only one physical file.

• The maximum number of files that can be opened at a time are 32 by default.

• One or more physical files form a logical unit called Tablespace.

Control files:

• A database’s overall physical architecture is maintained by its control files.

• These record control information about all the files within the database.

• A control file is associated with a single database

• Control files are automatically modified by Oracle; a user cannot edit them.

• They are used to maintain internal consistency and guide recovery operations.

Redo Log files:

• A Redo Log contains all the transactions that have occurred against the database.

• No activity in the database can take place without being recorded in Redo Logs.

• The Redo Logs are necessary to protect against data loss.

• Redo Logs are also called transaction logs.

• Every database requires a minimum of two Redo Logs.

• These are used in recovery operations to restore lost or damaged files.

Oracle Processes:

The front-line support for Oracle backup and recovery is the Oracle architecture. One part

of this architecture is the processes related to the Oracle database. Although the Oracle

database has a number of processes, only a few really matter with respect to backup and

recovery and will be mentioned in this text. These processes are as follows:

SMON

PMON

CKPT

LGWR

DBWR

ARCH

User processes

Let’s discuss each of these processes next so you can better understand how they impact

database recovery.

SMON (System Monitor):

SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.

When you startup your Oracle database, Oracle will perform several checks to see if the database is healthy. SMON manages your database for you!

If Oracle finds a problem at startup time, the System Monitor process (SMON) will perform recovery activities. SMON also performs certain types of database maintenance. These activities occur in the background and have no real impact on normal database operations.

Performs multiple maintenance tasks, including the following:

• Creates and manages the temporary tablespace metadata

• Reclaims space used by orphaned temp segments

• Maintains the undo tablespace by on-lining, off-lining, and shrinking undo segments based on undo space usage statistics

• Cleans up the data dictionary when in a transient and inconsistent state

• Maintains the SCN to time mapping table used to support Flashback

PMON (Process Monitor):

Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.

• The Process Monitor is the janitor of the database, cleaning-up trash left-over from aborted user sessions (for instance, if a client abnormally disconnects from the database). If this should happen, it is the job of the Process Monitor (PMON) process to cleanup after that failure. PMON will cleanup memory areas and other database resources that were in use by that user process. PMON constantly checks the status of user and database processes. In some cases, failed database processes can be restarted by PMON.

LGWR Process

The log writer process (LGWR) is responsible for keeping the online redo logs up-to-date. The

job of the LGWR process is to move redo from the volatile (nonpersistent) redo log buffer in

the System (sometimes called Shared) Global Area (SGA) to the persistence of the online redo

logs. A number of different things cause LGWR to wake up and write the redo data, among

them commits and when the redo log buffer fills to a certain point.

DBWR Process

The database writer process DBWn is responsible for writing to the database datafiles.

This writing occurs during events called checkpoints. A database checkpoint may, in reality,

happen at just about any time while the database is running. DBWR has very little

to do with recovery of the database (other than to support it by writing to the datafiles)

because database datafile writes are often delayed and the blocks within the datafiles themselves

are not consistent with the current state of the data inside of the SGA.

ARCH Process

The archiver process ARCn is responsible for the creation of archived redo logs. In a later

section in this chapter on redo logs, we will discuss how redo logs are filled with redo. Once

the redo log file fills, a log switch occurs and Oracle will begin to write to the next online

redo log. If the database is in ARCHIVELOG mode (see the section “NOARCHIVELOG

and ARCHIVELOG Modes”), the ARCH process will be responsible for taking that filled

archived redo log and copying it to one or more backup locations.

In Oracle Database 11g, the ARCH process starts automatically. Oracle can also start

more than one ARCH process if multiple redo logs need to be archived. For ARCH to work

properly, you will need to configure the appropriate archiving locations (see “Configuring the

Database for Backup and Recovery” later in this chapter for more). The ARCH process is so

vital to backup and recovery of the database that if it cannot copy the archived redo logs to the

mandatory archived log destinations, the database will eventually stall until the problem

is corrected.

User Processes

At first glance, it might seem that the user processes are not all that important to backup and

recovery. As you will see, user processes are actually an integral part of backup and recovery

since you have to be able to connect to the database instance to actually do a database backup

or recovery.

Tablespaces:

• A database is divided into logical divisions called tablespaces

• A database may have one or more tablespaces

• Each logical tablespace corresponds to one or more physical database files.

•The typical tablespace present in an Oracle database are (11g) :

SYSTEM Tablespace:

• Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.

In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.

• The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1.

Note: Once a tablespace is locally managed, it cannot be reverted back to being dictionary managed.

SYSAUX Tablespace:

• The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:

PERMANENT
READ WRITE
EXTENT MANAGMENT LOCAL
SEGMENT SPACE MANAGMENT AUTO

You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.

Undo Tablespaces:

• Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.

When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. In rare circumstances, if the instance does not have a designated undo tablespace, the transaction binds to the system undo segment.

• Each undo tablespace is composed of a set of undo files and is locally managed. Like other types of tablespaces, undo blocks are grouped in extents and the status of each extent is represented in the bitmap. At any point in time, an extent is either allocated to (and used by) a transaction table, or it is free.

Temporary Tablespace:

• When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

• If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning inALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.

USER Tablespace:

• information about users is stored

ORA-00603 ORA-00600 + [kqlidchg0] ORA-00604 ORA-00001

2014-08-26T15:42:00.002+05:30

Today we've encountered the below error, it seems to be,

============Alert log==============
Mon Aug 25 11:08:50 2014
Errors in file /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/trace/SID_NAME_ora_40108088.trc (incident=19385):
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident details in: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19385/SID_NAME_ora_40108088_i19385.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/trace/SID_NAME_ora_40108088.trc (incident=19386):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident details in: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19386/SID_NAME_ora_40108088_i19386.trc
Mon Aug 25 11:08:56 2014

============Trace file=============
Incident 19385 created, dump file: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19385/SID_NAME_ora_40108088_i19385.trc
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident 19386 created, dump file: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19386/SID_NAME_ora_40108088_i19386.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG
kgefec: fatal error 0
opitsk: network error occurred while two-task server trying tosend break; error code = 24365

Clause:

There are dependent objects in an invalid state.

Identify:

Please run the following query and review the results.

SQL> SET LINESIZE 100

SQL> SELECT object_id, object_name, object_type, owner, status

FROM dba_objects

WHERE object_name IN (SELECT symrep

FROM plscope_identifier$

WHERE obj# IN (SELECT obj#

FROM plscope_identifier$

MINUS

SELECT object_id

FROM dba_objects));

Solutions:

1. Compile the invalid objects to make it as valid or

2. Drop the invalid objects.
3. Purge the recyclebin to clear the dropped objects.
4. Drop the table that was failing to drop.

ORA-01565: error in identifying file / ORA-27037: unable to obtain file status.

2014-08-06T15:50:00.001+05:30

ORA-01565: error in identifying file / ORA-27037: unable to obtain file status.
When trying to create pfile from spfile getting below error.

ORA-01565: Unable to open Spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfile$SID.ora.

SQL> create pfile='/d01/oracle/initsetting.ora' from spfile;
create pfile='/d01/oracle/initsetting.ora' from spfile

ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Cause: Database is not running with spfile,It is running with memory.
issue seen while doing rman duplicate clone or Unexpectedly spfile/pfile dropped/missing while database is up and running fine..

Solution:-

SQL> create pfile='/d01/oracle/initsetting.ora' from memory;

File created.

Generate DDL scripts using DBMS_METADATA.GET_DDL

2014-07-10T12:51:00.005+05:30

Use DBMS_METADATA.GET_METADATA to copy the user scripts from one database to another database.
First, we will take the DDL scripts of the tablespace using the following command:

SQL> set head off echo off
SQL> select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||  tablespace_name   || ''') from dual;' from dba_tablespaces;
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TOOLS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','RDPSDT01') from dual;

SQL> select dbms_metadata.get_ddl('TABLESPACE','RDPSDT01') from dual;  
  CREATE TABLESPACE "RDPSDT01" DATAFILE 
  '/apps/oradata/RAIMUMDI2/RDPSDT01/RDPSDT01_01.dbf' SIZE 4294967296, 
  '/apps/oradata/RAIMUMDI2/RDPSDT01/RDPSDT01_02.dbf' SIZE 4294967296   
  AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M   
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192   
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  
  SEGMENT SPACE MANAGEMENT AUTO

Now, Get the script for the user creation also for the roles & object grant & system grants provided to user.

set head off
set pages 0
set long 9999999
 15:26:43 SYS > select  dbms_metadata.get_ddl('USER', 'OPS$RPO') || '/' usercreate from DUAL;
 USERCREATE
----------------------------------------------------------------------
 CREATE USER "OPS$RPO" IDENTIFIED BY VALUES 'S:C952B1BD452B5E63692668680C22A8F
7C8E9F26557E808DADB396E85ADBF;01F95BD48BDDF041'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE/

Get all their roles and grants as well

15:27:05 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','OPS$RPO') FROM DUAL;
 DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
  GRANT "CONNECT" TO "OPS$RPO"
  GRANT "RESOURCE" TO "OPS$RPO"
  GRANT "DBA" TO "OPS$RPO"
  GRANT "IVISION_USER" TO "OPS$RPO"

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ADMIN') FROM DUAL;
 15:27:34 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO') FROM DUAL;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT EXECUTE ANY PROCEDURE TO "OPS$RPO"
 GRANT ALTER ANY PROCEDURE TO "OPS$RPO"
 GRANT CREATE PROCEDURE TO "OPS$RPO"
 GRANT GRANT ANY ROLE TO "OPS$RPO"
 GRANT DROP PUBLIC SYNONYM TO "OPS$RPO"
 GRANT CREATE PUBLIC SYNONYM TO "OPS$RPO"
 GRANT SELECT ANY TABLE TO "OPS$RPO"
 GRANT CREATE TABLE TO "OPS$RPO"
 GRANT DROP USER TO "OPS$RPO"
 GRANT ALTER USER TO "OPS$RPO"
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT CREATE USER TO "OPS$RPO"
 GRANT UNLIMITED TABLESPACE TO "OPS$RPO"
 GRANT CREATE SESSION TO "OPS$RPO"

15:27:51 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OPS$RPO') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT EXECUTE, DEBUG ON "SYS"."DBMS_PIPE" TO "OPS$RPO"
 GRANT EXECUTE ON "OPS$VTF"."DBK_C_ERR" TO "OPS$RPO"

You can use the following scripts to get the DDL scripts

SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'','''||  TABLESPACE_NAME || ''') FROM DUAL;' FROM DBA_TABLESPACES;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','ADMIN_TBS')  FROM DUAL;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''USER'','''||  USERNAME || ''') FROM DUAL;' FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','ADMIN') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ADMIN') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','ADMIN') FROM DUAL;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLE'','''||  TABLE_NAME|| ''') FROM DUAL;' FROM DBA_TABLES;
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM all_db_links a;

ORA-00600 Metalink Notes

2014-07-04T19:41:00.007+05:30

ORA - 600 -- Look at these Metalink notes for Help

ORA-600 Errors 1 to 3000
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"

ORA-600 Errors 3001 to 6000
Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"

ORA-600 Errors 6001 to 9000
Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"

ORA-600 Errors 9001 to 15000
Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"

ORA-600 Errors 15001 to 17000
Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"

ORA-600 Errors 17001 to 30000
Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"

ORA-600 Errors kc
Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"

ORA-600 Errors kd to kw
Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"

ORA-600 Errors q to z
Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"

How to start/stop RAC componants

2014-03-13T18:38:00.004+05:30

How to Stop/Start RAC components
In this article, I am going to explain how to stop/start rac components. This is my environment:

My db version : 11.2.0.3
My Operating System : AIX 6.1
My servers hostname : node1-node2
My database name : TEST
My instance name : TEST1-TEST2

Here is the some basic commands, for commands details&options please review Reference docs:

$$CRS_HOME/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 763e07f95ac84ff6bf7132f672c5c47e (/dev/voting01) [OCR_VOTE]
2. ONLINE 2dc5840f8eeb4f6bbfe624bdf3f2c4a2 (/dev/voting02) [OCR_VOTE]
3. ONLINE 8422c3282a1f4f55bfa6b1242ff7b967 (/dev/voting03) [OCR_VOTE]

$$CRS_HOME/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2808
Available space (kbytes) : 259312
ID : 597557072
Device/File Name : +OCR_VOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

Checking CRS Status
[oracle@node1] $ORACLE_HOME/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[oracle@node2] $ORACLE_HOME/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Checking Node Status

[oracle@node1] $ORACLE_HOME/bin/srvctl status nodeapps

VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP 192.168.100.101 is enabled
VIP 192.168.100.101 is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

[oracle@node2] $ORACLE_HOME/bin/srvctl status nodeapps

VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP 192.168.100.101 is enabled
VIP 192.168.100.101 is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

Checking Clusterware Resource Status
[oracle@node1]$ cd $CRS_HOME/bin
[oracle@node1]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg ONLINE ONLINE node1
ONLINE ONLINE node2
ora.LISTENER.lsnr ONLINE ONLINE node1
ONLINE ONLINE node2
ora.OCR_VOTE.dg ONLINE ONLINE node1
ONLINE ONLINE node2
ora.asm ONLINE ONLINE node1 Started
ONLINE ONLINE node2 Started
ora.gsd OFFLINE OFFLINE node1
OFFLINE OFFLINE node2
ora.net1.network ONLINE ONLINE node1
ONLINE ONLINE node2
ora.ons ONLINE ONLINE node1
ONLINE ONLINE node2
ora.registry.acfs ONLINE ONLINE node1
ONLINE ONLINE node2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE node2
ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE node1
ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE node1
ora.cvu 1 ONLINE ONLINE node1
ora.oc4j 1 ONLINE ONLINE node1
ora.scan1.vip 1 ONLINE ONLINE node2
ora.scan2.vip 1 ONLINE ONLINE node1
ora.scan3.vip 1 ONLINE ONLINE node1
ora.node1.vip 1 ONLINE ONLINE node1
ora.node2.vip 1 ONLINE ONLINE node2

You can use below command which is not recommended for 11g and which is depreciated

[oracle@node1]crs_stat -t
Name Type Target State Host
————————————————————
ora….DATA.dg ora….up.type ONLINE ONLINE node1
ora….ER.lsnr ora….er.type ONLINE ONLINE node1
ora….N1.lsnr ora….er.type ONLINE ONLINE node1
ora….N2.lsnr ora….er.type ONLINE ONLINE node2
ora.ORADATA.dg ora….up.type ONLINE ONLINE node1
ora.asm ora.asm.type ONLINE ONLINE node1
ora.cvu ora.cvu.type ONLINE ONLINE node2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora….SM1.asm application ONLINE ONLINE node1
ora….11.lsnr application ONLINE ONLINE node1
ora….b11.gsd application OFFLINE OFFLINE
ora….b11.ons application ONLINE ONLINE node1
ora….b11.vip ora….t1.type ONLINE ONLINE node1
ora….SM2.asm application ONLINE ONLINE node2
ora….12.lsnr application ONLINE ONLINE node2
ora….b12.gsd application OFFLINE OFFLINE
ora….b12.ons application ONLINE ONLINE node2
ora….b12.vip ora….t1.type ONLINE ONLINE node2
ora….network ora….rk.type ONLINE ONLINE node1
ora.oc4j ora.oc4j.type ONLINE ONLINE node2
ora.ons ora.ons.type ONLINE ONLINE node1
ora.TEST.db ora….se.type ONLINE ONLINE node1
ora….int.svc ora….ce.type ONLINE ONLINE node2
ora….int.svc ora….ce.type ONLINE ONLINE node2
ora….kis.svc ora….ce.type ONLINE ONLINE node2
ora….est.svc ora….ce.type ONLINE ONLINE node1
ora….ry.acfs ora….fs.type ONLINE ONLINE node1
ora.scan1.vip ora….ip.type ONLINE ONLINE node1
ora.scan2.vip ora….ip.type ONLINE ONLINE node2

Oracle High Availability Services

– disable/enable Oracle HAS.
Use the “crsctl enable/disable has” command to disable automatic startup of the Oracle High Availability Services stack when the server boots up.

To can see current settings for Oracle High Availability Services stack when the server boots up, follow:

[root@node1]crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

or

[root@node1]cat /etc/oracle/scls_scr/node1/root/ohasdstr
enable

So as you can see my current setting is enable.If your system shown disable than :

For Disable:
[root@node1]crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.

[root@node1] crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.

# cat /etc/oracle/scls_scr/node1/root/ohasdstr
disable

For Enable:
[root@node1]crsctl enable has
CRS-4621: Oracle High Availability Services autostart is enabled.

Check new setting:

[root@node1] crsctl config has
CRS-4621: Oracle High Availability Services autostart is enabled.

[root@node1] cat /etc/oracle/scls_scr/node1/root/ohasdstr
enable

Stop the Oracle clusterware stack

You can use below commands:
With root user:

crsctl stop crs or crsctl stop has

[root@node1]crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘node1′
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘node1′
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘node1′
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.TEST.db’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘node1′
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘node1′
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.node1.vip’ on ‘node1′
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘node1′
CRS-2677: Stop of ‘ora.node1.vip’ on ‘node1′ succeeded
CRS-2672: Attempting to start ‘ora.node1.vip’ on ‘node2′
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘node1′ succeeded
CRS-2672: Attempting to start ‘ora.scan2.vip’ on ‘node2′
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘node1′ succeeded
CRS-2672: Attempting to start ‘ora.scan3.vip’ on ‘node2′
CRS-2676: Start of ‘ora.node1.vip’ on ‘node2′ succeeded
CRS-2677: Stop of ‘ora.TEST.db’ on ‘node1′ succeeded
CRS-2676: Start of ‘ora.scan2.vip’ on ‘node2′ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN2.lsnr’ on ‘node2′
CRS-2676: Start of ‘ora.scan3.vip’ on ‘node2′ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN3.lsnr’ on ‘node2′
CRS-2676: Start of ‘ora.LISTENER_SCAN2.lsnr’ on ‘node2′ succeeded
CRS-2676: Start of ‘ora.LISTENER_SCAN3.lsnr’ on ‘node2′ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.eons’ on ‘node1′
CRS-2677: Stop of ‘ora.ons’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘node1′
CRS-2677: Stop of ‘ora.net1.network’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘node1′ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘node1′ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.cssdmonitor’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘node1′
CRS-2677: Stop of ‘ora.cssdmonitor’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘node1′
CRS-2677: Stop of ‘ora.cssd’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘node1′
CRS-2677: Stop of ‘ora.gipcd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.diskmon’ on ‘node1′ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘node1′ has completed
CRS-4133: Oracle High Availability Services has been stopped.

Start the Oracle clusterware stack

You can use below commands:
With root user:
crsctl start crs or crsctl start has

[root@node1] crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Start the Oracle Database
To start all Oracle RAC instances for a database:
[oracle@node1]
$ORACLE_HOME/bin/srvctl start database -d db_name

PS: db_name is the name of the database this command is starting all the instances

Stop the Oracle Database
To shut down all Oracle RAC instances for a database:
[oracle@node1]
$ORACLE_HOME/bin/srvctl stop database -d db_name
PS: db_name is the name of the databasethis command is starting all the instances

Start the Oracle Instance:

[oracle@node1]
$ORACLE_HOME/bin/srvctl start database -d db_name -i instance_name

Stop the Oracle Instance:

[oracle@node1]
$ORACLE_HOME/bin/srvctl stop database -d db_name -i instance_name
Stop/Start Listener-SCAN_LISTENER

srvctl stop/start listener -n node1
srvctl stop/start listener -n node2
srvctl stop scan_listener

Stop ASM

srvctl stop asm [-o stop_options] [-f]
srvctl stop asm -n node1

How to simulate block corruption and do RMAN block recovery

2014-01-18T00:57:00.001+05:30

RMAN offers a blockrecover command to recover from a block corruption.Otherwise we would have to restore the entire datafile and thereby recover it.

Lets simulate block media recovery

1) Corrupt datafile users.dbf for instance

$ dd if=/dev/zero of=/u/oracle/oradata/test/users.dbf bs=8k conv=notrunc seek=20 count=1

2) verify the block corruption using dbverify utility:

$dbv file=/u/oracle/oradata/test/users.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Tue Jul 20 16:21:06 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u/oracle/oradata/test/users.dbf
Page 20 is marked corrupt
Corrupt block relative dba: 0x01000014 (file 4, block 20)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 131072
Total Pages Processed (Data) : 87614
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5449
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1449
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 36559
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 301640 (0.301640)

Block 20 of datafile 4 is corrupted.

3) Lets do RMAN block recovery using blockrecover command.

RMAN> blockrecover datafile 4 block 20;

Starting blockrecover at 20-JUL-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u1/flash_recovery_area/test/backupset/2010_07_16/o1_mf_nnndf_TAG20100716T181457_640o2b16_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u1/flash_recovery_area/test/backupset/2010_07_16/o1_mf_nnndf_TAG20100716T181457_640o2b16_.bkp tag=TAG20100716T181457
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:45

starting media recovery

archive log thread 1 sequence 17 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_17/o1_mf_1_17_642q4zbv_.arc
archive log thread 1 sequence 18 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_19/o1_mf_1_18_647vt1ps_.arc
archive log thread 1 sequence 19 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_19/o1_mf_1_19_647vvqrz_.arc
archive log thread 1 sequence 20 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_20/o1_mf_1_20_64byfgff_.arc
archive log thread 1 sequence 21 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_20/o1_mf_1_21_64byfgjf_.arc
archive log thread 1 sequence 22 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_20/o1_mf_1_22_64byfg8f_.arc
media recovery complete, elapsed time: 00:00:05
Finished blockrecover at 20-JUL-10

Recover Standby database With Missing Archivelogs on Dataguard setup

2014-01-16T13:14:00.003+05:30

After stoping dataguard service for some maintanence purposes, some of the archive logs got lost on primary database that were produced meantime. RMAN incremental backup was used to recover dataguard and resyncronize. Primary and standby databases don't use same RMAN catalog, so the backupset that is taken from primary backup needed to transfer and register on the standby side. Also there have been some datafile creations on primary side that were not applied on standby. Because of this, we needed to re-create the control file on standby and transfer the newly created datafiles from primary side. Here is a detailed article about this recovery process.

1-determine last SCN on standby db

PRIMARY

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3360225821

STANDBY

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3215410716

2-Stop log apply and transport services.

2.1 stop redo sent on primary

alter system set log_archive_dest_state_2 ='defer' scope=both ;

2.2 stop redo apply on standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3-Backup primary database incremental ; from SCN last applied on standby db.

--for faster backup try with multi channel

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

allocate channel ch5 device type disk;

allocate channel ch6 device type disk;

BACKUP INCREMENTAL FROM SCN 3215410716 DATABASE FORMAT '/intl_migration/cdrdb/backup/tmpForStandby_%U' tag 'FORSTANDBY';

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

release channel ch5;

release channel ch6;

}

4-Transfer backup sets to standby side.

because the incremental backup was 1 TB size ; I needed to seperate under different mount points.

Don't worry about keeping them in different folders. We will register them.

SOURCE FOLDERS

/intl_migration/cdrdb/backup/

DEST FOLDER

/medftp/backupDG

/app3/backupDG/

bin

prompt

lcd /intl_migration/cdrdb/backup/

cd /medftp/backupDG

mput tmpForStandby_rkk2lbe4_1_1 tmpForStandby_rlk2lbe5_1_1 tmpForStandby_rmk2lbe7_1_1 tmpForStandby_rnk2lbe9_1_1 tmpForStandby_rok2lbeb_1_1 tmpForStandby_rpk2lbed_1_1 tmpForStandby_rqk2m14c_1_1 tmpForStandby_rrk2m19j_1_1 tmpForStandby_rsk2m2bt_1_1 tmpForStandby_rtk2m2eu_1_1 tmpForStandby_ruk2m2km_1_1 tmpForStandby_rvk2m3m2_1_1

bin

prompt

lcd /intl_migration/cdrdb/backup/

cd /app3/backupDG/

mput tmpForStandby_s0k2mmu4_1_1 tmpForStandby_s1k2mn2d_1_1 tmpForStandby_s2k2mnlu_1_1 tmpForStandby_s3k2mnut_1_1 tmpForStandby_s4k2mob3_1_1 tmpForStandby_s5k2moee_1_1 tmpForStandby_s6k2nc22_1_1 tmpForStandby_s7k2ncda_1_1 tmpForStandby_s8k2nd6s_1_1 tmpForStandby_s9k2ne6n_1_1 tmpForStandby_sak2ne8i_1_1 tmpForStandby_sbk2nf3c_1_1 tmpForStandby_ssk2o28c_1_1

5-Register backup sets to stanby db

OnStandby db

rman target /

RMAN> CATALOG START WITH '/app3/backupDG/tmpForStandby';

RMAN> CATALOG START WITH '/medftp/backupDG/tmpForStandby';

6-Recover standby db ;

one important note ;

because this is a backup taken for only phisical standby db sync ; noredo key word is required.

See : http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#sthref955

RMAN>

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

allocate channel ch5 device type disk;

allocate channel ch6 device type disk;

allocate channel ch7 device type disk;

allocate channel ch8 device type disk;

RECOVER DATABASE NOREDO;

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

release channel ch5;

release channel ch6;

release channel ch7;

release channel ch8;

}

7-Create new standby control file

Before re-starting log apply service on standby db; create a new standby controlfile in primary db , copy it to standby .Creating a new controlfile is my suggestion because during non transferred and applied logs ; some chages may be done affecting controlfile like adding redo members, adding datafile, adding new tablespaces...etc

7-1 shutdown standby db instance

7-2 create new standby control file move it to standby side destinations (generally 3).

SQL> alter database create standby controlfile as '/tmp/stby.ctl'; --on primary db

scp /tmp/stby.ctl oracle@stdbyserver:/oradata/ctl<1>/ctl.dbf

7-3 start standby db in mount , and start log apply service MenagedRecoveryProcess;

SQL> startup mount;

8-OPTIONAL - Transfer newly created files.

If new datafiles were added during the time that dataguard had been stopped as it happened to me; you need to copy the newly created files .They were not included incremental backup set;

and not created cause of stopped MRP.

8-1 determine all datafiles from database (remember we have just created a new controlfile , both primary and standby has same information)

SQL> spool '/tmp/hede.txt';

SQL> select 'file ' ,name from v$datafile;

# sh /tmp/hede.txt > fileSatus.txt

# cat fileSatus.txt grep cannot

/oradata/file004.dbf : cannot open

/oradata/file005.dbf : cannot open

Means we have to copy these 2 files to standby side.

8-2 After determining missing datafiles ; backup them as image copy in primary db ,copy to standby side.

BACKUP AS COPY DATAFILE '/oradata/file004.dbf' FORMAT '/tmp/file004.dbf' TAG stdbyImgCopy;

BACKUP AS COPY DATAFILE '/oradata/file005.dbf' FORMAT '/tmp/file005.dbf' TAG stdbyImgCopy;

scp /tmp/file004.dbf oracle@stdbyserver:/oradata/file004.dbf

scp /tmp/file005.dbf oracle@stdbyserver:/oradata/file005.dbf

9-Re-start log apply and transfer services.

9.1 start redo sent on primary

alter system set log_archive_dest_state_2 ='enable' scope=both ;

9.2 start redo apply on standby

SQL> startup mount;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

9-3 check if for any problems; you may encounter problems. Check alert.log and status of proceesses

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

After success of this operation We were freed of time and space to re-establish all 30 TB database.

A similar workaound is documented in metalink for Oracle 9i : Doc ID:290817.1

Oracle RAC Interview Questions and Answers

2013-12-28T19:03:00.001+05:30

How does OCSSD starts first if voting disk & OCR resides in ASM Diskgroups?
You might wonder how CSSD, which is required to start the clustered ASM instance, can be started

If voting disks are stored in ASM? This sounds like a chicken-and-egg problem:
without access to the voting disks there is no CSS, hence the node cannot join the cluster.
But without being part of the cluster, CSSD cannot start the ASM instance.
To solve this problem the ASM disk headers have new metadata in 11.2:
you can use kfed to read the header of an ASM disk containing a voting disk.
The kfdhdb.vfstart and kfdhdb.vfend fields tell CSS where to find the voting file. This does not
require the ASM instance to be up.
Once the voting disks are located, CSS can access them and joins the cluster.

What is gsdctl in RAC? list gsdctl commands in Oracle RAC?GSDCTL stands for Global Service Daemon Control, we can use gsdctl commands to start, stop, and obtain the status of the GSD service on any platform.
The options for gsdctl are:-
$ gsdctl start -- To start the GSD service
$ gsdctl stop -- To stop the GSD service
$ gsdctl stat -- To obtain the status of the GSD service

Log file location for gsdctl:
$ ORACLE_HOME/srvm/log/gsdaemon_node_name.log

What is RAC? RAC stands for Real Application cluster.
It is a clustering solution from Oracle Corporation that ensures high availability of databases by
providing instance failover, media failover features.
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of
traditional shared-nothing and shared-disk approaches to provide a highly scalable and available
database solution for all the business applications.
Oracle RAC provides the foundation for enterprise grid computing.

What is Oracle RAC One Node?     Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition).

What is RAC and how is it different from non RAC databases?      Oracle Real Application clusters allows multiple instances to access a single database, the instances will be running on multiple nodes.
In Real Application Clusters environments, all nodes concurrently execute transactions against the same database.
Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.

What are the advantages of RAC (Real Application Clusters)? Reliability - if one node fails, the database won't fail
Availability - nodes can be added or replaced without having to shutdown the database
Scalability - more nodes can be added to the cluster as the workload increases

What is Cache Fusion? Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service (GES) monitors and Instance enqueue process manages the cache fusion.

How do we verify that RAC instances are running? SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.

How can you connect to a specific node in a RAC environment? tnsnames.ora ensure that you have INSTANCE_NAME specified in it.

Which is the "MASTER NODE" in RAC? The node with the lowest node number will become master node and dynamic remastering of the resources will take place.
to find out the master node for particular resource, you can query v$ges_resource for MASTER_NODE column.
To find out which is the master node, you can see ocssd.log file and search for "master node number".
when the first master node fails in the cluster the lowest node number will become master node.

What components in RAC must reside in shared storage? All datafiles, controlfiles, SPFIles, redo log files and archive logs must reside on cluster-aware shred storage.

Give few examples for solutions that support cluster storage? ·ASM (automatic storage management),
·Raw disk devices,
·Network file system (NFS),
·OCFS2 and
·OCFS (Oracle Cluster Fie systems).

What are Oracle Cluster Components? 1.Cluster Interconnect (HAIP)
2.Shared Storage (OCR/Voting Disk)
3.Clusterware software
4.Oracle Kernel Components

What are Oracle RAC Components? VIP, Node apps etc.

What are Oracle Kernel Components? Basically Oracle kernel need to switched on with RAC On option when you convert to RAC, that is the difference as it facilitates few RAC bg process like LMON,LCK,LMD,LMS etc.

How to turn on RAC? # link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

Disk architechture in RAC?SAN (Storage Area Networks) - generally using fibre to connect to the SAN
NAS (Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI

What is Oracle Clusterware? The Clusterware software allows nodes to communicate with each other and forms the cluster that makes the nodes work as a single logical server.
The software is run by the Cluster Ready Services (CRS) using the Oracle Cluster Registry (OCR) that records and maintains the cluster and node membership information and the voting disk which acts as a tiebreaker during communication failures. Consistent heartbeat information travels across the interconnect to the voting disk when the cluster is running.
Real Application Clusters
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all your business applications. Oracle RAC provides the foundation for enterprise grid computing.
Oracle’s Real Application Clusters (RAC) option supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages. Oracle RAC running on clusters provides Oracle’s highest level of capability in terms of availability, scalability, and low-cost computing.
One DB opened by multipe instances so the the db ll be Highly Available if an instance crashes.
Cluster Software. Oracles Clusterware or products like Veritas Volume Manager are required to provide the cluster support and allow each node to know which nodes belong to the cluster and are available and with Oracle Cluterware to know which nodes have failed and to eject then from the cluster, so that errors on that node can be cleared.
Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.
The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.
The voting disk is used to determine if a node has failed, i.e. become separated from the majority. If a node is deemed to no longer belong to the majority then it is forcibly rebooted and will after the reboot add itself again the the surviving cluster nodes.

What are the Oracle Clusterware key components? Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.

What is Voting Disk and OCR?Voting Disk
Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.
A node must be able to access more than half of the voting disks at any time.
For example, if you have 3 voting disks configured, then a node must be able to access at least two of the voting disks at any time. If a node cannot access the minimum required number of voting disks it is evicted, or removed, from the cluster.
Oracle Cluster Registry (OCR)
The cluster registry holds all information about nodes, instances, services and ASM storage if used,
it also contains state information ie they are available and up or similar.
The OCR must reside on shared disk that is accessible by all of the nodes in your cluster.

What are the administrative tasks involved with voting disk? Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks

Can you add voting disk online? Do you need voting disk backup? Yes, as per documentation, if you have multiple voting disk you can add online, but if you have only one voting disk , by that cluster will be down as its lost you just need to start crs in exclusive mode and add the votedisk using
crsctl add votedisk

What is the Oracle Recommendation for backing up voting disk? Oracle recommends us to use the dd command to backup the voting disk with a minimum block size of 4KB.

How do we backup voting disks? 1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
copy voting_disk_name backup_file_name

How do we verify an existing current backup of OCR? We can verify the current backup of OCR using the following command : ocrconfig -showbackup

You have lost OCR disk, what is your next step? The cluster stack will be down due to the fact that cssd is unable to maintain the integrity, this is true in 10g, From 11gR2 onwards, the crsd stack will be down, the hasd still up and running. You can add the ocr back by restoring the automatic backup or import the manual backup,

What are the major RAC wait events? In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs.The most common wait events related to this are gc cr request and gc buffer busy
GC CR request :the time it takes to retrieve the data from the remote cache
Reason: RAC Traffic Using Slow Connection or Inefficient queries (poorly tuned queries will increase the amount of data blocks requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.)
GC BUFFER BUSY: It is the time the remote instance locally spends accessing the requested data block.

What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report? This is most likely due to a fault in interconnect network.
Check netstat -s
if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.

How do you troubleshoot node reboot? Please check metalink ...
Note 265769.1 Troubleshooting CRS Reboots
Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions.

Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can
start it on both nodes? How do you identify the problem? Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.

What are Oracle Clusterware processes for 10g on Unix and Linux?Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.
Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user
Event manager daemon (evmd) —A background process that publishes events that crs creates.
Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

What are Oracle database background processes specific to RAC? Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

What is GRD? GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cahed block using global resource directory.This process is referred to as cache fusion and helps in data integrity.

What is ACMS? ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.

What is SCAN listener? A scan listener is something that additional to node listener which listens the incoming db connection requests from the client which got through the scan IP, it got end points configured to node listener where it routes the db connection requests to particular node listener.
SCAN IP can be disabled if not required. However SCAN IP is mandatory during the RAC installation. Enabling/disabling SCAN IP is mostly used in oracle apps environment by the concurrent manager (kind of job scheduler in oracle apps).
Steps to disable the SCAN IP,
i. Do not use SCAN IP at the client end.
ii. Stop scan listener
     srvctl stop scan_listener
iii.Stop scan
     srvctl stop scan (this will stop the scan vip's)
iv. Disable scan and disable scan listener
     srvctl disable scan

What are the different network components are in 10g RAC? public, private, and vip components
Private interfaces is for intra node communication.
VIP is all about availability of application. When a node fails then the VIP component fail over to some other node, this is the reason that all applications should based on vip components means tns entries should have vip entry in the host list

What is an interconnect network? An interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.

What is the use of cluster interconnect? Cluster interconnect is used by the Cache fusion for inter instance communication.

How can we configure the cluster interconnect? · Configure User Datagram Protocol (UDP) on Gigabit Ethernet for cluster interconnects.
· On UNIX and Linux systems we use UDP and RDS (Reliable data socket) protocols to be used by Oracle Clusterware.
· Windows clusters use the TCP protocol.

What is the purpose of Private Interconnect? Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.

What is a virtual IP address or VIP? A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.

What is the use of VIP? If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.

Why do we have a Virtual IP (VIP) in Oracle RAC? Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.
When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.

Give situations under which VIP address failover happens? VIP addresses failover happens when the node on which the VIP address runs fails; all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.

What is the significance of VIP address failover? When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.

What is the use of a service in Oracle RAC environment? Applications should use the services feature to connect to the Oracle database. Services enable us to define rules and characteristics to control how users and applications connect to database instances.

What are the characteristics controlled by Oracle services feature? The characteristics include a unique name, workload balancing, failover options, and high availability.

What enables the load balancing of applications in RAC? Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.

What are the types of connection load-balancing? Connection Workload management is one of the key aspects when you have RAC instances as you want to distribute the connections to specific nodes/instance or those have less load.
There are two types of connection load-balancing:
1.Client Side load balancing (also called as connect time load balancing)
2.Server side load balancing (also called as Listener connection load balancing)

What is the difference between server-side and client-side connection load balancing? Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.
Client Side load balancing:- Oracle client side load balancing feature enables clients to randomize the connection requests among all the available listeners based on their load.
An tns entry that contains all nodes entries and use load_balance=on (default its on) will use the connect time load balancing or client side load balancing.
Sample Client Side TNS Entry:-
     finance =
     (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = myrac2-vip)(PORT = 2042))
          (ADDRESS = (PROTOCOL = TCP)(HOST = myrac1-vip)(PORT = 2042))
          (ADDRESS = (PROTOCOL = TCP)(HOST = myrac3-vip)(PORT = 2042))
     (LOAD_BALANCE = yes)
     (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = FINANCE) (FAILOVER=ON)
     (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))
     )
     )
Server side load balancing:- This improves the connection performance by balancing the number of active connections among multiple instances and dispatchers. In a single instance environment (shared servers), the listener selects the least dispatcher to handle the incoming client requests. In a rac environments, PMON is aware of all instances load and dispatchers , and depending on the load information PMON redirects the connection to the least loaded node.
In a RAC environment, *.remote_listener parameter which is a tns entry containing all nodes addresses need to set to enable the load balance advisory updates to PMON.
Sample Tns entry should be in an instances of RAC cluster,
     local_listener=LISTENER_MYRAC1
     remote_listener = LISTENERS_MYRACDB

What are the administrative tools used for Oracle RAC environments? Oracle RAC cluster can be administered as a single image using the below
·       OEM (Enterprise Manager),
·       SQL*PLUS,
·       Server control (SRVCTL),
·       Cluster Verification Utility (CLUVFY),
·       DBCA,
·       NETCA

Name some Oracle Clusterware tools and their uses? ·OIFCFG - allocating and deallocating network interfaces.
·OCRCONFIG - Command-line tool for managing Oracle Cluster Registry.
·OCRDUMP - Identify the interconnect being used.
·CVU - Cluster verification utility to get status of CRS resources.

What is the difference between CRSCTL and SRVCTL? crsctl manages clusterware-related operations:
     Starting and stopping Oracle Clusterware
     Enabling and disabling Oracle Clusterware daemons
     Registering cluster resources
srvctl manages Oracle resource–related operations:
     Starting and stopping database instances and services
     Also from 11gR2 manages the cluster resources like network,vip,disks etc

How do we remove ASM from a Oracle RAC environment? We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n node_name
We can verify if ASM has been removed by issuing the following command:
srvctl config asm -n node_name

How do we verify that an instance has been removed from OCR after deleting an instance? Issue the following srvctl command:
srvctl config database -d database_name
cd CRS_HOME/bin
./crs_stat or crsctl stat res -t

What are the modes of deleting instances from ORacle Real Application cluster Databases? We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).

What are the background process that exists in 11gr2 and functionality? Process Name     Functionality
crsd     •The CRS daemon (crsd) manages cluster resources based on configuration information that is stored in Oracle Cluster Registry (OCR) for each resource. This includes start, stop, monitor, and failover operations. The crsd process generates events when the status of a resource changes.
cssd     •Cluster Synchronization Service (CSS): Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members when a node joins or leaves the cluster. If you are using certified third-party clusterware, then CSS processes interfaces with your clusterware to manage node membership information. CSS has three separate processes: the CSS daemon (ocssd), the CSS Agent (cssdagent), and the CSS Monitor (cssdmonitor). The cssdagent process monitors the cluster and provides input/output fencing. This service formerly was provided by Oracle Process Monitor daemon (oprocd), also known as OraFenceService on Windows. A cssdagent failure results in Oracle Clusterware restarting the node.
diskmon     •Disk Monitor daemon (diskmon): Monitors and performs input/output fencing for Oracle Exadata Storage Server. As Exadata storage can be added to any Oracle RAC node at any point in time, the diskmon daemon is always started when ocssd is started.
evmd     •Event Manager (EVM): Is a background process that publishes Oracle Clusterware events
mdnsd     •Multicast domain name service (mDNS): Allows DNS requests. The mDNS process is a background process on Linux and UNIX, and a service on Windows.
gnsd     •Oracle Grid Naming Service (GNS): Is a gateway between the cluster mDNS and external DNS servers. The GNS process performs name resolution within the cluster.
ons     •Oracle Notification Service (ONS): Is a publish-and-subscribe service for communicating Fast Application Notification (FAN) events
oraagent     •oraagent: Extends clusterware to support Oracle-specific requirements and complex resources. It runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1).
orarootagent     •Oracle root agent (orarootagent): Is a specialized oraagent process that helps CRSD manage resources owned by root, such as the network, and the Grid virtual IP address oclskd     •Cluster kill daemon (oclskd): Handles instance/node evictions requests that have been escalated to CSS
gipcd     •Grid IPC daemon (gipcd): Is a helper daemon for the communications infrastructure ctssd     •Cluster time synchronisation daemon(ctssd) to manage the time syncrhonization between nodes, rather depending on NTP

Under which user or owner the process will start? Component                                                         Name of the Process                   Owner
Oracle High Availability Service                       ohasd                                            init, root
Cluster Ready Service (CRS)                              Cluster Ready Services                root
Cluster Synchronization Service (CSS)              ocssd,cssd monitor, cssdagent      grid owner
Event Manager (EVM)                                        evmd, evmlogger                          grid owner
Cluster Time Synchronization Service (CTSS) octssd                                           root
Oracle Notification Service (ONS)                     ons, eons                                       grid owner
Oracle Agent                                                        oragent                                          grid owner
Oracle Root Agent                                               orarootagent                                 root
Grid Naming Service (GNS)                               gnsd                                              root
Grid Plug and Play (GPnP)                                 gpnpd                                            grid owner
Multicast domain name service (mDNS)            mdnsd                                           grid owner

What is the major difference between 10g and 11g RAC? There is not much difference between 10g and 11gR (1) RAC. But there is a significant difference in 11gR2.
Prior to 11gR1(10g) RAC, the following were managed by Oracle CRS
     Databases
     Instances
     Applications
     Node Monitoring
     Event Services
     High Availability
From 11gR2(onwards) its completed HA stack managing and providing the following resources as like the other cluster software like VCS etc.
     Databases
     Instances
     Applications
     Cluster Management
     Node Management
     Event Services
     High Availability
     Network Management (provides DNS/GNS/MDNSD services on behalf of other traditional services) and SCAN – Single Access Client Naming method, HAIP
     Storage Management (with help of ASM and other new ACFS filesystem)
     Time synchronization (rather depending upon traditional NTP)
     Removed OS dependent hang checker etc, manages with own additional monitor process

What is hangcheck timer? The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.

State the initialization parameters that must have same value for every instance in an Oracle RAC database? Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE (RDBMS or ASM)
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_passWORD_FILE
UNDO_MANAGEMENT

What is RAC? What is the benefit of RAC over single instance database? In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
Benefits:
Improve response time
Improve throughput
High availability
Transparency

Advantages of RAC (Real Application Clusters)
Reliability - if one node fails, the database won't fail
Availability - nodes can be added or replaced without having to shutdown the database
Scalability - more nodes can be added to the cluster as the workload increases

What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.

What is the use of VIP? If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.
Give situations under which VIP address failover happens:-
VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.
Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.

What is the significance of VIP address failover? When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.

What is voting disk? Voting Disk is a file that sits in the shared storage area and must be accessible by all nodes in the cluster. All nodes in the cluster registers their heart-beat information in the voting disk, so as to confirm that they are all operational. If heart-beat information of any node in the voting disk is not available that node will be evicted from the cluster. The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat of all nodes to the voting disk. When any node is not able to send heartbeat to voting disk, then it will reboot itself, thus help avoiding the split-brain syndrome.
For high availability, Oracle recommends that you have a minimum of three or odd number (3 or greater) of votingdisks.
Voting Disk - is file that resides on shared storage and Manages cluster members. Voting disk reassigns cluster ownership between the nodes in case of failure.
The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently members of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity.
Oracle Database 11g Release 2 provides the ability to store the voting disks in ASM along with the OCR. Oracle Clusterware can access the OCR and the voting disks present in ASM even if the ASM instance is down. As a result CSS can continue to maintain the Oracle cluster even if the ASM instance has failed.

How many voting disks are you maintaining ?
By default Oracle will create 3 voting disk files in ASM.
Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.
You should plan on allocating 280MB for each voting disk file. For example, if you are using ASM and external redundancy then you will need to allocate 280MB of disk for the voting disk. If you are using ASM and normal redundancy you will need 560MB.

Why we need to keep odd number of voting disks ? Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.

What are Oracle RAC software components? Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor

What are Oracle Clusterware processes for 10g ? Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.
Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user
Event manager daemon (evmd) —A background process that publishes events that crs creates.
Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

What are Oracle database background processes specific to RAC? LMS—Global Cache Service Process
LMD—Global Enqueue Service Daemon
LMON—Global Enqueue Service Monitor
LCK0—Instance Enqueue Process
Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

What is Cache Fusion? Transfor of data across instances through private interconnect is called cachefusion.Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion

What is SCAN? (11gR2 feature) Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.
SCAN provides a single domain name via (DNS), allowing and-users to address a RAC cluster as-if it were a single IP address. SCAN works by replacing a hostname or IP list with virtual IP addresses (VIP).
Single client access name (SCAN) is meant to facilitate single name for all Oracle clients to connect to the cluster database, irrespective of number of nodes and node location. Until now, we have to keep adding multiple address records in all clients tnsnames.ora, when a new node gets added to or deleted from the cluster.
Single Client Access Name (SCAN) eliminates the need to change TNSNAMES entry when nodes are added to or removed from the Cluster. RAC instances register to SCAN listeners as remote listeners. Oracle recommends assigning 3 addresses to SCAN, which will create 3 SCAN listeners, though the cluster has got dozens of nodes.. SCAN is a domain name registered to at least one and up to three IP addresses, either in DNS (Domain Name Service) or GNS (Grid Naming Service). The SCAN must resolve to at least one address on the public network. For high availability and scalability, Oracle recommends configuring the SCAN to resolve to three addresses.

What are SCAN components in a cluster? 1.SCAN Name
2.SCAN IPs (3)
3.SCAN Listeners (3)

What is FAN? Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAc uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.

What is TAF? TAF (Transparent Application Failover) is a configuration that allows session fail-over between different nodes of a RAC database cluster.
Transparent Application Failover (TAF). If a communication link failure occurs after a connection is established, the connection fails over to another active node. Any disrupted transactions are rolled back, and session properties and server-side program variables are lost. In some cases, if the statement executing at the time of the failover is a Select statement, that statement may be automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.
After an Oracle RAC node crashes—usually from a hardware failure—all new application transactions are automatically rerouted to a specified backup node. The challenge in rerouting is to not lose transactions that were "in flight" at the exact moment of the crash. One of the requirements of continuous availability is the ability to restart in-flight application transactions, allowing a failed node to resume processing on another server without interruption. Oracle's answer to application failover is a new Oracle Net mechanism dubbed Transparent Application Failover. TAF allows the DBA to configure the type and method of failover for each Oracle Net client.
TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level.

What are the requirements for Oracle Clusterware?
1. External Shared Disk to store Oracle Cluster ware file (Voting Disk and Oracle Cluster Registry - OCR)
2. Two netwrok cards on each cluster ware node (and three set of IP address) -
Network Card 1 (with IP address set 1) for public network
Network Card 2 (with IP address set 2) for private network (for inter node communication between rac nodes used by clusterware and rac database)
IP address set 3 for Virtual IP (VIP) (used as Virtual IP address for client connection and for connection failover)
3. Storage Option for OCR and Voting Disk - RAW, OCFS2 (Oracle Cluster File System), NFS, …..

Which enable the load balancing of applications in RAC?Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.

How to find location of OCR file when CRS is down? If you need to find the location of OCR (Oracle Cluster Registry) but your CRS is down.
When the CRS is down:
Look into “ocr.loc” file, location of this file changes depending on the OS:
On Linux: /etc/oracle/ocr.loc
On Solaris: /var/opt/oracle/ocr.loc
When CRS is UP:
Set ASM environment or CRS environment then run the below command:
ocrcheck

In 2 node RAC, how many NIC’s are r using ?
2 network cards on each clusterware node
Network Card 1 (with IP address set 1) for public network
Network Card 2 (with IP address set 2) for private network (for inter node communication between rac nodes used by clusterware and rac database)

In 2 node RAC, how many IP’s are r using ?6 - 3 set of IP address
## eth1-Public: 2
## eth0-Private: 2
## VIP: 2

How to find IP’s information in RAC ?Edit the /etc/hosts file as shown below:
# Do not remove the following line, or various programs
# that requires network functionality will fail.
127.0.0.1               localhost.localdomain localhost
## Public Node names
192.168.10.11          node1-pub.example.com     node1-pub
192.168.10.22          node2-pub.example.com     node2-pub

## Private Network (Interconnect)
192.168.0.11            node1-prv               node1-prv
192.168.0.22            node2-prv               node2-prv

## Virtual IPs
192.168.10.111          node1-vip.example.com     node1-vip
192.168.10.222          node2-vip.example.com    node2-vip

What is difference between RAC ip addresses ?Public IP adress is the normal IP address typically used by DBA and SA to manage storage, system and database. Public IP addresses are reserved for the Internet.
Private IP address is used only for internal clustering processing (Cache Fusion) (aka as interconnect). Private IP addresses are reserved for private networks.
VIP is used by database applications to enable fail over when one cluster node fails. The purpose for having VIP is so client connection can be failover to surviving nodes in case there is failure

Can application developer access the private ip ?No. private IP address is used only for internal clustering processing (Cache Fusion) (aka as interconnect)

Where are the Clusterware files stored on a RAC environment? The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)

Where are the database software files stored on a RAC environment?
The base software is installed on each node of the cluster and the database storage on the shared disks.

What kind of storage we can use for the shared Clusterware files? - OCFS (Release 1 or 2)
- raw devices
- third party cluster file system such as GPFS or Veritas

What kind of storage we can use for the RAC database storage? - OCFS (Release 1 or 2)
- ASM
- raw devices
- third party cluster file system such as GPFS or Veritas

What is a CFS? A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.

What is an OCFS2? The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster.

Which files can be placed on an Oracle Cluster File System? - Oracle Software installation (Windows only)
- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
- Shared configuration files (spfile)
- OCR and voting disk
- Files created by Oracle during runtime
Note: There are some platform specific limitations.

Do you know another Cluster Vendor? HP Tru64 Unix, Veritas, Microsoft

How is possible to install a RAC if we don’t have a CFS? This is possible by using a raw device.

What is a raw device? A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

What is a raw partition? A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.

When to use CFS over raw? A CFS offers:
- Simpler management
- Use of Oracle Managed Files with RAC
- Single Oracle Software installation
- Autoextend enabled on Oracle datafiles
- Uniform accessibility to archive logs in case of physical node failure
- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster.
Note: This option is very dependent on the availability of a CFS on your platform.

When to use raw over CFS? - Always when CFS is not available or not supported by Oracle.
- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk.
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed.

What CRS is? Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.

What is VIP IP used for? It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.

Why we need to have configured SSH or RSH on the RAC nodes? SSH (Secure Shell,10g+) or RSH (Remote Shell, 9i+) allows “oracle” UNIX account connecting to another RAC node and copy/ run commands as the local “oracle” UNIX account.

Is the SSH, RSH needed for normal RAC operations? No. SSH or RSH are needed only for RAC, patch set installation and clustered database creation.

Do we have to have Oracle RDBMS on all nodes? Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk).

What are the restrictions on the SID with a RAC database? Is it limited to 5 characters? The SID prefix in 10g Release 1 and prior versions was restricted to five characters by install/ config tools so that an ORACLE_SID of up to max of 5+3=8 characters can be supported in a RAC environment. The SID prefix is relaxed up to 8 characters in 10g Release 2, see bug 4024251 for more information.

Does Real Application Clusters support heterogeneous platforms? The Real Application Clusters do not support heterogeneous platforms in the same cluster.

Are there any issues for the interconnect when sharing the same switch as the public network by using VLAN to separate the network? RAC and Clusterware deployment best practices suggests that the interconnect (private connection) be deployed on a stand-alone, physically separate, dedicated switch. On big network the connections could be instable.

What is the Load Balancing Advisory? To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag.

How many nodes are supported in a RAC Database? With 10g Release 2, we support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. Currently DBCA has a bug where it will not go beyond 63 instances. There is also a documentation bug for the max-instances parameter. With 10g Release 1 the Maximum is 63.

What is the Cluster Verification Utiltiy (cluvfy)? The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.

What versions of the database can I use the cluster verification utility (cluvfy) with? The cluster verification utility is release with Oracle Database 10g Release 2 but can also be used with Oracle Database 10g Release 1.

If I am using Vendor Clusterware such as Veritas, IBM, Sun or HP, do I still need Oracle Clusterware to run Oracle RAC 10g? Yes. When certified, you can use Vendor Clusterware however you must still install and use Oracle Clusterware for RAC. Best Practice is to leave Oracle Clusterware to manage RAC. For details see Metalink Note 332257.1 and for Veritas SFRAC see 397460.1.

Is RAC on VMWare supported? Yes.

What is hangcheck timer used for ? The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.

Is the hangcheck timer still needed with Oracle RAC 10g? Yes.

What files can I put on Linux OCFS2? For optimal performance, you should only put the following files on Linux OCFS2:
- Datafiles
- Control Files
- Redo Logs
- Archive Logs
- Shared Configuration File (OCR)
- Voting File
- SPFILE

Is it possible to use ASM for the OCR and voting disk? No, the OCR and voting disk must be on raw or CFS (cluster file system).

Can I change the name of my cluster after I have created it when I am using Oracle Clusterware? No, you must properly uninstall Oracle Clusterware and then re-install.

What the O2CB is? The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems).

What the OCR file is used for? OCR is a file that manages the cluster and RAC configuration.

What the Voting Disk file is used for? The voting disk is nothing but a file that contains and manages information of all the node memberships.

What is the recommended method to make backups of a RAC environment?                         RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.

What command would you use to check the availability of the RAC system? crs_stat -t -v (-t -v are optional)

What is the minimum number of instances you need to have in order to create a RAC? You can create a RAC with just one server.

Name two specific RAC background processes RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.

Can you have many database versions in the same RAC? Yes, but Clusterware version must be greater than the greater database version.

What was RAC previous name before it was called RAC?OPS: Oracle Parallel Server

What RAC component is used for communication between instances?Private Interconnect.

What is the difference between normal views and RAC views?A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION

Which command will we use to manage (stop, start) RAC services in command-line mode? srvctl

How many alert logs exist in a RAC environment? A- One for each instance.

What are Oracle Clusterware Components
Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.
Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

How do you backup voting disk
#dd if=voting_disk_name of=backup_file_name

How do I identify the voting disk location
#crsctl query css votedisk

How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

TRANSPARENT APPLICATION FAILOVER(TAF) WITH SCAN IN 11GR2

2013-12-28T13:23:00.001+05:30

TRANSPARENT APPLICATION FAILOVER WITH SCAN

Transparent Application Failover is process by which the sessions are automatically failover to the other available instance in the cluster database. New connection would be point to the available instance on the cluster database and if you have any active transaction and it will be rollback to the last committed transaction.

TRANSPARENT APPLICATION FAILOVER (TAF) FAILOVER METHODS
(METHOD = BASIC/PRECONNECT)

BASIC FAILOVER

The second connection to the available instance is made only if and when the primary instance is down or disconnected for some reason.

PRECONNECT FAILOVER

The Second connection to another available instance is made at the same time as the connection to the primary instance. Having the backup connection already in place can reduce the time needed for a failover in the event that one needs to take place. We have considered the Over Head while the sessions are pre-connected.

TRANSPARENT APPLICATION FAILOVER (TAF) FAILOVER TYPE
(TYPE=SESSION/SELECT/NONE)

SESSION
When the TYPE= Session specified, it will failover to the available instance in the cluster database and it doesn’t continue with selected, if it is in the process. OCI will start the select process again.SELECT
When the type=select specified, it will failover to the available instance in the cluster database and the failover process also replays the queries that is in the progress.
NONE
No failover function is used. When you specify it as none, no failover function will be used.

TAF can be configured at the Oracle Client side in tnsnames.ora or at the Database Server side using "srvctl"

SRVCTL SYNTAX FOR CREATING SERVICE

srvctl add service -d -s   -r "" [-a ""] [-P {BASIC | NONE | PRECONNECT}]
-g [-c {UNIFORM | SINGLETON}]
[-k ]
[-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]]
[-y {AUTOMATIC | MANUAL}]
[-q {TRUE|FALSE}]
[-x {TRUE|FALSE}]
[-j {SHORT|LONG}]
[-B {NONE|SERVICE_TIME|THROUGHPUT}]
[-e {NONE|SESSION|SELECT}]
        -- This defines the type of TAF whether SESSION or SELECT.
[-m {NONE|BASIC}]
        --This defines the method of TAF.
[-z ]
        -- the number of times to attempt to connect after a failover.
[-w ]
        -- the amount of time in seconds to wait between connect attempts.

SYNTAX

           srvctl add service -d -s
           -r "" [-a ""] [-P {BASIC | NONE | PRECONNECT}]
          [-e {NONE|SESSION|SELECT}]
          [-m {NONE|BASIC}]
          [-z ]
          [-w ]

srvctl modify service -d orcl -s orcldb -r node1,node2 -P basic -e select -m basic -z 10 -w 2

-d = database name
-s = service name we want to modify
-r = preferd node list where this service is running
-a = available nodes - this we haven't given
-P = is type which can be BASIC | NONE | PRECONNECT
-e = is type i.e SELECT or SESSION
-z = Failover restries
-w = Failover delay
-m = defines the method of TAF.
Reference MOS note -

11gR2(11.2) RAC TAF Configuration for Admin and Policy Managed Databases [ID 1312749.1]

PRECONNECT USING SCAN NAMES

When you are connecting using the scan names and having the failover method as PRE-CONNECT then you use should configured with the (preferred instance & available instance).

Ex:

orcl =
(description =
   (address = (protocol=tcp)(host=orclrac-scan)(port=1521))
   (connect_data=
     (service_name=orclrac)
     (failover_mode= (backup=orclrac_preconnect)
       (type=select) (method=preconnect)
     )
   ) )

orclrac_preconnect =
(description =
   (address = (protocol=tcp)(host=orclrac-scan)(port=1521))
   (connect_data=(service_name=orclrac_preconnect))
)

BASIC USING SCAN NAMES

SCAN provides load balancing and failover for client connections to the database. The SCAN works as a cluster alias for databases in the cluster.

orclrac =
(description =
   (address = (protocol=tcp)(host=orclrac-scan)(port=1521))
   (connect_data=(service_name=orclrac))
)

TRADITIONAL METHOD

JDBC connection

ORCL=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ora01)(Port=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=ora02)(PORT = 1521))
(CONNECT_DATA=(SERVICE_NAME=ORCLRAC)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))))

TNS ENTRY

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(Host=ora01)(Port=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=ora02)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORCLRAC)
(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))))

Start the service
$ srvctl start service -d -s

Stop the service
$ srvctl stop service -d -s

To Modify the service
$ srvctl modify service -d -s

To see the configuration of the service
$ srvctl config service -d -s

To see the Connection of the pre-connect session
select inst_id,username,service_name,failover_type, failover_method,failed_over from gv$session where username='ORCL';

To find the dba_segment for schema's in the database

2013-12-19T03:50:00.001+05:30

Oracle Administration: Query to find the SCHEMA Size in oracle database

ORA-00392: log string of thread string is being cleared, operation not allowed

2013-12-19T03:13:00.003+05:30

Today I encountered this issue while upgrading the database

Environments: Oracle 11g, ASM, RMAN backup dumps. During RMAN 10g database restoring into 11g database for upgrade, alter database open resetlogs or alter database open resetlogs upgrade command failed as below,

Cause:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/u404/oradata/DATA/log_3a.rdo'
ORA-00312: online log 3 thread 1: '/u405/oradata/DATA/log_3b.rdo'

SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/u404/oradata/DATA/log_3a.rdo'
ORA-00312: online log 3 thread 1: '/u405/oradata/DATA/log_3b.rdo'

Solution:
Clear the logs as below for error log groups,

SQL> alter database clear unarchived logfile group 3;
Database altered.

Now you can proceed with,

SQL> alter database open resetlogs;
or
SQL> alter database open resetlogs upgrade;

Oracle RAC Clusterware startup sequence 11gR2

2013-12-09T18:50:00.001+05:30

Clusterware startup sequence 11gR2

Don't let this picture scare you too much. You aren't responsible for managing all of these processes, that is the Clusterware's job!

Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon). This daemon spawns 4 processes.

Level 1: OHASD Spawns:

cssdagent - Agent responsible for spawning CSSD.
orarootagent - Agent responsible for managing all root owned ohasd resources.
oraagent - Agent responsible for managing all oracle owned ohasd resources.
cssdmonitor - Monitors CSSD and node health (along wth the cssdagent).

Level 2: OHASD rootagent spawns:

CRSD - Primary daemon responsible for managing cluster resources.
CTSSD - Cluster Time Synchronization Services Daemon
Diskmon
ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

MDNSD - Used for DNS lookup
GIPCD - Used for inter-process and inter-node communication
GPNPD - Grid Plug & Play Profile Daemon
EVMD - Event Monitor Daemon
ASM - Resource for monitoring ASM instances

Level 3: CRSD spawns:

orarootagent - Agent responsible for managing all root owned crsd resources.
oraagent - Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

Network resource - To monitor the public network
SCAN VIP(s) - Single Client Access Name Virtual IPs
Node VIPs - One per node
ACFS Registery - For mounting ASM Cluster File System
GNS VIP (optional) - VIP for GNS

Level 4: CRSD oraagent spawns:

ASM Resouce - ASM Instance(s) resource
Diskgroup - Used for managing/monitoring ASM diskgroups.
DB Resource - Used for monitoring and managing the DB and instances
SCAN Listener - Listener for single client access name, listening on SCAN VIP
Listener - Node listener listening on the Node VIP
Services - Used for monitoring and managing services
ONS - Oracle Notification Service
eONS - Enhanced Oracle Notification Service
GSD - For 9i backward compatibility
GNS (optional) - Grid Naming Service - Performs name resolution

This image shows the various levels more clearly:

OHASD Phase:-

OHASD (Oracle High Availability Server Daemon) starts Firsts and it will start

OHASD Agent Phase:-

OHASD Agent starts and in turn this will start

gipcd	Grid interprocess communication daemon, used for monitoring cluster interconnect
mdnsd	Multicast DNS service It resolves DNS requests on behalf of GNS
gns	The Grid Naming Service (GNS), a gateway between DNS and mdnsd, resolves DNS requests
gpnpd	Grid Plug and Play Daemon, Basically a profile similar like OCR contents stored in XML format in $GI_HOME/gpnp/profiles/ etc., this is where used by OCSSD also to read the ASM disk locations to start up with out having ASM to be up, moreover this also provides the plug and play profile where this can be distributed across nodes to cluster
evmd/ evmlogger	Evm service will be provided by evmd daemon, which is a information about events happening in cluster, stop node,start node, start instance etc.

cssdagent (cluster synchronization service agent), in turn starts

ocssd

Cluster synchronization service daemon which manages node membership in the cluster

If cssd found that ocssd is down, it will reboot the node to protect the data integrity.

cssdmonitor (cluster synchronization service monitor), replaces oprocd and provides I/O fencing

OHASD orarootagent starts and in turn starts

crsd.bin	Cluster ready services, which manages high availability of cluster resources , like stopping , starting, failing over etc.
diskmon.bin	disk monitor (diskdaemon monitor) provides I/O fencing for exadata storage
octssd.bin	Cluster synchronization time services , provides Network time protocol services but manages its own rather depending on OS

CRSD Agent Phase:- crsd.bin starts two more agents

crsd orarootagent(Oracle root agent) starts and in turn this will start

gns	Grid interprocess communication daemon, used for monitoring cluster interconnect
gns vip	Multicast DNS service It resolves DNS requests on behalf of GNS
Network	Monitor the additional networks to provide HAIP to cluster interconnects
Scan vip	Monitor the scan vip, if found fail or unreachable failed to other node
Node vip	Monitor the node vip, if found fail or unreachable failed to other node

crsd oraagent(Oracle Agent) starts and in turn it will start (the same functionality in 11gr1 and 10g managed by racgmain and racgimon background process) which is now managed by crs Oracle agent itself.

ASM & disk groups	Start & monitor local asm instance
ONS	FAN feature, provides notification to interested client
eONS	FAN feature, provides notification to interested client
SCAN Listener	Start & Monitor scan listener
Node Listener	Start & monitor the node listener (rdbms?)

catupgrd.sql fails With ORA-01722 Invalid Number after running the Pre-Upgrade Script

2013-12-06T19:57:00.004+05:30

catupgrd.sql fails while Database upgrade from 10.2.0.4 to 11.2.0.3.

Today I encountered the below error while running catupgrd.sql scripts for DB upgrade from 10204 to 11203.

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number

Cause:
------------

The Pre-Upgrade Script is not creating the registry$database table & inserting the Platform DST Patch Information.The error may also appear if the the registry$database table exists, but does not contain the required information.

Solution:
-----------------

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION

----------- ------------------------------ ------------------------------ ----------

6 AIX-Based Systems (64-bit)

SQL> create table registry$database_b as select * from registry$database;

Table created.

select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION
----------- -------------------- ------------------------------ ----------
13 Linux x86 64-bit

SQL> INSERT into registry$database
2 (platform_id, platform_name, edition, tz_version)
3 VALUES ((select platform_id from v$database),
4 (select platform_name from v$database),
5 NULL,
6 (select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION

----------- ------------------------------ ------------------------------ ----------

6 AIX-Based Systems (64-bit)

6 AIX-Based Systems (64-bit) 14

SQL> commit;

Commit complete.

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION

----------- ------------------------------ ------------------------------ ----------

6 AIX-Based Systems (64-bit) 14

Ref Doc: catupgrd.sql fails With ORA-01722 Invalid Number after running the Pre-Upgrade Script [Metalink ID 1466464.1]

Glossary - Definitions of RAC Components/Back ground Process.. 11g Release 2 (11.2)

2013-11-28T18:23:00.000+05:30

Glossary :

Automatic Workload Repository (AWR)

A built-in repository that exists in every Oracle database. At regular intervals, Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR.

administrator-managed database

A database that you specifically define on which servers it can run, and where services can run within the database.

cache coherency

The synchronization of data in multiple caches so that reading a memory location through any cache will return the most recent data written to that location through any other cache. Sometimes called cache consistency.

Cache Fusion

A diskless cache coherency mechanism in Oracle RAC that provides copies of blocks directly from a holding instance's memory cache to a requesting instance's memory cache.

cardinality

The number of database instances you want running during normal operations.

cluster

Multiple interconnected computers or servers that appear as if they are one server to end users and applications.

cluster file system

A distributed file system that is a cluster of servers that collaborate to provide high performance service to their clients. Cluster file system software deals with distributing requests to storage cluster components.

cluster database

The generic term for a Oracle RAC database.

Cluster Ready Services Daemon (CRSD)

The primary Oracle Clusterware process that performs high availability recovery and management operations, such as maintaining OCR. Also manages application resources and runs as root user (or by a user in the admin group on Mac OS X-based systems) and restarts automatically upon failure.

Cluster Synchronization Services (CSS)

An Oracle Clusterware component that discovers and tracks the membership state of each node by providing a common view of membership across the cluster. CSS also monitors process health, specifically the health of the database instance. The Global Enqueue Service Monitor (LMON), a background process that monitors the health of the cluster database environment and registers and de-registers from CSS. See also, OCSSD.

Cluster Time Synchronization Service

A time synchronization mechanism that ensures that all internal clocks of all nodes in a cluster are synchronized.

Cluster Verification Utility (CVU)

A tool that verifies a wide range of Oracle RAC components such as shared storage devices, networking configurations, system requirements, Oracle Clusterware, groups, and users.

Distributed Transaction Processing (DTP)

The paradigm of distributed transactions, including both XA-type externally coordinated transactions, and distributed-SQL-type (database links in Oracle) internally coordinated transactions.

Event Manager (EVM)

The background process that publishes Oracle Clusterware events. EVM scans the designated callout directory and runs all scripts in that directory when an event occurs.

Event Manager Daemon (EVMD)

A Linux or UNIX event manager daemon that starts the racgevt process to manage callouts.

extended distance cluster

A cluster where the nodes in the cluster are separated by greater distances from two buildings across the street, to across a campus or across a city. For availability reasons, the data needs to be located at both sites, and therefore one needs to look at alternatives for mirroring the storage.

failure group

A failure group is a subset of the disks in a disk group, which could fail at the same time because they share hardware. Failure groups are used to store mirror copies of data.

Fast Application Notification (FAN)

Applications can use FAN to enable rapid failure detection, balancing of connection pools after failures, and re-balancing of connection pools when failed components are repaired. The FAN notification process uses system events that Oracle Database publishes when cluster servers become unreachable or if network interfaces fail.

Fast Connection Failover

Fast Connection Failover provides high availability to FAN integrated clients, such as clients that use JDBC, OCI, or ODP.NET. If you configure the client to use fast connection failover, then the client automatically subscribes to FAN events and can react to database UP and DOWN events. In response, Oracle Database gives the client a connection to an active instance that provides the requested database service.

forced disk write

In Oracle RAC, a particular data block can only be modified by one instance at a time. If one instance modifies a data block that another instance needs, then whether a forced disk write is required depends on the type of request submitted for the block.

General Parallel File System (GPFS)

General Parallel File System (GPFS) is a shared-disk IBM file system product that provides data access from all of the nodes in a homogenous or heterogeneous cluster.

Global Cache Service (GCS)

Process that implement Cache Fusion. It maintains the block mode for blocks in the global role. It is responsible for block transfers between instances. The Global Cache Service employs various background processes such as the Global Cache Service Processes (LMSn) and Global Enqueue Service Daemon (LMD).

Global Cache Service Processes (LMSn)

Processes that manage remote messages. Oracle RAC provides for up to 10 Global Cache Service Processes.

Global Cache Service (GCS) resources

Global resources that coordinate access to data blocks in the buffer caches of multiple Oracle RAC instances to provide cache coherency.

global database name

The full name of the database that uniquely identifies it from any other database. The global database name is of the form database_name.database_domain—for example: OP.US.FOO.COM

global dynamic performance views (GV$)

Dynamic performance views storing information about all open instances in an Oracle RAC cluster. (Not only the local instance.) In contrast, standard dynamic performance views (V$) only store information about the local instance.

Global Enqueue Service (GES)

A service that coordinates enqueues that are shared globally.

Global Enqueue Service Daemon (LMD)

The resource agent process that manages requests for resources to control access to blocks. The LMD process also handles deadlock detection and remote resource requests. Remote resource requests are requests

originating from another instance.

Global Enqueue Service Monitor (LMON)

The background LMON process monitors the entire cluster to manage global resources. LMON manages instance deaths and the associated recovery for any failed instance. In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as Cluster Group Services.

Global Services Daemon (GSD)

A component that receives requests from SRVCTL to execute administrative job tasks, such as startup or shutdown. The command is executed locally on each node, and the results are returned to SRVCTL. GSD is installed on the nodes by default.

Oracle Grid Infrastructure

The software that provides the infrastructure for an enterprise grid architecture. In a cluster this software includes Oracle Clusterware and Oracle Automatic Storage Management (Oracle ASM). For a standalone server, this software includes Oracle Restart and Oracle ASM. Oracle Database 11g release 2 (11.2) combines these infrastructure products into one software installation called the Oracle Grid Infrastructure home (Grid_home).

Grid Plug and Play Daemon (GPNPD

This process 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 node have the most recent profile.

High Availability Cluster Multi-Processing (HACMP)

High Availability Cluster Multi-Processing is an IBM AIX-based high availability cluster software product. HACMP has two major components: high availability (HA) and cluster multi-processing (CMP).

high availability

Systems with redundant components that provide consistent and uninterrupted service, even in the event of hardware or software failures. This involves some degree of redundancy.

instance

For an Oracle RAC database, each node in a cluster usually has one instance of the running Oracle software that references the database. When a database is started, Oracle Database allocates a memory area called the System Global Area (SGA) and starts one or more Oracle Database processes. This combination of the SGA and the Oracle Database processes is called an instance. Each instance has unique Oracle System Identifier (SID), instance name, rollback segments, and thread ID.

instance membership recovery

The method used by Oracle RAC guaranteeing that all cluster members are functional or active. instance membership recovery polls and arbitrates the membership. Any members that do not show a heartbeat by way of the control file or who do not respond to periodic activity inquiry messages are presumed terminated.

instance name

Represents the name of the instance and is used to uniquely identify a specific instance when clusters share common services names. The instance name is identified by the INSTANCE_NAME parameter in the instance initialization file, initsid.ora. The instance name is the same as the Oracle System Identifier (SID).

instance number

A number that associates extents of data blocks with particular instances. The instance number enables you to start up an instance and ensure that it uses the extents allocated to it for inserts and updates. This will ensure that it does not use space allocated for other instances.

interconnect

The communication link between nodes.

Logical Volume Manager (LVM)

A generic term that describes Linux or UNIX subsystems for online disk storage management.

Interprocess Communication (IPC)

A high-speed operating system-dependent transport component. The IPC transfers messages between instances on different nodes. Also referred to as the interconnect.

Master Boot Record (MBR)

A program that executes when a computer starts. Typically, the MBR resides on the first sector of a local hard disk. The program begins the startup process by examining the partition table to determine which partition to use for starting the system. The MBR program then transfers control to the boot sector of the startup partition, which continues the startup process.

metric

The rate of change in a cumulative statistic.

Network Attached Storage (NAS)

Storage that is attached to a server by way of a network.

Network Time Protocol (NTP)

An Internet standard protocol, built on top of TCP/IP, that ensures the accurate synchronization to the millisecond of the computer clock times in a network of computers.

Network Interface Card (NIC)

A card that you insert into a computer to connect the computer to a network.

node

A node is a computer system on which Oracle RAC and Oracle Clusterware software are installed.

Object Link Manager (OLM)

The Oracle interface that maps symbolic links to logical drives and displays them in the OLM graphical user interface.

OCSSD

A Linux or UNIX process that manages the Cluster Synchronization Services (CSS) daemon. Manages cluster node membership and runs as oracle user; failure of this process results in cluster restart.

Oracle Cluster File Systems

Oracle offers two cluster file systems, OCFS for Windows and OCFS2 for Linux. While OCFS for Windows is a proprietary file system, the source for OCFS2 for Linux is available to all under GNUs' General Public License (GPL). The two file systems are not compatible.

Oracle Cluster Registry (OCR)

The Oracle RAC configuration information repository that manages information about the cluster node list and instance-to-node mapping information. OCR also manages information about Oracle Clusterware resource profiles for customized applications.

Oracle Enterprise Manager Configuration Assistant (EMCA)

A graphical user interface-based configuration assistant that you can use to configure Oracle Enterprise Manager features.

Oracle Grid Naming Service Daemon (GNSD)

The Oracle Grid Naming Service is a gateway between the cluster mDNS and external DNS servers. The gnsd process performs name resolution within the cluster.

Oracle High Availability Services Daemon (OHASD)

This process anchors the lower part of the Oracle Clusterware stack, which consists of processes that facilitate cluster operations.

Oracle Interface Configuration Tool (OIFCFG)

A command-line tool for both noncluster Oracle databases and Oracle RAC databases that enables you to allocate and de-allocate network interfaces to components, direct components to use specific network interfaces, and retrieve component configuration information. The Oracle Universal Installer also uses OIFCFG to identify and display available interfaces.

Oracle Managed Files

A service that automates naming, location, creation, and deletion of database files such as control files, redo log files, data files and others, based on a few initialization parameters. You can use Oracle Managed Files on top of a traditional file system supported by the host operating system, for example, VxFS or ODM. It can simplify many aspects of the database administration by eliminating the need to devise your own policies for such details.

Oracle Notification Service

A publish and subscribe service for communicating information about all FAN events.

Oracle Clusterware

This is clusterware that is provided by Oracle to manage cluster database processing including node membership, group services, global resource management, and high availability functions.

Oracle Universal Installer

A tool to install Oracle Clusterware, the Oracle relational database software, and the Oracle RAC software. You can also use the Oracle Universal Installer to launch the Database Configuration Assistant (DBCA).

policy-managed database

A database that you define as a cluster resource. Management of the database is defined by how you configure the resource, including on which servers the database can run and how many instances of the database are necessary to support the expected workload.

raw device

A disk drive that does not yet have a file system set up. Raw devices are used for Oracle RAC because they enable the sharing of disks.

raw partition

A portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called a cooked partition. See also raw device.

Recovery Manager (RMAN)

An Oracle tool that enables you to back up, copy, restore, and recover data files, control files, and archived redo logs. It is included with the Oracle server and does not require separate installation. You can run RMAN as a command line utility from the operating system (O/S) prompt or use the GUI-based Oracle Enterprise Manager Backup Manager.

result cache

A result cache is an area of memory, either in the SGA or client application memory, that stores the result of a database query or query block for reuse. The cached rows are shared across statements and sessions unless they become stale.

Runtime Connection Load Balancing

Enables Oracle Database to make intelligent service connection decisions based on the connection pool that provides the optimal service for the requested application based on current workloads. The JDBC, ODP.NET, and OCI clients are integrated with the load balancing advisory; you can use any of these client environments to provide runtime connection load balancing.

scalability

The ability to add additional nodes to Oracle RAC applications and achieve markedly improved scale-up and speed-up.

Secure Shell (SSH)

A program for logging into a remote computer over a network. You can use SSH to execute commands on a remote system and to move files from one system to another. SSH uses strong authentication and secure communications over insecure channels.

Server Control Utility (SRVCTL)

Server Management (SRVM) comprises the components required to operate Oracle Enterprise Manager in Oracle RAC. The SRVM components, such as the Intelligent Agent, Global Services Daemon, and SRVCTL, enable you to manage cluster databases running in heterogeneous environments through an open client/server architecture using Oracle Enterprise Manager.

server

A computer system that has no Oracle software installed upon it.

server group

A logical partition of nodes in a cluster into a group that hosts applications, databases, or both. Server groups can be members of other server groups.

service level

A measure of the performance of a system.

services

Entities that you can define in Oracle RAC databases that enable you to group database workloads and route work to the optimal instances that are assigned to offer the service.

shared everything

A database architecture in which all instances share access to all of the data.

single client access name (SCAN)

Oracle Database 11g database clients use SCAN to connect to the database. SCAN can resolve to multiple IP addresses, reflecting multiple listeners in the cluster handling public client connections.

singleton services

Services that run on only one instance at any one time. By defining the Distributed Transaction Property (DTP) property of a service, you can force the service to be a singleton service.

split brain syndrome

Where two or more instances attempt to control a cluster database. In a two-node environment, for example, one instance attempts to manage updates simultaneously while the other instance attempts to manage updates.

system identifier (SID)

The Oracle system identifier (SID) identifies a specific instance of the running Oracle software. For an Oracle RAC database, each node within the cluster has an instance referencing the database.

transparent application failover (TAF)

A runtime failover for high-availability environments, such as Oracle RAC and Oracle RAC Guard, TAF refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and optionally resume a SELECT statement that was in progress. This reconnect happens automatically from within the Oracle Call Interface library.

voting disk

A file that manages information about node membership.

Wallet

A wallet is a data structure used to store and manage security credentials for an individual entity.

PRVF-5439: NTP daemon does not have slewing option “-x” set on node

2013-11-25T15:15:00.001+05:30

PRVF-5439: NTP daemon does not have slewing option “-x” set on node

When installing Oracle 11gR2 Grid Infrastructure either the cluster verify utility or the prerequisite checks find that the NTP daemon does not have the slewing option set.

- PRVF-5439 : NTP daemon does not slewing option "-x" set

  on node "ordrac1"

  - Cause: NTP daemon on the specified node does not have the 

  slewing option set

  - Action: Shutdown and restart the NTP daemon with the

  slewing option set. For more information on the NTP daemon

  slewing option refer the NTP daemon's man pages.

What is slewing?

The NTP daemon will periodically update the system clock with the time from a reference clock. If the time on the reference clock is behind the time on the system clock, the system clock will be set backwards in one large decrement. Such swift changes in time can lead to Oracle shutting down the node due to inconsistent timers. To avoid this problem, NTP can be configured to slew the clock. When slewing the clock the time on system is incremented slower until the system clock is in sync with the time on the reference system.

How to set up slewing

Stop the NTP service on the node.

[root@odrac1 ~]# service ntpd stop

Shutting down ntpd:                                        [  OK  ]

[root@odrac1 ~]#

[/text/]

Edit the file /etc/sysconfig/ntpd

1

# Drop root to id 'ntp:ntp' by default.

OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"

# Set to 'yes' to sync hw clock after successful ntpdate

SYNC_HWCLOCK=no

# Additional options for ntpdate

NTPDATE_OPTIONS=""

Change the line

OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"  

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

After saving the file restart the NTP service. Re-execute the cluster verify utility or the prerequisite checks.

slewing in clusters Linux:

In order to keep the system time synchronized with other nodes in an HACMP cluster or across the enterprise, Network Time Protocol (NTP) should be implemented. In its default configuration, NTP will periodically update the system time to match a reference clock by resetting the system time on the node. If the time on the reference clock is behind the time of the system clock, the system clock will be set backwards causing the same time period to be passed twice. This can cause internal timers in HACMP and Oracle databases to wait longer periods of time under some circumstances. When these circumstances arise, HACMP may stop the node or the Oracle instance may shut itself down.

Oracle will log an ORA-29740 error when it shuts down the instance due to inconsistent timers. The hatsd daemon utilized by HACMP will log a TS_THREAD_STUCK_ER error in the system error log just before HACMP stops a node due to an expired timer.

To avoid this issue, system managers should configure the NTP daemon to increment time on the node slower until the system clock and the reference clock are in sync (this is called "slewing" the clock) instead of resetting the time in one large increment. The behavior is configured with the -x flag for the xntpd daemon.

To check the current running configuration of xntpd for the -x flag:

#ps -aef | grep xntpd | grep -v grep

root 9306258 3866670 0 Nov 12 - 0:17 /usr/sbin/xntpd

To change with slewing options

[:root:/home/root:] chssys -s xntpd -a "-x"
0513-077 Subsystem has been changed.

To stop ntpd service/deamon:

[:root:/home/root:] stopsrc -s xntpd
0513-044 The /usr/sbin/xntpd Subsystem was requested to stop.

To start ntpd service/deamon:

[:root:/home/root:] startsrc -s xntpd
0513-059 The xntpd Subsystem has been started. Subsystem PID is 13041866.

[:root:/home/root:] ps -ef|grep ntpd
root 13041866 3801266 0 09:36:05 - 0:00 /usr/sbin/xntpd -x

Oracle database 11.2.0.3 installation on Linux/Unix Error: PRVF-5300, PRKC-1032, PRCT-1406

2013-10-21T16:05:00.000+05:30

while installation Oracle database 11.2.0.3 you may encountered this kind of error: PRVF-5300, PRKC-1032, PRCT-1406

An internal error occurred within cluster verification framework

ERRORMSG(DBSERVER): PRVF-5300: failed to retrieve active version for CRS on this node

PRKC-1032: Directory /fs01/oracle/product/11.2.0/gird does not exist

PRCT-1406: Oracle Home location: /fs01/oracle/product/11.2.0/gird does not contain bin/srvctl

Oracle Support has a note on this:

Installation of Standalone (Non-RAC) 11.2.0 Database fails with errors: PRVF-5300, PRKC-1033, PRCT-1406 [ID 1380126.1]

Symptoms

When installing standalone (non-RAC) Database 11.2.0 software, it's failing with errors similar to:

oracle.cluster.verification.VerificationException: An internal error occurred within cluster verification framework

PRVF-5300 : Failed to retrieve active version for CRS on this node

PRKC-1033 : Executable /u01/app/oracle/product/11.2.0/grid/bin/olsnodes does not exist

PRCT-1406 : Oracle Home location: /u01/app/oracle/product/11.2.0/grid does not contain bin/srvctl

Changes

11gR2 Grid Infrastructure was installed on the server at some point in the past, but was later removed.

Cause

11gR2 Grid Infrastructure was not completely removed / de-installed, resulting in the existence of one or both of the following Grid Infrastructure files:

/etc/ocr.loc

/etc/oracle/olr.loc

Solution

Because the Grid Infrastructure files /etc/ocr.loc and/or /etc/oracle/olr.loc exist, the installer assumes it to be a RAC installation and tries to check the active CRS Services on the Server. Those checks will fail because Clusterware (Grid Infrastructure) is no longer installed.

To resolve this situation:

1. Backup the files /etc/ocr.loc and /etc/oracle/olr.loc, then rename / delete them.

2. Retry the standalone (non-RAC) Database 11.2.0 installation.

Performing the switchover in 11gr2 datagurad steps

2013-10-01T19:04:00.003+05:30

Performing the switchover in 11gr2 datagurad steps

1) There are few steps we need to check before performing the switchover in dataguard environment
a)If we are using the OEM Enable a blackout for primary database in enterprise manager.
b)check the crontab jobs for Primary database need to disabled.
c)Check for executing backups/exports and cancel.
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

2)Verify that it is possible to perform a switchover operation On the primary database
On the primary database the switchover_status column of v$database check which is possible to perform a switchover operation

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO STANDBY
-In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.
SWITCHOVER_STATUS
-------------------------------
SESSIONS ACTIVE
-Then you should either disconnect all sessions manually (or) when performing step 3 you should append the “with session shutdown” clause.

For example:
SQL> alter database commit to switchover to standby with session shutdown;
(Note: that the clause "with session shutdown" also works with the switchover to primary command
Ex: alter database commit to switchover to primary with session shutdown;)

SWITCHOVER_STATUS
-----------------------------
NOT ALLOWED
-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.

SWITCHOVER_STATUS
-----------------------------
SESSIONS ACTIVE -
-Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER_STATUS
-----------------------------
SWITCHOVER PENDING -
-This is a standby database and the primary database switchover request has been received but not processed.

SWITCHOVER_STATUS
-----------------------------
SWITCHOVER LATENT -
-The switchover was in pending mode, but did not complete and went back to the primary database.

SWITCHOVER_STATUS
-----------------------------
TO PRIMARY
- This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

SWITCHOVER_STATUS
-----------------------------
TO STANDBY
- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

SWITCHOVER_STATUS
-----------------------------
RECOVERY NEEDED
- This is a standby database that has not received the switchover request.

During normal operations it is acceptable to see the following values for

SWITCHOVER_STATUS
-----------------------------
SESSIONS ACTIVE (or) TO STANDBY. ( on the primary database)
During normal operations it is acceptable to see these values

SWITCHOVER_STATUS
-----------------------------
NOT ALLOWED (or) SESSIONS ACTIVE. (on the standby database)
-During normal operations it is acceptable to see these values

3. Convert the primary database to the new standby:
SQL> alter database commit to switchover to physical standby ;
Database altered.

4. Shutdown the former primary and mount as a standby database:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 528484544 bytes
Database Buffers 285212672 bytes
Redo Buffers 2486272 bytes

SQL> alter database mount standby database;
Database altered.

5. Defer the remote archive destination on the old primary:
SQL> alter system set log_archive_dest_state_3=defer;

6. Verify that the physical standby can be converted to the new primary:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING
(Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 7.

7. Convert the physical standby to the new primary:
SQL> alter database commit to switchover to primary;
Database altered.

8. Shutdown and startup the new primary:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 482347200 bytes
Database Buffers 331350016 bytes
Redo Buffers 2486272 bytes
Database mounted.
Database opened.

9. Enable remote archiving on the new primary to the new standby:
SQL> alter system set log_archive_dest_state_2=enable;

10. Start managed recover on the new standby database:
SQL> recover managed standby database disconnect;
Media recovery complete.

11) There are few steps we need to check after performing the switchover in dataguard environment
a)If we are using the OEM check the status of primary database in enterprise manager.
b)check the crontab jobs for Primary database need to enabled
c)Check for executing backups/exports and start as per schedule
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

Perform Failover Operation to a Physical Standby

Use the following steps to perform a failover to a physical standby database.

Identify and resolve any archive log gaps that may exist on the target standby database.

Query the view V$ARCHIVE_GAP on the target standby database to determine if there are gaps in the archive sequence. This view contains the sequence numbers of any archived redo logs that are known to be missing for each thread. The data returned reflects the lowest and highest known gap in the archive sequence.

SQL> select * from v$archive_gap;

no rows selected
If the above query against V$ARCHIVE_GAP returns no rows, there are no known archive log gaps.

If the above query against V$ARCHIVE_GAP returns a record, it will display the sequence numbers of the archived redo logs known to be missing from the standby database as illustrated in the following example:

SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 854 859
From the output above, the physical standby database is currently missing archived redo logs from sequence 854 to sequence 859 for thread 1. If possible locate and copy all of the identified missing archived redo logs to the target standby database from either the primary database (if available) or from other standbys. If working in a RAC environment, this would need to include missing archived redo logs from all threads. Once copied over, the missing archived redo logs will need to be registered with the target standby database:

SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_854_6jmc5ngd_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_855_6jmc5pht_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_856_6jmc5t4t_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_857_6jmc6jq9_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_858_6jmc7z5o_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/PRODDB/archive/2010_12_16/o1_mf_1_859_6jmcb9m0_.arc';

Database altered.
Copy and register any other missing archived redo logs. Query the V$ARCHIVED_LOG view on all other available standby databases in the configuration (if any others exist) to obtain the highest log sequence number for each thread.

SQL> select unique thread# as "Thread", max(sequence#)
2 over (partition by thread#) as "Last" from v$archived_log;

Thread Last
---------- ----------
1 900
If you find any archived redo logs that contain sequence numbers higher than the highest sequence number available on the target standby database, copy and register them to the target standby database. If the standby database is in managed recovery mode (and why wouldn't it be), the newly registered archived redo logs will be automatically applied to the standby. If the standby database is not in managed recovery mode, then manually recover the newly registered archived redo logs. This must be done for all threads if you are in a RAC environment.

SQL> alter database register logfile '';

About Partial Archived Redo Logs

It is possible to copy over and register what is known as a partial archived redo log file. A partial archived redo log file contains all of the primary database redo data received by the standby database when the primary database fails, but the archived redo log is not automatically registered in the standby database.

When you register a partial archived redo log, it prevents the recovery of the standby redo logs (if they exist). Therefore, whether or not you have registered a partial archived redo log determines which failover command will be necessary to run (next step).

You will know if you registered a partial archived redo log if you get receive the following message when attempting to register the archived redo log:

Register archivelog 'filespec1' was created due to a network disconnect;
archivelog contents are valid but missing subsequent data
Perform terminal recovery on the target standby by issuing managed recovery mode with the FINISH keyword. If the physical standby database is configured with active standby redo logs AND you have not registered any partial archived redo log files, issue the following command to initiate the failover operation:

SQL> alter database recover managed standby database finish;

Database altered.
If the standby database was not configured with standby redo logs, or they are not active, you must enter the following command instead:

SQL> alter database recover managed standby database finish skip standby logfile;

Database altered.
The above SQL statement performs incomplete recovery until the last SCN included in the latest archived redo log available at the physical standby database.

Once the terminal recovery command completes, convert the physical standby into a primary database using the following command:

SQL> alter database commit to switchover to primary;

Database altered.
After the "COMMIT TO SWITCHOVER" command completes, you can no longer use this database as a standby database. Also, subsequent redo logs from the original primary database cannot be applied.

The above SQL statement will only succeed if the correct "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE ... FINISH;" statement was issued in the previous step. If you forgot the "... SKIP STANDBY LOGFILE" clause although you have no standby redo log files, the "COMMIT TO SWITCHOVER" will fail with the error that more media recovery is required.

If the "COMMIT TO SWITCHOVER" fails for any reason, you have to use the "ACTIVATE STANDBY DATABASE" SQL statement which forces the failover operation:

SQL> alter database activate standby database;
To complete the failover operation, restart the new primary database in read/write mode using the appropriate initialization parameter file (or SPFILE) for the primary role:

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size 1273420 bytes
Variable Size 318767540 bytes
Database Buffers 905969664 bytes
Redo Buffers 15503360 bytes
Database mounted.
Database opened.
Perform a full backup of the new primary database!

Keep in mind that if this was the only standby database in the Data Guard configuration, then the database environment will be running unprotected until a new standby database can be setup and synchronized with the current primary. If Flashback Database was enabled on the original primary database before the failover and the original primary database is still available after the original primary machine comes back online,

CRS-0215: Could not start resource 'ora.sample.sample1.inst'

2013-09-11T13:52:00.002+05:30

PRKP-1001 : Error starting instance sample1 on node server01
CRS-0215: Could not start resource 'ora.sample.sample1.inst'.

$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.sample.db application OFFLINE OFFLINE
ora....e1.inst application ONLINE UNKNOWN server01
ora....e2.inst application ONLINE UNKNOWN server02
ora....02.lsnr application ONLINE ONLINE server01
ora....c02.gsd application ONLINE ONLINE server01
ora....c02.ons application ONLINE ONLINE server01
ora....c02.vip application ONLINE ONLINE server01
ora....04.lsnr application ONLINE ONLINE server02
ora....c04.gsd application ONLINE ONLINE server02
ora....c04.ons application ONLINE ONLINE server02
ora....c04.vip application ONLINE ONLINE server02

SRVCTL has problems while starting the database (SRVCTL STOP DATABASE works ok).
Also, the database can be started and stopped fine using SQL*Plus. No problems there.

I encountered this issue in my cluster. May be this also one of the reason.

$ ./srvctl start database -d sample
PRKP-1001 : Error starting instance sample1 on node server01
CRS-0215: Could not start resource 'ora.sample.sample1.inst'.
PRKP-1001 : Error starting instance sample2 on node server02
CRS-0215: Could not start resource 'ora.sample.sample2.inst'.

check the logs
/u001/app/oracle/product/10.2.0/db_1/log/server01/racg
view imonsample.log

Oracle Database 10g RACG Release 10.2.0.5.0 Production Copyright 1996, 2005, Oracle. All rights reserved.
2013-09-10 20:08:47 : Could not make directory /u001/app/oracle/product/10.2.0/db_1/racg/init/server01/ reason: Permission denied
Oracle Database 10g RACG Release 10.2.0.5.0 Production Copyright 1996, 2005, Oracle. All rights reserved.
2013-09-10 20:08:59 : Could not make directory /u001/app/oracle/product/10.2.0/db_1/racg/init/server01/ reason: Permission denied
Oracle Database 10g RACG Release 10.2.0.5.0 Production Copyright 1996, 2005, Oracle. All rights reserved.
2013-09-10 20:09:47 : Could not make directory /u001/app/oracle/product/10.2.0/db_1/racg/init/server01/ reason: Permission denied
Oracle Database 10g RACG Release 10.2.0.5.0 Production Copyright 1996, 2005, Oracle. All rights reserved.
2013-09-10 20:10:52 : Could not make directory /u001/app/oracle/product/10.2.0/db_1/racg/init/server01/ reason: Permission denied

now check the permission for the required directory.
root@server01 # cd /u001/app/oracle/product/10.2.0/db_1/racg

root@server01 # ls -tlr
total 12
drwxr-x--- 2 ora102 oinstall 1024 Sep 10 09:32 mesg
drwxr-x--- 2 ora102 oinstall 512 Sep 10 09:34 log
drwxr-x--- 2 ora102 oinstall 512 Sep 10 09:34 tmp
drwxr-x--- 2 ora102 oinstall 512 Sep 10 09:34 dump
drwxr-x--- 2 ora102 oinstall 512 Sep 10 13:47 lib
drwxr----- 3 root root 512 Sep 10 15:47 init

if the init directory owned by root user,
change into oracle user.
root@server01 # chown -R ora102:oinstall init
root@server01 # ls -ltr
total 12
drwxr-x--- 2 ora102 oinstall 1024 Sep 10 09:32 mesg
drwxr-x--- 2 ora102 oinstall 512 Sep 10 09:34 log
drwxr-x--- 2 ora102 oinstall 512 Sep 10 09:34 tmp
drwxr-x--- 2 ora102 oinstall 512 Sep 10 09:34 dump
drwxr-x--- 2 ora102 oinstall 512 Sep 10 13:47 lib
drwxr----- 3 ora102 oinstall 512 Sep 10 15:47 init

$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.sample.db application ONLINE ONLINE server02
ora....e1.inst application ONLINE ONLINE server01
ora....e2.inst application ONLINE ONLINE server02
ora....02.lsnr application ONLINE ONLINE server01
ora....c02.gsd application ONLINE ONLINE server01
ora....c02.ons application ONLINE ONLINE server01
ora....c02.vip application ONLINE ONLINE server01
ora....04.lsnr application ONLINE ONLINE server02
ora....c04.gsd application ONLINE ONLINE server02
ora....c04.ons application ONLINE ONLINE server02
ora....c04.vip application ONLINE ONLINE server02

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet.

2013-08-30T12:23:00.001+05:30

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet.

oracle_orcl$ ./emctl upload agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

---------------------------------------------------------------

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

The first thing that should be done is checking the log file. Switch to the following directory and tail the log file:

cd /agent_home/sysman/log

tail -f emagent.trc

Here’s the output from the log file:

2013-08-30 01:20:31,892 Thread-1092000352 ERROR pingManager: nmepm_pingReposURL: Error in request response. code = 400. text =

2013-08-30 01:21:25,238 Thread-1092000352 ERROR pingManager: nmepm_pingReposURL: Did not receive a response header from repository

2013-08-30 01:21:45,434 Thread-1092000352 ERROR pingManager: nmepm_pingReposURL: Did not receive a response header from repository

2013-08-30 01:22:39,723 Thread-1092000352 ERROR pingManager: nmepm_pingReposURL: Did not receive a response header from repository

I checked the status of the agent and saw that it’s running :

oracle_orcl$ ./emctl status agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

---------------------------------------------------------------

Agent Version : 10.2.0.4.3

OMS Version : 10.2.0.4.3

Protocol Version : 10.2.0.4.3

Agent Home : /u001/app/oracle/product/11.2.0/db_1/ORCL

Agent binaries : /u001/app/oracle/product/11.2.0/db_1

Agent Process ID : 4797

Parent Process ID : 4636

Agent URL : https://hostname:3938/emd/main

Repository URL : https://hostname:1158/em/upload/

Started at : 2013-08-29 10:44:35

Started by user : oracle

Last Reload : 2013-08-29 11:07:39

Last successful upload : (none)

Last attempted upload : (none)

Total Megabytes of XML files uploaded so far : 0.00

Number of XML files pending upload : 367

Size of XML files pending upload(MB) : 10.54

Available disk space on upload filesystem : 21.50%

Data channel upload directory : /u001/app/oracle/product/11.2.0/db_1/ORCL/sysman/recv

Last attempted heartbeat to OMS : 2013-08-30 02:39:27

Last successful heartbeat to OMS : unknown

---------------------------------------------------------------

Agent is Running and Ready

Then I decided to clear unnecessary files that can’t be uploaded to the Grid Control under the following directories:

$AGENT_HOME/sysman/emd/collection

$AGENT_HOME/sysman/emd/state

$AGENT_HOME/sysman/emd/recv

$AGENT_HOME/sysman/emd/upload

$AGENT_HOME/sysman/emd/lastupld.xml

[oracle_orcl bin]$ ./emctl stop agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

Stopping agent …. stopped.

[oracle_orcl bin]$ ./emctl clearstate agent (this should delete all state files but sometimes it is not enough, but I experienced that it is better to use this command after deletion of the files)

[oracle_orcl bin]$ ./emctl clearstate agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

EMD clearstate completed successfully

[oracle_orcl bin]$ ./emctl secure agent (and specify the password on demand)

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

Enter Agent Registration password :

Agent is already stopped… Done.

Securing agent… Started.

Requesting an HTTPS Upload URL from the OMS… Done.

Requesting an Oracle Wallet and Agent Key from the OMS…

Done.

Check if HTTPS Upload URL is accessible from the agent… Done.

Configuring Agent for HTTPS in CENTRAL_AGENT mode… Done.

EMD_URL set in /u001/app/oracle/product/11.2.0/db_1/ORCL/sysman/config/emd.properties

Securing agent… Successful.

[oracle_orcl bin]$

[oracle_orcl bin]$ ./emctl start agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

Starting agent ….. started.

[oracle_orcl bin]$ ./emctl upload agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

—————————————————————

EMD upload completed successfully

[oracle_orcl bin]$ ./emctl upload agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0

—————————————————————

EMD upload completed successfully

[oracle_orcl bin]$

Then I checked Grid Control and made sure that the agent is working

ORA-19501 ORA-27070 OSD-04016 O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service

2013-08-28T12:55:00.001+05:30

ORA-19501 ORA-27070 OSD-04016 O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service

SYMPTOMS

RMAN-03009: failure of crosscheck command on ORA_MAINT_DISK_1 channel at 08/27/2013 23:03:46
ORA-19501: read error on file "F:\ORADATA\TEST\TEST01.DBF", blockno 3905 (blocksize=8192)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service

find out the error message for OS Errors.

D:\>net helpmsg 1450

Insufficient system resources exist to complete the requested service.

CAUSE

There are many causes for the OS-error OS-1450

The main error is the Windows OSD 1450 which basically means that the system is running out of
memory.

OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service.

This is a Microsoft Windows O.S. issue.

http://support.microsoft.com/support/kb/articles/Q236/9/64.ASP

SOLUTION

Set disk_asynch_io on FALSE.

for more details find the document(Doc ID 297498.1) from Metalink.

ORA-12638: credential retrieval failed

2013-08-22T14:35:00.002+05:30

ORA-12638: credential retrieval failed

The Oracle docs note this on the ora-12638 error:

ORA-12638: Credential retrieval failed

Cause: The authentication service failed to retrieve the credentials of a user.

Action: Enable tracing to determine the exact error.

I found following line in sqlnet.ora file from $ORACLE_HOME/network/admin
: SQLNET.AUTHENTICATION_SERVICES= (NTS)

Then i modified the above line as below and it started working

SQLNET.AUTHENTICATION_SERVICES= (NONE)

Reason :

Oracle client attempt to use your current Windows domain credentials to authenticate you with the Oracle server. This could fail for a couple of reasons:

- The Oracle server is not configured to support Windows authentication
- The credentials you use to login to your local machine are not sufficient to allow you to login to the server.

In my case, it was the later. This failed because I was logged on to my local machine using my normal domain credentials rather than my administrator account.

Beauty of Nature: @Kodaikanal

2013-08-10T23:55:00.001+05:30

Beauty of Nature: @Kodaikanal