Steps for creating Physical standby database

Physical standby database

1. Check t primary db weathr FORCE LOGGING mode is enabled or not...

select FORCE_LOGGING from v$database;

FOR
---
YES

If it is not enable then alter the db..
......................

ALTER DATABASE FORCE LOGGING;


2. Then set password file..

under dbs/orapwd file=orapwprimaryinstancename password=*** entries=10

3. Edit the primary db's pfile.

DB_NAME=primary dbname
DB_UNIQUE_NAME=primary dbname
SERVICE_NAMES=primary dbname

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary dbname,standby db name)'


LOG_ARCHIVE_DEST_1= 'LOCATION=/archine_path_primary DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary DB Name'

LOG_ARCHIVE_DEST_2= 'SERVICE=Standby_DB_Name VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Standby DB name'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=standby dbname
FAL_CLIENT=primary dbname
DB_FILE_NAME_CONVERT= '/standby path/','/primary db datafiles path/','/standby path/','/primary db datafiles path/'
LOG_FILE_NAME_CONVERT= '/standby path/','/primary db redolog path/','/standby path/' ,'/primary db redolog path/'
STANDBY_FILE_MANAGEMENT=AUTO


ALter ur primary db to archiv log mode....

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Create a Backup Copy of the Primary Database Datafiles

Create a Control File for the Standby Database

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/path/control.ctl';


Prepare an Initialization Parameter File for the Standby Database

SQL> CREATE PFILE='/tmp/initstandby.ora' FROM SPFILE;
and modify the parameters..

STANDBY DB'S Pfile


DB_NAME=primary dbname
DB_UNIQUE_NAME=standby db name
SERVICE_NAMES=standby db name
control_file='standby path'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary dbname,standby db name)'

LOG_ARCHIVE_DEST_1='LOCATION=/archive_path_Standby DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Standby DB Name'
LOG_ARCHIVE_DEST_2='SERVICE=Primary DB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Primary DB Name'

FAL_SERVER=primary dbname
FAL_CLIENT=standby dbname

DB_FILE_NAME_CONVERT='/primary db datafiles path/','/standby path/','/primary db datafiles path/','/standby path/'
LOG_FILE_NAME_CONVERT='/primary db redolog path/','/standby path/','/primary db redolog path/','/standby path/'

These are the parameters mainly changed in both primary n standby databse.



Copy Files from the Primary System to the Standby System

a. Backup datafiles,
b. Standby controlfile,
c. pfile

Craete Passwordfile for the standby db..

set up the listener
% lsnrctl stop
% lsnrctl start



In the SQLNET.ORA it resides in netwrk/admin
make
SQLNET.EXPIRE_TIME parameter to 2 (minutes)

export ur new standby database instance
and startup the database in nomount state... (use the newly created pfile)

sql>startup nomount pfile='/new standbypath/initstandby.ora'

then creaet spfile and alter the db to nomount state

sql>create spfile from pfile;

sql>alter database mount standby database;

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

Verify the Physical Standby Database Is Performing Properly

On the standby database,

sql>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- -------------
------------
ARCH CONNECTED ARCH 0 0 0
0
ARCH CONNECTED ARCH 0 0 0MRP0 WAIT_FOR_LOG N/A 63 0 0
0
RFS IDLE N/A 0 0 0
0
RFS IDLE LGWR 63 3097 0
0




On the primary database,

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.


SQL>SELECT ERROR,STATUS FROM V$ARCHIVE_DEST;
ERROR STATUS
----------------------------------------------------------------- ---------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE

Verify the new redo data was archived on the standby database.

sql>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;


PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS
KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- -------------
------------
ARCH CONNECTED ARCH 0 0 0
0
ARCH CONNECTED ARCH 0 0 0
0
MRP0 WAIT_FOR_LOG N/A 64 0 0
0
RFS IDLE N/A 0 0 0
0
RFS IDLE LGWR 64 409 0
0

Note MRPO and LGRW are present..

To verify the sequence is applied or not

SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
---------- ---
56 YES
57 YES
58 YES
59 YES
60 YES
61 YES
62 YES
63 YES
64 YES

NOTE:

Suppose if the archiv is not shipmented to standby properly then may b listener problem with netservice name... chk in tnsnames.ora n also chk alertlog file to rectify....


DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT these 2 parameters are used wen
the directory structures in primary and standby are diffrent.


For Failover
sql>Alter database activate standby database;

For switchover to standby:
sql>alter database commit to switchover to standby;

Then shut t database and start it in mount state and apply the recovery manager.

For switchover to primary
sql>alter database commit to switchover to primary;

Hope wil help tis info

ASM Creation (Windows)

You can follow this steps and create a ASM diskgroup on your local machine and play with it ( Windows)

1) Creating a dummy disks

F:\>mkdir asmdisks
F:\>cd asmdisks
F:\asmdisks>asmtool -create F:\asmdisks\ disk1 512
F:\asmdisks>asmtool -create F:\asmdisks\ disk2 512
F:\asmdisks>asmtool -create F:\asmdisks\ disk3 512

Now you have 3 disks(dummy) of 512mb each which can be used to create a ASM disk group.

2) Create ASM instance

a) Configure Cluster Synchronization Servie

C:\>c:\oracle\product\ 10.2.0\db_ 1\BIN\localconfi g add

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa' , privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup
b) Create Init pfile
Open notepad edit the following parameters and save file as "C:\oracle\product\ 10.2.0\db_ 1\database\ init+ASM. ora"
INSTANCE_TYPE= ASM
DB_UNIQUE_NAME= +ASM
LARGE_POOL_SIZE= 8M
ASM_DISKSTRING= 'F:\asmdisks\ *'
_ASM_ALLOW_ONLY_ RAW_DISKS= FALSE

c) Create service and password file
oradim will create an ASM instance and start it automatically.
c:\> orapwd file=C:\oracle\ product\10. 2.0\db_1\ database\ PWD+ASM.ora password=asm
c:\> oradim -NEW -ASMSID +ASM -STARTMODE auto

3) Create ASM disk group
a) Create asm disk group
SQL> select path, mount_status from v$asm_disk;
PATH MOUNT_S
------------ --------- --------- --
F:\ASMDISKS\ DISK1 CLOSED
F:\ASMDISKS\ DISK3 CLOSED
F:\ASMDISKS\ DISK2 CLOSED

SQL> create diskgroup data external redundancy disk
2 'F:\ASMDISKS\ DISK1',
3 'F:\ASMDISKS\ DISK2',
4* 'F:\ASMDISKS\ DISK3';
Diskgroup created.
b) Change PFILE to SPFILE, Add ASM Diskgroup parameter and your all set to go and use ASM.
SQL> create spfile from pfile;
SQL> startup force;
SQL> alter system set asm_diskgroups= data scope=spfile;
SQL> startup force;
SQL> startup force
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
Now you can go ahead and use your DBCA and create a database and on step 6 of 13, you can use Automatic Storage management as your Filesystem.