set define [SQL*Plus]
set define on
set define off
set define x
set define x
set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).
set define +
select * from dba_objects
where object_name like '%+object_name%';
set define on
Turns on substitution variables.
set define on
select '&hello' from dual;
If define is set to on and SQL*Plus finds the current substituion prefix, it asks for a string to be entered. In the following example, I entered: this string was entered
Enter value for hello: this string was entered
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
'THISSTRINGWASENTERED'
-----------------------
this string was entered
It might be annoying to see the following lines printed by SQL*Plus:
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
This behavior can be turned off by setting verify off.
set define off
Turns off substitution variables.
set define off
select '&hello' from dual;
'&HELL
------
&hello
miércoles, 22 de julio de 2009
martes, 14 de julio de 2009
optimal
The following is an example of how to resize the online log groups:
NOTE: Examples are given for 9i and higher. In prior releases, you needed
to use Server Manager and connect as the internal user.
1. First see the size of the current logs:
> sqlplus /nolog
SQL> connect / as sysdba
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE
Logs are 1MB from above, let's size them to 10MB.
2. Retrieve all the log member names for the groups:
SQL> select group#, member from v$logfile;
GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf
3. In older versions of the database you needed to shutdown and issue the following
commands in restricted mode. You can still do this, but the database can be online
to perform these changes.
Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
size:
SQL> alter database add logfile group 4
'/usr/oracle/dbs/log4PROD.dbf' size 10M;
SQL> alter database add logfile group 5
'/usr/oracle/dbs/log5PROD.dbf' size 10M;
SQL> alter database add logfile group 6
'/usr/oracle/dbs/log6PROD.dbf' size 10M;
4. Now run a query to view the v$log status:
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED
From the above we can see log group 2 is current, and this is one of the
smaller groups we must drop. Therefore let's switch out of this group into
one of the newly created log groups.
5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:
SQL> alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **
6. Run the query again to verify the current log group is group 4:
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
7. Now drop redo log groups 1, 2, and 3:
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
Verify the groups were dropped, and the new groups' sizes are correct.
SVRMGR> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
--------- --------- ----------------
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED
8. At this point, you consider taking a backup of the database.
9. You can now go out to the operating system and delete the files associated
with redo log groups 1, 2, and 3 in step 2 above as they are no longer
needed:
% rm /usr/oracle/dbs/log1PROD.dbf
% rm /usr/oracle/dbs/log2PROD.dbf
% rm /usr/oracle/dbs/log3PROD.dbf
Monitor the alert.log for the times of redo log switches. Due to increased
redo log size, the groups should not switch as frequently under the same
load conditions.
NOTE: Examples are given for 9i and higher. In prior releases, you needed
to use Server Manager and connect as the internal user.
1. First see the size of the current logs:
> sqlplus /nolog
SQL> connect / as sysdba
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE
Logs are 1MB from above, let's size them to 10MB.
2. Retrieve all the log member names for the groups:
SQL> select group#, member from v$logfile;
GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf
3. In older versions of the database you needed to shutdown and issue the following
commands in restricted mode. You can still do this, but the database can be online
to perform these changes.
Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
size:
SQL> alter database add logfile group 4
'/usr/oracle/dbs/log4PROD.dbf' size 10M;
SQL> alter database add logfile group 5
'/usr/oracle/dbs/log5PROD.dbf' size 10M;
SQL> alter database add logfile group 6
'/usr/oracle/dbs/log6PROD.dbf' size 10M;
4. Now run a query to view the v$log status:
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED
From the above we can see log group 2 is current, and this is one of the
smaller groups we must drop. Therefore let's switch out of this group into
one of the newly created log groups.
5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:
SQL> alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **
6. Run the query again to verify the current log group is group 4:
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
7. Now drop redo log groups 1, 2, and 3:
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
Verify the groups were dropped, and the new groups' sizes are correct.
SVRMGR> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
--------- --------- ----------------
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED
8. At this point, you consider taking a backup of the database.
9. You can now go out to the operating system and delete the files associated
with redo log groups 1, 2, and 3 in step 2 above as they are no longer
needed:
% rm /usr/oracle/dbs/log1PROD.dbf
% rm /usr/oracle/dbs/log2PROD.dbf
% rm /usr/oracle/dbs/log3PROD.dbf
Monitor the alert.log for the times of redo log switches. Due to increased
redo log size, the groups should not switch as frequently under the same
load conditions.
RMAN Faster Backup, Faster Recovery
Faster Backup, Faster Recovery
http://www.oracle.com/technology/oramag/oracle/04-nov/o64rman.html
Use Block Change Tracking
The acquired bank's data warehouse runs on Oracle Database 10g, which offers a few tools John can use to achieve his objectives. For the first objective, he decides to use the Block Change Tracking feature, new in this version. Block change tracking causes the changed database blocks to be flagged in a special file.
ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING USING FILE '/u01/backup/rman/rman.trc';
SQL> alter database enable block change tracking using file '/u01/backup/tracking.trc';
During incremental backup, RMAN checks this file to see which blocks need to be backed up instead of checking all the blocks of a data file. It dramatically reduces CPU cycles and speeds up incremental backup in the process.
The block change tracking file, if present, is used automatically by the incremental backup; no special RMAN syntax is required. Using a block change tracking file saves enough time and CPU cycles for John to take incremental backups every day (or night) instead of once a week.
How big will this block change tracking file be? The size of this file does not depend on the frequency or the size of updates to the database. For John's data warehouse database, a single instance with one old backup retained, the file will be approximately 10MB for 1TB of total database size, 20MB for 2TB, and so on.
John can ensure that block change tracking is turned on and check the location and name of the file used by querying a new dictionary view:
SELECT * FROM V$BLOCK_CHANGE_TRACKING;
The output is shown in vertical format:
STATUS : ENABLED
FILENAME: /oracle/admin/SMILEY/bct.dbf
BYTES : 11599872
Here the file size is about 11MB, for John's test database of about 1TB. John can also change the location of this file. One option is to do a normal data file rename when the database is in the
MOUNTED state:
ALTER DATABASE RENAME FILE
'/oracle/admin/SMILEY/bct.dbf'
TO '/tmp/bct.dbf';
If a database shutdown is not an option, John can disable the tracking and re-enable it with a different filename as follows:
SQL>; ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;
Database altered.
SQL> ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE '/tmp/bct.dbf';
Database altered.
Using Incremental Merge
Now that John has a plan to address the first objective—to reduce the time required for incremental backup—he moves on to the next objective—to reduce the recovery time using only RMAN. Since incremental backups will be feasible every day thanks to the block change tracking file, John proposes a new backup strategy and schedule
BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;
This will create an RMAN backup set with the tag WEEKLY. The daily RMAN command for Monday will look like the following:
Backup Incremental LEVEL 1 TAG = MON DATABASE;
The daily command will be repeated for all other days, with appropriate tag values representing the weekday. After a week, if John queries the RMAN recovery catalog, he will see something similar to Listing 1.
Now let's examine a failure scenario with John's revised incremental backup schedule (which includes daily incremental backups). If the data file for tablespace USERS fails on Saturday, John has to restore the Level 0 backup taken (with tag WEEKLY) on Sunday and then apply all the incremental backups with keys from 36 through 46 from the output shown in Listing 1. After that he needs to apply the archived logs. The restore and recovery operations are shown in Listing 2. Note how all the incremental backups with tags MON, TUE, and so on are applied one after the other. After all of the incremental backups have been applied, the media recovery begins and applies the information in the archived logs.
If a full backup had been taken every day, the recovery would not have had to apply all the incremental backups, saving considerable time. But since that is not possible, what if John could apply the incremental backups taken on weekdays to the Level 0 backup taken earlier? This would make the Level 0 backup up-to-date with the latest day's changes and eliminate the need to apply all the subsequent incremental backups during recovery. In Oracle Database 10g, using a disk-based backup strategy, John can accomplish that using the Incremental Merge feature of the RMAN backup. With such an approach, the daily RMAN incremental backup script will look like this:
BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY
WITH TAG WEEKLY DATABASE;
RECOVER COPY OF DATABASE
WITH TAG WEEKLY;
Adding ASM
Create an ASM instance on the data warehouse server.
You do this by creating a parameter file with a special parameter named INSTANCE_TYPE=ASM and bringing up the instance. Note that there are no control files, redo log files, or data files for ASM instances.
Once the ASM instance is started, create a disk group RMANDG as follows:
CREATE DISKGROUP rmandg
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';
This assumes that John has four disks with which he wants to make an ASM group. He can expand it to as many disks as he likes. The above disk group is analogous to mirrored disks with the creation of failure groups, and each failure group is striped across two disks.
Change the flash recovery area of the database to point to the disk group:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+RMANDG'
Note the plus (+) sign before the disk group name, which indicates an ASM disk group.
use RMAN to back up the recovery area from ASM to tape (at the network backup server) through the following command:
BACKUP RECOVERY AREA;
This backs up the entire recovery area, including data file backups, archived logs, and control file backups from the ASM disk group to the tape.
There is no need to keep multiple copies of the backups on tape. The following RMAN command deletes the old backup sets:
DELETE OBSOLETE DEVICE TYPE sbt;
John proposes to back up the ASM disks to tape every week, so the tapes will contain a week's worth of data. In case of a data warehouse database failure, the recovery can be quick from the SATA disks. If the disks fail, the database can also be recovered from the tape.
Reducing Traffic with Compression
John's last objective is to reduce the size of the incremental backups significantly so that they do not overwhelm the network. John addresses this issue by proposing to enable the compression feature of RMAN, which uses a proprietary algorithm to compress the backup sets. John's proposed backup scripts change a little to add the compression feature:
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL
LEVEL 1 TAG = WEEKLY DATABASE;
Incrementally Updated Backups
Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup. The following example shows how this can be used:
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}The RECOVER COPY... line will not do anything until the script has been running for more than 7 days. The BACKUP INCREMENTAL line will perform a complete backup (level 0) the first day it is run, with all subsequent backups being level 1 incremental backups. After 7 days, the RECOVER COPY... line will start to take effect, merging all incremental backups older than 7 days into the level 0 backup, effectively moving the level 0 backup forward. The effect of this is that you will permanently have a 7 day recovery window with a 7 day old level 0 backup and 6 level 1 incremental backups. Notice that the tag must be used to identify which incremental backups apply to which image copies.
If you wanted to keep your image copy as up to date as possible you might do the following:
RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
RECOVER COPY OF DATABASE WITH TAG 'incr_backup';
}In this example the incremental backup is merged into the image copy as soon as it is completed.
Fast Incremental Backups
There are performance issues associated with incremental backups as the whole of each datafile must be scanned to identify changed blocks. In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed using the following query:
SELECT status FROM v$block_change_tracking;Change tracking is enabled using the ALTER DATABASE command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;By default the change tracking file is created as an Oracle Managed File (OMF) in the location pointed to by the DB_CREATE_FILE_DEST parameter. An alternate location can be specified using the following command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/MYSID/rman_change_track.f' REUSE;The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.
Change tracking can be disabled using the following command:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Estimating Size of the Change Tracking File on Disk
The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:
•To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.
•For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.
http://www.oracle.com/technology/oramag/oracle/04-nov/o64rman.html
Use Block Change Tracking
The acquired bank's data warehouse runs on Oracle Database 10g, which offers a few tools John can use to achieve his objectives. For the first objective, he decides to use the Block Change Tracking feature, new in this version. Block change tracking causes the changed database blocks to be flagged in a special file.
ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING USING FILE '/u01/backup/rman/rman.trc';
SQL> alter database enable block change tracking using file '/u01/backup/tracking.trc';
SQL> alter database disable block change tracking;
During incremental backup, RMAN checks this file to see which blocks need to be backed up instead of checking all the blocks of a data file. It dramatically reduces CPU cycles and speeds up incremental backup in the process.
The block change tracking file, if present, is used automatically by the incremental backup; no special RMAN syntax is required. Using a block change tracking file saves enough time and CPU cycles for John to take incremental backups every day (or night) instead of once a week.
How big will this block change tracking file be? The size of this file does not depend on the frequency or the size of updates to the database. For John's data warehouse database, a single instance with one old backup retained, the file will be approximately 10MB for 1TB of total database size, 20MB for 2TB, and so on.
John can ensure that block change tracking is turned on and check the location and name of the file used by querying a new dictionary view:
SELECT * FROM V$BLOCK_CHANGE_TRACKING;
The output is shown in vertical format:
STATUS : ENABLED
FILENAME: /oracle/admin/SMILEY/bct.dbf
BYTES : 11599872
Here the file size is about 11MB, for John's test database of about 1TB. John can also change the location of this file. One option is to do a normal data file rename when the database is in the
MOUNTED state:
ALTER DATABASE RENAME FILE
'/oracle/admin/SMILEY/bct.dbf'
TO '/tmp/bct.dbf';
If a database shutdown is not an option, John can disable the tracking and re-enable it with a different filename as follows:
SQL>; ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;
Database altered.
SQL> ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE '/tmp/bct.dbf';
Database altered.
Using Incremental Merge
Now that John has a plan to address the first objective—to reduce the time required for incremental backup—he moves on to the next objective—to reduce the recovery time using only RMAN. Since incremental backups will be feasible every day thanks to the block change tracking file, John proposes a new backup strategy and schedule
BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;
This will create an RMAN backup set with the tag WEEKLY. The daily RMAN command for Monday will look like the following:
Backup Incremental LEVEL 1 TAG = MON DATABASE;
The daily command will be repeated for all other days, with appropriate tag values representing the weekday. After a week, if John queries the RMAN recovery catalog, he will see something similar to Listing 1.
Now let's examine a failure scenario with John's revised incremental backup schedule (which includes daily incremental backups). If the data file for tablespace USERS fails on Saturday, John has to restore the Level 0 backup taken (with tag WEEKLY) on Sunday and then apply all the incremental backups with keys from 36 through 46 from the output shown in Listing 1. After that he needs to apply the archived logs. The restore and recovery operations are shown in Listing 2. Note how all the incremental backups with tags MON, TUE, and so on are applied one after the other. After all of the incremental backups have been applied, the media recovery begins and applies the information in the archived logs.
If a full backup had been taken every day, the recovery would not have had to apply all the incremental backups, saving considerable time. But since that is not possible, what if John could apply the incremental backups taken on weekdays to the Level 0 backup taken earlier? This would make the Level 0 backup up-to-date with the latest day's changes and eliminate the need to apply all the subsequent incremental backups during recovery. In Oracle Database 10g, using a disk-based backup strategy, John can accomplish that using the Incremental Merge feature of the RMAN backup. With such an approach, the daily RMAN incremental backup script will look like this:
BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY
WITH TAG WEEKLY DATABASE;
RECOVER COPY OF DATABASE
WITH TAG WEEKLY;
Adding ASM
Create an ASM instance on the data warehouse server.
You do this by creating a parameter file with a special parameter named INSTANCE_TYPE=ASM and bringing up the instance. Note that there are no control files, redo log files, or data files for ASM instances.
Once the ASM instance is started, create a disk group RMANDG as follows:
CREATE DISKGROUP rmandg
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';
This assumes that John has four disks with which he wants to make an ASM group. He can expand it to as many disks as he likes. The above disk group is analogous to mirrored disks with the creation of failure groups, and each failure group is striped across two disks.
Change the flash recovery area of the database to point to the disk group:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+RMANDG'
Note the plus (+) sign before the disk group name, which indicates an ASM disk group.
use RMAN to back up the recovery area from ASM to tape (at the network backup server) through the following command:
BACKUP RECOVERY AREA;
This backs up the entire recovery area, including data file backups, archived logs, and control file backups from the ASM disk group to the tape.
There is no need to keep multiple copies of the backups on tape. The following RMAN command deletes the old backup sets:
DELETE OBSOLETE DEVICE TYPE sbt;
John proposes to back up the ASM disks to tape every week, so the tapes will contain a week's worth of data. In case of a data warehouse database failure, the recovery can be quick from the SATA disks. If the disks fail, the database can also be recovered from the tape.
Reducing Traffic with Compression
John's last objective is to reduce the size of the incremental backups significantly so that they do not overwhelm the network. John addresses this issue by proposing to enable the compression feature of RMAN, which uses a proprietary algorithm to compress the backup sets. John's proposed backup scripts change a little to add the compression feature:
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL
LEVEL 1 TAG = WEEKLY DATABASE;
Incrementally Updated Backups
Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup. The following example shows how this can be used:
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}The RECOVER COPY... line will not do anything until the script has been running for more than 7 days. The BACKUP INCREMENTAL line will perform a complete backup (level 0) the first day it is run, with all subsequent backups being level 1 incremental backups. After 7 days, the RECOVER COPY... line will start to take effect, merging all incremental backups older than 7 days into the level 0 backup, effectively moving the level 0 backup forward. The effect of this is that you will permanently have a 7 day recovery window with a 7 day old level 0 backup and 6 level 1 incremental backups. Notice that the tag must be used to identify which incremental backups apply to which image copies.
If you wanted to keep your image copy as up to date as possible you might do the following:
RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
RECOVER COPY OF DATABASE WITH TAG 'incr_backup';
}In this example the incremental backup is merged into the image copy as soon as it is completed.
Fast Incremental Backups
There are performance issues associated with incremental backups as the whole of each datafile must be scanned to identify changed blocks. In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed using the following query:
SELECT status FROM v$block_change_tracking;Change tracking is enabled using the ALTER DATABASE command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;By default the change tracking file is created as an Oracle Managed File (OMF) in the location pointed to by the DB_CREATE_FILE_DEST parameter. An alternate location can be specified using the following command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/MYSID/rman_change_track.f' REUSE;The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.
Change tracking can be disabled using the following command:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Estimating Size of the Change Tracking File on Disk
The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:
•To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.
•For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.
sábado, 4 de julio de 2009
Oracle 10g Automatic Storage Management (ASM)
Initialization Parameters and ASM Instance CreationThe initialization parameters that are of specific interest for an ASM instance are:
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered. Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.
INSTANCE_TYPE=ASM
Next, using SQL*Plus connect to the ide instance.
export ORACLE_SID=+ASM
sqlplus / as sysdba
Create an spfile using the contents of the init+ASM.ora file.
SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.Finally, start the instance with the NOMOUNT option.
SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
SQL>
The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.
SQL> shutdown
ASM instance shutdown
SQL>
Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST
Startup and Shutdown of ASM InstancesASM instance are started and stopped in a similar way to normal database instances.
The options for the STARTUP command are:
FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT - Starts the ASM instance without mounting any disk groups.
OPEN - This is not a valid option for an ASM instance. The options for the SHUTDOWN command are:
NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.
Administering ASM Disk Groups
Disks
Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:
NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID. In addition failure groups and preferred names for disks can be defined. If the NAME clause is omitted the disks are given a system generated name like "disk_group_1_0001". The FORCE option can be used to move a disk from another disk group into this one.
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;
Disk groups can be deleted using the DROP DISKGROUP statement.
DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
Disks can be added or removed from disk groups using the
ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.
-- Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';
-- Drop a disk.
ALTER DISKGROUP disk_group_1 DROP DISK diska2;
Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The statement can be used to resize individual disks, all disks in a failure group or all disks in the disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned by the OS.
-- Resize a specific disk.
ALTER DISKGROUP disk_group_1
RESIZE DISK diska1 SIZE 100G;
-- Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;
-- Resize all disks in a disk group.
ALTER DISKGROUP disk_group_1
RESIZE ALL SIZE 100G;
The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.
ALTER DISKGROUP disk_group_1 UNDROP DISKS;
Disk groups can be rebalanced manually using the REBALANCE clause of the
ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.
ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;
Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;
TemplatesTemplates are named groups of attributes that can be applied to the files within a disk group. The following example show how templates can be created, altered and dropped.
-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);
-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);
-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;Available attributes include:
UNPROTECTED - No mirroring or striping regardless of the redundancy setting.
MIRROR - Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
COARSE - Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
FINE - Specifies higher granularity for striping. This attribute cannot be set for external redundancy.
DirectoriesA directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing. The following examples show how ASM directories can be created, modified and deleted.
-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';
-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';
-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;
AliasesAliases allow you to reference ASM files using user-friendly names, rather than the fully qualified ASM filenames.
-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1/mydb/datafile/my_ts.342.3';
-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1.342.3';
-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
TO '+disk_group_1/my_dir/my_file2.dbf';
-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS '+disk_group_1/my_dir/my_file.dbf';
Attempting to drop a system alias results in an error.
FilesFiles are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.
-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';
-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';
-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';
Checking MetadataThe internal consistency of disk group metadata can be checked in a number of ways using the CHECK clause of the ALTER DISKGROUP statement.
-- Check metadata for a specific file.
ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'
-- Check metadata for a specific failure group in the disk group.
ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;
-- Check metadata for a specific disk in the disk group.
ALTER DISKGROUP disk_group_1
CHECK DISK diska1;
-- Check metadata for all disks in the disk group.
ALTER DISKGROUP disk_group_1 CHECK ALL;
ASM ViewsThe ASM configuration can be viewed using the V$ASM_% views, which often contain different information depending on whether they are queried from the ASM instance, or a dependant database instance.
View
ASM Instance
DB Instance
V$ASM_ALIAS Displays a row for each alias present in every disk group mounted by the ASM instance. Returns no rows
V$ASM_CLIENT Displays a row for each database instance using a disk group managed by the ASM instance.
Displays a row for the ASM instance if the database has open ASM files.
V$ASM_DISK Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.
Displays a row for each disk in disk groups in use by the database instance.
V$ASM_DISKGROUP Displays a row for each disk group discovered by the ASM instance.
Displays a row for each disk group mounted by the local ASM instance.
V$ASM_FILE Displays a row for each file for each disk group mounted by the ASM instance.
Displays no rows.
V$ASM_OPERATION Displays a row for each file for each long running operation executing in the ASM instance.
Displays no rows.
V$ASM_TEMPLATE Displays a row for each template present in each disk group mounted by the ASM instance.
SQL and ASMASM
filenames can be used in place of conventional filenames for most Oracle file types, including controlfiles, datafiles, logfiles etc. For example, the following command creates a new tablespace with a datafile in the disk_group_1 disk group.
CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;
Migrating to ASM Using RMAN
The following method shows how a primary database can be migrated to ASM from a disk based backup:
Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> SHUTDOWN IMMEDIATE
Modify the parameter file of the target database as follows:
Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
Start the database in nomount mode.
RMAN> STARTUP NOMOUNT
Restore the controlfile into the new location from the old location.
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
Mount the database.
RMAN> ALTER DATABASE MOUNT;
Copy the database into the ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
Switch all datafile to the new ASM location.
RMAN> SWITCH DATABASE TO COPY;
Open the database.
RMAN> ALTER DATABASE OPEN;
Create new redo logs in ASM and delete the old ones.
Enable change tracking if it was being used.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Example
1. Using RMAN, connect to the target database as follows:
[oracle@oradb1 oracle]$ rman
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target
connected to target database: SSKYDB (DBID=2290365532)
2. Using SQL, offline the tablespace that will be the migration
candidate:
RMAN> SQL "ALTER TABLESPACE EXAMPLE OFFLINE";
3. Using the “backup as copy” operation, perform the following
operation:
RMAN> BACKUP AS COPY TABLESPACE EXAMPLE FORMAT '+ASMGRP1';
Starting backup at 01-OCT-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=252 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u14/oradata/SSKYDB/example01.dbf
output filename=+ASMGRP1/sskydb/datafile/example.258.1
tag=TAG20041001T221236 recid=2 stamp=538438446
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
Finished backup at 01-OCT-04
RMAN>
4. Once the “copy complete” message is received, indicating a successful
copy, switch the tablespace to use the copied datafile:
RMAN> SWITCH TABLESPACE EXAMPLE TO COPY;
datafile 5 switched to datafile copy "+ASMGRP1/sskydb/
datafile/example.258.1"
RMAN>
5. The final step is to ONLINE the tablespace using SQL:
RMAN> SQL "ALTER TABLESPACE EXAMPLE ONLINE";
6. Verify the operation by connecting to the target database and
checking the V$ views.
Converting non-ASM datafile to ASM using
DBMS_FILE_TRANSFER stored procedure
The DBMS_FILE_TRANSFER package provides a means to copy files between two locations. In Oracle Database 10g, this procedure is used to move or copy files between ASM disk groups and is the primary utility used to instantiate an ASM Data Guard database. Using this procedure, the following
transfer scenarios are possible:
1. Copy files from one ASM disk group to another ASM disk group.
2. Copy files from an ASM disk group to an external storage media
such as a file system at the operating system level.
3. Copy files from a file system at the operating system level to an
ASM-configured disk group.
4. Copy files from a file system at the operating system level to
another location or raw device at the operating system level.
Steps to be performed to move datafiles from one location to another
using the DBMS_FILE_TRANSFER procedure are as follows:
1. Identify the datafile to be moved or copied from one location to
another:
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------
+ASMGRP1/sskydb/datafile/users.259.571954319
+ASMGRP1/sskydb/datafile/sysaux.257.571954317
+ASMGRP1/sskydb/datafile/undotbs1.258.571954317
+ASMGRP1/sskydb/datafile/system.256.571954317
+ASMGRP1/sskydb/datafile/example.264.571954419
+ASMGRP1/sskydb/datafile/undotbs2.265.571954545
+ASMGRP1/sskydb/datafile/undotbs3.266.571954547
+ASMGRP1/sskydb/datafile/sorac_data.272.572018797
+ASMGRP1/sskydb/datafile/bmf_data.273.572018897
2. Identify the destination (ASM or non-ASM) where the file will be
copied.
3. The datafile is copied to an external OCFS-based file system location.
4. Take the datafile offline:
SQL> ALTER DATABASE DATAFILE '+ASMGRP1/SSKYDB/DATAFILE/
BMF_DATA.273.572018897' OFFLINE;
5. Copy the file to the new location by first creating a
DIRECTORY_NAME for the source and target locations and using
the following procedure:
SQL> CREATE DIRECTORY ASMSRC AS '+ASMGRP1/SSKYDB/
DATAFILE';
Directory created.
SQL> CREATE DIRECTORY OSDEST AS '/ocfs9/oradata';
Directory created.
SQL> BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('ASMRC',
'BMF_DATA.273.572018897',
'OSDEST',
'BMF.dbf');
END;
/
6. Bring the datafile online:
ALTER DATABASE DATAFILE '+ASMGRP1/SSKYDB/DATAFILE/
BMF_DATA.273.572018897' ONLINE;
7. Verify the copied file:
[oracle@oradb1 oradata]$ ls –ltr /ocfs9/oradata
Viewing Information About Automatic Storage Management
You can use these views to query information about Automatic Storage Management:
View Description
V$ASM_DISKGROUP In an ASM instance, describes a disk group (number, name, size related info, state, and redundancy type).
In a DB instance, contains one row for every ASM disk group mounted by the local ASM instance.
This view performs disk discovery every time it is queried.
V$ASM_DISK In an ASM instance, contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group.
In a DB instance, contains rows only for disks in the disk groups in use by that DB instance.
This view performs disk discovery every time it is queried.
V$ASM_DISKGROUP_STAT Has the same columns as V$ASM_DISKGROUP, but to reduce overhead, does not perform a discovery when it is queried. It therefore does not return information on any disks that are new to the storage system. For the most accurate data, use V$ASM_DISKGROUP instead.
V$ASM_DISK_STAT Has the same columns as V$ASM_DISK, but to reduce overhead, does not perform a discovery when it is queried. It therefore does not return information on any disks that are new to the storage system. For the most accurate data, use V$ASM_DISK instead.
V$ASM_FILE In an ASM instance, contains one row for every ASM file in every disk group mounted by the ASM instance.
In a DB instance, contains no rows.
V$ASM_TEMPLATE In an ASM or DB instance, contains one row for every template present in every disk group mounted by the ASM instance.
V$ASM_ALIAS In an ASM instance, contains one row for every alias present in every disk group mounted by the ASM instance.
In a DB instance, contains no rows.
V$ASM_OPERATION In an ASM instance, contains one row for every active ASM long running operation executing in the ASM instance.
In a DB instance, contains no rows.
V$ASM_CLIENT In an ASM instance, identifies databases using disk groups managed by the ASM instance.
In a DB instance, contains one row for the ASM instance if the database has any open ASM files.
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered. Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.
INSTANCE_TYPE=ASM
Next, using SQL*Plus connect to the ide instance.
export ORACLE_SID=+ASM
sqlplus / as sysdba
Create an spfile using the contents of the init+ASM.ora file.
SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.Finally, start the instance with the NOMOUNT option.
SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
SQL>
The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.
SQL> shutdown
ASM instance shutdown
SQL>
Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST
Startup and Shutdown of ASM InstancesASM instance are started and stopped in a similar way to normal database instances.
The options for the STARTUP command are:
FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT - Starts the ASM instance without mounting any disk groups.
OPEN - This is not a valid option for an ASM instance. The options for the SHUTDOWN command are:
NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.
Administering ASM Disk Groups
Disks
Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:
NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID. In addition failure groups and preferred names for disks can be defined. If the NAME clause is omitted the disks are given a system generated name like "disk_group_1_0001". The FORCE option can be used to move a disk from another disk group into this one.
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;
Disk groups can be deleted using the DROP DISKGROUP statement.
DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
Disks can be added or removed from disk groups using the
ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.
-- Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';
-- Drop a disk.
ALTER DISKGROUP disk_group_1 DROP DISK diska2;
Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The statement can be used to resize individual disks, all disks in a failure group or all disks in the disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned by the OS.
-- Resize a specific disk.
ALTER DISKGROUP disk_group_1
RESIZE DISK diska1 SIZE 100G;
-- Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;
-- Resize all disks in a disk group.
ALTER DISKGROUP disk_group_1
RESIZE ALL SIZE 100G;
The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.
ALTER DISKGROUP disk_group_1 UNDROP DISKS;
Disk groups can be rebalanced manually using the REBALANCE clause of the
ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.
ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;
Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;
TemplatesTemplates are named groups of attributes that can be applied to the files within a disk group. The following example show how templates can be created, altered and dropped.
-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);
-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);
-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;Available attributes include:
UNPROTECTED - No mirroring or striping regardless of the redundancy setting.
MIRROR - Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
COARSE - Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
FINE - Specifies higher granularity for striping. This attribute cannot be set for external redundancy.
DirectoriesA directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing. The following examples show how ASM directories can be created, modified and deleted.
-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';
-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';
-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;
AliasesAliases allow you to reference ASM files using user-friendly names, rather than the fully qualified ASM filenames.
-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1/mydb/datafile/my_ts.342.3';
-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1.342.3';
-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
TO '+disk_group_1/my_dir/my_file2.dbf';
-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS '+disk_group_1/my_dir/my_file.dbf';
Attempting to drop a system alias results in an error.
FilesFiles are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.
-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';
-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';
-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';
Checking MetadataThe internal consistency of disk group metadata can be checked in a number of ways using the CHECK clause of the ALTER DISKGROUP statement.
-- Check metadata for a specific file.
ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'
-- Check metadata for a specific failure group in the disk group.
ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;
-- Check metadata for a specific disk in the disk group.
ALTER DISKGROUP disk_group_1
CHECK DISK diska1;
-- Check metadata for all disks in the disk group.
ALTER DISKGROUP disk_group_1 CHECK ALL;
ASM ViewsThe ASM configuration can be viewed using the V$ASM_% views, which often contain different information depending on whether they are queried from the ASM instance, or a dependant database instance.
View
ASM Instance
DB Instance
V$ASM_ALIAS Displays a row for each alias present in every disk group mounted by the ASM instance. Returns no rows
V$ASM_CLIENT Displays a row for each database instance using a disk group managed by the ASM instance.
Displays a row for the ASM instance if the database has open ASM files.
V$ASM_DISK Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.
Displays a row for each disk in disk groups in use by the database instance.
V$ASM_DISKGROUP Displays a row for each disk group discovered by the ASM instance.
Displays a row for each disk group mounted by the local ASM instance.
V$ASM_FILE Displays a row for each file for each disk group mounted by the ASM instance.
Displays no rows.
V$ASM_OPERATION Displays a row for each file for each long running operation executing in the ASM instance.
Displays no rows.
V$ASM_TEMPLATE Displays a row for each template present in each disk group mounted by the ASM instance.
SQL and ASMASM
filenames can be used in place of conventional filenames for most Oracle file types, including controlfiles, datafiles, logfiles etc. For example, the following command creates a new tablespace with a datafile in the disk_group_1 disk group.
CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;
Migrating to ASM Using RMAN
The following method shows how a primary database can be migrated to ASM from a disk based backup:
Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> SHUTDOWN IMMEDIATE
Modify the parameter file of the target database as follows:
Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
Start the database in nomount mode.
RMAN> STARTUP NOMOUNT
Restore the controlfile into the new location from the old location.
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
Mount the database.
RMAN> ALTER DATABASE MOUNT;
Copy the database into the ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
Switch all datafile to the new ASM location.
RMAN> SWITCH DATABASE TO COPY;
Open the database.
RMAN> ALTER DATABASE OPEN;
Create new redo logs in ASM and delete the old ones.
Enable change tracking if it was being used.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Example
1. Using RMAN, connect to the target database as follows:
[oracle@oradb1 oracle]$ rman
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target
connected to target database: SSKYDB (DBID=2290365532)
2. Using SQL, offline the tablespace that will be the migration
candidate:
RMAN> SQL "ALTER TABLESPACE EXAMPLE OFFLINE";
3. Using the “backup as copy” operation, perform the following
operation:
RMAN> BACKUP AS COPY TABLESPACE EXAMPLE FORMAT '+ASMGRP1';
Starting backup at 01-OCT-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=252 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u14/oradata/SSKYDB/example01.dbf
output filename=+ASMGRP1/sskydb/datafile/example.258.1
tag=TAG20041001T221236 recid=2 stamp=538438446
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
Finished backup at 01-OCT-04
RMAN>
4. Once the “copy complete” message is received, indicating a successful
copy, switch the tablespace to use the copied datafile:
RMAN> SWITCH TABLESPACE EXAMPLE TO COPY;
datafile 5 switched to datafile copy "+ASMGRP1/sskydb/
datafile/example.258.1"
RMAN>
5. The final step is to ONLINE the tablespace using SQL:
RMAN> SQL "ALTER TABLESPACE EXAMPLE ONLINE";
6. Verify the operation by connecting to the target database and
checking the V$ views.
Converting non-ASM datafile to ASM using
DBMS_FILE_TRANSFER stored procedure
The DBMS_FILE_TRANSFER package provides a means to copy files between two locations. In Oracle Database 10g, this procedure is used to move or copy files between ASM disk groups and is the primary utility used to instantiate an ASM Data Guard database. Using this procedure, the following
transfer scenarios are possible:
1. Copy files from one ASM disk group to another ASM disk group.
2. Copy files from an ASM disk group to an external storage media
such as a file system at the operating system level.
3. Copy files from a file system at the operating system level to an
ASM-configured disk group.
4. Copy files from a file system at the operating system level to
another location or raw device at the operating system level.
Steps to be performed to move datafiles from one location to another
using the DBMS_FILE_TRANSFER procedure are as follows:
1. Identify the datafile to be moved or copied from one location to
another:
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------
+ASMGRP1/sskydb/datafile/users.259.571954319
+ASMGRP1/sskydb/datafile/sysaux.257.571954317
+ASMGRP1/sskydb/datafile/undotbs1.258.571954317
+ASMGRP1/sskydb/datafile/system.256.571954317
+ASMGRP1/sskydb/datafile/example.264.571954419
+ASMGRP1/sskydb/datafile/undotbs2.265.571954545
+ASMGRP1/sskydb/datafile/undotbs3.266.571954547
+ASMGRP1/sskydb/datafile/sorac_data.272.572018797
+ASMGRP1/sskydb/datafile/bmf_data.273.572018897
2. Identify the destination (ASM or non-ASM) where the file will be
copied.
3. The datafile is copied to an external OCFS-based file system location.
4. Take the datafile offline:
SQL> ALTER DATABASE DATAFILE '+ASMGRP1/SSKYDB/DATAFILE/
BMF_DATA.273.572018897' OFFLINE;
5. Copy the file to the new location by first creating a
DIRECTORY_NAME for the source and target locations and using
the following procedure:
SQL> CREATE DIRECTORY ASMSRC AS '+ASMGRP1/SSKYDB/
DATAFILE';
Directory created.
SQL> CREATE DIRECTORY OSDEST AS '/ocfs9/oradata';
Directory created.
SQL> BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('ASMRC',
'BMF_DATA.273.572018897',
'OSDEST',
'BMF.dbf');
END;
/
6. Bring the datafile online:
ALTER DATABASE DATAFILE '+ASMGRP1/SSKYDB/DATAFILE/
BMF_DATA.273.572018897' ONLINE;
7. Verify the copied file:
[oracle@oradb1 oradata]$ ls –ltr /ocfs9/oradata
Viewing Information About Automatic Storage Management
You can use these views to query information about Automatic Storage Management:
View Description
V$ASM_DISKGROUP In an ASM instance, describes a disk group (number, name, size related info, state, and redundancy type).
In a DB instance, contains one row for every ASM disk group mounted by the local ASM instance.
This view performs disk discovery every time it is queried.
V$ASM_DISK In an ASM instance, contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group.
In a DB instance, contains rows only for disks in the disk groups in use by that DB instance.
This view performs disk discovery every time it is queried.
V$ASM_DISKGROUP_STAT Has the same columns as V$ASM_DISKGROUP, but to reduce overhead, does not perform a discovery when it is queried. It therefore does not return information on any disks that are new to the storage system. For the most accurate data, use V$ASM_DISKGROUP instead.
V$ASM_DISK_STAT Has the same columns as V$ASM_DISK, but to reduce overhead, does not perform a discovery when it is queried. It therefore does not return information on any disks that are new to the storage system. For the most accurate data, use V$ASM_DISK instead.
V$ASM_FILE In an ASM instance, contains one row for every ASM file in every disk group mounted by the ASM instance.
In a DB instance, contains no rows.
V$ASM_TEMPLATE In an ASM or DB instance, contains one row for every template present in every disk group mounted by the ASM instance.
V$ASM_ALIAS In an ASM instance, contains one row for every alias present in every disk group mounted by the ASM instance.
In a DB instance, contains no rows.
V$ASM_OPERATION In an ASM instance, contains one row for every active ASM long running operation executing in the ASM instance.
In a DB instance, contains no rows.
V$ASM_CLIENT In an ASM instance, identifies databases using disk groups managed by the ASM instance.
In a DB instance, contains one row for the ASM instance if the database has any open ASM files.
Oracle 10g Automatic Storage Management (ASM)
Initialization Parameters and ASM Instance CreationThe initialization parameters that are of specific interest for an ASM instance are:
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Oracle 10g Automatic Storage Management (ASM)
Initialization Parameters and ASM Instance Creation
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.
To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.
INSTANCE_TYPE=ASM
Next, using SQL*Plus connect to the ide instance.
export ORACLE_SID=+ASM
sqlplus / as sysdba
Create an spfile using the contents of the init+ASM.ora file.
SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.
Finally, start the instance with the NOMOUNT option.
SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.
SQL> shutdown
ASM instance shutdown
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.
To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.
INSTANCE_TYPE=ASM
Next, using SQL*Plus connect to the ide instance.
export ORACLE_SID=+ASM
sqlplus / as sysdba
Create an spfile using the contents of the init+ASM.ora file.
SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.
Finally, start the instance with the NOMOUNT option.
SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.
SQL> shutdown
ASM instance shutdown
Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST
Startup and Shutdown of ASM Instances
ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:
FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT - Starts the ASM instance without mounting any disk groups.
OPEN - This is not a valid option for an ASM instance.
Administering ASM Disk Groups
DisksDisk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:
NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.
The options for the SHUTDOWN command are:
NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;
Disk groups can be deleted using the DROP DISKGROUP statement.
DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
Disks can be added or removed from disk groups using the ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.
-- Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';
-- Drop a disk.
ALTER DISKGROUP disk_group_1 DROP DISK diska2;
Suscribirse a:
Entradas (Atom)
