从RAC恢复到单实例数据库操作步骤___

当前位置: 数据库>oracle

从RAC恢复到单实例数据库操作步骤

来源: 互联网发布时间：2017-05-07

本文导语: 自己做了一次RAC数据库恢复到单实例数据库的测试，特此记录。从RAC恢复到单实例数据库操作步骤 1、检查目标端环境变量[Oracle@RHEL5 ~]$ env | grep ORA PATH=$PATH:$HOME/binexport TMP=/tmpexport TMPDIR=$TMPexport ORACLE_BASE=/u01/appexport ORACLE_HOME=/u01/a...

自己做了一次RAC数据库恢复到单实例数据库的测试，特此记录。

从RAC恢复到单实例数据库操作步骤

1、检查目标端环境变量
[Oracle@RHEL5 ~]$ env | grep ORA

PATH=$PATH:$HOME/bin
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/product/11.1.0/db_1
export ORACLE_SID=csora
export PATH=$PATH:$ORACLE_HOME/bin:/usr/bin
2、进入RMAN命令行模式，并启动到NOMOUNT状态:
[oracle@RHEL5 ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Sep 6 14:30:14 2012

connected to target database (not started)

RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ¨/data/ora10g/product/10.2.0/db_1/dbs/initjssdb.ora¨

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 2082400 bytes

Variable Size 67111328 bytes

Database Buffers 83886080 bytes

Redo Buffers 6303744 bytes

脑袋里蹦出了个大问号：怎么没见创建初始化参数文件，也能启动到NOMOUNT呢？当然可以啦，具体参考"涂抹ORACLE--三思笔记"中9.2.4.2小节中的相关内容。

启动过程中报错了，不管它，这里startup的目的只是为了给ORACLE分配相应的内存区，以便让他能够执行下面的restore操作。

3、从备份集中恢复spfile并保存成pfile

RMAN> restore spfile to pfile "/u01/app/product/11.1.0/db_1/dbs/pfilecsora.ora" from "/mnt/rmanbak/csora_rmanbackup/ctl_c-3232208281-20120825-09";

Starting restore at 04-SEP-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /mnt/rmanbak/csora_rmanbackup/ctl_c-3232208281-20120825-09
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 04-SEP-12

4、修改恢复的pfile文件
主要修改两个方面：
1、修改含文件路径的参数，达到符合当前服务器环境的实际情况。
2、修改多实例相关的参数

恢复后的原pfile内容如下：
[oracle@RHEL5 dbs]$ more pfilecsora.ora.bak20120904
csora1.__db_cache_size=3019898880
csora2.__db_cache_size=3154116608
csora2.__java_pool_size=67108864
csora1.__java_pool_size=134217728
csora2.__large_pool_size=67108864
csora1.__large_pool_size=67108864
csora1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
csora2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
csora2.__pga_aggregate_target=4496293888
csora1.__pga_aggregate_target=4496293888
csora2.__sga_target=8388608000
csora1.__sga_target=8388608000
csora2.__shared_io_pool_size=0
csora1.__shared_io_pool_size=0
csora1.__shared_pool_size=4966055936
csora2.__shared_pool_size=4966055936
csora2.__streams_pool_size=67108864
csora1.__streams_pool_size=134217728
*.audit_file_dest='/u01/app/oracle/admin/csora/adump'
*.audit_trail='db'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='11.1.0.0.0'
*.control_files='+DG1/csora/controlfile/current.256.702839005','+DG1/csora/controlfile/current.257.702839007'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DG1'
*.db_domain=''
*.db_name='csora'
*.db_recovery_file_dest='+DG1'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=csoraXDB)'
csora2.instance_number=2
csora1.instance_number=1
csora2.local_listener='LISTENER_CSORA2'
csora1.local_listener='LISTENER_CSORA1'
*.log_archive_dest_1='location=+DG1/'
*.log_archive_format='%t_%s_%r.arch'
*.memory_max_target=12884901888
*.memory_target=12884901888
*.open_cursors=300
*.optimizer_features_enable='9.2.0'
*.processes=300
*.remote_listener='LISTENERS_CSORA'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_max_size=10737418240
csora2.thread=2
csora1.thread=1
csora1.undo_tablespace='UNDOTBS1'
csora2.undo_tablespace='UNDOTBS2'

修改后的 pfile 文件内容
[oracle@RHEL5 dbs]$ more pfilecsora.ora
*.audit_file_dest='/u01/app/admin/csora/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oradata/csora/control01.ctl','/u01/app/oradata/csora/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='csora'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=csoraXDB)'
*.instance_number=1
*.log_archive_dest_1='location=db_recovery_file_dest'
*.log_archive_format='%t_%s_%r.arch'
*.memory_max_target=8884901888
*.memory_target=8884901888
*.open_cursors=300
*.optimizer_features_enable='9.2.0'
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_max_size=6737418240
*.undo_tablespace='UNDOTBS1'

5、恢复控制文件，切记设置DBID
[oracle@RHEL5 ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Sep 4 17:58:42 2012

connected to target database: CSORA (not mounted)

RMAN> set DBID=3232208281

executing command: SET DBID

RMAN> restore controlfile from "/mnt/rmanbak/csora_rmanbackup/ctl_c-3232208281-20120825-0e";

Starting restore at 04-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=317 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oradata/csora/control01.ctl
output file name=/u01/app/oradata/csora/control02.ctl
Finished restore at 04-SEP-12

6、启动数据库到mount状态

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

7、查看控制文件中rman备份信息是否为最新备份

A、查看全库备份集
RMAN> list backup of database;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31710 Full 15.21G DISK 00:45:25 05-AUG-12
BP Key: 31779 Status: AVAILABLE Compressed: YES Tag: TAG20120805T040034
Piece Name: /mnt/rmanbak/csora_rmanbackup/dbfull_31103_1_790488035
List of Datafiles in backup set 31710
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1292469329 05-AUG-12 +DG1/csora/datafile/system.262.702839009
2 Full 1292469329 05-AUG-12 +DG1/csora/datafile/sysaux.263.702839013
3 Full 1292469329 05-AUG-12 +DG1/csora/datafile/undotbs1.264.702839015
4 Full 1292469329 05-AUG-12 +DG1/csora/datafile/undotbs2.266.702839025
5 Full 1292469329 05-AUG-12 +DG1/csora/datafile/users.267.702839029
6 Full 1292469329 05-AUG-12 +DG1/csora/datafile/wcm.dbf
9 Full 1292469329 05-AUG-12 +DG1/csora/datafile/wcm.295.778461773
10 Full 1292469329 05-AUG-12 +DG1/csora/datafile/wcm_log.1312.778462571
11 Full 1292469329 05-AUG-12 +DG1/csora/datafile/wcm_document.488.778462999
12 Full 1292469329 05-AUG-12 +DG1/csora/datafile/wcm_plugin.486.778465097
14 Full 1292469329 05-AUG-12 +DG1/csora/datafile/wcmvideo.1197.778465867
15 Full 1292469329 05-AUG-12 +DG1/csora/datafile/trswcmv6_adintrs.294.778466237

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32050 Full 15.21G DISK 00:43:20 12-AUG-12
BP Key: 32119 Status: AVAILABLE Compressed: YES Tag: TAG20120812T040033
Piece Name: /mnt/rmanbak/csora_rmanbackup/dbfull_31444_1_791092833
List of Datafiles in backup set 32050
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1296113309 12-AUG-12 +DG1/csora/datafile/system.262.702839009
2 Full 1296113309 12-AUG-12 +DG1/csora/datafile/sysaux.263.702839013
3 Full 1296113309 12-AUG-12 +DG1/csora/datafile/undotbs1.264.702839015
4 Full 1296113309 12-AUG-12 +DG1/csora/datafile/undotbs2.266.702839025
5 Full 1296113309 12-AUG-12 +DG1/csora/datafile/users.267.702839029
6 Full 1296113309 12-AUG-12 +DG1/csora/datafile/wcm.dbf
9 Full 1296113309 12-AUG-12 +DG1/csora/datafile/wcm.295.778461773
10 Full 1296113309 12-AUG-12 +DG1/csora/datafile/wcm_log.1312.778462571
11 Full 1296113309 12-AUG-12 +DG1/csora/datafile/wcm_document.488.778462999
12 Full 1296113309 12-AUG-12 +DG1/csora/datafile/wcm_plugin.486.778465097
14 Full 1296113309 12-AUG-12 +DG1/csora/datafile/wcmvideo.1197.778465867
15 Full 1296113309 12-AUG-12 +DG1/csora/datafile/trswcmv6_adintrs.294.778466237

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32390 Full 15.21G DISK 00:42:40 19-AUG-12
BP Key: 32459 Status: AVAILABLE Compressed: YES Tag: TAG20120819T040023
Piece Name: /mnt/rmanbak/csora_rmanbackup/dbfull_31784_1_791697623
List of Datafiles in backup set 32390
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1299362165 19-AUG-12 +DG1/csora/datafile/system.262.702839009
2 Full 1299362165 19-AUG-12 +DG1/csora/datafile/sysaux.263.702839013
3 Full 1299362165 19-AUG-12 +DG1/csora/datafile/undotbs1.264.702839015
4 Full 1299362165 19-AUG-12 +DG1/csora/datafile/undotbs2.266.702839025
5 Full 1299362165 19-AUG-12 +DG1/csora/datafile/users.267.702839029
6 Full 1299362165 19-AUG-12 +DG1/csora/datafile/wcm.dbf
9 Full 1299362165 19-AUG-12 +DG1/csora/datafile/wcm.295.778461773
10 Full 1299362165 19-AUG-12 +DG1/csora/datafile/wcm_log.1312.778462571
11 Full 1299362165 19-AUG-12 +DG1/csora/datafile/wcm_document.488.778462999
12 Full 1299362165 19-AUG-12 +DG1/csora/datafile/wcm_plugin.486.778465097
14 Full 1299362165 19-AUG-12 +DG1/csora/datafile/wcmvideo.1197.778465867
15 Full 1299362165 19-AUG-12 +DG1/csora/datafile/trswcmv6_adintrs.294.778466237

dbfull_31784_1_791697623是我们最新的全库备份，所以这里不需要注册全库备份集

B、查看归档日志备份集的备份信息是否完整

RMAN> list backup of archivelog all;

C、查看归档日志是否完整

D、如果恢复的控制文件中的备份信息不完整，则需要我们手工拷贝并注册到控制文件中，如下：
注册备份集：
catalog backuppiece "/mnt/rmanbak/csora_rmanbackup/dbfull_31784_1_791697623";
注册归档日志：
单个归档文件注册：SQL > alter database register logfile '/var/arch/arch_1_101.arc';
注册整个归档目录：RMAN > catalog start with '/var/arch';

8、如果之前在RMAN中配置了默认通道，这里也要将这些配置清除，操作如下：

RMAN> configure channel 1 device type disk clear;

old RMAN configuration parameters:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT ¨*¨;

old RMAN configuration parameters are successfully deleted

9、查看原来RAC库数据文件的位置，编辑脚本rename到新的位置
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CSORA

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DG1/csora/datafile/system.262.702839009
2 0 SYSAUX *** +DG1/csora/datafile/sysaux.263.702839013
3 0 UNDOTBS1 *** +DG1/csora/datafile/undotbs1.264.702839015
4 0 UNDOTBS2 *** +DG1/csora/datafile/undotbs2.266.702839025
5 0 USERS *** +DG1/csora/datafile/users.267.702839029
6 0 WCMTS *** +DG1/csora/datafile/wcm.dbf
9 0 WCM *** +DG1/csora/datafile/wcm.295.778461773
10 0 WCM_LOG *** +DG1/csora/datafile/wcm_log.1312.778462571
11 0 WCM_DOCUMENT *** +DG1/csora/datafile/wcm_document.488.778462999
12 0 WCM_PLUGIN *** +DG1/csora/datafile/wcm_plugin.486.778465097
14 0 WCMVIDEO *** +DG1/csora/datafile/wcmvideo.1197.778465867
15 0 TRSWCMV6_ADINTRS *** +DG1/csora/datafile/trswcmv6_adintrs.294.778466237

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 +DG1/csora/tempfile/temp.265.702839017
2 0 WCMTEMP 32767 +DG1/csora/datafile/wcmtemp.dbf
3 0 WCM_TMP 10240 +DG1/csora/tempfile/wcm_tmp.492.721280993
5 0 TRSWCMV6_ADINTRS_TEMP 0 +DG1/csora/tempfile/trswcmv6_adintrs_temp.1304.749667811

rman批处理脚本：
RUN {
SET NEWNAME FOR DATAFILE 1 to "/u01/app/oradata/csora/system01.dbf";
SET NEWNAME FOR DATAFILE 2 to "/u01/app/oradata/csora/sysaux01.dbf";
SET NEWNAME FOR DATAFILE 3 to "/u01/app/oradata/csora/undotbs1.dbf";
SET NEWNAME FOR DATAFILE 4 to "/u01/app/oradata/csora/undotbs2.dbf";
SET NEWNAME FOR DATAFILE 5 to "/u01/app/oradata/csora/users01.dbf";
SET NEWNAME FOR DATAFILE 6 to "/u01/app/oradata/csora/wcmts.dbf";
SET NEWNAME FOR DATAFILE 9 to "/u01/app/oradata/csora/wcm.dbf";
SET NEWNAME FOR DATAFILE 10 to "/u01/app/oradata/csora/wcm_log.dbf";
SET NEWNAME FOR DATAFILE 11 to "/u01/app/oradata/csora/wcm_document.dbf";
SET NEWNAME FOR DATAFILE 12 to "/u01/app/oradata/csora/wcm_plugin.dbf";
SET NEWNAME FOR DATAFILE 14 to "/u01/app/oradata/csora/wcmvideo.dbf";
SET NEWNAME FOR DATAFILE 15 to "/u01/app/oradata/csora/trswcmv6_adintrs.dbf";
SET NEWNAME FOR TEMPFILE 1 to "/u01/app/oradata/csora/temp01.dbf";
SET NEWNAME FOR TEMPFILE 2 to "/u01/app/oradata/csora/wcmtemp.dbf";
SET NEWNAME FOR TEMPFILE 3 to "/u01/app/oradata/csora/wcm_tmp.dbf";
SET NEWNAME FOR TEMPFILE 5 to "/u01/app/oradata/csora/trswcmv6_adintrs_temp.dbf";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}

10、执行以上批处理脚本，进行文件（数据文件和临时文件）重定义，并RESTORE数据库

11、查看日志SCN号，这里只用归档日志备份集恢复，如用到归档日志文件恢复，则查询V$ARCHIVED_LOG视图

RMAN> list backupset;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31710 Full 15.21G DISK 00:45:25 05-AUG-12
BP Key: 31779 Status: AVAILABLE Compressed: YES Tag: TAG20120805T040034
Piece Name: /mnt/rmanbak/csora_rmanbackup/dbfull_31103_1_790488035
List of Datafiles in backup set 31710
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/system01.dbf
2 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/sysaux01.dbf
3 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/undotbs1.dbf
4 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/undotbs2.dbf
5 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/users01.dbf
6 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/wcmts.dbf
9 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/wcm.dbf
10 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/wcm_log.dbf
11 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/wcm_document.dbf
12 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/wcm_plugin.dbf
14 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/wcmvideo.dbf
15 Full 1292469329 05-AUG-12 /u01/app/oradata/csora/trswcmv6_adintrs.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
31711 3.44M DISK 00:00:01 05-AUG-12
BP Key: 31780 Status: AVAILABLE Compressed: YES Tag: TAG20120805T044608
Piece Name: /mnt/rmanbak/csora_rmanbackup/arch_31105_1_790490768

List of Archived Logs in backup set 31711
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6911 1292469298 05-AUG-12 1292480692 05-AUG-12
2 6854 1292469302 05-AUG-12 1292480688 05-AUG-12

……………………省略中间部分…………………………

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
32699 3.44M DISK 00:00:01 25-AUG-12
BP Key: 32924 Status: AVAILABLE Compressed: YES Tag: TAG20120825T140029
Piece Name: /mnt/rmanbak/csora_rmanbackup/arch_32097_1_792252029

List of Archived Logs in backup set 32699
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7909 1302135697 25-AUG-12 1302146359 25-AUG-12
1 7910 1302146359 25-AUG-12 1302146578 25-AUG-12
2 7845 1302135702 25-AUG-12 1302146353 25-AUG-12
2 7846 1302146353 25-AUG-12 1302146582 25-AUG-12

12、根据SCN号RECOVER数据库
从11步的查询结果可以看到Next SCN 为 1302146582

RMAN> recover database until scn 1302146582;

最后报如下错误，归档日志找不到，可能是日志损坏或没有注册，这是不管它了，只要数据库SCN号一致就OK,继续。

unable to find archived log
archived log thread=1 sequence=7911
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/04/2012 19:10:38
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7911 and starting SCN of 1302146578

13、重建控制文件，以修复联机日志文件的路径：

这里做个说明：可以通过"alter database rename file"方式修改重做日志文件路径，事实上"alter database rename file"方式极有可能触发" ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [], [], [] " 错误，经查这是ORACLE的一个BUG，对应BUG为7207932，通常是RAC环境从asm向文件系统迁移时被触发，在10204版本中依然存在，Doc ID: 742289.1对此有详细说明，号称11g版本中对该问题进行了修复。

A、备份当前控制文件到trace
SQL> alter database backup controlfile to trace;

Database altered.
B、修改控制文件创建脚本，重启数据库到nomount状态重建控制文件

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size 2160312 bytes
Variable Size 2550139208 bytes
Database Buffers 4160749568 bytes
Redo Buffers 34676736 bytes

CREATE CONTROLFILE REUSE DATABASE "CSORA" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 (
'/u01/app/oradata/csora/redo1a',
'/u01/app/oradata/csora/redo1b'
) SIZE 50M,
GROUP 2 (
'/u01/app/oradata/csora/redo2a',
'/u01/app/oradata/csora/redo2b'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oradata/csora/system01.dbf',
'/u01/app/oradata/csora/sysaux01.dbf',
'/u01/app/oradata/csora/undotbs1.dbf',
'/u01/app/oradata/csora/undotbs2.dbf',
'/u01/app/oradata/csora/users01.dbf',
'/u01/app/oradata/csora/wcmts.dbf',
'/u01/app/oradata/csora/wcm.dbf',
'/u01/app/oradata/csora/wcm_log.dbf',
'/u01/app/oradata/csora/wcm_document.dbf',
'/u01/app/oradata/csora/wcm_plugin.dbf',
'/u01/app/oradata/csora/wcmvideo.dbf',
'/u01/app/oradata/csora/trswcmv6_adintrs.dbf'
CHARACTER SET AL32UTF8
;

14、打开数据库

SQL> alter database open resetlogs;

Database altered.

15、重建相应临时表空间数据文件
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oradata/csora/temp01.dbf' size 50m;

Tablespace altered.

16、清除未使用线程的redo日志组

A、查询联机日志信息
SQL> SELECT v$logfile.member, v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread#
FROM v$log, v$logfile
WHERE v$log.group# = v$logfile.group#
ORDER BY v$log.thread#,v$logfile.group#;

MEMBER GROUP# STATUS ARC V$LOG.BYTES/1024/1024 THREAD#
-------------------------------------------------- ---------- ---------------- --- --------------------- ----------
/u01/app/oradata/csora/redo1b 1 CURRENT NO 50 1
/u01/app/oradata/csora/redo1a 1 CURRENT NO 50 1
/u01/app/oradata/csora/redo2a 2 UNUSED YES 50 1
/u01/app/oradata/csora/redo2b 2 UNUSED YES 50 1
/u01/app/flash_recovery_area/CSORA/onlinelog/o1_mf 3 ACTIVE YES 100 2
_3_84cs4sdp_.log
/u01/app/flash_recovery_area/CSORA/onlinelog/o1_mf 4 UNUSED YES 100 2
_4_84cs4v38_.log

6 rows selected.

B、删除线程2日志组

SQL> alter database disable thread 2;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

C、查看删除后的日志信息

SQL> SELECT v$logfile.member, v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread#
2 FROM v$log, v$logfile
3 WHERE v$log.group# = v$logfile.group#
4 ORDER BY v$log.thread#,v$logfile.group#;

4 rows selected.

17、删除多余的UNDO表空间

SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.

您可能感兴趣的文章:

与Oracle RAC相关的连接配置写法实例

基于Linux平台的Oracle RAC 10g集群教程：删除节点所需要的步骤

基于Linux平台的Oracle RAC 10g集群教程：添加节点所需要的步骤

本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。