At OS prompt, Execute the following command to kill all ORACLE process
$ kill -9 `ps -ef |grep PROD |awk '{print $2}'`
Tuesday, December 9, 2008
Wednesday, December 3, 2008
RMAN Backup Validation Check
To Test and Check the Integrity for Backups
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The database prevents operations that result in unusable backup files or corrupt restored datafiles. The database server automatically does the following:
* Blocks access to datafiles while they are being restored or recovered
* Allows only one restore operation for each datafile at a time
* Ensures that incremental backups are applied in the correct order
* Stores information in backup files to allow detection of corruption
To Detect of Logical Block Corruption
--------------------------------------
This tests data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log
1. Start RMAN in nocatalog mode and connect to your database:
From the operating system prompt issue at database host :
$ rman target / nocatalog
2. From the RMAN> prompt issue the validate command with the "check logical"
clause:
The following example shows how to validate a single datafile:
run {
allocate channel d1 type disk;
backup check logical validate datafile 77;
release channel d1;
}
If you wish to monitor the progress of RMAN backup validate, you may issue this
query:
SQL> select sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork
/
3. Once the validate process is complete, you either check the alert log or a
view depending on the version of Oracle being used.
In Oracle9i and beyond you can query the view name V$DATABASE_BLOCK_CORRUPTION
to determine what corruption, if any, was found by RMAN.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The database prevents operations that result in unusable backup files or corrupt restored datafiles. The database server automatically does the following:
* Blocks access to datafiles while they are being restored or recovered
* Allows only one restore operation for each datafile at a time
* Ensures that incremental backups are applied in the correct order
* Stores information in backup files to allow detection of corruption
To Detect of Logical Block Corruption
--------------------------------------
This tests data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log
1. Start RMAN in nocatalog mode and connect to your database:
From the operating system prompt issue at database host :
$ rman target / nocatalog
2. From the RMAN> prompt issue the validate command with the "check logical"
clause:
The following example shows how to validate a single datafile:
run {
allocate channel d1 type disk;
backup check logical validate datafile 77;
release channel d1;
}
If you wish to monitor the progress of RMAN backup validate, you may issue this
query:
SQL> select sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork
/
3. Once the validate process is complete, you either check the alert log or a
view depending on the version of Oracle being used.
In Oracle9i and beyond you can query the view name V$DATABASE_BLOCK_CORRUPTION
to determine what corruption, if any, was found by RMAN.
Tuesday, December 2, 2008
Query to find All Background_processes in the Database
Background Processes
Background processes support various types of Oracle processing
Each version of Oracle has a set of associated background processes. Each background process has a descriptor in the fixed area of the SGA. Background process descriptors are of type ksbdp. The descriptors can be seen in the level 2 GLOBAL_AREA dump for example:
ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';
Every background process descriptor has an internal (structure) name e.g. ktmprc_ and an external (display) name e.g. SMON. A list of background process descriptors together with the external and internal names can be obtained using the following query:
SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;
Background processes support various types of Oracle processing
Each version of Oracle has a set of associated background processes. Each background process has a descriptor in the fixed area of the SGA. Background process descriptors are of type ksbdp. The descriptors can be seen in the level 2 GLOBAL_AREA dump for example:
ALTER SESSION SET EVENTS 'immediate trace name global_area level 2';
Every background process descriptor has an internal (structure) name e.g. ktmprc_ and an external (display) name e.g. SMON. A list of background process descriptors together with the external and internal names can be obtained using the following query:
SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;
how to change expired status of user accounts.txt
SQL> select username,account_status from dba_users where username='DIP';
USERNAME ACCOUNT_STATUS
----------------------- ---------------
SCOTT EXPIRED & LOCKED
SQL> alter user SCOTT account unlock;
User altered.
SQL> select username,account_status from dba_users where username='SCOTT';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED
SQL> select password from dba_users where username='SCOTT';
PASSWORD
------------------------------
CE4A36B8E06CA59C
SQL> alter user scott identified by values 'CE4A36B8E06CA59C';
User altered.
SQL> select username,account_status from dba_users where username='SCOTT';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT OPEN
USERNAME ACCOUNT_STATUS
----------------------- ---------------
SCOTT EXPIRED & LOCKED
SQL> alter user SCOTT account unlock;
User altered.
SQL> select username,account_status from dba_users where username='SCOTT';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT EXPIRED
SQL> select password from dba_users where username='SCOTT';
PASSWORD
------------------------------
CE4A36B8E06CA59C
SQL> alter user scott identified by values 'CE4A36B8E06CA59C';
User altered.
SQL> select username,account_status from dba_users where username='SCOTT';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT OPEN
Difference Between AFN & RFN
RELATIVE FILE NO.
Relative file no is the file no. generated which belongs to only one tablespace.
Every tablespace has its own datafiles will be referred by Relative file no.
So the relative file no. can be repeated in each tablespaces.
Eg; TAblespace users(TS#1) has 2 datafiles then Relative file no. REL file no. 1 and 2 for tablespace #1.
Tablespace userdata(TS#2) has 2 datafiles then Relative file no. will be 1 and 2 for tablespace # 2.
A tablespace can have only 1022 max datafiles .. so Relative file no will be 1022 for each tablespace max.
ABSOLUTE FILE NO.
Whereas Absolute file no is Datafile No. generated uniquely at database level.
Relative file no is the file no. generated which belongs to only one tablespace.
Every tablespace has its own datafiles will be referred by Relative file no.
So the relative file no. can be repeated in each tablespaces.
Eg; TAblespace users(TS#1) has 2 datafiles then Relative file no. REL file no. 1 and 2 for tablespace #1.
Tablespace userdata(TS#2) has 2 datafiles then Relative file no. will be 1 and 2 for tablespace # 2.
A tablespace can have only 1022 max datafiles .. so Relative file no will be 1022 for each tablespace max.
ABSOLUTE FILE NO.
Whereas Absolute file no is Datafile No. generated uniquely at database level.
Monday, November 24, 2008
How to use crontab to rsync CCM log and out files
About CRON
cron is a unix utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix.
Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.
Crontab Commands
export EDITOR=vi specify a editor to open crontab file
crontab -e edit/create crontab file
crontab -l display your crontab file.
crontab -r remove your crontab file.
crontab -v * display the last time crontab file was edited
* This option is only available on a few systems.
Crontab syntax
A crontab file has five fields for specifying day, date and time followed by the command to be run at that interval.
* * * * * command to be executed- - - - -| | | | || | | | +----- day of week (0 - 6) (Sunday=0)| | | +------- month (1 - 12)| | +--------- day of month (1 - 31)| +----------- hour (0 - 23)+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas.
An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .
Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30 18 * * * rm /home/someuser/tmp/*
Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * At 00:30 Hrs on 1st of Jan, June & Dec.
:
0 20 * 10 1-5 At 8.00 PM every weekday (Mon-Fri) only in Oct.
:
0 0 1,10,15 * * At midnight on 1st ,10th & 15th of month
:
5,10 0 10 * 1 At 12.05,12.10 every Monday & on 10th of every month
:
Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.
Workshop Task - Establish log and out File Synchronization
In the workshop, the requirement is to synchronise the concurrent manager “log” and “out” files from the Primary to the Standby machine, every 15 minutes.
In this example, we are using the following machines:-
machine01.uk.oracle.com is machine A (Primary machine)
machine02.uk.oracle.com is machine B (Standby machine)
On the Standby machine:
mkdir -p/log
mkdir -p/out
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/out
On the Primary Machine
mkdir -p/log
mkdir -p/out
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/out
From machine A
We will be pushing files from machine A every 30 minutes, on the hour and half hour, so our entries will be:
$ crontab -e
#
## Push log and out files from this machine to machine02 every 30 minutes, on the hour and half hour
#
00,30 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc oracle@machine02: /oracle/PROD/inst/apps/PROD_machine01/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_00-30.log
#
As crontab uses “vi” syntax, save changes using :wq!
From machine B
We will also be pushing files from machine B every 30 minutes, on the quarter hour and three quarter hour, so our entries will be:
#
## Push log and out from this machine to machine01 every 30 minutes at quarter past, and quarter to.
#
15,45 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc oracle@machine01:/oracle/PROD/inst/apps/PROD_machine02/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_15-45.log
#
cron is a unix utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix.
Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.
Crontab Commands
export EDITOR=vi specify a editor to open crontab file
crontab -e edit/create crontab file
crontab -l display your crontab file.
crontab -r remove your crontab file.
crontab -v * display the last time crontab file was edited
* This option is only available on a few systems.
Crontab syntax
A crontab file has five fields for specifying day, date and time followed by the command to be run at that interval.
* * * * * command to be executed- - - - -| | | | || | | | +----- day of week (0 - 6) (Sunday=0)| | | +------- month (1 - 12)| | +--------- day of month (1 - 31)| +----------- hour (0 - 23)+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas.
An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .
Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30 18 * * * rm /home/someuser/tmp/*
Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * At 00:30 Hrs on 1st of Jan, June & Dec.
:
0 20 * 10 1-5 At 8.00 PM every weekday (Mon-Fri) only in Oct.
:
0 0 1,10,15 * * At midnight on 1st ,10th & 15th of month
:
5,10 0 10 * 1 At 12.05,12.10 every Monday & on 10th of every month
:
Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.
Workshop Task - Establish log and out File Synchronization
In the workshop, the requirement is to synchronise the concurrent manager “log” and “out” files from the Primary to the Standby machine, every 15 minutes.
In this example, we are using the following machines:-
machine01.uk.oracle.com is machine A (Primary machine)
machine02.uk.oracle.com is machine B (Standby machine)
On the Standby machine:
mkdir -p
mkdir -p
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc/out
On the Primary Machine
mkdir -p
mkdir -p
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/log
mkdir -p /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc/out
From machine A
We will be pushing files from machine A every 30 minutes, on the hour and half hour, so our entries will be:
$ crontab -e
#
## Push log and out files from this machine to machine02 every 30 minutes, on the hour and half hour
#
00,30 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine01/logs/appl/conc oracle@machine02: /oracle/PROD/inst/apps/PROD_machine01/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_00-30.log
#
As crontab uses “vi” syntax, save changes using :wq!
From machine B
We will also be pushing files from machine B every 30 minutes, on the quarter hour and three quarter hour, so our entries will be:
#
## Push log and out from this machine to machine01 every 30 minutes at quarter past, and quarter to.
#
15,45 * * * * rsync -av /oracle/PROD/inst/apps/PROD_machine02/logs/appl/conc oracle@machine01:/oracle/PROD/inst/apps/PROD_machine02/logs/appl --rsync-path=/usr/bin/rsync >> /tmp/rsync_01_15-45.log
#
How to Setup SSH
On Primary Node: (Machine 1) Example: primary.oracle.com
- Log in as oracle
- Generate the ssh key by issuing the following command:
ssh-keygen -t rsa (then press enter 3 times)
$ ssh-keygen -t rsa
Generating public/private rsa key pair.
- Press the Enter key three times.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
5a:58:7e:65:ae:71:1a:82:2c:95:98:5b:48:b3:52:ac oracle@primary.oracle.com
- CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.
$ ls -lrt $HOME/.ssh/
total 24
-rw-r--r-- 1 oracle dba 948 Mar 3 10:57 known_hosts
-rw-r--r-- 1 oracle dba 240 Mar 4 10:15 id_rsa.pub
-rw------- 1 oracle dba 887 Mar 4 10:15 id_rsa
- Update authorized_keys file to enable ssh ‘loopback’
$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys
- Use SCP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.
$ scp $HOME/.ssh/id_rsa.pub oracle@stdby.oracle.com:/tmp
oracle@stdby.oracle.com's password:
id_rsa.pub 100% 240 0.2KB/s 00:00
On the Remote Node(s): Example: stdby.oracle.com
- Login as the oracle user
- Remove specialized .login, .profile or similar files which cause extra verbiage to be displayed to the screen.
- Create a directory called .ssh in the home directory
$ mkdir $HOME/.ssh
mkdir: cannot create directory `/home/oracle/.ssh': File exists
- Copy the content of /tmp/id_rsa.pub (copied from the Primary Node) into a new file named authorized_keys. (If you already have a file called authorized_keys in this directory, open it in vi, go to the bottom of the file, add one blank line and then copy and paste the contents of the file /tmp/id_rsa.pub into the existing file and save it.)
$ cat /tmp/id_rsa.pub >> $HOME/.ssh/authorized_keys
- Verify that the .ssh directory permission is set to 700
$ chmod 700 $HOME/.ssh
- Verify that the authorized_keys file permission is set to 600
$ chmod 600 $HOME/.ssh/authorized_keys
- Verify that the $HOME directory does not have write permissions for group and others.
$ chmod go-w $HOME
- Ensure that the ssh daemon is running
$ ps -ef |grep -i ssh
root 2418 22004 0 10:25 ? 00:00:00 sshd: oracle [priv]
oracle 2420 2418 0 10:25 ? 00:00:00 sshd: oracle@pts/1
oracle 2690 2421 0 10:37 pts/1 00:00:00 grep -i ssh
root 22004 1 0 Feb29 ? 00:00:00 /usr/sbin/sshd
Test the installation:
- Login to the PrimaryNode as oracle user.
- Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:
ssh -l [user] [SERVER]
$ uname -n
primary.oracle.com
$ ssh -l oracle primary (connect to same machine)
Last login: Tue Mar 4 10:41:52 2008 from primary.oracle.com
$ uname -n
primary.oracle.com
- When prompted to confirm the identity, respond "Yes".
- Disconnect from the server.
$ exit
Connection to stdby closed
$ ssh -l oracle stdby (connect to machine 2)
Last login: Tue Mar 4 10:41:52 2008 from primary.oracle.com
$ uname -n
stdby.oracle.com
- When prompted to confirm the identity, respond "Yes".
- Disconnect from the server.
$ exit
Connection to stdby closed
- Execute the following commands:
$ uname -n
primary.oracle.com
$ ssh oracle@stdby uname -n
stdby.oracle.com
The remote hostname should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:
- The content of the authorized_keys file contains the content of id_rsa.pub of the client
- The permissions of the authorized_keys file are set to 600.
- The permissions of the .ssh directory are set to 700.
The permissions of the $HOME directory do not include execute for group and other.
Now repeat the steps again as follows (to enable SSH in the opposite direction)
On Remote Node: (machine 2)
- Log in as oracle
- Generate the ssh key by issuing the following command:
ssh-keygen -t rsa (then press enter 3 times)
$ ssh-keygen -t rsa
Generating public/private rsa key pair.
- Press the Enter key three times.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
6c:7d:4c:5c:21:9d:d6:8e:1f:6d:9c:52:2f:88:1c:3d oracle@stdby.oracle.com
- CD into the .ssh directory, which is located under the home directory. Note that a file called id_rsa.pub is created.
$ ls -lrt $HOME/.ssh/
-rw-r--r-- 1 oracle dba 703 Feb 18 09:54 known_hosts
-rw-r--r-- 1 oracle dba 240 Mar 4 10:32 authorized_keys
-rw-r--r-- 1 oracle dba 240 Mar 4 10:49 id_rsa.pub
-rw------- 1 oracle dba 887 Mar 4 10:49 id_rsa
- Update authorized_keys file to enable ssh ‘loopback’
$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys
- Use SCP to copy id_rsa.pub to the /tmp directory of the Remote Nodes.
$ scp $HOME/.ssh/id_rsa.pub oracle@primary.oracle.com:/tmp
oracle@primary.oracle.com's password:
id_rsa.pub 100% 240 0.2KB/s 00:00
On the Primary Node: (Machine 1)
- Login as the oracle user
- Copy the content of /tmp/id_rsa.pub (copied from the Primary Node) into file named authorized_keys
$ cat /tmp/id_rsa.pub >> $HOME/.ssh/authorized_keys
- Verify that the .ssh directory permission is set to 700
$ chmod 700 $HOME/.ssh
- Verify that the authorized_keys file permission is set to 600
$ chmod 600 $HOME/.ssh/authorized_keys
- Verify that the $HOME directory does not have write permissions for group and others.
$ chmod go-w $HOME
- Ensure that the ssh daemon is running
$ ps -ef |grep -i ssh
root 2418 22004 0 10:25 ? 00:00:00 sshd: oracle [priv]
oracle 2420 2418 0 10:25 ? 00:00:00 sshd: oracle@pts/1
oracle 2690 2421 0 10:37 pts/1 00:00:00 grep -i ssh
root 22004 1 0 Feb29 ? 00:00:00 /usr/sbin/sshd
Test the installation:
- Login to the Remote Node as oracle user (Machine 2).
- Execute the following command, replacing [user] with the username on the Remote Node, and [SERVER] with the host name of the Remote Node:
ssh -l [user] [SERVER]
$ uname -n
stdby.oracle.com
$ ssh -l oracle stdby (connect to same machine)
Last login: Tue Mar 4 10:41:52 2008 from stdby.oracle.com
$ uname -n
stdby.oracle.com
$ exit
Connection to stdby closed
$ ssh -l oracle primary (connect to machine 1)
Last login: Tue Mar 4 10:41:52 2008 from stdby.oracle.com
$ uname -n
primary.oracle.com
- When prompted to confirm the identity, respond "Yes".
- Disconnect from the server.
$ exit
Connection to primary closed
- Execute the following commands:
$ uname -n
stdby.oracle.com
$ ssh oracle@primary uname -n
primary.oracle.com
The remote hostname should appear, followed by the prompt, and you should not be asked to enter and ID or password. If you are prompted for a password, verify the following:
- The content of the authorized_keys file contains the content of id_rsa.pub of the client
- The permissions of the authorized_keys file are set to 600.
- The permissions of the .ssh directory are set to 700.
- The permissions of the $HOME directory do not include execute for group and other.
Thursday, November 6, 2008
11g ASM New Features
The new features in Automatic Storage Management (ASM) extend the storage management automation, improve scalability, and further simplify management for Oracle Database files.
■ ASM Fast Mirror Resync
A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executed
after a failed disk has been repaired. The command first brings the disk online for
writes so that no new writes are missed. Subsequently, it initiates a copy of all extents
marked as stale on a disk from their redundant copies.
This feature significantly reduces the time it takes to repair a failed diskgroup,
potentially from hours to minutes. The repair time is proportional to the number of
extents that have been written to or modified since the failure.
■ ASM Manageability Enhancements
The new storage administration features for ASM manageability include the following:
■ New attributes for disk group compatibility
To enable some of the new ASM features, you can use two new disk group
compatibility attributes, compatible.rdbms and compatible.asm. These
attributes specify the minimum software version that is required to use disk
groups for the database and for ASM, respectively. This feature enables
heterogeneous environments with disk groups from both Oracle Database 10g and
Oracle Database 11g. By default, both attributes are set to 10.1. You must advance
these attributes to take advantage of the new features.
■ New ASM command-line utility (ASMCMD) commands and options
ASMCMD allows ASM disk identification, disk bad block repair, and backup and
restore operations in your ASM environment for faster recovery.
■ ASM fast rebalance
Rebalance operations that occur while a disk group is in RESTRICTED mode
eliminate the lock and unlock extent map messaging between ASM instances in
Oracle RAC environments, thus improving overall rebalance throughput.
This collection of ASM management features simplifies and automates storage
management for Oracle databases.
■ ASM Preferred Mirror Read
When ASM failure groups are defined, ASM can now read from the extent that is
closest to it, rather than always reading the primary copy. A new initialization
parameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administrator
specify a list of failure group names that contain the preferred read disks for each node
in a cluster.
In an extended cluster configuration, reading from a local copy provides a great
performance advantage. Every node can read from its local diskgroup (failure group),
resulting in higher efficiency and performance and reduced network traffic.
■ ASM Rolling Upgrade
Rolling upgrade is the ability of clustered software to function when one or more of
the nodes in the cluster are at different software versions. The various versions of the
software can still communicate with each other and provide a single system image.
The rolling upgrade capability will be available when upgrading from Oracle
Database 11g Release 1 (11.1).
This feature allows independent nodes of an ASM cluster to be migrated or patched
without affecting the availability of the database. Rolling upgrade provides higher
uptime and graceful migration to new releases.
■ ASM Scalability and Performance Enhancements
This feature increases the maximum data file size that Oracle can support to 128 TB.
ASM supports file sizes greater than 128 TB in any redundancy mode. This provides
near unlimited capacity for future growth. The ASM file size limits are:
■ External redundancy - 140 PB
■ Normal redundancy - 42 PB
■ High redundancy - 15 PB
Customers can also increase the allocation unit size for a disk group in powers of 2 up
to 64 MB.
■ Convert Single-Instance ASM to Clustered ASM
This feature provides support within Enterprise Manager to convert a non-clustered
ASM database to a clustered ASM database by implicitly configuring ASM on all
nodes. It also extends the single-instance to Oracle RAC conversion utility to support
standby databases.
Simplifying the conversion makes it easier for customers to migrate their databases
and achieve the benefits of scalability and high availability provided by Oracle RAC.
■ New SYSASM Privilege for ASM Administration
This feature introduces the new SYSASM privilege to allow for separation of database
management and storage management responsibilities.
The SYSASM privilege allows an administrator to manage the disk groups that can be
shared by multiple databases. The SYSASM privilege provides a clear separation of
duties from the SYSDBA privilege.
For complete list of 11g ASM New Feature, You can refer the following
Note 551205.1 11g ASM New Features Technical White Paper
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-asm.html
■ ASM Fast Mirror Resync
A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executed
after a failed disk has been repaired. The command first brings the disk online for
writes so that no new writes are missed. Subsequently, it initiates a copy of all extents
marked as stale on a disk from their redundant copies.
This feature significantly reduces the time it takes to repair a failed diskgroup,
potentially from hours to minutes. The repair time is proportional to the number of
extents that have been written to or modified since the failure.
■ ASM Manageability Enhancements
The new storage administration features for ASM manageability include the following:
■ New attributes for disk group compatibility
To enable some of the new ASM features, you can use two new disk group
compatibility attributes, compatible.rdbms and compatible.asm. These
attributes specify the minimum software version that is required to use disk
groups for the database and for ASM, respectively. This feature enables
heterogeneous environments with disk groups from both Oracle Database 10g and
Oracle Database 11g. By default, both attributes are set to 10.1. You must advance
these attributes to take advantage of the new features.
■ New ASM command-line utility (ASMCMD) commands and options
ASMCMD allows ASM disk identification, disk bad block repair, and backup and
restore operations in your ASM environment for faster recovery.
■ ASM fast rebalance
Rebalance operations that occur while a disk group is in RESTRICTED mode
eliminate the lock and unlock extent map messaging between ASM instances in
Oracle RAC environments, thus improving overall rebalance throughput.
This collection of ASM management features simplifies and automates storage
management for Oracle databases.
■ ASM Preferred Mirror Read
When ASM failure groups are defined, ASM can now read from the extent that is
closest to it, rather than always reading the primary copy. A new initialization
parameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administrator
specify a list of failure group names that contain the preferred read disks for each node
in a cluster.
In an extended cluster configuration, reading from a local copy provides a great
performance advantage. Every node can read from its local diskgroup (failure group),
resulting in higher efficiency and performance and reduced network traffic.
■ ASM Rolling Upgrade
Rolling upgrade is the ability of clustered software to function when one or more of
the nodes in the cluster are at different software versions. The various versions of the
software can still communicate with each other and provide a single system image.
The rolling upgrade capability will be available when upgrading from Oracle
Database 11g Release 1 (11.1).
This feature allows independent nodes of an ASM cluster to be migrated or patched
without affecting the availability of the database. Rolling upgrade provides higher
uptime and graceful migration to new releases.
■ ASM Scalability and Performance Enhancements
This feature increases the maximum data file size that Oracle can support to 128 TB.
ASM supports file sizes greater than 128 TB in any redundancy mode. This provides
near unlimited capacity for future growth. The ASM file size limits are:
■ External redundancy - 140 PB
■ Normal redundancy - 42 PB
■ High redundancy - 15 PB
Customers can also increase the allocation unit size for a disk group in powers of 2 up
to 64 MB.
■ Convert Single-Instance ASM to Clustered ASM
This feature provides support within Enterprise Manager to convert a non-clustered
ASM database to a clustered ASM database by implicitly configuring ASM on all
nodes. It also extends the single-instance to Oracle RAC conversion utility to support
standby databases.
Simplifying the conversion makes it easier for customers to migrate their databases
and achieve the benefits of scalability and high availability provided by Oracle RAC.
■ New SYSASM Privilege for ASM Administration
This feature introduces the new SYSASM privilege to allow for separation of database
management and storage management responsibilities.
The SYSASM privilege allows an administrator to manage the disk groups that can be
shared by multiple databases. The SYSASM privilege provides a clear separation of
duties from the SYSDBA privilege.
For complete list of 11g ASM New Feature, You can refer the following
Note 551205.1 11g ASM New Features Technical White Paper
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-asm.html
Tuesday, November 4, 2008
How to Start the CSS Process ,ASM & DB Instances Services On Windows using Command Prompt.
1. To start the services for CSS, ASM & DB Instances:
3. To stop the above services
C:\> net start oraclecsservice
C:\> net start OracleASMService+ASM
C:\> net start OracleServiceDBAASMW
2.Check whether above services are running using following command :C:\> net start
Example as shows:
C:\> net start
These Windows services are started: OracleASMService+ASM
OracleCSService
OracleServiceDBAASMW3. To stop the above services
C:\> net stop OracleServiceDBAASMW
C:\> net stop OracleASMService+ASM
C:\> net stop oraclecsservice
Oracle Database 11g Top New Features : Summary
Oracle Database 11g Top New Features : Summary
1) Automatic Diagnostic Repository [ADR]
2) Database Replay
3) Automatic Memory Tuning
4) Case sensitive password
5) Virtual columns and indexes
6) Interval Partition and System Partition
7) The Result Cache
8) ADDM RAC Enhancements
9) SQL Plan Management and SQL Plan Baselines
10) SQL Access Advisor & Partition Advisor
11) SQL Query Repair Advisor
12) SQL Performance Analyzer (SPA) New
13) DBMS_STATS Enhancements
14) The Result Cache
15) Total Recall (Flashback Data Archive)
Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently
Oracle 11g Database DBA New Features with brief explanation
==========================================
# Database Capture/replay database workloads :
This allows the total database workload to be captured, transferred to a test database
create from a backup or standby database, then replayed to test the affects of an upgrade or
system change. Currently, these are working to a capture performance overhead of 5%,
so this will capture real production workloads
# Automatic Memory Tuning:
Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was already
introduced in Oracle 10g. But In 11g, all memory can be tuned automatically by setting one
parameter. We can literally tell Oracle how much memory it has and it determines how
much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set
# Interval partitioning for tables :
Interval partitions are extensions to range partitioning. These provide automation for
equi-sized range partitions. Partitions are created as metadata and only the start partition is
made persistent. The additional segments are allocated as the data arrives. The additional
partitions and local indexes are automatically created.
# Feature Based Patching:
All one-off patches will be classified as to which feature they affect. This allows you to easily
identify which patches are necessary for the features you are using. EM will allow you to
subscribe to a feature based patching service, so EM automatically scans for available
patches for the features you are using
# RMAN UNDO bypass :
Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of
useless information. Now rman can bypass those types of tablespace. Great for exporting a
tablespace from backup.
# Virtual columns/indexes :
User can create Virtual index on table. This Virtual index is not visible to optimizer, so it will
not affect performance, Developer can user HINT and see is Index is useful or not.Invisible
Indexesprevent premature use of newly created indexes
# New default audit settings :
Oracle database where general database auditing was "off" by default, logging is intended
to be enabled by default with the Oracle Database 11g beta secure configuration. Notable
performance improvements are planned to be introduced to reduce the performance
degradation typically associated with auditing.
# Case sensitive password :
Passwords are expected to also become case sensitive This and other changes should result
in better protection against password guessing scenarios. For
example, in addition to limiting the number of failed login attempts to 10 (default
configuration in 10gR2), Oracle 11g beta’s planned default settings should expire
passwords every 180 days, and limit to seven the number of times a user can login with an
expired password before disabling access.
# Faster DML triggers : Create a disabled trigger; specify trigger firing order
# Fine grained access control for Utl_TCP:
in 10g all port are available, now it is controlled.
# Data Guard supports "Flashback Standby"
# New Trigger features
# Partitioning by logical object and automated partition creation.
# LOB's - New high-performance LOB features.
# New Oracle11g Advisors
# Enhanced Read only tables
# Table trigger firing order
# Enhanced Index rebuild online : - Online index build with NO pause to DML.
# No recompilation of dependent objects:- When
A) Columns added to tables
B) Procedures added to packages
# Improved optimizer statistics collection speed
# Online index build with NO pause to DML
# Read only table :-
alter table t read only
alter table t read write
Oracle 11g Database SQL/PL-SQL New Features
----------------------------------------------
> Fine Grained Dependency Tracking:
In 11g we track dependencies at the level of element within unit. so that these changes have
no consequence
• Transparent performance improvement
•Unnecessary recompilation certainly consumes CPU
create table t(a number)
create view v as select a from t
alter table t add(Unheard_Of number)
select status from User_Objectswhere Object_Name = 'V'
- -----
VALID
No recompilation of dependent objects when Columns added to tables OR Procedures
added to packages
> Named and Mixed Notation from SQL:
select fun(P4=>10) from DUAL
In 10g not possible to call function in select statment by passing 4th parameter,
but in 11g it is possible
> PL/SQL "continue" keyword - It is same as we read in c/c++ loop
> Support for “super”: It is same "super" in Java.
> Powerfull Regular Expression:
Now we can access data between TAGS like data between tags
The new built-in REGEXP_COUNT returns the number of times the pattern is matched in the
input string.
> New table Data Type "simple_integer"
> SQL Performance Analyzer(SPA) :
It is same as Database replay except it not capture all transaction.The SQL Performance
Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA
provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a
performance baseline before a major database or system change, make the desired change
to the system, and then replay the SQL workload against the modified database or
configuration. The before and after performance of the SQL workload can then be compared
with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that
are now performing poorly and tune them via the SQL Tuning Advisor
> Caching The Results with /*+ result_cache */ :
select /*+ result_cache */ * from my_table, New for Oracle 11g, the result_cache hint caches
the result set of a select statement. This is similar to alter table table_name cache,but as you
can adding predicates makes /*+ result_cache */ considerably more powerful by caching a
subset of larger tables and common queries.
select /*+ result_cache */ col1, col2, col3 from my_table where colA = :B1
> The compound trigger :
A compound trigger lets you implement actions for each of the table DML timing points in a
single trigger
> PL/SQL unit source can exceeds 32k characters
> Easier to execute table DDL operations online:
Option to wait for active DML operations instead of aborting
> Fast add column with default value:
Does not need to updateall rows to default value.
Oracle 11g Database Backup & Recovery New Features
------------------------------------------------
* Enhanced configuration of archive deletion policies Archive can be deleted , if it is not
need DG , Streams Flashback etc When you CONFIGURE an archived log deletion policy
applies to all archiving destinations, including the flash recovery area. BACKUP ... DELETE
INPUT and DELETE... ARCHIVELOG use this configuration, as does the flash recovery area.
When we back up the recovery area, RMAN can fail over to other archived redo log
destinations if the flash recovery area is inaccessible.
* Configuring backup compression:
In 11g can use CONFIGURE command to choose between the BZIP2 and ZLIB compression
algorithms for RMAN backups.
* Active Database Duplication:
Now DUPLICATE command is network aware i.e.we can create a duplicate or standby
database over the network without taking backup or using old backup.
* Parallel backup and restore for very large files:
RMAN Backups of large data files now use multiple parallel server processes to efficiently
distribute theworkload for each file. This features improves the performance of backups.
* Improved block media recovery performance:
RECOVER command can recover individual data blocks.
RMAN take older, uncorrupted blocks from flashback and the RMAN can use these blocks,
thereby speeding up block media recovery.
* Fast incremental backups on physical standby database:
11g has included new feature of enable block change tracking on a physical standby
database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING SQL statement).
This new 11g feature enables faster incremental backups on a physical standby database
than in previous releases.because RMAN identifywe the changed blocks sincethe last
incremental backup.
11g ASM New Features
-----------------------
The new features in Automatic Storage Management (ASM) extend the storage
management automation, improve scalability, and further simplify management for
Oracle Database files.
■ ASM Fast Mirror Resync
A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executed
after a failed disk has been repaired. The command first brings the disk online for
writes so that no new writes are missed. Subsequently, it initiates a copy of all extents
marked as stale on a disk from their redundant copies.
This feature significantly reduces the time it takes to repair a failed diskgroup,
potentially from hours to minutes. The repair time is proportional to the number of
extents that have been written to or modified since the failure.
■ ASM Manageability Enhancements
The new storage administration features for ASM manageability include the following:
■ New attributes for disk group compatibility
To enable some of the new ASM features, you can use two new disk group
compatibility attributes, compatible.rdbms and compatible.asm. These
attributes specify the minimum software version that is required to use disk
groups for the database and for ASM, respectively. This feature enables
heterogeneous environments with disk groups from both Oracle Database 10g and
Oracle Database 11g. By default, both attributes are set to 10.1. You must advance
these attributes to take advantage of the new features.
■ New ASM command-line utility (ASMCMD) commands and options
ASMCMD allows ASM disk identification, disk bad block repair, and backup and
restore operations in your ASM environment for faster recovery.
■ ASM fast rebalance
Rebalance operations that occur while a disk group is in RESTRICTED mode
eliminate the lock and unlock extent map messaging between ASM instances in
Oracle RAC environments, thus improving overall rebalance throughput.
This collection of ASM management features simplifies and automates storage
management for Oracle databases.
■ ASM Preferred Mirror Read
When ASM failure groups are defined, ASM can now read from the extent that is
closest to it, rather than always reading the primary copy. A new initialization
parameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administrator
specify a list of failure group names that contain the preferred read disks for each node
in a cluster.
In an extended cluster configuration, reading from a local copy provides a great
performance advantage. Every node can read from its local diskgroup (failure group),
resulting in higher efficiency and performance and reduced network traffic.
■ ASM Rolling Upgrade
Rolling upgrade is the ability of clustered software to function when one or more of
the nodes in the cluster are at different software versions. The various versions of the
software can still communicate with each other and provide a single system image.
The rolling upgrade capability will be available when upgrading from Oracle
Database 11g Release 1 (11.1).
This feature allows independent nodes of an ASM cluster to be migrated or patched
without affecting the availability of the database. Rolling upgrade provides higher
uptime and graceful migration to new releases.
■ ASM Scalability and Performance Enhancements
This feature increases the maximum data file size that Oracle can support to 128 TB.
ASM supports file sizes greater than 128 TB in any redundancy mode. This provides
near unlimited capacity for future growth. The ASM file size limits are:
■ External redundancy - 140 PB
■ Normal redundancy - 42 PB
■ High redundancy - 15 PB
Customers can also increase the allocation unit size for a disk group in powers of 2 up
to 64 MB.
■ Convert Single-Instance ASM to Clustered ASM
This feature provides support within Enterprise Manager to convert a non-clustered
ASM database to a clustered ASM database by implicitly configuring ASM on all
nodes. It also extends the single-instance to Oracle RAC conversion utility to support
standby databases.
Simplifying the conversion makes it easier for customers to migrate their databases
and achieve the benefits of scalability and high availability provided by Oracle RAC.
■ New SYSASM Privilege for ASM Administration
This feature introduces the new SYSASM privilege to allow for separation of database
management and storage management responsibilities.
The SYSASM privilege allows an administrator to manage the disk groups that can be
shared by multiple databases. The SYSASM privilege provides a clear separation of
duties from the SYSDBA privilege.
For complete list of 11g ASM New Feature, You can refer the following
Note 551205.1 11g ASM New Features Technical White Paper
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-asm.html
For Complete list of 11g New Features, You can refer to the following link
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html
Subscribe to:
Posts (Atom)