Database administration - Tips and Tricks (Oracle, SQLServer)

The goal of this note is to provide steps to add, remove, replace or move an Oracle Cluster Repository (OCR) or voting disk in Oracle Clusterware 10gR2, 11gR1 and 11gR2 environment. It will also provide steps to move OCR / voting and ASM devices from raw device to block device.

This article is intended for DBA and Support Engineers who need to modify, or move OCR and voting disks files, customers who have an existing clustered environment deployed on a storage array and might want to migrate to a new storage array with minimal downtime.

Typically, one would simply cp or dd the files once the new storage has been presented to the hosts. In this case, it is a little more difficult because:

1. The Oracle Clusterware has the OCR and voting disks open and is actively using them. (Both primary and mirrors)
2. There is an API provided for this function (ocrconfig and crsctl), which is the appropriate interface than typical cp and/or dd commands.

It is highly recommended to take a backup of the voting disk, and OCR device before making any changes.

Oracle Cluster Registry (OCR) and Voting Disk Additional clarifications

For Voting disks (never use even number of voting disks):
External redundancy means 1 voting disk
Normal redundancy means 3 voting disks
High redundancy means 5 voting disks
For OCR: 10.2 and 11.1, maximum 2 OCR devices: OCR and OCRMIRROR
11.2+, upto 5 OCR devices can be added.

Fix

Prepare the disks

For OCR or votind disk addition or replacement, new disks need to be prepared. Please refer to Clusteware/Gird Infrastructure installation guide for different platform for the disk requirement and preparation.

1. Size

For 10.1:
OCR device minimum size (each): 100M
Voting disk minimum size (each): 20M
For 10.2:
OCR device minimum size (each): 256M
Voting disk minimum size (each): 256M
For 11.1:
OCR device minimum size (each): 280M
Voting disk minimum size (each): 280M
For 11.2:
OCR device minimum size (each): 300M
Voting disk minimum size (each): 300M

2. For raw or block device (pre 11.2)

Please refer to Clusterware installation guide on different platform for more details.
On windows platform the new raw device link is created via $CRS_HOME\bin\GUIOracleOBJManager.exe, for example:
\\.\VOTEDSK2
\\.\OCR2

3. For ASM disks (11.2+)

On Windows platform, please refer to Document 331796.1 How to setup ASM on Windows
On Linux platform, please refer to Document 580153.1 How To Setup ASM on Linux Using ASMLIB Disks, Raw Devices or Block Devices?
For other platform, please refer to Clusterware/Gird Infrastructure installation guide.

4. For cluster file system

If OCR is on cluster file system, the new OCR or OCRMIRROR file must be touched before add/replace command can be issued. Otherwise PROT-21: Invalid parameter (10.2/11.) or PROT-30 The Oracle Cluster Registry location to be added is not accessible (for 11.2) will occur.

As root user
# touch 
/cluster_fs/ocrdisk.dat
# touch /cluster_fs/ocrmirror.dat
# chown 
root:oinstall /cluster_fs/ocrdisk.dat  /cluster_fs/ocrmirror.dat
# chmod 640 
/cluster_fs/ocrdisk.dat  /cluster_fs/ocrmirror.dat

It is not required to pre-touch voting disk file on cluster file system.
After delete command is issued, the ocr/voting files on the cluster file system require to be removed manually.

5. Permissions

For OCR device:
chown root:oinstall <OCR device>
chmod 640 <OCR device>
For Voting device:
chown <crs/grid>:oinstall <Voting device>
chmod 644 <Voting device>
For ASM disks used for OCR/Voting disk:
chown griduser:asmadmin <asm disks>
chmod 660 <asm disks>

ADD/REMOVE/REPLACE/MOVE OCR Device

Note: You must be logged in as the root user, because root owns the OCR files. "crsctl -replace" command can only be issued when CRS is running, otherwise "PROT-1: Failed to initialize ocrconfig" will occur.

Please ensure CRS is running on ALL cluster nodes during this operation, otherwise the change will not reflect in the CRS down node, CRS will have problem to startup from this down node. "ocrconfig -repair" option will be required to fix the ocr.loc file on the CRS down node.

For 11.2+ with OCR on ASM diskgroup, due to unpublished Bug 8604794 - FAIL TO CHANGE OCR LOCATION TO DG WITH 'OCRCONFIG -REPAIR -REPLACE', "ocrconfig -repair" to change OCR location to different ASM diskgroup does not work currently. Workaround is to manually edit /etc/oracle/ocr.loc or /var/opt/ocr.loc or Windows registry HYKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ocr, point to desired diskgroup.

Make sure there is a recent copy of the OCR file before making any changes:

ocrconfig -showbackup

If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file. Use the following command to generate an export of the online OCR file:

In 10.2

# ocrconfig -export <OCR export_filename> -s online

In 11.1 and 11.2

# ocrconfig -manualbackup node1 2008/08/06 06:11:58 /crs/cdata/crs/backup_20080807_003158.ocr

To recover using this file, the following command can be used:

# 
ocrconfig -import <OCR export_filename>

From 11.2+, please also refer How to restore ASM based OCR after complete loss of the CRS diskgroup on Linux/Unix systems Document 1062983.1

To see whether OCR is healthy, run an ocrcheck, which should return with like below.

# ocrcheck Status of Oracle Cluster Registry is as follows : Version : 2 Total space (kbytes) : 497928 Used space (kbytes) : 312 Available space (kbytes) : 497616 ID : 576761409 Device/File Name : /dev/raw/raw1 Device/File integrity check succeeded Device/File Name : /dev/raw/raw2 Device/File integrity check succeeded Cluster registry integrity check succeeded For 11.1+, ocrcheck as root user should also show: Logical corruption check succeeded

1. To add an OCRMIRROR device when only OCR device is defined:

To add an OCR mirror device, provide the full path including file name.
10.2 and 11.1:

# ocrconfig -replace ocrmirror <filename> eg: # ocrconfig -replace ocrmirror /dev/raw/raw2 # ocrconfig -replace ocrmirror /dev/sdc1 # ocrconfig -replace ocrmirror /cluster_fs/ocrdisk.dat > ocrconfig -replace ocrmirror \\.\OCRMIRROR2 - for Windows

11.2+: From 11.2 onwards, upto 4 ocrmirrors can be added

# ocrconfig -add <filename> eg: # ocrconfig -add +OCRVOTE2 # ocrconfig -add /cluster_fs/ocrdisk.dat

2. To remove an OCR device

To remove an OCR device:
10.2 and 11.1:

# ocrconfig -replace ocr

11.2+:

# ocrconfig -delete <filename> eg: # ocrconfig -delete +OCRVOTE1

* Once an OCR device is removed, ocrmirror device automatically changes to be OCR device.
* It is not allowed to remove OCR device if only 1 OCR device is defined, the command will return PROT-16.

To remove an OCR mirror device:
10.2 and 11.1:

# ocrconfig -replace ocrmirror

11.2+:

# ocrconfig -delete <ocrmirror filename> eg: # ocrconfig -delete +OCRVOTE2

After removal, the old OCR/OCRMIRROR can be deleted if they are on cluster filesystem.

3. To replace or move the location of an OCR device

Note. 1. An ocrmirror must be in place before trying to replace the OCR device. The ocrconfig will fail with PROT-16, if there is no ocrmirror exists.
2. If an OCR device is replaced with a device of a different size, the size of the new device will not be reflected until the clusterware is restarted.

10.2 and 11.1:
To replace the OCR device with <filename>, provide the full path including file name.

# ocrconfig -replace ocr <filename> eg: # ocrconfig -replace ocr /dev/sdd1 $ ocrconfig -replace ocr \\.\OCR2 - for Windows

To replace the OCR mirror device with <filename>, provide the full path including file name.

# ocrconfig -replace ocrmirror <filename> eg: # ocrconfig -replace ocrmirrow /dev/raw/raw4 # ocrconfig -replace ocrmirrow \\.\OCRMIRROR2 - for Windows

11.2:
The command is same for replace either OCR or OCRMIRRORs (at least 2 OCR exist for replace command to work):

# ocrconfig -replace 
<current filename> -replacement <new filename>
eg:
# ocrconfig 
-replace /cluster_file/ocr.dat -replacement +OCRVOTE
# ocrconfig -replace 
+CRS -replacement +OCRVOTE

ADD/DELETE/MOVE Voting Disk

Note: 1. crsctl votedisk commands must be run as root for 10.2 and 11.1, but can be run as grid user for 11.2+
2. For 11.2, when using ASM disks for OCR and voting, the command is same for Windows and Unix platform.

To take a backup of voting disk:

$ dd if=voting_disk_name of=backup_file_name

For Windows:

ocopy \\.\votedsk1 o:\backup\votedsk1.bak

For 10gR2 release

Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes before making any modification to the voting disk. Determine the current voting disk location using:
crsctl query css votedisk

1. To add a Voting Disk, provide the full path including file name:

# crsctl add css votedisk <VOTEDISK_LOCATION> -force eg: # crsctl add css votedisk /dev/raw/raw1 -force # crsctl add css votedisk /cluster_fs/votedisk.dat -force > crsctl add css votedisk \\.\VOTEDSK2 -force - for windows

2. To delete a Voting Disk, provide the full path including file name:

# crsctl delete css votedisk <VOTEDISK_LOCATION> -force eg: # crsctl delete css votedisk /dev/raw/raw1 -force # crsctl delete css votedisk /cluster_fs/votedisk.dat -force > crsctl delete css votedisk \\.\VOTEDSK1 -force - for windows

3. To move a Voting Disk, provide the full path including file name, add a device first before deleting the old one:

# crsctl add css votedisk <NEW_LOCATION> -force # crsctl delete css votedisk <OLD_LOCATION> -force eg: # crsctl add css votedisk /dev/raw/raw4 -force # crsctl delete css votedisk /dev/raw/raw1 -force

After modifying the voting disk, start the Oracle Clusterware stack on all nodes

# crsctl start crs

Verify the voting disk location using

# crsctl query css votedisk

For 11gR1 release

Starting with 11.1.0.6, the below commands can be performed online (CRS is up and running).

1. To add a Voting Disk, provide the full path including file name:

# crsctl add css 
votedisk <VOTEDISK_LOCATION>
eg:
# crsctl add css votedisk 
/dev/raw/raw1
# crsctl add css votedisk /cluster_fs/votedisk.dat
> 
crsctl add css votedisk \\.\VOTEDSK2        - for windows

2. To delete a Voting Disk, provide the full path including file name:

# crsctl delete css 
votedisk <VOTEDISK_LOCATION>
eg:
# crsctl delete css votedisk 
/dev/raw/raw1 -force
# crsctl delete css votedisk 
/cluster_fs/votedisk.dat
> crsctl delete css votedisk \\.\VOTEDSK1     - 
for windows

3. To move a Voting Disk, provide the full path including file name:

# crsctl add css 
votedisk <NEW_LOCATION> 
# crsctl delete css votedisk 
<OLD_LOCATION>
eg:
# crsctl add css votedisk /dev/raw/raw4
# 
crsctl delete css votedisk /dev/raw/raw1

Verify the voting disk location:

# crsctl query css 
votedisk

For 11gR2 release

From 11.2, votedisk can be stored on either ASM diskgroup or cluster file systems. The following commands can only be executed when Grid Infrastructure is running. As grid user:

1. To add a Voting Disk
a. When votedisk is on cluster file system:

$ crsctl add css 
votedisk <cluster_fs/filename>

b. When votedisk is on ASM diskgroup, no add option available.
The number of votedisk is determined by the diskgroup redundancy. If more copy of votedisk is desired, one can move votedisk to a diskgroup with higher redundancy. See step 4.
If a votedisk is removed from a normal or high redundancy diskgroup for abnormal reason, it can be added back using:

alter diskgroup <vote diskgroup 
name> add disk '</path/name>' force;

2. To delete a Voting Disk
a. When votedisk is on cluster file system:

$ crsctl delete css 
votedisk <cluster_fs/filename>

b. When votedisk is on ASM, no delete option available, one can only replace the existing votedisk group with another ASM diskgroup

3. To move a Voting Disk on cluster file system

$ crsctl add css votedisk 
<new_cluster_fs/filename>
$ crsctl delete css votedisk 
<old_cluster_fs/filename>

4. To move voting disk on ASM from one diskgroup to another diskgroup due to redundancy change or disk location change

$ crsctl replace 
votedisk <+diskgroup>|<vdisk>

Example here is moving from external redundancy +OCRVOTE diskgroup to normal redundancy +CRS diskgroup

1. create new diskgroup +CRS as 
desired



2. $ crsctl query css votedisk
##  
STATE    File Universal Id                File Name Disk group
--  -----    
-----------------                --------- ---------
 1. ONLINE   
5e391d339a594fc7bf11f726f9375095 (ORCL:ASMDG02) [+OCRVOTE]
Located 1 voting 
disk(s).

3. $ crsctl replace votedisk +CRS
Successful addition of 
voting disk 941236c324454fc0bfe182bd6ebbcbff.
Successful addition of voting 
disk 07d2464674ac4fabbf27f3132d8448b0.
Successful addition of voting disk 
9761ccf221524f66bff0766ad5721239.
Successful deletion of voting disk 
5e391d339a594fc7bf11f726f9375095.
Successfully replaced voting disk group 
with +CRS.
CRS-4266: Voting file(s) successfully replaced



4. $ crsctl query css votedisk
##  STATE    File Universal 
Id                File Name Disk group
--  -----    
-----------------                --------- ---------
 1. ONLINE   
941236c324454fc0bfe182bd6ebbcbff (ORCL:CRSD1) [CRS]
 2. ONLINE   
07d2464674ac4fabbf27f3132d8448b0 (ORCL:CRSD2) [CRS]
 3. ONLINE   
9761ccf221524f66bff0766ad5721239 (ORCL:CRSD3) [CRS]
Located 3 voting 
disk(s).

5. To move voting disk between ASM diskgroup and cluster file system
a. Move from ASM diskgroup to cluster file system:

$ crsctl query css votedisk
##  
STATE    File Universal Id                File Name Disk group
--  -----    
-----------------                --------- ---------
 1. ONLINE   
6e5850d12c7a4f62bf6e693084460fd9 (ORCL:CRSD1) [CRS]
 2. ONLINE   
56ab5c385ce34f37bf59580232ea815f (ORCL:CRSD2) [CRS]
 3. ONLINE   
4f4446a59eeb4f75bfdfc4be2e3d5f90 (ORCL:CRSD3) [CRS]
Located 3 voting 
disk(s).

$ crsctl replace votedisk /rac_shared/oradata/vote.test3
Now 
formatting voting disk: /rac_shared/oradata/vote.test3.
CRS-4256: Updating 
the profile
Successful addition of voting disk 
61c4347805b64fd5bf98bf32ca046d6c.
Successful deletion of voting disk 
6e5850d12c7a4f62bf6e693084460fd9.
Successful deletion of voting disk 
56ab5c385ce34f37bf59580232ea815f.
Successful deletion of voting disk 
4f4446a59eeb4f75bfdfc4be2e3d5f90.
CRS-4256: Updating the profile
CRS-4266: 
Voting file(s) successfully replaced

$ crsctl query css votedisk
##  
STATE    File Universal Id                File Name Disk group
--  -----    
-----------------                --------- ---------
 1. ONLINE   
61c4347805b64fd5bf98bf32ca046d6c (/rac_shared/oradata/vote.disk) []
Located 1 
voting disk(s).

b. Move from cluster file system to ASM diskgroup

$ crsctl query css votedisk
##  
STATE    File Universal Id                File Name Disk group
--  -----    
-----------------                --------- ---------
 1. ONLINE   
61c4347805b64fd5bf98bf32ca046d6c (/rac_shared/oradata/vote.disk) []
Located 1 
voting disk(s).

$ crsctl replace votedisk +CRS
CRS-4256: Updating the 
profile
Successful addition of voting disk 
41806377ff804fc1bf1d3f0ec9751ceb.
Successful addition of voting disk 
94896394e50d4f8abf753752baaa5d27.
Successful addition of voting disk 
8e933621e2264f06bfbb2d23559ba635.
Successful deletion of voting disk 
61c4347805b64fd5bf98bf32ca046d6c.
Successfully replaced voting disk group 
with +CRS.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) 
successfully replaced

[oragrid@auw2k4 crsconfig]$ crsctl query css 
votedisk
##  STATE    File Universal Id                File Name Disk 
group
--  -----    -----------------                --------- 
---------
 1. ONLINE   41806377ff804fc1bf1d3f0ec9751ceb (ORCL:CRSD1) 
[CRS]
 2. ONLINE   94896394e50d4f8abf753752baaa5d27 (ORCL:CRSD2) [CRS]
 3. 
ONLINE   8e933621e2264f06bfbb2d23559ba635 (ORCL:CRSD3) [CRS]
Located 3 voting 
disk(s).

6. To verify:

$ crsctl query css 
votedisk

Thursday, October 04, 2012

Oracle 11g Grid Control Command

What works? Commands

Set environment variables. (emctl from OMS home has to be used )

$ export ORACLE_HOME=<path to OMS installation> $ export PATH=$ORACLE_HOME/bin:$PATH $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Start OMS
$ emctl start oms
This will start oms as well as the required weblogic processes. [starts opmn, HTTP_SERVER, Node Manager]

Check Status of OMS
$ emctl status oms [-details]

=====================
$ export ORACLE_HOME=/u01/app/Middleware/oms11g $ export PATH=$ORACLE_HOME/bin:$PATH $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
gridcontrol:/u01/app/Middleware/oms11g []$ cd bin gridcontrol:/u01/app/Middleware/oms11g/bin []$ ./emctl status oms Oracle Enterprise Manager 11g Release 1 Grid Control Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. WebTier is Up Oracle Management Server is Up

=====================

The "details" option is very informative. It will ask for Sysman password.
gridcontrol:/u01/app/Middleware/oms11g/bin []$ ./emctl status oms -details Oracle Enterprise Manager 11g Release 1 Grid Control Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. Enter Enterprise Manager Root (SYSMAN) Password : Console Server Host : bnair.localdomain HTTP Console Port : 7788 HTTPS Console Port : 7799 HTTP Upload Port : 4889 HTTPS Upload Port : 4900 SLB or virtual hostname: bnair.localdomain Agent Upload is unlocked. OMS Console is unlocked. Active CA ID: 1

Stop OMS
$ emctl stop oms [-all]
Using the "all" option stop node manager and Admin Server as well.

List OMS
$ emctl list oms
This provides the oms name configured in the local ORACLE_HOME. A "*" is displayed next to OMS name if admin server is configured on the same host.

What "also" works?
Though opmnctl cannot be used to start oms, you can still use opmnctl to start | stop | check status of HTTP Server components.

$export ORACLE_INSTANCE=/u01/gc_inst/WebTierIH1 $ cd $ORACLE_INSTANCE/bin gridcontrol:/u01/gc_inst/WebTierIH1/bin []$ ./opmnctl status Processes in Instance: instance1 ---------------------------------+--------------------+---------+--------- ias-component | process-type | pid | status ---------------------------------+--------------------+---------+--------- ohs1 | OHS | 3962 | Alive

Log file locations

OMS Application logs: <EM_INSTANCE_HOME>/sysman/log OPMN logs: <Webtier Instance Home>/diagnostics/logs/OPMN/opmn HTTP Server: <Webtier Instance Home>/diagnostics/logs/OHS/ohs1

Directory Structure

The diagram shows the locations related to oms, agents and webtier only. (Based on more detailed diagram available at oracle support).

Wednesday, August 29, 2012

Oracle GoldenGate – an introduction « Oracle DBA – Tips and Techniques

http://gavinsoorma.com/2010/02/oracle-goldengate-an-introduction/

Wednesday, July 18, 2012

Oracle Virtualization – Installing Oracle VM Server Oracle VM Manager and Deploying Oracle RAC 11gR2 (11.2.0.2) Oracle VM templates Linux x86 64 bit for test configuration « Guenadi N Jilevski's Oracle BLOG

http://gjilevski.com/2014/05/26/build-two-node-oracle-rac-12c-12-1-0-1-cluster-on-oel-6-5-using-oracle-vm-virtual-box-download/

http://gjilevski.wordpress.com/2011/02/11/oracle-virtualization-installing-oracle-vm-server-2-2-1-oracle-vm-manager-2-2-0-and-deploying-oracle-rac-11gr2-11-2-0-2-oracle-vm-templates-linux-x86-64-bit-for-test-configuration/

Thursday, May 31, 2012

Cloning with Direct NFS

Clonedb is a new Direct NFS (DNFS) feature introduced in the 11.2.0.2. clonedb uses dNFS technology to instantly fire up a clone using an existing backup of a database as the data store. The clone uses copy-on-write technology, so only changed blocks need to be stored locally, while the unchanged data is referenced directly from the backup files. This drastically increases the speed of cloning a system and means that several separate clones can function against a single set of backup datafiles, thus saving considerable amounts of space.

Configuration, Setup

NFS Server : viviana (ES Linux)
Primary DB server : Chopin
Clone DB server : Wolfgang

Create an file with the following attributes for each NFS server to be accessed using Direct NFSserver:

viviana

path: 192.168.1.3
export: /u01/nfs/backup mount: C:\backup

Oracle Database uses an ODM library, , to enable Direct NFS. To replace the standard ODM library, , with the ODM NFS library,complete the following steps:

Shutdown database
C:\> copy oraodm11.dll oraodm11.dll.stub
C:\> copy /Y oranfsodm11.dll oraodm11.dll
On the NFS server, Export the directory as an NFS share by adding the following lines to the "/etc/exports" file
/u01/nfs/backup *(rw,wdelay,insecure,no_root_squash,no_subtree_check)

Make sure the NFS service is available after reboot and restart the NFS service.

# chkconfig nfs on
# service nfs restart

Take a image backup of the production database:

run {
set nocfau;
backup as copy database format '/host/backups/prod/%U' ;
}

Use the following views for Direct NFS management:

v$dnfs_servers: Shows a table of servers accessed using Direct NFS.
v$dnfs_files: Shows a table of files currently open using Direct NFS.
v$dnfs_channels: Shows a table of open network paths (or channels) to servers for which Direct NFS is providing files.
v$dnfs_stats: Shows a table of performance statistics for Direct NFS

Thursday, May 24, 2012

Oracle Data Guard Broker DGMGRL Configuration

Step-by-step instructions on how to set up DG broker

Set up init parameters on primary to enable broker

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
ALTER SYSTEM SET DB_UNIQUE_NAME='apple1';
ALTER SYSTEM SET DB_DOMAIN='db_domain';
Set up init parameters on standby

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
ALTER SYSTEM SET DB_UNIQUE_NAME='apple2';
ALTER SYSTEM SET DB_DOMAIN='db_domain';

GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>> in listener.ora on all instances of both primary and standby.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
        (GLOBAL_DBNAME = apple1_dgmgrl)
        (ORACLE_HOME = c:\oracle\1020)
        (SID_NAME = apple1)
        )
)

TNSNAMES.ora need to change to the correct service_names as the listener.ora

This is important otherwise you'll have TNS-12154 error during switchover operation.
Create the configuration

CREATE CONFIGURATION 'AppleDR' AS
> PRIMARY DATABASE IS 'apple1'
> CONNECT IDENTIFIER IS 'apple1'ADD DATABASE 'apple2' AS
> CONNECT IDENTIFIER IS 'apple2'
maintained as physical;
Enable the configuration

DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> SHOW CONFIGURATION
> show database verbose apple1
Troubleshooting
Let us see some sample issues and their fix

Issue

DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available
Fix
Set dg_broker_start=true

Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings

Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.

Monitoring the Data Guard Broker Configuration

If we receive any error or warnings we can obtain more information about the same by running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.

DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

;

Monday, May 07, 2012

Installing Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86

http://st-curriculum.oracle.com/obe/em/emgc1110/installemgc11101linux/emgc11101install.htm

Install and Configure Oracle Enterprise Linux 5

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/oel5gainst/oel5gainst.htm

Tuesday, April 24, 2012

Move Database from Linux to Windows

There are two basic methods to achieve this.

Method 1
========
Steps 1:
Check the ENDIAN format of the platforms. Both Windows and linux should have the same format.
In our case we are moving from Linux 32-bit to Windows 32-bit:

col PLATFORM_NAME format a40
select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Microsoft Windows IA (64-bit) Little

Step 2:
Check if the database can be transported. We need to use DBMS_TDB.CHECK_DB, to check if our database can be
transported to the target OS, in the way it is currently.
Need to start the database in READ ONLY mode:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> set serveroutput on
SQL> declare
2 check_db boolean;
3 begin
4 check_db:=dbms_tdb.check_db('Microsoft Windows IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.

As we see no errors or message, so our database is ready to be transported to Windows 32-bit.

Step 3:
Check if there are any external files associated with the database, they will not be transported using RMAN.
SQL> set serveroutput on
SQL> declare
2 chk boolean;
3 begin
4 chk:=dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.FOR_HR, SYS.BLOB_TEST, SYS.IDR_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR,
SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.AUDIT_DIR, SYS.DATA_PUMP_DIR,
SYS.ORACLE_OCM_CONFIG_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.
The above directories exists in the database, we will need to recreate them with new locations once we complete the move.

Step 4:
Now we need to use RMAN convert database command to convert the source database to windows 32-bit.
The source database must be in read only mode.

RMAN> convert database new database 'ORCL'
2> transport script '/home/oracle/transport1.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/home/oracle/product/oradata/test' '/home/oracle/con_dbf';

Starting conversion at source at 23-MAR-09
using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.FOR_HR found in the database
Directory SYS.BLOB_TEST found in the database
Directory SYS.IDR_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.AUDIT_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/home/oracle/product/oradata/test/users01.dbf
converted datafile=/home/oracle/con_dbf/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:21
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/home/oracle/product/oradata/test/sysaux01.dbf
converted datafile=/home/oracle/con_dbf/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/home/oracle/product/oradata/test/system01.dbf
converted datafile=/home/oracle/con_dbf/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/home/oracle/product/oradata/test/example01.dbf
converted datafile=/home/oracle/con_dbf/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/home/oracle/product/oradata/test/undotbs01.dbf
converted datafile=/home/oracle/con_dbf/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Edit init.ora file /home/oracle/product/11g/db/dbs/init_00kal9vi_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /home/oracle/transport1.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-MAR-09

RMAN>

Below are our converted database files:

oracle@oracle:~/con_dbf$ pwd
/home/oracle/con_dbf
oracle@oracle:~/con_dbf$ ls -lrt
total 7267080
-rw-r----- 1 oracle oracle 5608382464 2009-03-23 16:17 users01.dbf
-rw-r----- 1 oracle oracle 882057216 2009-03-23 16:18 sysaux01.dbf
-rw-r----- 1 oracle oracle 744497152 2009-03-23 16:19 system01.dbf
-rw-r----- 1 oracle oracle 104865792 2009-03-23 16:20 example01.dbf
-rw-r----- 1 oracle oracle 94380032 2009-03-23 16:20 undotbs01.dbf
-rw-r--r-- 1 oracle oracle 1710 2009-03-23 17:01 init_00kal9vi_1_0.ora
-rw-r--r-- 1 oracle oracle 2698 2009-03-23 17:01 transport1.sql
Screen shot of the transport1.sql file:

Step 5:
FTP the files to the windows server. I have used filezilla to copy the files to the windows server.

Step 6:
Use oradim to create the service:
C:\Documents and Settings\Administrator>oradim -new -sid orcl -intpwd oracle -s
artmode manual -pfile C:\oracle\11g\product\11.1.0\db_1\dbs\init_orcl.ora
Instance created.

Use the init file (init_00kal9vi_1_0.ora) to startup nomount the db on the windows machine:
Make required changes to the paths in the init ora file.
Screen shot of the init.ora file:

C:\Documents and Settings\Administrator>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 23 17:31:25 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1333620 bytes
Variable Size 310380172 bytes
Database Buffers 104857600 bytes
Redo Buffers 6098944 bytes
SQL>

Step 7:
Create the control file:
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\oracle\oradata\ORCL\log1.rdo' SIZE 50M,
9 GROUP 2 'C:\oracle\oradata\ORCL\log2.rdo' SIZE 50M,
10 GROUP 3 'C:\oracle\oradata\ORCL\log3.rdo' SIZE 50M
11 DATAFILE
12 'C:\oracle\oradata\ORCL\system01.dbf',
13 'C:\oracle\oradata\ORCL\sysaux01.dbf',
14 'C:\oracle\oradata\ORCL\undotbs01.dbf',
15 'C:\oracle\oradata\ORCL\users01.dbf',
16 'C:\oracle\oradata\ORCL\example01.dbf'
17 CHARACTER SET AL32UTF8;

Control file created.

Step 8:
Open the database.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL> select name from v$database;
ERROR:
ORA-03114: not connected to ORACLE

Meanwhile and checked if the redo log files were created, Saw that they were created. Logged in again and:

SQL> select name from v$database;
NAME
---------
ORCL

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

Step 9:
Add the tempfile.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\oradata\ORCL\temp01.dbf' SIZE
54525952 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Step 10:
Do a sanity check of the database.
Run $ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-03-24 14:43:54
PL/SQL procedure successfully completed.
TIMESTAMP
-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2009-03-24 14:43:56
PL/SQL procedure successfully completed.
OBJECTS WITH ERRORS
-------------------
0
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- ----------
ORCL READ WRITE

Database moved from linux to windows machine!!!!

Method 2
========
There another way to do the datafile conversion, we can convert the datafiles on the destination server also.
The rman script changes a little in that case.
RMAN> convert database on target platform
2> convert script '/home/oracle/convert.sql'
3> transport script '/home/oracle/transport.sql'
4> new database 'orcl'
5> format '/home/oracle/%U_%d';

The datafiles create by the above rman command should be copied to a temp directory on the destination server.
This will create a transport script to create the database instance on the destination server.
It will also create a rman script to convert the datafiles on the destination server.
It will look like:
run {
CONVERT DATAFILE 'C:\oracle\oradata\ORCL\SYSTEM.DBF'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT 'c:\temp\SYSTEM.DBF';
} ;
In the above case the files copied from the linux server were kept in c:\temp and then they would be converted to
C:\oracle\oradata\ORCL location.
After the conversion, run the transport.sql script after making the required changes.
After you have opened the database the rest of the steps are the same.

Flashback Database

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.

If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.

-- initialization

SQL >alter system set db_recovery_file_dest_size = 20G;

SQL >alter system set db_recovery_file_dest = '/home/oracle/FRA';

SQL > alter database archivelog
SQL > alter database flashback on;

-- create restore point

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- check

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT

check usage

SQL> select file_type, space_used*percent_space_used/100/1024/1024 used,
space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable, frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

-- mount database and flash back

SQL> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_UPGRADE’;
SQL> ALTER DATABASE OPEN RESETLOGS;

-- drop flashback

SQL> drop restore point before_upgrade;

Flashback database - the RMAN way

create restore point

RMAN> create restore point before_upgrade guarantee flashback database;

Statement processed

list restore point

RMAN> list restore point all;

SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
2019135 GUARANTEED 21-MAR-17 BEFORE_UPGRADE

flashback database to restore point

RMAN> flashback database to restore point before_upgrade;

Starting flashback at 21-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 21-MAR-17

drop restore point

RMAN> drop restore point before_upgrade;

Statement processed

CRSCTL Commands CheatSheet

You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

Relocate services

#srvctl status service -s opera -s slors

#srvctl relocate service -d opera -s slors -i opera3 -t opera1

Start Oracle Clusterware

#crsctl start crs

Stop Oracle Clusterware

#crsctl stop crs

Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

Checking Voting disk Location

$crsctl query css votedisk

0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).

Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

Add Voting disk

#crsctl add css votedisk path

Remove Voting disk

#crsctl delete css votedisk path

Check CRS Status

$crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

You can also see particular daemon status

$crsctl check cssd

Cluster Synchronization Services appears healthy

$crsctl check crsd

Cluster Ready Services appears healthy

$crsctl check evmd

Event Manager appears healthy

You can also check Clusterware status on both the nodes using

$crsctl check cluster

prod01 ONLINE

prod02 ONLINE

Checking Oracle Clusterware Version

To determine software version (binary version of the software on a particular cluster node) use

$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.

There are other options for CRSCTL too which can be seen using

$crsctl

$crsctl help

Thursday, March 01, 2012

SQLServer commands

Command	Purpose	Sample Usage
sp_helpdb	This gives you information about all databases in the instance or specific information about one database.	sp_helpdb sp_helpdb databasename
fn_virtualfilestats	This command will show you the number of read and writes to a data file. Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.	SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid) SELECT * FROM :: fn_virtualfilestats(1, 1)
fn_get_sql()	Returns the text of the SQL statement for the specified SQL handle. This is similar to using DBCC INPUTBUFFER, but this command will show you additional information. This can also be embedded in a process easier then using the DBCC commandMSSQLTips additional info	DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 SELECT * FROM ::fn_get_sql(@Handle)
sp_lock	This command shows you all of the locks that the system is currently tracking This is similar to information you can see in Enterprise Manager.	sp_lock sp_lock spid sp_lock spid1, spid2
sp_help	This command gives you information about the objects within a database. The command without an objectname will give you a list of all objects within the database.	sp_help sp_help objectname
sp_who2	Gives you process information similar to what you see when using Enterprise Manager.	sp_who2 sp_who2 spid
sp_helpindex	Gives you information about the indexes on a table as well as the columns used for the index.MSSQLTips additional info	sp_helpindex objectname
sp_spaceused	This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.	sp_spaceused sp_spaceused objectname
DBCC CACHESTATS	Displays information about the objects currently in the buffer cache.	DBCC CACHESTATS
DBCC CHECKDB	This will check the allocation of all pages in the database as well as check for any integrity issues.	DBCC CHECKDB
DBCC CHECKTABLE	This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.	DBCC CHECKTABLE (‘tableName’)
DBCC DBREINDEX	This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.MSSQLTips additional info	DBCC DBREINDEX (tablename, indexname, fillfactor) DBCC DBREINDEX (authors, '', 70) DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
DBCC PROCCACHE	This command will show you information about the procedure cache and how much is being used. Spotlight will also show you this same information.	DBCC PROCCACHE
DBCC MEMORYSTATUS	Displays how the SQL Server buffer cache is divided up, including buffer activity.	DBCC MEMORYSTATUS
DBCC SHOWCONTIG	This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.	DBCC SHOWCONTIG DBCC SHOWCONTIG WITH ALL_INDEXES DBCC SHOWCONTIG tablename
DBCC SHOW_STATISTICS	This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.	DBCC SHOW_STATISTICS (tablename, indexname)
DBCC SHRINKFILE	This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.MSSQLTips additional info	DBCC SHRINKFILE (filename, size in MB) DBCC SHRINKFILE (DataFile, 1000)
DBCC SQLPERF	This command will show you much of the transaction logs are being used.	DBCC SQLPERF(LOGSPACE)
DBCC TRACEON	This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.	DBCC TRACEON(traceflag)
DBCC TRACEOFF	This command turns off a trace flag.

Saturday, January 28, 2012

Oracle incremental update using image copy

Explains the RMAN Image copy feature and incrementally updated backups New in Oracle 10G.

• Rman is configured with Recovery Catalog.
• Recovery Catalog Database Name-Orcl
• Database Name test is taken for demo
• Database Test is Running in no archive log mode
• Configured Backup location is 'D:\ORACLE\RMAN\ORA10G'
• During the first and second run of the backup scripts I made some changes in the Database. Also during the second and the third run I made some changes in the Database.

Expanded Image Copying Features: A standard RMAN backup set contains one or more backup pieces, and each of these pieces consists of the data blocks for a particular datafile stored in a special compressed format. When a datafile needs to be restored, therefore, the entire datafile essentially needs to be recreated from the blocks present in the backup piece.

An image copy of a datafile, on the other hand, is much faster to restore because the physical structure of the datafile already exists. Oracle 10g now permit image copies to be created at the database, tablespace, or datafile level through the new RMAN directive BACKUP AS COPY. For example, here is a command script to create image copies for all datafiles in the entire database:
Backup Procedure
Step 1 : Create a image copy of full database
RUN {
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'D:\oracle\rman\ora10G\U%';
BACKUP AS COPY DATABASE; }

Step 2 : Backup incrementally and restore to the image copy
RUN {
# Roll forward any available changes to image copy files
# From the previous set of incremental Level 1 backups
RECOVER COPY OF DATABASE WITH TAG 'cool';
# Create incremental level 1 backup of all datafiles in the database
# For roll-forward application against image copies
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'cool' DATABASE; }

Restore/recovery procedure
In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:
RMAN> alter database mount;
RMAN> switch database to copy;

If you want to restore the database
RMAN > restore database;
RMAN > recover database;

DBMS SCHEDULER

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g. The older job scheduler, DBMS_JOB, is still available, is easier to use in simple cases
Make sure the OracleJobScheduler Service is started.
Create a job
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name => 'myjob',
   job_type => 'EXECUTABLE',
   job_action => 'd:\oracle\script\vng.bat',
   repeat_interval => 'FREQ=MINUTELY',
   enabled => TRUE );
END;
Remove a job
EXEC DBMS_SCHEDULER.DROP_JOB('myjob');
Change job attributes
Examples:
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
BEGIN
   DBMS_SCHEDULER.SET_ATTRIBUTE ('WEEKNIGHT_WINDOW',    'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');END;/
Enable
/
Disable a job
BEGIN
   DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
   DBMS_SCHEDULER.DISABLE('myjob');
END;
Monitoring jobs
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'myjob';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'myjob';
Use user_scheduler_jobs and user_scheduler_job_log to only see jobs that belong to your user (current schema).

Friday, January 20, 2012

Oracle image copy

With Oracle 10g R2 we can recover datafile copies like we recover the real datafiles.This gives us the oportunity to recover the entire database without having to restore it from backup first.Which of course saves very valuable time in case of a disaster.

Backup Procedure
Step 1 : Create a image copy of full database
RUN {
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'D:\oracle\rman\ora10G\U%';
BACKUP AS COPY DATABASE; }

Step 2 : Backup incrementally and restore to the image copy
RUN {
# Roll forward any available changes to image copy files
# From the previous set of incremental Level 1 backups
RECOVER COPY OF DATABASE WITH TAG 'cool';
# Create incremental level 1 backup of all datafiles in the database
# For roll-forward application against image copies
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'cool' DATABASE; }

Restore/recovery procedure
In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:
RMAN> alter database mount;
RMAN>switch database to copy;

If you want to restore the database
RMAN > restore database;
RMAN > recover database;

Investigating high redo archive generation

If you have ASH, you can use it to find the sessions and queries that waited the most for “log file sync” event. I found that this has some correlation with the worse redo generators.view sourceprint?

find the sessions and queries causing most redo and when it happened

select SESSION_ID,user_id,sql_id,round(sample_time,'hh'),count(*)
from V$ACTIVE_SESSION_HISTORY
where event like 'log file sync'
AND SESSION_ID=506
group by SESSION_ID,user_id,sql_id,round(sample_time,'hh')
order by count(*) desc

you can look the the SQL itself by:

select * from DBA_HIST_SQLTEXT
where sql_id='dwbbdanhf7p4a'

Another ways from Oracle metalink is by checking block_changes

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 , 1, 2, 3, 4;

you can also check using this script if you are using 10g and above

SELECT dhso.object_name, sum(db_block_changes_delta)
FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj# AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date(’2012_01_19 18',’YYYY_MM_DD HH24')
AND to_date(’2012_01_19 19',’YYYY_MM_DD HH24')
GROUP BY dhso.object_name order by sum(db_block_changes_delta) desc

SELECT distinct dbms_lob.substr(sql_text,4000,1)
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE ‘%INT_UPLOAD_STATUS%’
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2;

Saturday, January 22, 2011

How to create SQL Server 2005 Clustering with Virtual Server 2005

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 1) - Balmukund - Site Home - MSDN Blogs

Tuesday, August 17, 2010

VirtualBox Commands

Using VirtualBox you can run multiple Virtual Machines (VMs) on a single server, allowing you to run both RAC nodes on a single machine. Here are some of the useful command to create a virtual SAN storage.

To set a UUID of a hard drive run this
> VBoxManage internalcommands setvdiuuid disk2.vdi

Create the disks and associate them with VirtualBox as virtual media.
> VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI --variant Fixed
Connect them to the VM.
> VBoxManage storageattach ol6-112-rac1 --storagectl "SATA" --port 1 --device 0 --type hdd \
--medium asm1.vdi --mtype shareable

To manually clone a virtual disk
> VBoxManage clonehd /u01/VirtualBox/ol6-112-rac1/ol6-112-rac1.vdi /u03/VirtualBox/ol6-112-rac2/ol6-112-rac2.vdi

High Availbility

OS & Virtualization

Tuesday, October 09, 2012

Applies to:

Goal

Fix