Data Guard Background processes

DMON – The Data Guard Broker process.

SNP – The snapshot process.

MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.

ORBn – performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB – is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS – Remote File Server process – In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN – Queue Monitor Process (QMNn) – Used to manage Oracle Streams Advanced Queuing.

How to set Data Protection Mode of a Primary Database

Step 1 Select a data protection mode that meets your availability, performance and data protection requirements.

Step 2 Verify that redo transport is configured to at least one standby database The value of the LOG_ARCHIVE_DEST_n database initialization parameter that corresponds to the standby database must include the redo transport attributes listed for the data protection mode that you are moving to.

Maximum Protection, Availability – AFFIRM,SYNC, DB_UNIQUE_NAME
Maximum Performance – NOAFFIRM, ASYNC, DB_UNIQUE_NAME

Step 3 Verify that the DB_UNIQUE_NAME database initialization parameter has been set to a unique name on the primary and standby database.

Step 4 Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database and that its value includes a DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary and standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(MMPRY,MMSTB)’;

Step 5 Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.
For example:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 6 Set the data protection mode.
Execute the following SQL statement on the primary database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

Step 7 Open the primary database.
SQL> ALTER DATABASE OPEN;

Step 8 Confirm that the primary database is operating in the new protection mode.

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

How to know Linux machine is 32 or 64 bit?

# uname -m

i386 or i686 then it is 32 bit
x86_64 is 64bit

HP-Unix machine is 32 or 64 bit?

Method 1:
# getconf KERNEL_BITS

Method 2:
# ps -ef |grep RT

Method 3:

# file /stand/vmunix

If 64 bit it will show something like: ELF-64 executable object file

How to know Sun Solaris machine is 32 or 64 bit?

# isainfo -v

Stored Outlines:
A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable.
The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to display information about existing outlines.
Under normal running the optimizer chooses the most suitable execution plan for the current circumstances.
By using a stored outline you may be forcing the optimizer to choose a substandard execution plan, so you should monitor the affects of your stored outlines over time to make sure this isn’t happening. As a DBA you already know what works well today may not tomorrow with the same plan.

– Grant the necessary privileges.
CONN sys/password AS SYSDBA
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;

The following example uses the CREATE OUTLINE statement to create an outline for a specified SQL statement. The statement is assigned to an outline category called SCOTT_OUTLINES to ease administration. If the category is not specified the outline is assigned to the default category.

CONN scott/tiger

— Create an outline for a specific SQL statement.
CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

— Check the outline as been created correctly.
COLUMN name FORMAT A30
SELECT name, category, sql_text FROM user_outlines WHERE category = ‘SCOTT_OUTLINES’;

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.

Do simple search for ocr.loc

/var/opt/oracle/ocr.loc
or
/etc/ocr.loc
or
# ocrcheck

Method 1: Alter Temp Tablespace and the Tempfile

SQL> ALTER ADD TEMPFILE REUSE;

Note : Method 1 will work on 99% if fails then go with the method 2 and it is clean

Method 2: Create a New Temporary Tablespace and recreate the old one

Create New Temporary Tablespace Temp1

SQL> create temporary tablespace temp1 tempfile ‘/u01/oracle/oratemp.dbf’ size 10m;

Make Temp1 as default temporary tablespace and drop the original default temporary tablespace

SQL>alter database default temporary tablespace temp1;

SQL> drop tablespace temp including contents and datafiles;

Now create temporay tablespace with original name and make it as default tablespace of database

SQL>create temporary tablespace temporary tempfile ‘/database/temp01.dbf’ size 500m;

SQL>alter database default temporary tablespace temporary;

Drop the temp1 tablespace

sql>drop tablespace temp1 including contents and datafiles;

RMAN Interview Questions

1) if one archive deleted before applying on standby and u do not have backup how u resolve this issue?. Database size is in TB so recreating Standby is not good idea.

2) if block change tacking is enabled on production db will it be enabled on cloned db automatically?

3) You have assigned 3-4 channels in rman backup how u will identified whether all are utilized or not?

4) how u do performance tuning for rman backups?

5) can we register manual cold backup in rman?

6) can we recreate oracle inventory if we do not have any backup?

7) what is diff in imagecopy & normal backup in terms of performance

10K blocks in ur db got corrupted how u will resolve this