Anand R: ASM Creation (Windows)

Anand R: ASM Creation (Windows)

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.

Performing a Manual Upgrade

1. Install Oracle Database 11g, Release 1 in a new
ORACLE_HOME.

2. Analyze the existing database:
– Use rdbms/admin/utlu111i.sql with the existing
server.
– SQL> spool pre_upgrade.log
– SQL> @utlu111i

3. Adjust redo logs and tablespace sizes if necessary.

4. Copy existing initialization files to the new
ORACLE_HOME and make recommended adjustments.

5. Shut down immediately, back

6. Start up using the Oracle Database 11g, Release 1
server:
– SQL> startup upgrade

7. If you are upgrading from 9.2, create a SYSAUX
tablespace:
– SQL> create tablespace SYSAUX datafile
'e:\oracle\oradata\empdb\sysaux01.dbf'
size 500M reuse
extent management local
segment space management auto
online;

8. Run the upgrade (automatically shuts down database):
– SQL> spool upgrade.log
– SQL> @catupgrd.sqlup

9. Restart the database instance in normal mode:
– SQL> startup

10.Run the Post-Upgrade Status Tool to display the
results of the upgrade:
– SQL>@utlu111s.sql

11.Run post-upgrade actions:
– SQL> @catuppst.sql

12.Recompile and revalidate any remaining application
objects:
– SQL> @utlrp (parallel compile on multiprocessor
system)


Downgrading a Database


1. Major release downgrades are supported back to 10.2 and
10.1.

2. Downgrade to only the release from which you upgraded.

3. Shut down and start up the instance in DOWNGRADE mode:
– SQL> startup downgrade

4. Run the downgrade script, which automatically determines
the version of the database and calls the specific component
scripts:
– SQL> SPOOL downgrade.log
– SQL> @catdwgrd.sql

5. Shut down the database immediately after the downgrade
script ends:
– SQL> shutdown immediate;

6. Move to the old ORACLE_HOME environment and start up the
database in the upgrade mode:
– SQL> startup upgrade

7. Reload the old packages and views:
– SQL> SPOOL reload.sql
– SQL> @catrelod.sql

8. Shut down and restart the instance for normal operation:
– SQL> shutdown immediate;
– SQL> startup

9. Run utlrp.sql to recompile all existing packages,
procedures, and types that were previously in an INVALID
state:
– SQL> @utlrp

10. Perform any necessary post-downgrade tasks.

Referance from Oracle..

Query to see the Reserved, Used and Free space of your database

SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;

Manually Resolve archive GAP

Manually Resolve archive GAP

When ever there is a GAP, check the alert logfile for any errors on the Physical or Logical Standby database. If the logfiles are not shipped to physical or logical automatically, then follow these steps and transfer all the log files.

1. Identify the ARCHIVE GAP, ARCHIVE LOG Sequence and THREAD

2. Connect RMAN on PRIMARY Database

3. Backup a copy of ARCHIVELOG TO DISK

BACKUP AS COPY ARCHIVELOG SEQUENCE 3912 THREAD 1 FORMAT ‘/D01/3911.ARC’;

4. Transfer Files to Standby Server

a. SCP – Secure copy

scp filename1 userid@hostname:filename2

b.

5. REGISTER LOGFILE

a. ALTER DATABASE REGISTER LOGFILE ‘XYZ’;

b. ALTER DATABASE REGISTER LOGICAL LOGFILE ‘XYZ’; ----- For LOGICAL

6. Selelct * from v$managed_standby; -- MRP process should be applying the log file

Look for MRP process trace file for additional info.

7. Select * from v$archive_gap; -- To find the GAP