Creating Active Standby database (ASM To FileSystem) using RMAN

Database version must be 11g


Source database db_unique_name = orcl
Duplicate database db_unique_name = sb

In standby server:

1. Check standby server meats our requirements or not.
2. Check standby server has sufficient privileges.

3. Check whether database software is installed or not.

4.  Check mount point has enough space or not.

5.  Check Oracle net configured or not.

Creating Physical Standby Database:

For Primary :

1. Check Oracle Net must be configured
Start the listener

2. Database must be up and running.

3. Database must be in archivelog mode.
SQL>SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, LOG_MODE, FORCE_LOGGING, DATABASE_ROLE FROM V$DATABASE;

Otherwise enable the archive log mode when the database in mount start
SQL> ALTER DATABASE ARCHIVELOG;

4. Enable FORCE LOGGING and SUPPLIMENTARY LOGGING.
SQL> ALTER DATBASE FORCE LOGGING;

5. Create a standby redo log files more than Online redo logfiles.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
('/...../standby_redo01.rdo',  '/...../standby_redo01.rdo', '/...../standby_redo01.rdo','/...../standby_redo01.rdo');

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

6. Create a password file.

7. Check whether database using spfile.
SQL>show parameter spfile

8. Change the parameters of the database which is running using spfile.

SQL>ALTER SYSTEM SET PARAMETER= value SCOPE=BOTH;

Add below parameter

*.DB_UNIQUE_NAME='ORCL' (Must be configured at the time of db creation means already there)
*.FAL_CLIENT='ORCL'
*.FAL_SERVER='sb' #standby database name
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,sb)'

*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_2='SERVICE=rac2 LGWR async

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sb'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'

Note: No need to restart the database because all the above parameters are dynamic parameters.

9. create a pfile.
SQL:> CREATE PFILE FROM SPFILE;

10. SCP your pfile to standby server location


For Standby :

1. Configure Oracle Net at standby server.
2. copy the initORCL.ora and orapwORCL into ORACLE_HOME/dbs/ location
3. make sure password file exist in ORALCLE_HOME/dbs location

4. Rename pfile and edit the pfile.

change the below parameter for standby

vi initsb.ora
control_files='/u01/app/oracle/oradata/sb/control_standby.ctl' # change
DB_UNIQUE_NAME='sb' #change
FAL_CLIENT='sb' #change
FAL_SERVER='ORCL' #change

log_archive_dest_1='LOCATION=use_db_recovery_file_dest
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sb' #change

log_archive_dest_2='SERVICE=rac1 LGWR async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'#change

db_create_file_dest='/u01/app/oracle/oradata/test'
db_file_name_convert='+data','/u01/app/oracle/oradata/test'
:wq


5. start the database using pfile in nomount
$export ORACLE_SID=sb

$sqlplus / as sysdba
SQL> startup nomount

6. create a spfile
create spfile from pfile.

7. shut the database

8. start the database in nomount state
$export ORACLE_SID=sb
$sqlplus / as sysdba
SQL> startup nomount

9. Connect to RMAN
$rman target=system/system@tnsorcl catalog=rman/rman@catalog auxiliary system/system@tnssb

connected to recovery catalog database
connected to auxiliary database: sb(DBID=99469477)

RMAN> run{

allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;

set newname for datafile 1 to "/u02/oradata/sb/system01.dbf";
set newname for datafile 2 to "/u02/oradata/sb/sysaux01.dbf";
set newname for datafile 3 to "/u02/oradata/sb/undotbs01.dbf";
set newname for datafile 4 to "/u02/oradata/sb/users01.dbf";

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;

release channel ch2;
release channel aux1;
release channel aux2;

}

OUTPUT


allocated channel: aux1
channel aux1: sid=156 devtype=DISK
Starting Duplicate Db at 01-JAN-10
contents of Memory Script:
.......................................................
........................................................
....................................................
contents of Memory Script:
{
Alter clone database open resetlogs;
}

executing Memory Script
database opened
Finished Duplicate Db


10. Start the MRP process:
sql> recover managed standby database using current logfile disconnect from session;







log_file_name_convert='+data','/u01/app/oracle/oradata/test'






connected to target database: ORCL (DBID=231273269)


set newname for tempfile 1 to "/u02/oradata/sb/temp01.dbf";





release channel ch1;

Migrating the Database from Non-ASM FileSystem to ASM FileSystem

1. Create a ASM groups say "DATA1".

2.  connect to database which is in READ_WRITE mode.

3. 

sql> show parameter spfile;

sql> show parameter control_file;

sql> select name from v$datafile;

4. 

sql> alter system set db_create_file_dest='+DATA1' scope=spfile;

sql> alter system set control_files='+DATA1' scope=spfile;

5. shut immediate

6. startup nomount

7. connect to RMAN

rman> restore controlfile from  '/../../control01.ctl';

rman> sql 'alter database mount'

rman> backup as copy database format '+DATA1';

rman> switch database to copy;

rman> sql 'alter database open''

8. 

sql> show parameter spfile;
sql> show parameter control_file;
sql> select name from v$datafile;

9. 
sql> create pfile from spfile;
sql> create spfile='+DATA1/spfile/spfileDB.ora' from pfile;

10. goto ORACLE_HOME/dbs location

$> vi initDB.ora
spfile='+DATA1/spfile/spfileDB.ora'
:wq

11.  bounce database (restart database)

note:
online redolog files and temporary datafiles are not in ASM. So we need to move them manually.

12. create default temporary tablespace
sql> alter database default temporary tablespace temp001;

13. drop previous default temporary tablespace

sql> select name from v$tempfile;

sql> drop tablespace <defaultTSName>;

14. bounce database

SQL Query Execution in Oracle (For DBA)

SQL Statement Execution Phases:
-----------------------------------------

When Application queries a request, it creates a user process and gives to server process through dispatcher.

server process places sql query in shared pool - sql area

Insert Statement:
In shared pool it goes to 3 stages Parsing, Execution and Fetching.

Parsing:

it performs syntactical, semantic checks
if success it will generate sql_id and hash value for the sql query and will be stored in ShardPool --> Library Cache.
if same hash value already exists in Library Cache then
it try to get the data from Database Buffer Cache is called Soft Parsing is also called Cache Hit.
otherwise it is called Cache Miss then it goes for Hard Parsing.


Execution:

     Optimization:
In hard parsing it performs shared pool check.
Here optimizer evaluates multiple plans depending on
Optimizer statistics, Access paths, Optimizer hints
expressions, conditions and constraints.

Fetching:

Chooses lowest cost execution plan will be executed by server process and gets the data from datafile and places it in the database buffer cache.
finally the result set will be displayed in cursor area.

Update Statement:

If it is update command then copy of a result set will be stored into the undo segments for roll-back and copy of a database buffer cache will be modified and marked as dirty buffers.

Server process writes all redos to redo log buffers.

When commit is issued all the redo entries to that transaction will be written to online redo logfiles by LGWR.

When checkpoint  (log switch) occurs (CKPT) will signals the db writer (DBWr). db writer writes all related dirty blocks to the datafiles using logfiles and checkpoint will update the latest scn to datafile header and controlfile.

finally result will be displayed to cursor area.

Insert Statement:

If it is insert command then values are stored in database buffer cache and the pointer for the table is stored in the undo tablespace.

When commit is issued  log writer writes redo entries to redo logfiles 

when checkpoint occurs (CKPT) will signals the db writer (DBWr). db writer writes all related blocks to the datafile
and checkpoint will update the latest scn to datafile header and controlfile.

For DDL:

issue a COMMIT before executing Create statement.
Verify privileges
Verify which tablespace
Verify tablespace quotas is not exceed.
Verify same object not exist same schema.
Create a table into the data dictionary.
issue a commit or roll-back.

Upgrading the database from 11.1.0.6 TO 11.2.0.2

UPGRADE 11.1.0.6 TO 11.2.0.2
---------------------------------------
1. Install required version 11.2.0.1
old version 11.1.0.6 (db_1)
new version 11.2.0.1 (db_2)

2. Install patches on new ORACLE_HOME if required.

Using Opatch apply

3. goto ORACLE_HOME (db_2) location
 copy /rdbms/admin/utlu112i.sql to  /tmp/utlu112i.sql

4. copy network configuration files tnsnames.ora and listener.ora to new ORACLE_HOME

5. Copy pfile or spfile from old ORACLE_HOME/dbs to new ORACLE_HOME/dbs location

6. ensure your system, sys user using same tablespace or not.

7. check for backups.
select * from v$backup;
if any wait to complete.

8. purge dba_recyclebin;

9. collect the dictionary statistics
exec dbas_stats.gather_dictionary_stats();

10. select * from dba_2pc_pending;
If there is any transactions close them.

11. Disable all cronjobs.

12. check the integrity of the database
sql> spool /tmp/dbup.txt
sql> @/tmp/utlu112i.sql
sql> spool off
take necessary steps if needed.

13. vi /etc/oratab
change old ORACLE_HOME (db_1)TO new ORACLE_HOME (db_2)

14. shut immediate

15. Take full backup.

16.
. oraenv
sqlplus / as sysdba
startup upgrade;
[check file version, home version
it will start the database in temp mode]

17. sql> spool /tmp/dbup.txt
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/utlu112s.sql
@?/rdbms/admin/catuppst.sql
sql> spool off

18. sql> select * from dba_registry;

19.check for invalid objects.
sql> select object_name, object_type, status from dba_objects where status like 'INVALID';

If there is any invalid objects
sql> @?/rdbms/admin/utlrp.sql

still any invalid objects
sql> alter object_type, object_name compile;
sql> alter table <table_name> compile;
sql> alter procedure <procedure_name> compile;

20. take full backup

21. collect the statistics.

22. stop the listener and start the new listener in new ORACLE_HOME.

23. Enable crontab job.

Database slient Installation using response file

1. In database software 
copy database/response/db_install.rsp    file to /tmp  folder

2. modify the response file which is in   /tmp/db_install.rsp    file (for 11.2)

For reference (what are the parameters u have to modify) verify the response file shown below.

3. create the database path where u want to install (must be specified in response file)
mkdir -p /u01/app/oracle/product/11.2.0/db_1

4. modify the permissions for the folders.

chown  -R oracle:dba    /path

chmod  -R 775   /path

5. su - oracle

goto database folder where runInstaller is present.

$ runInstaller   -slient  -responseFile  /tmp/db_install.rsp

responsefile parameters:

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

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0

#-------------------------------------------------------------------------------
# The installation option can be one of the following
# 1. INSTALL_DB_SWONLY
# 2. INSTALL_DB_AND_CONFIG
# 3. UPGRADE_DB
#-------------------------------------------------------------------------------

oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=rac2.oracl.com
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/u01/app/oracle

#-------------------------------------------------------------------------------
# Installation Edition of the component.                      
#                                                           
# The value should contain only one of these choices.      
# EE     : Enterprise Edition                              
# SE     : Standard Edition                                
# SEONE  : Standard Edition One
# PE     : Personal Edition (WINDOWS ONLY)
#-------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=false
oracle.install.db.customComponents=
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.CLUSTER_NODES=

#-------------------------------------------------------------------------------
# One of the following
# - GENERAL_PURPOSE                            
# - TRANSACTION_PROCESSING                     
# - DATAWAREHOUSE                              
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.SYSMAN=
oracle.install.db.config.starterdb.password.DBSNMP=

#-------------------------------------------------------------------------------
# Can be one of the following
# 1. GRID_CONTROL
# 2. DB_CONTROL
# -----------------------------------------------------------------------

oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.dbcontrol.emailAddress=
oracle.install.db.config.starterdb.dbcontrol.SMTPServer=
oracle.install.db.config.starterdb.automatedBackup.enable=false
oracle.install.db.config.starterdb.automatedBackup.osuid=
oracle.install.db.config.starterdb.automatedBackup.ospwd=

#-------------------------------------------------------------------------------
# Can be one of the following
# - FILE_SYSTEM_STORAGE
# - ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=

Database Switchover and Switchback

Prerequisites:
1. Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

Query to check if the managed recovery process is running on the standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CLOSING             157
ARCH      CLOSING             155
ARCH      CONNECTED             0
ARCH      CLOSING             156
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                158
MRP0      WAIT_FOR_LOG        158

The above fig. shows that the Managed Recovery Process (MRP0) is running on the standby database. If MRP is not running, then start the process with real time enabled using the below query in the standby database.

SQL>alter database recover managed standby disconnect from session;

Once when the MRP has started on the standby database, make sure that the archive logs generated at the primary end are shipped and getting applied to the standby database.

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

 THREAD# MAX(SEQUENCE#)
---------- --------------
     1            163

In the above case, sequence# 163 is the maximum sequence generated at the primary database and the same has been applied to the standby database.

2. Verify primary and standby tempfiles match

For each temporary tablespace on the standby, verify that temporary files associated with that tablespace on the primary database also exist on the standby database. Tempfiles added after initial standby creation are not propagated to the standby. Run this query on both the primary and target physical standby databases and verify that they match.

 SQL>select tmp.name filename,bytes,ts.name tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

3.  Verify that all datafiles are online on both primary and standby databases

Check whether all the datafiles are online prior to the switchover on both primary and standby databases

SQL>select name from v$datafile where status=’OFFLINE’;no rows selected

4. Check if there are any jobs running on the primary database using the below query.

SQL> select * from dba_jobs_running;

no rows selected

If there are any jobs running on the primary database and if its execution is not very important, then terminate the job to continue further.

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.

SQL> sho parameter job_que

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

In the above fig. the job_queue_processes parameter is set to 1000. Set this parameter to the value 0.

SQL>alter system set job_queue_processes=0 scope=spfile;

System altered

5. Query the switchover_status column of the v$database view on the primary database to determine whether the primary database can be switched over to the standby.

SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

--------------------
TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly.

6.  Ensure standby log files exist on the primary database.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

7. Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database.

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS GAP_STATUS
---------    ------------------------
VALID    NO GAP

Do not proceed until the value of the STATUS column is VALID and the value of the GAP_STATUS column is NOGAP, for the row that corresponds to the standby database.

8.  Ensure both tns listeners are pinging and registered with the database.

$tnsping  tnsstby

9. Make sure all datagaurd parameters for switchover configured. Like fall_server, log_archive_dest and log_archive_dest_state

Procedure:

primary name: db1
standby name: stby

on primary (db1)

sql> alter system set log_archive_dest_state_2='defer';
sql> select max(sequence#) from v$log_history;
sql> atler database commit to switchover to physical standby;
sql> shut immediate;

sql> startup mount;
sql> select name, open_mode, database_role from v$database;
sql> select max(sequence#) from v$log_history;

on standby (stby)

sql> alter database recover managed standby database cancel;
sql> select max(sequence#) from v$log_history;
sql> atler database commit to switchover to primary;
sql> shut immediate;

sql> startup 
sql> select name, open_mode, database_role from v$database;
sql> select max(sequence#) from v$log_history;
sql> alter system switch logfile;
sql> alter system switch logfile;
sql> select max(sequence#) from v$log_history;

on primary (db1)
sql> alter database recover managed standby database disconnect from session;
sql> select max(sequence#) from v$log_history;


switching back

on standby (db1)

sql> alter database recover managed standby database cancel;
sql> shut immediate

sql> startup 
sql> alter database commit to switchover to primary;
sql> shut immediate
sql> startup

on primary (stby)

sql> select name, open_mode, database_role from v$database;
sql> atler database commit to switchover to physical standby;
sql> shut immediate;

sql> startup mount;
sql> startup nomount
sql> alter database mount standby database;

sql> select name, open_mode, database_role from v$database;

sql> alter database recover managed standby database disconnect from session;
sql> select max(sequence#) from v$log_history;

on standby (db1)

sql> alter system set log_archive_dest_state_2='enable';
sql> select max(sequence#) from v$log_history;
sql> alter system switch logfile;
sql> /
sql>/
sql> select max(sequence#) from v$log_history;

on primary (stby)

sql> select max(sequence#) from v$log_history;