Prior to 10g, dropping a database consisted of shutting the instance down and manually removing its associated datafiles, logfiles, controlfiles and associated init.ora file at operating system level. You might also want to remove any reference to it in /etc/oratab or /var/opt/oracle/oratab.
It could be a problem to get rid of all the Backup information in the RMAN CATALOG after you had deleted a database with DBCA for instance. You had to manually remove all the records from the catalog.
Starting with Oracle 10gR1 we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG.
Here is a demo how it works:
Login to the target database server and make sure you export the correct ORACLE_SID
$Export ORACLE_SID=target_db_name
$ rman target / catalog rman_user@rman_db
RMAN> startup mount
RMAN> sql ‘alter system enable restricted session’;
RMAN> drop database including backups;
Database name is “ORCL” and DBID is 1114494726
Do you really want to drop all backups and the database (enter YES or NO)? Yes
…
database name is “ORCL” and DBID is 1114494726
database dropped
database name is “ORCL” and DBID is 1114494726
database unregistered from the recovery catalog
RMAN> exit
RMAN will not delete the entry in the /etc/oratab, so we have to do this either by editing /etc/oratab or by using dbca to delete the service
Feb 5, 2010
Feb 4, 2010
Raw Devices and Oracle - 20 Common Questions and Answers
1. What is a raw device?
A raw device, also known as a raw partition, is a disk partition that is
not mounted and written to via the UNIX filesystem, but is accessed via
a character-special device driver; it is up to the application how the
data is written, since there is no filesystem to do this on the
application's behalf.
2. How can a raw device be recognized?
In the /dev directory, there are essentially two type of files: block
special and character special. Block special files are used when data is
transferred to or from a device in fixed size amounts (blocks), whereas
character special files are used when data is transferred in varying
size amounts. Raw devices use character special files; a long listing
of the /dev directory shows them with a 'c' at the leftmost position of
the permissions field, e.g.
crw-rw-rw- 1 root system 15, 0 Mar 12 09:45 rfd0
In addition, character special files usually have names beginning with
an 'r', as shown in the above example. Some devices, principally disks,
have both a block special device and a character special device
associated with them; for the floppy diskette shown above, there is also
a device
brw-rw-rw- 1 root system 15, 0 Apr 16 15:42 /dev/fd0
So the presence of a 'c' in a device does NOT necessarily mean this is a
raw device suitable for use by Oracle (or another application).
Generally, a raw device needs to be created and set aside for Oracle (or
whatever application is going to use it) when the UNIX system is set
up - therefore, this needs to be done with close co-operation between
the DBA and UNIX system administrator.
Once a raw device is in use by Oracle, it must be owned by the oracle
account, and may be identified in this way.
3. What are the benefits of raw devices?
There can be a performance benefit from using raw devices, since a write
to a raw device bypasses the UNIX buffer cache; the data is transferred
direct from the Oracle buffer cache to the disk. This is not guaranteed,
though; if there is no I/O bottleneck, raw devices will not help. The
performance benefit if there is a bottleneck can vary between a few
percent to something like 40%. Note that the overall amount of I/O is
not reduced; it is just done more efficiently.
Another, lesser, benefit of raw devices is that no filesystem overhead
is incurred, in terms of inode allocation and maintenance, or free block
allocation and maintenance.
4. How can I tell if I will benefit from using raw devices?
There are two distinct parts to this: first, the Oracle database and application
should be examined and tuned as necessary, using one or both of the following:
Server Manager or SQLDBA "monitor fileio"
UTLBstat and UTLestat utilities (in $ORACLE_HOME/rdbms/admin)
After checking your Oracle database and application, the next stage is to identify
UNIX-level I/O bottlenecks. This can be done using a UNIX utility such as sar or
vmstat. See the relevant manual pages for details.
If you identify that there is a UNIX-level problem with I/O, now is the
time to start using raw devices. This may well require reorganisation of
the entire UNIX system (assuming there are no spare partitions
available).
5. Are there circumstances when raw devices have to be used?
Yes. If you are using the Oracle Parallel Server, all data files,
control files and redo log files must be placed on raw partitions so
they can be shared between nodes. This is a limitation with the UNIX
operating system. Also, if you wish to use List I/O or Asynchronous I/O,
some versions of UNIX require the data files and control files to be on
raw devices for this to work. Consult your platform-specific
documentation for details.
6. Can I use the entire raw partition for Oracle?
No. You should specify a tablespace slightly smaller in size than the
raw partition size, specifically at least two Oracle block sizes
smaller.
7. Can I use the first partition of a disk for a raw device?
This is not recommended. On older versions of UNIX, the first partition
contained such information as the disk partition table or logical volume
control information, which if overwritten could render the disk useless.
More recent UNIX versions do not have this problem, as disk management
is done in a more sophisticated manner; consult your operating system
vendor for more details, but if in any doubt do not use the first
partition.
8. Who to own the raw device?
You will need to create the raw devices as root, but the ownership
should be changed to the oracle account afterwards. The group must also
be changed to the dba group (usually called dba).
9. How do I specify a raw device in Oracle commands?
Enclose the full pathname in single quotes, e.g. if there are two raw
devices, each 30Mb in size, and the database has a 4K block size, the
relevant command would look like this:
Create tablespace raw_tabspace datafile '/dev/raw1' size 30712K
datafile '/dev/raw2' size 30712K
10. Does the Oracle block size have any relevance on a raw device?
It is of less importance than for a UNIX file; the size of the Oracle
block can be changed, but it must be a multiple of the physical block
size, as it is only possible to seek to physical block boundaries, and
hence write only in multiples of the physical block size.
11. How can I back up my database files if they are on raw devices?
You cannot use utilities such as tar or cpio, which expect a filesystem
to be present. You must use the dd command, as follows:
dd if=/dev/raw1 of=/dev/rmt0 bs=16k
See the UNIX man page on dd for further details.
It is also possible to copy the raw device file (using dd) to a normal
UNIX file, and then use a utility such as tar or cpio, but this
requires more disk space and has a greater administrative overhead.
12. Providing I am not using Parallel Server, can I use a mixture of raw partitions
and filesystem files for my tablespace locations?
Yes. The drawback is that this makes your backup strategy more
complicated.
13. Should I store my redo log files on raw partitions?
Redo logs are particularly suitable candidates for being located on raw
partitions, as they are write-intensive and in addition are written to
sequentially. If Parallel Server is being used, redo logs must be
stored on raw partitions.
14. Can I use raw partitions for archive logs?
No. Archive logs must be stored on a partition with a UNIX file system.
15. Can I have more than one data file on a raw partition?
No. This means you should be careful when setting up the raw
partition: too small a size will necessitate reorganization when you
run out of space, whereas too large a size will waste any space the
file does not use.
16. Should my raw partitions be on the same disk device?
This is inadvisable, as there is likely to be contention. You should
place raw devices on different disks, which should also be on different
controllers.
17. Do I need to make my raw partitions all the same size?
This is not essential, but it provides flexibility in the event of
having to change the database configuration.
18. Do I need to change any UNIX kernel parameters if I decide to use raw devices?
No, but you may wish to reduce the size of the UNIX buffer cache if no
other applications are using the machine.
19. What other UNIX-level changes could help to improve I/O performance?
RAID and disk mirroring can be beneficial, depending on the application
characteristics, especially whether it is read or write-intensive, or a
mixture.
20. How can I gain further performance benefits, after considering all the above?
You will need to buy more disk drives and controllers for your system,
to spread the I/O load between devices.
A raw device, also known as a raw partition, is a disk partition that is
not mounted and written to via the UNIX filesystem, but is accessed via
a character-special device driver; it is up to the application how the
data is written, since there is no filesystem to do this on the
application's behalf.
2. How can a raw device be recognized?
In the /dev directory, there are essentially two type of files: block
special and character special. Block special files are used when data is
transferred to or from a device in fixed size amounts (blocks), whereas
character special files are used when data is transferred in varying
size amounts. Raw devices use character special files; a long listing
of the /dev directory shows them with a 'c' at the leftmost position of
the permissions field, e.g.
crw-rw-rw- 1 root system 15, 0 Mar 12 09:45 rfd0
In addition, character special files usually have names beginning with
an 'r', as shown in the above example. Some devices, principally disks,
have both a block special device and a character special device
associated with them; for the floppy diskette shown above, there is also
a device
brw-rw-rw- 1 root system 15, 0 Apr 16 15:42 /dev/fd0
So the presence of a 'c' in a device does NOT necessarily mean this is a
raw device suitable for use by Oracle (or another application).
Generally, a raw device needs to be created and set aside for Oracle (or
whatever application is going to use it) when the UNIX system is set
up - therefore, this needs to be done with close co-operation between
the DBA and UNIX system administrator.
Once a raw device is in use by Oracle, it must be owned by the oracle
account, and may be identified in this way.
3. What are the benefits of raw devices?
There can be a performance benefit from using raw devices, since a write
to a raw device bypasses the UNIX buffer cache; the data is transferred
direct from the Oracle buffer cache to the disk. This is not guaranteed,
though; if there is no I/O bottleneck, raw devices will not help. The
performance benefit if there is a bottleneck can vary between a few
percent to something like 40%. Note that the overall amount of I/O is
not reduced; it is just done more efficiently.
Another, lesser, benefit of raw devices is that no filesystem overhead
is incurred, in terms of inode allocation and maintenance, or free block
allocation and maintenance.
4. How can I tell if I will benefit from using raw devices?
There are two distinct parts to this: first, the Oracle database and application
should be examined and tuned as necessary, using one or both of the following:
Server Manager or SQLDBA "monitor fileio"
UTLBstat and UTLestat utilities (in $ORACLE_HOME/rdbms/admin)
After checking your Oracle database and application, the next stage is to identify
UNIX-level I/O bottlenecks. This can be done using a UNIX utility such as sar or
vmstat. See the relevant manual pages for details.
If you identify that there is a UNIX-level problem with I/O, now is the
time to start using raw devices. This may well require reorganisation of
the entire UNIX system (assuming there are no spare partitions
available).
5. Are there circumstances when raw devices have to be used?
Yes. If you are using the Oracle Parallel Server, all data files,
control files and redo log files must be placed on raw partitions so
they can be shared between nodes. This is a limitation with the UNIX
operating system. Also, if you wish to use List I/O or Asynchronous I/O,
some versions of UNIX require the data files and control files to be on
raw devices for this to work. Consult your platform-specific
documentation for details.
6. Can I use the entire raw partition for Oracle?
No. You should specify a tablespace slightly smaller in size than the
raw partition size, specifically at least two Oracle block sizes
smaller.
7. Can I use the first partition of a disk for a raw device?
This is not recommended. On older versions of UNIX, the first partition
contained such information as the disk partition table or logical volume
control information, which if overwritten could render the disk useless.
More recent UNIX versions do not have this problem, as disk management
is done in a more sophisticated manner; consult your operating system
vendor for more details, but if in any doubt do not use the first
partition.
8. Who to own the raw device?
You will need to create the raw devices as root, but the ownership
should be changed to the oracle account afterwards. The group must also
be changed to the dba group (usually called dba).
9. How do I specify a raw device in Oracle commands?
Enclose the full pathname in single quotes, e.g. if there are two raw
devices, each 30Mb in size, and the database has a 4K block size, the
relevant command would look like this:
Create tablespace raw_tabspace datafile '/dev/raw1' size 30712K
datafile '/dev/raw2' size 30712K
10. Does the Oracle block size have any relevance on a raw device?
It is of less importance than for a UNIX file; the size of the Oracle
block can be changed, but it must be a multiple of the physical block
size, as it is only possible to seek to physical block boundaries, and
hence write only in multiples of the physical block size.
11. How can I back up my database files if they are on raw devices?
You cannot use utilities such as tar or cpio, which expect a filesystem
to be present. You must use the dd command, as follows:
dd if=/dev/raw1 of=/dev/rmt0 bs=16k
See the UNIX man page on dd for further details.
It is also possible to copy the raw device file (using dd) to a normal
UNIX file, and then use a utility such as tar or cpio, but this
requires more disk space and has a greater administrative overhead.
12. Providing I am not using Parallel Server, can I use a mixture of raw partitions
and filesystem files for my tablespace locations?
Yes. The drawback is that this makes your backup strategy more
complicated.
13. Should I store my redo log files on raw partitions?
Redo logs are particularly suitable candidates for being located on raw
partitions, as they are write-intensive and in addition are written to
sequentially. If Parallel Server is being used, redo logs must be
stored on raw partitions.
14. Can I use raw partitions for archive logs?
No. Archive logs must be stored on a partition with a UNIX file system.
15. Can I have more than one data file on a raw partition?
No. This means you should be careful when setting up the raw
partition: too small a size will necessitate reorganization when you
run out of space, whereas too large a size will waste any space the
file does not use.
16. Should my raw partitions be on the same disk device?
This is inadvisable, as there is likely to be contention. You should
place raw devices on different disks, which should also be on different
controllers.
17. Do I need to make my raw partitions all the same size?
This is not essential, but it provides flexibility in the event of
having to change the database configuration.
18. Do I need to change any UNIX kernel parameters if I decide to use raw devices?
No, but you may wish to reduce the size of the UNIX buffer cache if no
other applications are using the machine.
19. What other UNIX-level changes could help to improve I/O performance?
RAID and disk mirroring can be beneficial, depending on the application
characteristics, especially whether it is read or write-intensive, or a
mixture.
20. How can I gain further performance benefits, after considering all the above?
You will need to buy more disk drives and controllers for your system,
to spread the I/O load between devices.
Change database name using DBNEWID utility
Make a whole database backup before changing the database name.
Invoke SQL*Plus and connect as a user with SYSDBA privileges.
$sqlplus / as sysdba
Issue the following query to determine the current database name:
SQL>SELECT name FROM v$database;
Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL.
SQL>SHUTDOWN IMMEDIATE
Start the instance and mount the database.
SQL>STARTUP MOUNT
Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify the DBNAME parameter and supply your new database name. You must also specify the YES value for the SETNAME parameter to indicate that only the DBNAME is to be changed. DBNEWID performs validations of the headers of the control files, but not the data files, before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, and exits. After DBNEWID completes successfully, the database is left mounted but is not yet usable.
$nid TARGET=username/password@alias DBNAME=newdbname SETNAME=YES LOGFILE=db_rename.log
After DBNEWID successfully changes the database name, shut down the instance:
SQL>SHUTDOWN IMMEDIATE
Change the DB_NAME initialization parameter to your new database name.
$vi init$ORACLE_SID.ora
$DB_NAME=new_db_name
Create a new password file using the ORAPWD utility:
$orapwd file=orapw password= entries=
Start the instance and open the database:
SQL>STARTUP
Verify the change to the database name by issuing the following query:
SQL>SELECT name FROM v$database;
We can check the options used for DBNEWID from below command
$nid help=y
Jan 12, 2010
Create 10g ASM instance manually
- Create partitions to use for ASM using "fdisk" command
- #fdisk /dev/sda
- #fdisk -l /dev/sda
- Create ASM disks using the partitions we created in the above step.
- #/etc/init.d/oracleasm createdisk VOL1 /dev/sda1
- #/etc/init.d/oracleasm createdisk VOL2 /dev/sda2
- Scan for the ASM disks
- #/etc/init.d/oracleasm scandisks
- List the ASM disks
- #/etc/init.d/oracleasm listdisks
- To create ASM CSSD process must be up and running.
- #$ORACLE_HOME/bin/localconfig all
- #ps -ef|grep -i css
- Create admin directories for ASM instance.
- mkdir -p $ORACLE_BASE/admin/+ASM/bdump
- mkdir -p $ORACLE_BASE/admin/+ASM/cdump
- mkdir -p $ORACLE_BASE/admin/+ASM/hdump
- mkdir -p $ORACLE_BASE/admin/+ASM/pfile
- mkdir -p $ORACLE_BASE/admin/+ASM/udump
- Create pfile(init+ASM.ora) using the below parameters
- background_dump_dest=/u01/app/oracle/admin/+ASM/bdump
- core_dump_dest=/u01/app/oracle/admin/+ASM/cdump
- user_dump_dest=/u01/app/oracle/admin/+ASM/udump
- Instance_name=+ASM
- instance_type=asm
- compatible=10.2.0
- large_pool_size=20M
- remote_login_passwordfile=exclusive
- Start the ASM instance
- Export ORACLE_SID=+ASM
- Sqlplus / as sysdba
- Sql>Startup
- Create spfile for the ASM instance.
- Sql>create spfile='$ORACLE_HOME/dbs/spfile+ASM.ora' from pfile='$ORACLE_HOME/init+ASM.ora';
- Shutdown immediate
- Startup the instance using the spfile.
Subscribe to:
Posts (Atom)