column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Tuesday, October 31, 2006
Tuesday, October 24, 2006
9.2.0.6=>9.2.0.7 upgrade on windows platform
Error occurs when progress goes to 51%.
To fix this error, stop the service "Distributed Transaction coordinator".
To fix this error, stop the service "Distributed Transaction coordinator".
Sunday, October 22, 2006
How to obtain the number of the blocks that really contain data
実際にデータを含んでいるデータブロック数を調べる方法はANALYZE TABLEがよく使用されますが、これよりも早い方法があります。
実際に全件検索を行って数えてしまう方法です。
以下のSQLで実現できます。
oracle7.X
select count(distinct(substr(rowid,1,8)||substr(rowid,15,4))) blocks_used from [テーブル名];
oracle8.X
select count(distinct(substr(rowid,1,15))) blocks_used from [テーブル名];
これにより、実データサイズを算出することが出来ます。
実データ＝上記SELECT文のblock数 * ブロックサイズ(初期化パラメータdb_block_size)ただし、PCTFREE/PCTUSEDなどの設定や断片化によるオーバヘッドがありえるため、完全なデータサイズではありません。
あくまでも実際にデータを含んでいるデータブロックを計算しています。
実際に全件検索を行って数えてしまう方法です。
以下のSQLで実現できます。
oracle7.X
select count(distinct(substr(rowid,1,8)||substr(rowid,15,4))) blocks_used from [テーブル名];
oracle8.X
select count(distinct(substr(rowid,1,15))) blocks_used from [テーブル名];
これにより、実データサイズを算出することが出来ます。
実データ＝上記SELECT文のblock数 * ブロックサイズ(初期化パラメータdb_block_size)ただし、PCTFREE/PCTUSEDなどの設定や断片化によるオーバヘッドがありえるため、完全なデータサイズではありません。
あくまでも実際にデータを含んでいるデータブロックを計算しています。
Thursday, October 19, 2006
How to change password back
SQL> SELECT PASSWORD FROM DBA_USERS WHERE USERNAME = 'test';
PASSWORD
------------------------------
F84870711C9D9E90
SQL> ALTER USER test IDENTIFIED BY ABC;
用户已更改。
SQL> ALTER USER DAIJC IDENTIFIED BY VALUES 'F84870711C9D9E90';
用户已更改。
PASSWORD
------------------------------
F84870711C9D9E90
SQL> ALTER USER test IDENTIFIED BY ABC;
用户已更改。
SQL> ALTER USER DAIJC IDENTIFIED BY VALUES 'F84870711C9D9E90';
用户已更改。
tablespace usage
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
Wednesday, October 18, 2006
Incrementally Updated Backups: A Basic Example
To create incremental backups for use in an incrementally updated backups strategy, you must use the BACKUP... FOR RECOVER OF COPY WITH TAG form of the BACKUP command. How the command works is best understood in the context of an example script that would implement the strategy.
This script, run on a regular basis, is all that is required to implement a strategy based on incrementally updated backups:
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;
}
The syntax used in the script does not, however, make it clear how the strategy works. To understand the script and the strategy, it is necessary to understand the effects of these two commands when no datafile copies or incremental backups exist.
The RECOVER COPY OF DATABASE WITH TAG... command causes RMAN to apply any available incremental level 1 backups to a set of datafile copies with the specified tag.
If there is no incremental backup or no datafile copy, the command generates a message but does not generate an error.
The first time the script runs, this command has no effect, because there is neither a datafile copy nor a level 1 incremental backup.
The second time the script runs, there is a datafile copy (created by the first BACKUP command), but no incremental level 1 backup, so again, the command has no effect.
On the third run and all subsequent runs, there is a datafile copy and a level 1 incremental from the previous run, so the level 1 incremental is applied to the datafile copy, bringing the datafile copy up to the checkpoint SCN of the level 1 incremental.
---------------------------------------------------------------------------
如果不想使用这种循环执行的方式，而是希望手动完成：
1.创建lv0的image copy
backup as copy incremental level 0 database
2.创建lv1的backup set
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY DATABASE
这样，当需要更新image copy的时候运行：
RECOVER COPY OF DATABASE
（以上3条语句可以加上tag参数）
This script, run on a regular basis, is all that is required to implement a strategy based on incrementally updated backups:
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;
}
The syntax used in the script does not, however, make it clear how the strategy works. To understand the script and the strategy, it is necessary to understand the effects of these two commands when no datafile copies or incremental backups exist.
The RECOVER COPY OF DATABASE WITH TAG... command causes RMAN to apply any available incremental level 1 backups to a set of datafile copies with the specified tag.
If there is no incremental backup or no datafile copy, the command generates a message but does not generate an error.
The first time the script runs, this command has no effect, because there is neither a datafile copy nor a level 1 incremental backup.
The second time the script runs, there is a datafile copy (created by the first BACKUP command), but no incremental level 1 backup, so again, the command has no effect.
On the third run and all subsequent runs, there is a datafile copy and a level 1 incremental from the previous run, so the level 1 incremental is applied to the datafile copy, bringing the datafile copy up to the checkpoint SCN of the level 1 incremental.
---------------------------------------------------------------------------
如果不想使用这种循环执行的方式，而是希望手动完成：
1.创建lv0的image copy
backup as copy incremental level 0 database
2.创建lv1的backup set
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY DATABASE
这样，当需要更新image copy的时候运行：
RECOVER COPY OF DATABASE
（以上3条语句可以加上tag参数）
Tuesday, October 17, 2006
How to manage flash recovery area and restore point
Oracle
oracle 10.2.0.2
Restore point and flash recovery area space management
Definition
-------------
The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that
provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area.
RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery.
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark
or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However,
they also provide specific functionality related to the use of the Flashback Database feature.
About the Flashback Database Window
---------------------------------------------------------
DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may
be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the
flash recovery area.
The flashback retention target is a target, not an absolute guarantee that Flashback Database will be available.
If your flash recovery area is not large enough to hold both the flashback logs and files that must be retained to meet the
retention policy, such as archived redo logs and other backups, then the flashback logs from the earliest SCNs may be
deleted to make room in the flash recovery area for other files.
When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN and
V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME as shown in this example:
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
Requirements for Using Guaranteed Restore Points
--------------------------------------------------------------------------
The COMPATIBLE initialization parameter must be set to 10.2 or greater.
The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your
database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.
A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed
restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required
logs in the flash recovery area.
If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed
restore point (or if all previously created guaranteed restore points have been dropped).
Note: There are no special requirements for using normal restore points.
Age Out Rules of Restore Points
-----------------------------------------------
Normal restore points eventually age out of the control file, even if not explicitly dropped.
The rules governing retention of restore points in the control file are:
-The most recent 2048 restore points are always kept in the control file, regardless of their age.
-Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained,
regardless of how many restore points are defined.
Normal restore points that do not meet either of these conditions may age out of the control file.
Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.
About Logging for Flashback Database and Guaranteed Restore Points
-------------------------------------------------------------------------------------------------------
If no files are eligible for deletion from the flash recovery area because of the requirements imposed by your retention policy
and the guaranteed restore point, then the database behaves as if it has encountered a disk full condition. In many
circumstances, this causes your database to halt.
・Logging for Guaranteed Restore Points With Flashback Logging Disabled
The available block images can be used to re-create the datafile contents at the time of a guaranteed restore point using
FLASHBACK DATABASE, but you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed
restore points and the current time, as is possible when logging for Flashback Database is enabled. If you need to return
the database to an intermediate point in time, your only option is database point-in-time recovery.
Because each block that changes is only logged once, disk space usage for logging for guaranteed restore points when
flashback logging is disabled is generally considerably less than normal flashback logging. You could maintain a guaranteed
restore point for days or even weeks without concern over the ongoing growth of flashback logs that occurs if logging for
Flashback Database is enabled. The performance overhead of logging for a guaranteed restore point without flashback
database logging is generally lower as well.
・Logging for Flashback Database With Guaranteed Restore Points Defined
If Flashback Database is enabled and one or more guaranteed restore points is defined, then the database performs normal
flashback logging, which causes some performance overhead and, depending upon the pattern of activity on your database,
can cause signifcant space pressure in the flash recovery area. However, unlike normal logging for Flashback Database,
the flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back
as the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure,
if they are required to satisfy the guarantee.
Estimating Disk Space Requirements for Flashback Database Logs
--------------------------------------------------------------------------------------------------
The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area
for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the
database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the
following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG
Rules for Retention and Deletion of Flashback Logs
--------------------------------------------------------------------------
The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:
A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space
in the flash recovery area.
A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the
oldest flashback log is reused instead.
If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make
space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of
FLASHBACK DATABASE are also deleted.
Obtain Information, Space Management Rules and How to Resolve Space Pressure
------------------------------------------------------------------------------------------------------------------------
You can query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space
reclaimable by deleting files, and total number of files in the Flash Recovery Area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose.
The following rules apply for files to become eligible for deletion from the Flash Recovery Area:
-Any backups which have become obsolete as per the retention policy.
-Any files in the Flash Recovery Area which has been already backed up to a tertiary device such as tape.
-Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.
There are a number of choices on how to resolve a full Flash Recovery Area when there are NO files eligible for deletion:
-Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space.
-Use the command BACKUP RECOVERY AREA, to back up the contents of the Flash Recovery Area to a tertiary
device such as tape.
-Delete unnecessary files from the Flash Recovery Area using the RMAN delete command.
-You may also need to consider changing your backup retention policy
-When using Data Guard, consider changing your archivelog deletion policy.
Reference:
Oracle® Database Backup and Recovery Basics
10g Release 2 (10.2)
Part Number B14192-03
(Data Protection with Restore Points and Flashback Database)
Oracle® Database Reference
10g Release 2 (10.2)
Part Number B14237-02
(DB_FLASHBACK_RETENTION_TARGET)
MetaLink Note:305812.1 Flash Recovery area - Space management Warning & Alerts
MetaLink Note:315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.
oracle 10.2.0.2
Restore point and flash recovery area space management
Definition
-------------
The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that
provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area.
RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery.
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark
or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However,
they also provide specific functionality related to the use of the Flashback Database feature.
About the Flashback Database Window
---------------------------------------------------------
DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may
be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the
flash recovery area.
The flashback retention target is a target, not an absolute guarantee that Flashback Database will be available.
If your flash recovery area is not large enough to hold both the flashback logs and files that must be retained to meet the
retention policy, such as archived redo logs and other backups, then the flashback logs from the earliest SCNs may be
deleted to make room in the flash recovery area for other files.
When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN and
V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME as shown in this example:
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
Requirements for Using Guaranteed Restore Points
--------------------------------------------------------------------------
The COMPATIBLE initialization parameter must be set to 10.2 or greater.
The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your
database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.
A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed
restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required
logs in the flash recovery area.
If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed
restore point (or if all previously created guaranteed restore points have been dropped).
Note: There are no special requirements for using normal restore points.
Age Out Rules of Restore Points
-----------------------------------------------
Normal restore points eventually age out of the control file, even if not explicitly dropped.
The rules governing retention of restore points in the control file are:
-The most recent 2048 restore points are always kept in the control file, regardless of their age.
-Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained,
regardless of how many restore points are defined.
Normal restore points that do not meet either of these conditions may age out of the control file.
Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.
About Logging for Flashback Database and Guaranteed Restore Points
-------------------------------------------------------------------------------------------------------
If no files are eligible for deletion from the flash recovery area because of the requirements imposed by your retention policy
and the guaranteed restore point, then the database behaves as if it has encountered a disk full condition. In many
circumstances, this causes your database to halt.
・Logging for Guaranteed Restore Points With Flashback Logging Disabled
The available block images can be used to re-create the datafile contents at the time of a guaranteed restore point using
FLASHBACK DATABASE, but you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed
restore points and the current time, as is possible when logging for Flashback Database is enabled. If you need to return
the database to an intermediate point in time, your only option is database point-in-time recovery.
Because each block that changes is only logged once, disk space usage for logging for guaranteed restore points when
flashback logging is disabled is generally considerably less than normal flashback logging. You could maintain a guaranteed
restore point for days or even weeks without concern over the ongoing growth of flashback logs that occurs if logging for
Flashback Database is enabled. The performance overhead of logging for a guaranteed restore point without flashback
database logging is generally lower as well.
・Logging for Flashback Database With Guaranteed Restore Points Defined
If Flashback Database is enabled and one or more guaranteed restore points is defined, then the database performs normal
flashback logging, which causes some performance overhead and, depending upon the pattern of activity on your database,
can cause signifcant space pressure in the flash recovery area. However, unlike normal logging for Flashback Database,
the flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back
as the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure,
if they are required to satisfy the guarantee.
Estimating Disk Space Requirements for Flashback Database Logs
--------------------------------------------------------------------------------------------------
The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area
for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the
database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the
following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG
Rules for Retention and Deletion of Flashback Logs
--------------------------------------------------------------------------
The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:
A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space
in the flash recovery area.
A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the
oldest flashback log is reused instead.
If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make
space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of
FLASHBACK DATABASE are also deleted.
Obtain Information, Space Management Rules and How to Resolve Space Pressure
------------------------------------------------------------------------------------------------------------------------
You can query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space
reclaimable by deleting files, and total number of files in the Flash Recovery Area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose.
The following rules apply for files to become eligible for deletion from the Flash Recovery Area:
-Any backups which have become obsolete as per the retention policy.
-Any files in the Flash Recovery Area which has been already backed up to a tertiary device such as tape.
-Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.
There are a number of choices on how to resolve a full Flash Recovery Area when there are NO files eligible for deletion:
-Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space.
-Use the command BACKUP RECOVERY AREA, to back up the contents of the Flash Recovery Area to a tertiary
device such as tape.
-Delete unnecessary files from the Flash Recovery Area using the RMAN delete command.
-You may also need to consider changing your backup retention policy
-When using Data Guard, consider changing your archivelog deletion policy.
Reference:
Oracle® Database Backup and Recovery Basics
10g Release 2 (10.2)
Part Number B14192-03
(Data Protection with Restore Points and Flashback Database)
Oracle® Database Reference
10g Release 2 (10.2)
Part Number B14237-02
(DB_FLASHBACK_RETENTION_TARGET)
MetaLink Note:305812.1 Flash Recovery area - Space management Warning & Alerts
MetaLink Note:315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.
RMAN: Full Recovery When the Recovery Catalog and Controlfile are Lost
当数据库使用controlfile存放repository而没有使用catalog的时候，丢失所有的controlfile就等于丢失repository。
前提是有controlfile的自动备份，或者知道手动备份的备份集
对应方法：
1.startup nomount
2.rman target /
3.set dbid=XXXXXXX
(如果不知道dbid，第4步时需要手动在备份集中指定controlfile的备份)
4.restore controlfile from autobackup;
(如果第3步没有设置dbid，restore controlfile from 'backupset_name';)
注意：rman只会搜索windows的％ORACLE_HOME%/database或unix的$ORACLE_HOME/dbs目录
5. sql 'alter database mount'
6.restore database
7.recover database
(如果你的online redo log也损坏了，需要指定until条件)
8.sql 'alter database open resetlogs'
9.重新备份数据库
前提是有controlfile的自动备份，或者知道手动备份的备份集
对应方法：
1.startup nomount
2.rman target /
3.set dbid=XXXXXXX
(如果不知道dbid，第4步时需要手动在备份集中指定controlfile的备份)
4.restore controlfile from autobackup;
(如果第3步没有设置dbid，restore controlfile from 'backupset_name';)
注意：rman只会搜索windows的％ORACLE_HOME%/database或unix的$ORACLE_HOME/dbs目录
5. sql 'alter database mount'
6.restore database
7.recover database
(如果你的online redo log也损坏了，需要指定until条件)
8.sql 'alter database open resetlogs'
9.重新备份数据库
Thursday, October 12, 2006
guidelines for db_block_size
Verdict~~~~~
Use db_block_size = 2048
only if you really know what you are doing. The best use of 2KB database blocks that I know is in stress tests in which you are trying to drive server workload artificially high so you can analyze the bottlenecks less expensively (i.e., without generating mountains of test data and test transactions).
Use db_block_size = 8192
for most large transactional processing systems. This represents a good balance between advantages and the disadvantage for undo segments.
Use db_block_size of larger than 8KB
for OLTP systems in which your data structures drive the block size to a naturally larger size. This will help to to avoid chained and migrated rows.
Use db_block_size of larger values than 8KB
for systems in which your undo generation is not a meaningful part of your workload. Data warehouses fit this profile. With bigger blocks, you reduce total system I/O setup costs dramatically, yet you incur none of the disadvantages that you would incur in an OLTP system because people generally are not executing transactions (inserts, updates, deletes, and selects for update).
The maximum size of a single index entry is approximately one-half the data block size(8i only).
block size maximum size of a single index entry
(tested on RHEL2.1, oracle8.1.7)
~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4KB 1578bytes
8KB 3218bytes
16KB 6498bytes
Data block size affacts the size of SGA(8i only)
The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:
DB_BLOCK_SIZE: The size, in bytes, of a single data block and database buffer.
DB_BLOCK_BUFFERS: The number of database buffers, each the size of DB_BLOCK_SIZE, allocated for the SGA. The total amount of space allocated for the database buffer cache in the SGA is DB_BLOCK_SIZE times DB_BLOCK_BUFFERS.
Please make a full and up-to-date backup of your database before export and import
Wednesday, October 11, 2006
changes of data types between 9.2 and 10.2
9.2
char column maximum size: 2000 bytes
long raw column maximum size: 2G
long column maximum size: 2G
raw column maximum size:2000 bytes
blob, clob, nclob maximum size: 4G
bfile maximum size: 4G
10.2
char column maximum size: 2000 bytes
long raw column maximum size: 2G
long column maximum size: 2G
raw column maximum size:2000 bytes
blob, clob, nclob maximum size: 8T~128T
bfile maximum size: 4G
char column maximum size: 2000 bytes
long raw column maximum size: 2G
long column maximum size: 2G
raw column maximum size:2000 bytes
blob, clob, nclob maximum size: 4G
bfile maximum size: 4G
10.2
char column maximum size: 2000 bytes
long raw column maximum size: 2G
long column maximum size: 2G
raw column maximum size:2000 bytes
blob, clob, nclob maximum size: 8T~128T
bfile maximum size: 4G
Database Hangs for DML Activity ORA-16038, ORA-00354, ORA-00312
fact: Oracle Server - Enterprise Edition 8.1.7.4.0
fact: Oracle Server - Enterprise Edition 9.0.1
fact: Oracle Server - Enterprise Edition 9.2
symptom: Database Hangs for DML Activity ORA-16038, ORA-00354, ORA-00312
symptom: Database hangs for dml (insert, update and delete) activity
symptom: ORA-16038: log %s sequence# %s cannot be archived
symptom: ORA-00354: corrupt redo log block header
symptom: ORA-00312: online log %s thread %s: '%s'
cause: Most probable a hardware failure caused an online redolog corruption.
If database is open then try for log number that is corrupted (check from error or v$log):
1 alter database clear logfile group;
When not working (unlikely to succeed) then:
2 alter database clear unarchived logfile group;
If this is successfully, the db is running (not hanging for dml activity anymore) but you miss an archive. This means you have to make a backup first, as long as the backup is not complete you cannot perform media recovery using previous backup as you miss an archive.
3 if 1 and 2 both do not work you will have to perform an incomplete recovery（注1）. Shutdown db, restore all datafiles (only datafiles) of a backup and startup mount and issue recover database until cancel and recover until corrupted logfile, then open with alter database open resetlogs; You have dataloss (last corrupted logfile) and you first have to make a new backup after db opens).If the database is closed and cannot be opened you only have option 3.
注1：
以上是metalink提供的解决办法，缺点是情况3时会丢失数据。
实际情况中发生类似3的时候，如果能够正常关闭数据库（immediate），可以先冷备数据库，尝试不用restore，直接recover database until cancel，然后open resetlogs。
这样不会丢失数据。
fact: Oracle Server - Enterprise Edition 9.0.1
fact: Oracle Server - Enterprise Edition 9.2
symptom: Database Hangs for DML Activity ORA-16038, ORA-00354, ORA-00312
symptom: Database hangs for dml (insert, update and delete) activity
symptom: ORA-16038: log %s sequence# %s cannot be archived
symptom: ORA-00354: corrupt redo log block header
symptom: ORA-00312: online log %s thread %s: '%s'
cause: Most probable a hardware failure caused an online redolog corruption.
If database is open then try for log number that is corrupted (check from error or v$log):
1 alter database clear logfile group
When not working (unlikely to succeed) then:
2 alter database clear unarchived logfile group
If this is successfully, the db is running (not hanging for dml activity anymore) but you miss an archive. This means you have to make a backup first, as long as the backup is not complete you cannot perform media recovery using previous backup as you miss an archive.
3 if 1 and 2 both do not work you will have to perform an incomplete recovery（注1）. Shutdown db, restore all datafiles (only datafiles) of a backup and startup mount and issue recover database until cancel and recover until corrupted logfile, then open with alter database open resetlogs; You have dataloss (last corrupted logfile) and you first have to make a new backup after db opens).If the database is closed and cannot be opened you only have option 3.
注1：
以上是metalink提供的解决办法，缺点是情况3时会丢失数据。
实际情况中发生类似3的时候，如果能够正常关闭数据库（immediate），可以先冷备数据库，尝试不用restore，直接recover database until cancel，然后open resetlogs。
这样不会丢失数据。
Tuesday, October 10, 2006
Points to Consider for Full Database Exports and Imports
A full database export and import can be a good way to replicate or clean up a database. However, to avoid problems be sure to keep the following points in mind:
1.A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before or after the full import. Oracle recommends that you re-create them after the import in case they define actions that would impede progress of the import.
2.If possible, before beginning, make a physical copy of the exported database and the database into which you intend to import. This ensures that any mistakes are reversible.
3.Before you begin the export, it is advisable to produce a report that includes the following information:
(1)A list of tablespaces and datafiles
(2)A list of rollback segments
(3)A count, by user, of each object type such as tables, indexes, and so on
This information lets you ensure that tablespaces have already been created and that the import was successful
4.If you are creating a completely new database from an export, remember to create an extra rollback segment in SYSTEM and to make it available in your initialization parameter file (init.ora)before proceeding with the import.
5.When you perform the import, ensure you are pointing at the correct instance. This is very important because on some UNIX systems, just the act of entering a subshell can change the database against which an import operation was performed.
6.Do not perform a full import on a system that has more than one database unless you are certain that all tablespaces have already been created. A full import creates any undefined tablespaces using the same datafile names as the exported database. This can result in problems in the following situations:
(1)If the datafiles belong to any other database, they will become corrupted. This is especially true if the exported database is on the same system, because its datafiles will be reused by the database into which you are importing.
(2)
If the datafiles have names that conflict with existing operating system files.
1.A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before or after the full import. Oracle recommends that you re-create them after the import in case they define actions that would impede progress of the import.
2.If possible, before beginning, make a physical copy of the exported database and the database into which you intend to import. This ensures that any mistakes are reversible.
3.Before you begin the export, it is advisable to produce a report that includes the following information:
(1)A list of tablespaces and datafiles
(2)A list of rollback segments
(3)A count, by user, of each object type such as tables, indexes, and so on
This information lets you ensure that tablespaces have already been created and that the import was successful
4.If you are creating a completely new database from an export, remember to create an extra rollback segment in SYSTEM and to make it available in your initialization parameter file (init.ora)before proceeding with the import.
5.When you perform the import, ensure you are pointing at the correct instance. This is very important because on some UNIX systems, just the act of entering a subshell can change the database against which an import operation was performed.
6.Do not perform a full import on a system that has more than one database unless you are certain that all tablespaces have already been created. A full import creates any undefined tablespaces using the same datafile names as the exported database. This can result in problems in the following situations:
(1)If the datafiles belong to any other database, they will become corrupted. This is especially true if the exported database is on the same system, because its datafiles will be reused by the database into which you are importing.
(2)
If the datafiles have names that conflict with existing operating system files.
Removing a Node from a 10g RAC Cluster
Metalink：Note:269320.1
PURPOSE
-------------
The purpose of this note is to provide the user with a document that can be used as a guide to remove a cluster node from an Oracle 10g RealApplications environment.
REMOVING A NODE FROM A 10g RAC CLUSTER
--------------------------------------------------------------
If you have to remove a node from a RAC 10g database, even if the node will no longer be available to the environment, there is a certain amount of cleanup that needs to be done. The remaining nodes need to be informed of the change of status of the departing node.
The most important 3 steps that need to be followed are;
A. Remove the instance using DBCA.
B. Remove the node from the cluster.
C. Reconfigure the OS and remaining hardware.
Here is a breakdown of the above steps.
A. Remove the instance using DBCA.
--------------------------------------
1.Verify that you have a good backup of the OCR (Oracle Configuration Repository) using ocrconfig -showbackup.
2. Run DBCA from one of the nodes you are going to keep. Leave the database up and also leave the departing instance up and running.
3. Choose "Instance Management"
4. Choose "Delete an instance"
5.On the next screen, select the cluster database from which you will delete an instance. Supply the system privilege username and password.
6. On the next screen, a list of cluster database instances will appear. Highlight the instance you would like to delete then click next.
7. If you have services configured, reassign the services. Modify the services so that each service can run on one of the remaining instances. Set "not used" for each service regarding the instance that is to be deleted. Click Finish.
8. If your database is in archive log mode you may encounter the following errors:
ORA-350
ORA-312
This may occur because the DBCA cannot drop the current log, as it needs archiving. This issue is fixed in the 10.1.0.3 patchset. But previous to this patchset you should click the ignore button and when the DBCA completes, manually archive the logs for the deleted instance and dropt the log group.
SQL> alter system archive log all;
SQL> alter database drop logfile group 2;
9. Verify that the dropped instance's redo thread has been removed by querying v$log. If for any reason the redo thread is not disabled then disable the thread.
SQL> alter database disable public thread 2;
10.Verify that the instance was removed from the OCR (Oracle Configuration Repository) with the following commands:
srvctl config database -d
$ORA_CRS_HOME/bin/./crs_stat
11. If this node had an ASM instance and the node will no longer be a part of the cluster you will now need to remove the ASM instance with:
srvctl stop asm -n
srvctl remove asm -n
Verify that asm is removed with:
srvctl config asm -n
B. Remove the Node from the Cluster
---------------------------------------------
Once the instance has been deleted. The process of removing the node from the cluster is a manual process. This is accomplished by running scripts on the deleted node to remove the CRS install, as well as scripts on the remaining nodes to update the node list. The following steps assume that the node to be removed is still functioning.
1.First as the root user determine the node name and node number on each node as stored in the Cluster Registry.
From the CRS_HOME/bin
olsnodes -n
1
2
2. To delete node number 2 first stop and remove the nodeapps on the node you are removing. Assuming that you have removed the ASM instance as the root user on a remaining node;
srvctl stop nodeapps -n
3.Run NETCA. Choose "Cluster Configuration".
4. Only select the node you are removing and click next.
5. Choose "Listener Configuration" and click next.
6. Choose "Delete" and delete any listeners configured on the node you are removing.
7. Run $ORA_CRS_HOME/bin/crs_stat. Make sure that all database resources are running on nodes that are going to be kept.
For example:
NAME=ora..db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on
Ensure that this resource is not running on a node that will be removed. Use $ORA_CRS_HOME/bin/crs_relocate to perform this.
Example:
crs_relocate ora..db
8. As the root user, remove the nodeapps on the node you are removing.
srvctl remove nodeapps -n
9. Next as the Oracle user run the installer with the updateNodeList option on the node you are deleting.
a. DISPLAY=ipaddress:0.0; export DISPLAY
This should be set even though the gui does not run.
b. $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/10g CLUSTER_NODES=, ,
This should be run as one command. With this command we are defining the nodes that now are part of the cluster.
10. Change to the root user to finish the removal on the node that is being removed(在准备删除的节点上运行). This command will stop the CRS stack and delete the ocr.loc file on the node to be removed. The nosharedvar option assumes the ocr.loc file is not on a shared file sytem. If it does exist on a shared file system then specify sharedvar instead. Run the rootdelete.sh script from $ORA_CRS_HOME/install.
Example:
$ORA_CRS_HOME/install/./rootdelete.sh remote nosharedvar
11. On a node that will be kept, the root user should run the rootdeletenode.sh script from the $ORA_CRS_HOME/install directory. When running this script from the CRS home specify both the node name and the node number. The node name and the node number are visiable in olsnodes -n. Also do NOT put a space after the comma between the two.
olsnodes -n
1
2
$ORA_CRS_HOME/install> ./.rootdeletenode.sh,2
12. Confirm success by running OLSNODES.
$ORA_CRS_HOME/bin>: ./olsnodes -n
1
13. Now switch back to the oracle user account and run the same runInstaller command as before. Run it this time from the ORA_CRS_HOME instead of the ORACLE_HOME. Specify all of the remaining nodes.
a. DISPLAY=ipaddress:0.0; export DISPLAY
b. $ORA_CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME= CLUSTER_NODES=, , CRS=TRUE
14. Once the node updates are done you will need to manually delete the $ORACLE_HOME and $CRS_HOME from the node to be expunged, unless, of course, either of these is on a shared file system that is still being used.
a. $ORACLE_HOME>: rm -rf *
b. $CRS_HOME> : rm -rf * (as root)
15. Next, as root, from the deleted node, verify that all init scripts and soft links are removed:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
Linux:
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
16. You can also remove the /etc/oracle directory, the /etc/oratab file, and the Oracle inventory (if desired)
17.To remove an ADDITIONAL ORACLE_HOME or EM_HOME from the inventory on all remaining nodes, run the installer to update the node list. Example (if removing node 2):
runInstaller -updateNodeList -local ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=node1,node3,node4
PURPOSE
-------------
The purpose of this note is to provide the user with a document that can be used as a guide to remove a cluster node from an Oracle 10g RealApplications environment.
REMOVING A NODE FROM A 10g RAC CLUSTER
--------------------------------------------------------------
If you have to remove a node from a RAC 10g database, even if the node will no longer be available to the environment, there is a certain amount of cleanup that needs to be done. The remaining nodes need to be informed of the change of status of the departing node.
The most important 3 steps that need to be followed are;
A. Remove the instance using DBCA.
B. Remove the node from the cluster.
C. Reconfigure the OS and remaining hardware.
Here is a breakdown of the above steps.
A. Remove the instance using DBCA.
--------------------------------------
1.Verify that you have a good backup of the OCR (Oracle Configuration Repository) using ocrconfig -showbackup.
2. Run DBCA from one of the nodes you are going to keep. Leave the database up and also leave the departing instance up and running.
3. Choose "Instance Management"
4. Choose "Delete an instance"
5.On the next screen, select the cluster database from which you will delete an instance. Supply the system privilege username and password.
6. On the next screen, a list of cluster database instances will appear. Highlight the instance you would like to delete then click next.
7. If you have services configured, reassign the services. Modify the services so that each service can run on one of the remaining instances. Set "not used" for each service regarding the instance that is to be deleted. Click Finish.
8. If your database is in archive log mode you may encounter the following errors:
ORA-350
ORA-312
This may occur because the DBCA cannot drop the current log, as it needs archiving. This issue is fixed in the 10.1.0.3 patchset. But previous to this patchset you should click the ignore button and when the DBCA completes, manually archive the logs for the deleted instance and dropt the log group.
SQL> alter system archive log all;
SQL> alter database drop logfile group 2;
9. Verify that the dropped instance's redo thread has been removed by querying v$log. If for any reason the redo thread is not disabled then disable the thread.
SQL> alter database disable public thread 2;
10.Verify that the instance was removed from the OCR (Oracle Configuration Repository) with the following commands:
srvctl config database -d
$ORA_CRS_HOME/bin/./crs_stat
11. If this node had an ASM instance and the node will no longer be a part of the cluster you will now need to remove the ASM instance with:
srvctl stop asm -n
srvctl remove asm -n
Verify that asm is removed with:
srvctl config asm -n
B. Remove the Node from the Cluster
---------------------------------------------
Once the instance has been deleted. The process of removing the node from the cluster is a manual process. This is accomplished by running scripts on the deleted node to remove the CRS install, as well as scripts on the remaining nodes to update the node list. The following steps assume that the node to be removed is still functioning.
1.First as the root user determine the node name and node number on each node as stored in the Cluster Registry.
From the CRS_HOME/bin
olsnodes -n
2. To delete node number 2 first stop and remove the nodeapps on the node you are removing. Assuming that you have removed the ASM instance as the root user on a remaining node;
srvctl stop nodeapps -n
3.Run NETCA. Choose "Cluster Configuration".
4. Only select the node you are removing and click next.
5. Choose "Listener Configuration" and click next.
6. Choose "Delete" and delete any listeners configured on the node you are removing.
7. Run $ORA_CRS_HOME/bin/crs_stat. Make sure that all database resources are running on nodes that are going to be kept.
For example:
NAME=ora.
TYPE=application
TARGET=ONLINE
STATE=ONLINE on
Ensure that this resource is not running on a node that will be removed. Use $ORA_CRS_HOME/bin/crs_relocate to perform this.
Example:
crs_relocate ora.
8. As the root user, remove the nodeapps on the node you are removing.
srvctl remove nodeapps -n
9. Next as the Oracle user run the installer with the updateNodeList option on the node you are deleting.
a. DISPLAY=ipaddress:0.0; export DISPLAY
This should be set even though the gui does not run.
b. $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/10g CLUSTER_NODES=
This should be run as one command. With this command we are defining the nodes that now are part of the cluster.
10. Change to the root user to finish the removal on the node that is being removed(在准备删除的节点上运行). This command will stop the CRS stack and delete the ocr.loc file on the node to be removed. The nosharedvar option assumes the ocr.loc file is not on a shared file sytem. If it does exist on a shared file system then specify sharedvar instead. Run the rootdelete.sh script from $ORA_CRS_HOME/install.
Example:
$ORA_CRS_HOME/install/./rootdelete.sh remote nosharedvar
11. On a node that will be kept, the root user should run the rootdeletenode.sh script from the $ORA_CRS_HOME/install directory. When running this script from the CRS home specify both the node name and the node number. The node name and the node number are visiable in olsnodes -n. Also do NOT put a space after the comma between the two.
olsnodes -n
$ORA_CRS_HOME/install> ./.rootdeletenode.sh
12. Confirm success by running OLSNODES.
$ORA_CRS_HOME/bin>: ./olsnodes -n
13. Now switch back to the oracle user account and run the same runInstaller command as before. Run it this time from the ORA_CRS_HOME instead of the ORACLE_HOME. Specify all of the remaining nodes.
a. DISPLAY=ipaddress:0.0; export DISPLAY
b. $ORA_CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=
14. Once the node updates are done you will need to manually delete the $ORACLE_HOME and $CRS_HOME from the node to be expunged, unless, of course, either of these is on a shared file system that is still being used.
a. $ORACLE_HOME>: rm -rf *
b. $CRS_HOME> : rm -rf * (as root)
15. Next, as root, from the deleted node, verify that all init scripts and soft links are removed:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
Linux:
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
16. You can also remove the /etc/oracle directory, the /etc/oratab file, and the Oracle inventory (if desired)
17.To remove an ADDITIONAL ORACLE_HOME or EM_HOME from the inventory on all remaining nodes, run the installer to update the node list. Example (if removing node 2):
runInstaller -updateNodeList -local ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=node1,node3,node4
Saturday, October 07, 2006
the way to know which transaction is using a large number of undo space
select used_ublk from v$transaction order by used_ublk desc;
Thursday, September 28, 2006
daily work of a dba(未排版)
ORACLE数据库管理员的职责 ORACLE数据库管理员应按如下方式对ORACLE数据库系统做定期监控： (1). 每天对ORACLE数据库的运行状态,日志文件,备份情况,数据 库的空间使用情况,系统资源的使用情况进行检查,发现并解决 问题。 (2). 每周对数据库对象的空间扩展情况,数据的增长情况进行监控,对数据库做健康检查,对数据库对象的状态做检查。 (3). 每月对表和索引等进行Analyze,检查表空间碎片,寻找数据库 性能调整的机会,进行数据库性能调整,提出下一步空间管理 计划。对ORACLE数据库状态进行一次全面检查。 每天的工作 (1).确认所有的INSTANCE状态正常 登陆到所有数据库或例程,检测ORACLE后台进程: $ps –efgrep ora (2). 检查文件系统的使用（剩余空间）。如果文件系统的剩余空间小于20%，需删除不用的文件以释放空间。 $df –k (3). 检查日志文件和trace文件记录alert和trace文件中的错误。 连接到每个需管理的系统 ? 使用’telnet’ ? 对每个数据库,cd 到bdump目录,通常是$ORACLE_BASE//bdump ? 使用 Unix ‘tail’命令来查看alert_.log文件 ? 如果发现任何新的ORA- 错误,记录并解决 (4). 检查数据库当日备份的有效性。 对RMAN备份方式: 检查第三方备份工具的备份日志以确定备份是否成功 对EXPORT备份方式: 检查exp日志文件以确定备份是否成功 对其他备份方式: 检查相应的日志文件 (5). 检查数据文件的状态记录状态不是“online”的数据文件，并做恢复。 Select file_name from dba_data_files where status=’OFFLINE’ (6). 检查表空间的使用情况 SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') '%' AS pct_free FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name), ( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m, count(blocks) AS count_blocks, sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name (7). 检查剩余表空间 SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) /(1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks FROM dba_free_space GROUP BY tablespace_name; (8). 监控数据库性能 运行bstat/estat生成系统报告 或者使用statspack收集统计数据 (9). 检查数据库性能，记录数据库的cpu使用、IO、buffer命中率等等 使用vmstat,iostat,glance,top等命令 (10). 日常出现问题的处理。 每周的工作 (1). 控数据库对象的空间扩展情况 根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相 应的措施 -- 删除历史数据 --- 扩表空间 alter tablespace add datafile ‘’ size --- 调整数据对象的存储参数 next extent pct_increase (2). 监控数据量的增长情况 根据本周每天的检查情况找到记录数量增长很快的数据库对象,并采 取相应的措施 -- 删除历史数据 --- 扩表空间 alter tablespace add datafile ‘’ size (3). 系统健康检查 检查以下内容: init.ora controlfile redo log file archiving sort area size tablespace(system,temporary,tablespace fragment) datafiles(autoextend,location) object(number of extent,next extent,index) rollback segment logging &tracing(alert.log,max_dump_file_size,sqlnet) (4). 检查无效的数据库对象 SELECT owner, object_name, object_type FROM dba_objects WHERE status=’INVALID’。 (5). 检查不起作用的约束 SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status = 'DISABLED’ AND constraint_type = 'P' (6). 检查无效的trigger SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED’ 每月的工作 (1). Analyze Tables/Indexes/Cluster analyze table estimate statistics sample 50 percent; (2). 检查表空间碎片 根据本月每周的检查分析数据库碎片情况,找到相应的解决方法 (3). 寻找数据库性能调整的机会 比较每天对数据库性能的监控报告,确定是否有必要对数据库性能进 行调整 (4). 数据库性能调整 如有必要,进行性能调整 (5). 提出下一步空间管理计划 根据每周的监控,提出空间管理的改进方法Oracle DBA 日常管理 目的：这篇文档有很详细的资料记录着对一个甚至更多的ORACLE 数据库每天的，每月的， 每年的运行的状态的结果及检查的结果，在文档的附录中你将会看到所有检查，修改的SQL 和PL/SQL 代码。 目录 1.日常维护程序 A． 检查已起的所有实例 B． 查找一些新的警告日志 C． 检查DBSNMP 是否在运行 D． 检查数据库备份是否正确 E． 检查备份到磁带中的文件是否正确 F． 检查数据库的性能是否正常合理，是否有足够的空间和资源 G． 将文档日志复制到备份的数据库中 H． 要常看DBA 用户手册 2.晚间维护程序 A．收集VOLUMETRIC 的数据 3.每周维护工作 A． 查找那些破坏规则的OBJECT B． 查找是否有违反安全策略的问题 C． 查看错误地方的SQL*NET 日志 D． 将所有的警告日志存档 E． 经常访问供应商的主页 4.月维护程序 A． 查看对数据库会产生危害的增长速度 B． 回顾以前数据库优化性能的调整 C． 查看I/O 的屏颈问题 D． 回顾FRAGMENTATION E． 将来的执行计划 F． 查看调整点和维护 5.附录 A． 月维护过程 B． 晚间维护过程 C． 周维护过程 6.参考文献 ---------------------------------------------------------------- 一．日维护过程 A．查看所有的实例是否已起 确定数据库是可用的，把每个实例写入日志并且运行日报告或是运行测试 文件。当然有一些操作我们是希望它能自动运行的。 可选择执行：用ORACLE 管理器中的‘PROBE’事件来查看 B．查找新的警告日志文件 1. 联接每一个操作管理系统 2. 使用‘TELNET’或是可比较程序 3. 对每一个管理实例，经常的执行$ORACLE_BASE//bdump 操 作，并使其能回退到控制数据库的SID。 4. 在提示下，使用UNIX 中的‘TAIL’命令查看alert_.log，或是 用其他方式检查文件中最近时期的警告日志 5. 如果以前出现过的一些ORA_ERRORS 又出现，将它记录到数据库 恢复日志中并且仔细的研究它们，这个数据库恢复日志在〈FILE〉中 C．查看DBSNMP 的运行情况 检查每个被管理机器的‘DBSNMP’进程并将它们记录到日志中。 在UNIX 中，在命令行中，键入ps –ef grep dbsnmp,将回看到2 个 DBSNMP 进程在运行。如果没有，重启DBSNMP。 D．查数据库备份是否成功 E．检查备份的磁带文档是否成功 F．检查对合理的性能来说是否有足够的资源 1. 检查在表空间中有没有剩余空间。 对每一个实例来说，检查在表空间中是否存在有剩余空间来满足当天 的预期的需要。当数据库中已有的数据是稳定的，数据日增长的平均 数也是可以计算出来，最小的剩余空间至少要能满足每天数据的增 长。 A） 运行‘FREE.SQL’来检查表空间的剩余空间。 B） 运行‘SPACE.SQL’来检查表空间中的剩余空间百分率 2. 检查回滚段 回滚段的状态一般是在线的，除了一些为复杂工作准备的专用 段，它一般状态是离线的。 a) 每个数据库都有一个回滚段名字的列表。 b) 你可以用V$ROLLSTAT 来查询在线或是离线的回滚段的现在状 态. c) 对于所有回滚段的存储参数及名字， 可用 DBA_ROLLBACK_SEGS 来查询。但是它不如V$ROLLSTAT 准确。 3. 识别出一些过分的增长 查看数据库中超出资源或是增长速度过大的段，这些段的存储参 数需要调整。 a） 收集日数据大小的信息， 可以用 ‘ANALYZE5PCT.SQL’。如果你收集的是每晚的信息， 则可跳过这一步。 b） 检查当前的范围，可用‘NR.EXTENTS.SQL’。 c） 查询当前表的大小信息。 d） 查询当前索引大小的信息。 e） 查询增长趋势。 4. 确定空间的范围。 如果范围空间对象的NEXT_EXTENT 比表空间所能提供的最大范 围还要大，那么这将影响数据库的运行。如果我们找到了这个目标，可 以用‘ALTER TABLESPACE COALESCE’调查它的位置，或加另外 的数据文件。 A）运行‘SPACEBOUND.SQL’。如果都是正常的，将不返回任何行。 5. 回顾CPU，内存，网络，硬件资源论点的过程 A）检查CPU的利用情况，进到x:\web\phase2\default.htm =>system metrics=>CPU 利用页，CPU 的最大限度为400，当CPU 的占用保持 在350 以上有一段时间的话，我们就需要查看及研究出现的问题。 G．将存档日志复制到备用数据库中 如果有一个备用数据库，将适当的存档日志复制到备用数据库的期望 位置，备用数据库中保存最近期的数据。 H. 经常查阅DBA 用户手册 如果有可能的话，要广泛的阅读，包括DBA 手册，行业杂志，新闻 组或是邮件列表。 ------------------------------------------------------------- 二．晚间维护过程 大部分的数据库产品将受益于每晚确定的检查进程的运行。 A. 收集VOLUMETRIC 数据 1. 分析计划和收集数据 更准确的分析计算并保存结果。 a） 如果你现在没有作这些的话，用‘MK VOLFACT.SQL’来创建测定体积的 表。 b） 收集晚间数据大小的信息，用‘ANALYZE COMP.SQL’。 c） 收集统计结果，用‘POP VOL.SQL’。 d） 在空闲的时候检查数据，可能的话，每周或每个月进行。 我是用MS EXCEL 和ODBC 的联接来检查数据和图表的增长 ------------------------------------------------------------- 三．每周维护过程 A． 查找被破坏的目标 1. 对于每个给定表空间的对象来说，NEXT_EXTENT 的大小是相同的，如 12/14/98，缺省的NEXT_EXTENT 的DATAHI 为1G，DATALO 为500MB， INDEXES 为256MB。 A） 检查NEXT_EXTENT 的设置，可用‘NEXTEXT。SQL’。 B） 检查已有的EXTENTS，可用‘EXISTEXT。SQL’。 2. 所有的表都应该有唯一的主键 a） 查看那些表没有主键，可用‘NO_PK.SQL’。 b） 查找那些主键是没有发挥作用的，可用‘DIS_PK.SQL’。 c） 所有作索引的主键都要是唯一的，可用‘ NONUPK。SQL’来检 查。 3. 所有的索引都要放到索引表空间中。运行‘MKREBUILD_IDX。SQL’ 4. 不同的环境之间的计划应该是同样的，特别是测试环境和成品环境之间的 计划应该相同。 a） 检查不同的2 个运行环境中的数据类型是否一致，可用 ‘DATATYPE.SQL’。 b） 在2 个不同的实例中寻找对象的不同点， 可用 ‘OBJ_COORD.SQL’。 c） 更好的做法是，使用一种工具，象寻求软件的计划管理器那样的 工具。 B． 查看是否有危害到安全策略的问题。 C． 查看报错的SQL*NET 日志。 1. 客户端的日志。 2. 服务器端的日志。 D．.将所有的警告日志存档 E．.供应商的主页 1. ORACLE 供应商 http://www.oracle.com http://technet.oracle.com http://www.oracle.com/support http://www.oramag.com 2. Quest Software http://www.quests.com 3. Sun Microsystems http://www.sun.com ---------------------------------------------------------------- 四．月维护过程 A．查看对数据库会产生危害的增长速度 1. 从以前的记录或报告中回顾段增长的变化以此来确定段增长带来危害 B． 回顾以前数据库优化性能的调整 1. 回顾一般ORACLE 数据库的调整点，比较以前的报告来确定有害的发展 趋势。 C． 查看I/O 的屏颈问题 1. 查看前期数据库文件的活动性，比较以前的输出来判断有可能导致屏颈 问题的趋势。 D． 回顾FRAGMENTATION E． 计划数据库将来的性能 1. 比较ORACLE 和操作系统的CPU，内存，网络，及硬盘的利用率以此 来确定在近期将会有的一些资源争夺的趋势 2. 当系统将超出范围时要把性能趋势当作服务水平的协议来看 F． 完成调整和维护工作 1.使修改满足避免系统资源的争夺的需要，这里面包括增加新资源或使预期 的停工。 ---------------------------------------------------------------- 五．附录 A. 日常程序 -- free.sql --To verify free space in tablespaces --Minimum amount of free space --document your thresholds: -- = m SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks FROM dba_free_space GROUP BY tablespace_name 1. Space.sql -- space.sql -- To check free, pct_free, and allocated space within a tablespace -- 11/24/98 SELECT tablespace_name, largest_free_chunk , nr_free_chunks, sum_alloc_blocks, sum_free_blocks , to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') '%' AS pct_free FROM ( SELECT tablespace_name , sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name ) , ( SELECT tablespace_name AS fs_ts_name , max(blocks) AS largest_free_chunk , count(blocks) AS nr_free_chunks , sum(blocks) AS sum_free_blocks FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name 2. analyze5pct.sql -- analyze5pct.sql -- To analyze tables and indexes quickly, using a 5% sample size -- (do not use this script if you are performing the overnight -- collection of volumetric data) -- 11/30/98 BEGIN dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ; END ; /3. nr_extents.sql -- nr_extents.sql -- To find out any object reaching -- extents, and manually upgrade it to allow unlimited -- max_extents (thus only objects we *expect* to be big -- are allowed to become big) -- 11/30/98 SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents , to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB FROM dba_extents e , dba_segments s WHERE e.segment_name = s.segment_name GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents HAVING count(*) > &THRESHOLD OR ( ( s.max_extents - count(*) ) < &&THRESHOLD ) ORDER BY count(*) desc 4. spacebound.sql -- spacebound.sql -- To identify space-bound objects. If all is well, no rows are returned. -- If any space-bound objects are found, look at value of NEXT extent -- size to figure out what happened. -- Then use coalesce (alter tablespace coalesce . -- Lastly, add another datafile to the tablespace if needed. -- 11/30/98 SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a, ( SELECT tablespace_name, max(bytes) as big_chunk FROM dba_free_space GROUP BY tablespace_name ) f WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk B. 每晚处理程序 1. mk_volfact.sql -- mk_volfact.sql (only run this once to set it up; do not run it nightly!) -- -- Table UTL_VOL_FACTS CREATE TABLE utl_vol_facts ( table_name VARCHAR2(30), num_rows NUMBER, meas_dt DATE ) TABLESPACE platab STORAGE ( INITIAL 128k NEXT 128k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS unlimited ) / -- Public Synonym CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts / -- Grants for UTL_VOL_FACTS GRANT SELECT ON utl_vol_facts TO public / 2. analyze_comp.sql -- -- analyze_comp.sql -- BEGIN sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE'); END ; / 3. pop_vol.sql -- -- pop_vol.sql -- insert into utl_vol_facts select table_name , NVL ( num_rows, 0) as num_rows , trunc ( last_analyzed ) as meas_dt from all_tables -- or just user_tables where owner in ('&OWNER') -- or a comma-separated list of owners / commit / C. 每周处理程序 1. nextext.sql -- -- nextext.sql -- -- To find tables that don't match the tablespace default for NEXT extent. -- The implicit rule here is that every table in a given tablespace should -- use the exact same value for NEXT, which should also be the tablespace's -- default value for NEXT. -- -- This tells us what the setting for NEXT is for these objects today. -- -- 11/30/98 SELECT segment_name, segment_type, ds.next_extent as Actual_Next , dt.tablespace_name, dt.next_extent as Default_Next FROM dba_tablespaces dt, dba_segments ds WHERE dt.tablespace_name = ds.tablespace_name AND dt.next_extent !=ds.next_extent AND ds.owner = UPPER ( '&OWNER' ) ORDER BY tablespace_name, segment_type, segment_name 2. existext.sql -- -- existext.sql -- -- To check existing extents -- -- This tells us how many of each object's extents differ in size from -- the tablespace's default size. If this report shows a lot of different -- sized extents, your free space is likely to become fragmented. If so, -- this tablespace is a candidate for reorganizing. -- -- 12/15/98 SELECT segment_name, segment_type , count(*) as nr_exts , sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts , dt.tablespace_name, dt.next_extent as dflt_ext_size FROM dba_tablespaces dt, dba_extents dx WHERE dt.tablespace_name = dx.tablespace_name AND dx.owner = '&OWNER' GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent 3. No_pk.sql -- -- no_pk.sql -- -- To find tables without PK constraint -- -- 11/2/98 SELECT table_name FROM all_tables WHERE owner = '&OWNER' MINUS SELECT table_name FROM all_constraints WHERE owner = '&&OWNER' AND constraint_type = 'P' 4. disPK.sql -- -- disPK.sql -- -- To find out which primary keys are disabled -- -- 11/30/98 SELECT owner, constraint_name, table_name, status FROM all_constraints WHERE owner = '&OWNER' AND status = 'DISABLED’ AND constraint_type = 'P' 5. nonuPK.sql -- -- nonuPK.sql -- -- To find tables with nonunique PK indexes. Requires that PK names -- follow a naming convention. An alternative query follows that -- does not have this requirement, but runs more slowly. -- -- 11/2/98 SELECT index_name, table_name, uniqueness FROM all_indexes WHERE index_name like '&PKNAME%' AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE' SELECT c.constraint_name, i.tablespace_name, i.uniqueness FROM all_constraints c , all_indexes i WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE' AND c.constraint_type = 'P' AND i.index_name = c.constraint_name 6. mkrebuild_idx.sql -- -- mkrebuild_idx.sql -- -- Rebuild indexes to have correct storage parameters -- -- 11/2/98 SELECT 'alter index ' index_name ' rebuild ' , 'tablespace INDEXES storage ' ' ( initial 256 K next 256 K pctincrease 0 ) ; ' FROM all_indexes WHERE ( tablespace_name != 'INDEXES' OR next_extent != ( 256 * 1024 ) ) AND owner = '&OWNER' / 7. datatype.sql -- -- datatype.sql -- -- To check datatype consistency between two environments -- -- 11/30/98 SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable FROM all_tab_columns -- first environment WHERE owner = '&OWNER' MINUS SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable FROM all_tab_columns@&my_db_link -- second environment WHERE owner = '&OWNER2' order by table_name, column_name 8. obj_coord.sql -- -- obj_coord.sql -- -- To find out any difference in objects between two instances -- -- 12/08/98 SELECT object_name, object_type FROM user_objects MINUS SELECT object_name, object_type FROM user_objects@&my_db_link 六. 参考文献 1. Loney, Kevin Oracle8 DBA Handbook 2. Cook, David Database Management from Crisis to Confidence [http://www.orapub.com/] 3. Cox, Thomas B. The Database Administration Maturity Model
Thursday, September 21, 2006
RHEL2.1/3/4以及windows上oracle VLM的设置
RHEL2.1/3/4
Meta：317055.1，317141.1，200266.1
windows
Meta：46001.1，46053.1，225349.1
Meta：317055.1，317141.1，200266.1
windows
Meta：46001.1，46053.1，225349.1
32bit windows中/3GB,/PAE以及AWE的对比
32bit windows将默认最大可识别内存（4G）中的2G预留给了自己的内核，
而/3GB开关将这部分预留的大小从2G减为1G。
/PAE扩大了原有的32位寻址方式，使得windows可以识别更多的内存，但是仍然没有改变每个进程仅能使用4G内存的限制。
AWE使用的内存映射功能，配合/PAE可以使单个进程使用大量的内存（不受4G限制）。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
/3GB和/PAE共同使用，windows无法识别任何16G以上的内存。
/PAE单独使用（注1），每个进程最多使用4G内存。
注1：/PAE开关同时打开或者关闭pae和awe功能，
但是只有在对oracle等程序进行相应的配置后，
AWE功能才被使用。
而/3GB开关将这部分预留的大小从2G减为1G。
/PAE扩大了原有的32位寻址方式，使得windows可以识别更多的内存，但是仍然没有改变每个进程仅能使用4G内存的限制。
AWE使用的内存映射功能，配合/PAE可以使单个进程使用大量的内存（不受4G限制）。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
/3GB和/PAE共同使用，windows无法识别任何16G以上的内存。
/PAE单独使用（注1），每个进程最多使用4G内存。
注1：/PAE开关同时打开或者关闭pae和awe功能，
但是只有在对oracle等程序进行相应的配置后，
AWE功能才被使用。
the instance needs recovery and all controlfiles are damaged
Scenario:
1.your database needs recovery(i.e. instance crash, power off, media problem)
2.all of the controlfiles are damaged
3.you have a available backup, all the archivelogs.
Solution:
1.restore all the files needs restoration
2.recover database using backup controlfile until cancel
3.print "auto" when prompted
4.recover database using backup controlfile until cancel
Go to step 5 and step 6 if online redo logs are damaged, go to step 7 and step 8 if not
5.print "cancel"
6.alter database open resetlogs
7.print the name of one online log(you may have to try several times to find which one is correct)
8.alter database open
1.your database needs recovery(i.e. instance crash, power off, media problem)
2.all of the controlfiles are damaged
3.you have a available backup, all the archivelogs.
Solution:
1.restore all the files needs restoration
2.recover database using backup controlfile until cancel
3.print "auto" when prompted
4.recover database using backup controlfile until cancel
Go to step 5 and step 6 if online redo logs are damaged, go to step 7 and step 8 if not
5.print "cancel"
6.alter database open resetlogs
7.print the name of one online log(you may have to try several times to find which one is correct)
8.alter database open
Wednesday, September 20, 2006
oracle10g中Segment shrink online不适用的场合
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
IOT mapping tables
Tables with rowid based materialized views
Tables with function-based indexes
IOT mapping tables
Tables with rowid based materialized views
Tables with function-based indexes
Tuesday, September 19, 2006
remove oracle from OS
ORACLE 完全删除
Windows: 参考：Note:190096.1 及124353.1
1、控制面板，管理工具，服务：停止oracle相关服务。
2、使用oracle universal installer卸载oracle软件。
3、进入注册表regedit 删除：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle相关
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\Oracle相关
4、检查控制面板，系统，环境变量是否有oracle相关，删除。系统重新启动
5、删除oracle安装目录及其所有文件（如果有不让删除的，可以先改名，重启系统再删除），删除开始菜单中oracle相关及桌面的oracle快捷方式。
6、删除c:\program files\oracle及其所有文件。
7、系统重启，确认3，5，6步操作完成。
LINUX: Note:275493.1
1、停止oracle相关服务。
2、删除$ORACLE_HOME。
3、删除$ORACLE_BASE/oraInventory目录。
4、修改/etc/oratab文件，删除数据库记录信息。
5、删除/tmp下oracle相关文件
6、删除/opt/orcl开头的文件
7、/usr/local/bin目录下的文件不必删除，但在下次安装oracle软件后运行root.sh时会提示你已经有文件了，到时选择覆盖即可。
Windows: 参考：Note:190096.1 及124353.1
1、控制面板，管理工具，服务：停止oracle相关服务。
2、使用oracle universal installer卸载oracle软件。
3、进入注册表regedit 删除：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle相关
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\Oracle相关
4、检查控制面板，系统，环境变量是否有oracle相关，删除。系统重新启动
5、删除oracle安装目录及其所有文件（如果有不让删除的，可以先改名，重启系统再删除），删除开始菜单中oracle相关及桌面的oracle快捷方式。
6、删除c:\program files\oracle及其所有文件。
7、系统重启，确认3，5，6步操作完成。
LINUX: Note:275493.1
1、停止oracle相关服务。
2、删除$ORACLE_HOME。
3、删除$ORACLE_BASE/oraInventory目录。
4、修改/etc/oratab文件，删除数据库记录信息。
5、删除/tmp下oracle相关文件
6、删除/opt/orcl开头的文件
7、/usr/local/bin目录下的文件不必删除，但在下次安装oracle软件后运行root.sh时会提示你已经有文件了，到时选择覆盖即可。
Subscribe to:
Posts (Atom)
