RAC 通过rman、duplicate迁移至单实例
2018-07-25 11:21:32Oracle
RAC 通过rman、duplicate迁移至单实例
rac 11g/10g 通过rman 迁移至另一独立的单实例，除了通过rman与duplicate 外，还可使用expdp,以下主要讨论 rman+duplicate。
1，环境
RAC环境： db_name= dominic sid=dominic1/dominic2
单实例： db_name=dominic sid=dominic
# that require network functionality will fail.
127.0.0.1localhost.localdomain localhost
::1localhost6.localdomain6 localhost6
#*******************public_ip
192.168.103.189lmocm189
192.168.103.190lmocm190
#**********clustestorage_disk
192.168.103.86gtadb_86
#*********************priv-ip
192.168.56.189priv189
192.168.56.190priv190
#**********************vip-ip
192.168.103.200 vip189
192.168.103.201 vip190
#**********************scan-ip
192.168.103.203 scan.com
192.168.103.204 scan.com
192.168.103.205 scan.com
#**********************other-ip
192.168.103.192lmocm192
—–红色为rac 相关IP,蓝色为 单实例IP
2，创建监听
通过netmgr 或者在 $ORALCE_HOME/NETWORK/ADMIN/下配置 listener.ora和tnsname.ora文件：
同时rac 和 单实例下都需要配置，tnsping ok 就可。
rac ： 配置单实例的， node 下，配置rac的，10g,可以单独的,11g 使用scan,或者vip 也可以(建议使用scan，或者vip )
[oracle@lmocm189 admin]$ cat tnsnames.ora
DOMINIC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominic)
)
)
DOMINIC_SING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lmocm192)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominic)
)
)
[oracle@lmocm192 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dominic)
(ORACLE_HOME = /u01/app/oracle/product/11.2/dbhome_1)
(SID_NAME = dominic)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lmocm192)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
3,修改参数文件
通过RAC 的spfile 修改后 scp 至 node 下。
RAC 原spfile 文件：
[oracle@lmocm189 ~]$ cat /u01/app/oracle/dd.pfile
dominic1.__db_cache_size=234881024
dominic2.__db_cache_size=268435456
dominic1.__java_pool_size=16777216
dominic2.__java_pool_size=16777216
dominic1.__large_pool_size=16777216
dominic2.__large_pool_size=16777216
dominic1.__pga_aggregate_target=402653184
dominic2.__pga_aggregate_target=402653184
dominic1.__sga_target=754974720
dominic2.__sga_target=754974720
dominic1.__shared_io_pool_size=0
dominic2.__shared_io_pool_size=0
dominic1.__shared_pool_size=436207616
dominic2.__shared_pool_size=436207616
dominic1.__streams_pool_size=33554432
dominic2.__streams_pool_size=0 --这要删除
*.audit_file_dest='/u01/app/oracle/admin/dominic/adump'
*.audit_trail='db'
*.cluster_database=true --这要删除
*.compatible='11.2.0.0.0'
*.control_files='+DATAFILE/dominic/controlfile/current.260.825093345','+ARCHIVELOG/dominic/controlfile/current.256.825093345' --修改
*.db_block_size=8192
*.db_create_file_dest='+DATAFILE' --修改
*.db_domain=''
*.db_name='dominic'
*.db_recovery_file_dest='+ARCHIVELOG' --修改
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
dominic2.instance_number=2
dominic1.instance_number=1 --这要删除
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1153433600
*.open_cursors=300
*.processes=150
*.remote_listener='scan.com:1521' --这要删除
*.remote_login_passwordfile='exclusive'
*.sessions=170
dominic2.thread=2
dominic1.thread=1
dominic2.undo_tablespace='UNDOTBS2' --这要删除，保留一个。
dominic1.undo_tablespace='UNDOTBS1'
NODE PFILE 文件来源于修改后的RAC 文件:
[oracle@lmocm192 ~]$ cat /u01/app/ppfile
dominic.__db_cache_size=452984832
dominic.__java_pool_size=16777216
dominic.__large_pool_size=16777216
dominic.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dominic.__pga_aggregate_target=469762048
dominic.__sga_target=687865856
dominic.__shared_io_pool_size=0
dominic.__shared_pool_size=184549376
dominic.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dominic/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/dominic/controlfile/current.260.825093345','/u01/app/oracle/oradata/controlfile/current.256.825093345'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/dominic'
*.db_domain=''
*.db_file_name_convert=('+DATAFILE/dominic/datafile','/u01/app/oracle/oradata/dominic')
*.db_file_name_convert=('+DATAFILE/dominic/tempfile','/u01/app/oracle/oradata/dominic/tempfile')
*.db_name='DOMINIC'#Reset to original value by RMAN
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/oracle/archive_log'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert=('+DATAFILE/dominic/onlinelog','/u01/app/oracle/oradata/logfile')
*.log_file_name_convert=('+ARCHIVELOG/dominic/onlinelog','/u01/app/oracle/onlinelog/logfile')
*.memory_target=1153433600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
– 注意红色两行，因为RAC的目录结构和我们的单实例不一样，所以我们这里进行转换一下。 这里面要根据自己的情况来决定。 比如我将redo 放到了FRA了。所以在这里也需要进行转换。有几个路基，就需要转几次。
4,创建相关目录
3步之前，最好通过 vlogfile,vdatafile,v$tempfile 查看一下数据文件路径。 通过mkdir -pv 创建 NODE 下的路径。同时创建orapwdSID 密码文件。
a,通过rac 的密码文件ORACLEHOME/DBS下，scp至node下。b,[oracle@lmocm192] ORAPWDFILE=＜ ORAPW_SID＞PASSWORD
=＜ PASSWORD ＞ ENTRIES=< MAX_USERS >
各命令参数的含义为：
FILENAME：密码文件名；
PASSWORD：设置INTERNAL／SYS帐号的口令；
MAX_USERS：密码文件中可以存放的最大用户数，对应于允许以SYSDBA／SYSOPER权限登录数据库的最大用户数。由于在以后的维护中，若用户数超出了此限制，则需要重建密码文件，所以此参数可以根据需要设置得大一些。
有了密码文件之后，需要设置初始化参数REMOTE_LOGIN_PASSWORDFILE来控制密码文件的使用状态。
5,备份数据
备份rac 所有数据，一个节点上备份，另一个节点的archivelog 日志备份后，也得拷贝过去.
rac 1:
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/u01/app/oracle/db_back/%U_%T.DB';
sql 'alter system archive log current';
BACKUP FORMAT '/u01/app/oracle/db_back/%U_%T.ARCHIVE' ARCHIVELOG ALL DELETE INPUT;
backup current controlfile FORMAT '/u01/app/oracle/db_back/%T.CTL';
backup spfile tag='spfile' format='/u01/app/oracle/db_back/%T.PFILE';
RELEASE CHANNEL C2;
RELEASE CHANNEL C1;
}
rac 2:
backup archivelog all format '/u01/app/oracle/db_back/%U_%T.archivelog'
6,创建系统参数文件
把node 单实例通过pfile 加载到 nomount 状态后，通过pfile 创建spfile 文件。
7，通过 rman duplicate拷贝数据库
通过 rman target sys/oracle@DOMINIC auxiliary / 在NODE 登陆。
通过 duplicate target database to dominic 恢复数据。 —-这两部，有可能会报错，根据报错，修改信息。
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 687866376 bytes
Database Buffers 452984832 bytes
Redo Buffers 9371648 bytes
SQL> host
[oracle@lmocm192 db_back]$ rman target sys/oracle@DOMINIC auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Sep 9 23:17:12 2013
Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved.
connected to target database: DOMINIC (DBID=1954767264)
connected to auxiliary database: DOMINIC (not mounted)
RMAN> duplicate target database to dominic ；
Starting Duplicate Db at 09-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''DOMINIC'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DOMINIC'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DOMINIC'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DOMINIC'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 687866376 bytes
Database Buffers 452984832 bytes
Redo Buffers 9371648 bytes
Starting restore at 09-SEP-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/db_back/20130909.CTL
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/db_back/20130909.CTL tag=TAG20130909T152516
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:03
output file name=/u01/app/oracle/dominic/controlfile/current.260.825093345
output file name=/u01/app/oracle/oradata/controlfile/current.256.825093345
Finished restore at 09-SEP-13
database mounted
Using previous duplicated file /u01/app/oracle/oradata/dominic/system.256.825091903 for datafile 1 with checkpoint SCN of 2318677
Using previous duplicated file /u01/app/oracle/oradata/dominic/sysaux.257.825091907 for datafile 2 with checkpoint SCN of 2318677
Using previous duplicated file /u01/app/oracle/oradata/dominic/undotbs1.258.825091909 for datafile 3 with checkpoint SCN of 2318657
Using previous duplicated file /u01/app/oracle/oradata/dominic/users.259.825091909 for datafile 4 with checkpoint SCN of 2318657
Using previous duplicated file /u01/app/oracle/oradata/dominic/example.264.825093429 for datafile 5 with checkpoint SCN of 2318677
Using previous duplicated file /u01/app/oracle/oradata/dominic/undotbs2.265.825093865 for datafile 6 with checkpoint SCN of 2318657
Using previous duplicated file /u01/app/oracle/oradata/dominic/gta_dcsys_data.269.825163105 for datafile 7 with checkpoint SCN of 2318657
contents of Memory Script:
{
set until scn 2996999;
set newname for datafile 1 to
"/u01/app/oracle/oradata/dominic/system.256.825091903";
set newname for datafile 2 to
"/u01/app/oracle/oradata/dominic/sysaux.257.825091907";
set newname for datafile 3 to
"/u01/app/oracle/oradata/dominic/undotbs1.258.825091909";
set newname for datafile 4 to
"/u01/app/oracle/oradata/dominic/users.259.825091909";
set newname for datafile 5 to
"/u01/app/oracle/oradata/dominic/example.264.825093429";
set newname for datafile 6 to
"/u01/app/oracle/oradata/dominic/undotbs2.265.825093865";
set newname for datafile 7 to
"/u01/app/oracle/oradata/dominic/gta_dcsys_data.269.825163105";
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
contents of Memory Script:
{
catalog clone datafilecopy "/u01/app/oracle/oradata/dominic/system.256.825091903",
"/u01/app/oracle/oradata/dominic/sysaux.257.825091907",
"/u01/app/oracle/oradata/dominic/undotbs1.258.825091909",
"/u01/app/oracle/oradata/dominic/users.259.825091909",
"/u01/app/oracle/oradata/dominic/example.264.825093429",
"/u01/app/oracle/oradata/dominic/undotbs2.265.825093865",
"/u01/app/oracle/oradata/dominic/gta_dcsys_data.269.825163105";
switch clone datafile 1 to datafilecopy
"/u01/app/oracle/oradata/dominic/system.256.825091903";
switch clone datafile 2 to datafilecopy
"/u01/app/oracle/oradata/dominic/sysaux.257.825091907";
switch clone datafile 3 to datafilecopy
"/u01/app/oracle/oradata/dominic/undotbs1.258.825091909";
switch clone datafile 4 to datafilecopy
"/u01/app/oracle/oradata/dominic/users.259.825091909";
switch clone datafile 5 to datafilecopy
"/u01/app/oracle/oradata/dominic/example.264.825093429";
switch clone datafile 6 to datafilecopy
"/u01/app/oracle/oradata/dominic/undotbs2.265.825093865";
switch clone datafile 7 to datafilecopy
"/u01/app/oracle/oradata/dominic/gta_dcsys_data.269.825163105";
}
executing Memory Script
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/system.256.825091903 RECID=10 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/sysaux.257.825091907 RECID=11 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/undotbs1.258.825091909 RECID=12 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/users.259.825091909 RECID=13 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/example.264.825093429 RECID=14 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/undotbs2.265.825093865 RECID=15 STAMP=825722444
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dominic/gta_dcsys_data.269.825163105 RECID=16 STAMP=825722444
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/system.256.825091903
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/sysaux.257.825091907
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/undotbs1.258.825091909
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/users.259.825091909
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/example.264.825093429
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/undotbs2.265.825093865
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=825722444 file name=/u01/app/oracle/oradata/dominic/gta_dcsys_data.269.825163105
contents of Memory Script:
{
set until scn 2996999;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 09-SEP-13
using channel ORA_AUX_DISK_1
starting media recovery
Oracle instance started
Total System Global Area 1152450560 bytes
Fixed Size 2227704 bytes
Variable Size 687866376 bytes
Database Buffers 452984832 bytes
Redo Buffers 9371648 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DOMINIC'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
}
executing Memory Script
sql statement: alter system set db_name = ''DOMINIC'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/09/2013 23:20:57
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 46 and starting SCN of 2990968 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 45 and starting SCN of 2990878 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 44 and starting SCN of 2904738 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 43 and starting SCN of 2811911 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 42 and starting SCN of 2768543 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 41 and starting SCN of 2698110 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 40 and starting SCN of 2665715 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 39 and starting SCN of 2561227 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 38 and starting SCN of 2468986 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 37 and starting SCN of 2380959 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 36 and starting SCN of 2318917 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 35 and starting SCN of 2318877 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 34 and starting SCN of 2318176 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 72 and starting SCN of 2975716 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 71 and starting SCN of 2897150 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 70 and starting SCN of 2831168 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 69 and starting SCN of 2723427 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 68 and starting SCN of 2612996 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 67 and starting SCN of 2525003 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 66 and starting SCN of 2418165 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 65 and starting SCN of 2318921 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 64 and starting SCN of 2318880 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 63 and starting SCN of 2318172 found to restore
---这个报错，是应为在dominic2 目录下，而dominic1 不能访问dominic2 目录下的文件(不是aSM)。 这是说这几个归档数据文件丢失，如果这样的话，可以这样备(冷备)执行一样。就不会报这错了。
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orcl/temp.263.736599505";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/undotbs1.258.736598599";
catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/sysaux.257.736598563";
catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/users.259.736598641";
catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/undotbs2.264.736599805";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/orcl/temp.263.736599505 in control file
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs1.258.736598599 recid=1 stamp=737150639
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/sysaux.257.736598563 recid=2 stamp=737150640
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/users.259.736598641 recid=3 stamp=737150641
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs2.264.736599805 recid=4 stamp=737150642
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=737150639 filename=/u01/app/oracle/oradata/orcl/undotbs1.258.736598599
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=737150640 filename=/u01/app/oracle/oradata/orcl/sysaux.257.736598563
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=737150641 filename=/u01/app/oracle/oradata/orcl/users.259.736598641
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=737150642 filename=/u01/app/oracle/oradata/orcl/undotbs2.264.736599805
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 07-DEC-10
RMAN>
至此，RMAN的duplicate 已经完成。 迁移基本完成。
8，单实例后续扫尾工作
8.1 ：清除多余的undo文件
查看UNDO 信息：
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
因为我们使用的是UNDOTBS1，在pfile里设置的，所以把UNDOTBS2删除掉。
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
8.2 ：清除未使用线程的redo日志组
默认情况下，RAC 环境下，每个实例都有2个redo。 在单实例下，就没有必要了。 我们删除点线程2的redo 信息。
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE
SQL> select group#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 YES UNUSED
2 1 NO CURRENT
3 2 NO CURRENT
4 2 YES UNUSED
SQL> alter database disable thread 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 1 NO CURRENT
现在就剩2个了。 一般的单实例是3个online redo。 我们在添加一组。
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03.log') size 10m;
Database altered.
SQL> select group#,status from v$log;
GROUP# THREAD# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 1 NO CURRENT
3 1 YES UNUSED
8.3： 重建临时表空间，并删除原来的数据文件
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ---------------------------------------------------------------------
1 /u01/app/oracle/oradata/orcl/temp.263.736599505