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;