恢复undo log file-归档模式
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
baf6024
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /bafrman/archive
Oldest online log sequence 132
Next log sequence to archive 133
Current log sequence 133
RMAN ???Ý?º
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/bafrman/rman/baf_ctrlfile_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/bafrman/rman/snapcf_baf6024.f';
RMAN> backup database format '/bafrman/rman/baf_%t_%s_%u.bak'; Starting backup at 15-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00002 name=/bafdata/baf6024/undotbs01.dbf input datafile fno=00005 name=/bafdata/baf6024/baf_data01.dbf input datafile fno=00006 name=/bafdata/baf6024/baf_ind01.dbf input datafile fno=00008 name=/bafdata/baf6024/baf_data02.dbf input datafile fno=00009 name=/bafdata/baf6024/baf_data03.dbf input datafile fno=00001 name=/bafdata/baf6024/system01.dbf
input datafile fno=00003 name=/bafdata/baf6024/sysaux01.dbf input datafile fno=00004 name=/bafdata/baf6024/users01.dbf input datafile fno=00007 name=/bafdata/baf6024/perfstat.dbf input datafile fno=00015 name=/bafdata/baf6024/BAF_TST.dbf input datafile fno=00013 name=/bafdata/baf6024/TBS_TEMP03_1.dbf input datafile fno=00010 name=/bafdata/baf6024/TBS_TEMP01_1.dbf input datafile fno=00011 name=/bafdata/baf6024/TBS_TEMP01_2.dbf input datafile fno=00012 name=/bafdata/baf6024/TBS_TEMP02_1.dbf input datafile fno=00014 name=/bafdata/baf6024/TBS_TEMP03_2.dbf channel ORA_DISK_1: starting piece 1 at 15-FEB-12
channel ORA_DISK_1: finished piece 1 at 15-FEB-12
piece handle=/bafrman/rman/baf6024_20120215.bak
tag=TAG20120215T145800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 Finished backup at 15-FEB-12
Starting Control File and SPFILE Autobackup at 15-FEB-12 piece handle=/bafrman/rman/baf_ctrlfile_c-1464331247-20120215-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15-FEB-12
?íÎó?º
SQL> startup;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 2099712Variable Size 637535744 bytes
Database Buffers 2566914048 bytes
Redo Buffers 14675968 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/bafdata/baf6024/undotbs01.dbf'
?â?ö????Ò??º
1.ÒÔresetlogs Æô???º
SQL> show parameter spfile
NAME TYPE VALUE ------------------------------------ -----------
------------------------------
spfile string
/opt/oracle/products/10.2.0/dbs/initbaf6024.ora
SQL> create pfile='/opt/oracle/products/10.2.0/dbs/initbaf6024.ora' from spfile; --????init ?ÎÊý
SQL> shutdown immediate;
SQL> startup pfile='/opt/oracle/products/10.2.0/dbs/init.ora' mount; SQL> alter database datafile '/bafdata/baf6024/undotbs01.dbf' offline drop;
SQL> shutdown immediate;
SQL> startup;
SQL> create undo tablespace UNDOTBS02 datafile
'/bafdata/baf6024/undotbs02.dbf' size 100M reuse autoextend on; SQL> alter system set undo_tablespace=UNDOTBS02 scope=spfile; SQL> drop tablespace UNDOTBS01 including contents and datafiles; --ÔÙÉ??ýundotbs1
?â?ö?????þ?º
ÀûÓÃÖØ???ØÖÆÎÄ?þ???Ö??Êý?Ý?â??
1.???Ý?ØÖÆÎÄ?þ?º
SQL> alter database backup controlfile to '/tmp/controlfile.ora'; ???Ý?ØÖÆÎÄ?þ
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BAF6024" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 7303
LOGFILE
GROUP 1 (
'/bafdata/baf6024/log01a.dbf',
'/bafdata/baf6024/log01b.dbf'
) SIZE 100M,
GROUP 2 (
'/bafdata/baf6024/log02a.dbf',
'/bafdata/baf6024/log02b.dbf'
) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/bafdata/baf6024/system01.dbf',
'/bafdata/baf6024/sysaux01.dbf',
'/bafdata/baf6024/users01.dbf',
'/bafdata/baf6024/baf_data01.dbf',
'/bafdata/baf6024/baf_ind01.dbf',
'/bafdata/baf6024/perfstat.dbf',
'/bafdata/baf6024/baf_data02.dbf',
'/bafdata/baf6024/baf_data03.dbf',
'/bafdata/baf6024/TBS_TEMP01_1.dbf',
'/bafdata/baf6024/TBS_TEMP01_2.dbf',
'/bafdata/baf6024/TBS_TEMP02_1.dbf',
'/bafdata/baf6024/TBS_TEMP03_1.dbf',
'/bafdata/baf6024/TBS_TEMP03_2.dbf',
'/bafdata/baf6024/BAF_TST.dbf',
'/bafdata/baf6024/undotbs02.dbf' CHARACTER SET AL32UTF8
;
2.ÐÞ?Ä?ÎÊýÎÄ?þ?º
$vi initbaf6024.ora
#*.undo_management='AUTO'
#*.undo_retention=10800
#*.undo_tablespace='UNDOTBS1'
3.Æô??µ?nomount:
SQL> startup nomount
pfile='/opt/oracle/products/10.2.0/dbs/initbaf6024.ora';
4.ÖØ???ØÖÆÎÄ?þ?º
CREATE CONTROLFILE REUSE DATABASE "BAF6024" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 7303
LOGFILE
GROUP 1
('/bafdata/baf6024/log01a.dbf','/bafdata/baf6024/log01b.dbf') SIZE
100M,
GROUP 2 ('/bafdata/baf6024/logDATAFILE
'/bafdata/baf6024/system01.dbf',
'/bafdata/baf6024/sysaux01.dbf',
'/bafdata/baf6024/users01.dbf',
'/bafdata/baf6024/baf_data01.dbf',
'/bafdata/baf6024/baf_ind01.dbf',
'/bafdata/baf6024/perfstat.dbf',
'/bafdata/baf6024/baf_data02.dbf',
'/bafdata/baf6024/baf_data03.dbf',
'/bafdata/baf6024/TBS_TEMP01_1.dbf',
'/bafdata/baf6024/TBS_TEMP01_2.dbf',
'/bafdata/baf6024/TBS_TEMP02_1.dbf',
'/bafdata/baf6024/TBS_TEMP03_1.dbf',
'/bafdata/baf6024/TBS_TEMP03_2.dbf',
'/bafdata/baf6024/BAF_TST.dbf'
;
×??ºÉ??ýundo tablespaceÊý?ÝÎÄ?þ.
SQL> alter database open resetlogs;
SQL> alter tablespace TEMP add tempfile '/bafdata/baf6024/temp01.dbf'
size 512M REUSE;
SQL> alter tablespace TEMP add tempfile '/bafdata/baf6024/temp02.dbf'
size 512M REUSE;
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO "baf6024.alibabacorp.com";
SQL> shutdown immediate;
SQL> startup;
SQL> select * from v$instance;
4.ÖØ??undo :
SQL> create undo tablespace UNDOTBS01 datafile '/bafdata/baf6024/undotbs01.dbf' size 100M reuse autoextend on; SQL> shutdown immediate;
SQL> startup
pfile='/opt/oracle/products/10.2.0/dbs/initbaf6024.ora'; SQL> drop tablespace UNDOTBS02 including contents and datafiles; --ÔÙÉ??ýundotbs1
5.ÖØ??SPFILE:
ÐÞ?Ä?ÎÊýÎÄ?þ,È?Ïû×?ÊÍ?º
$vi initbaf6024.ora
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
SQL> create spfile from
pfile='/opt/oracle/products/10.2.0/dbs/initbaf6024.ora'; SQL> shutdown immediate;
SQL> startup;
SQL> show parameter spfile;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/products/10.2.0/dbs/spfilebaf6024.ora SQL> show parameter undo;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900 undo_tablespace string UNDOTBS01
1).É??ý?Éundotbs:
SQL> alter tablespace undotbs01 offline normal; --ÏÈoffline undotbs1
SQL> drop tablespace UNDOTBS01 including contents and datafiles; --ÔÙÉ??ýundotbs1
2).ÐÂ??undo tablespace:
SQL> show parameter undo
NAME TYPE VALUE ------------------------------------ -----------
------------------------------
undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS02 --µ?Ç?undo tablespace
SQL> create undo tablespace UNDOTBS01 datafile
'/bafdata/baf6024/undotbs01.dbf' size 100M reuse autoextend on; SQL> alter system set undo_tablespace=UNDOTBS01 scope=spfile; SQL> alter tablespace undotbs02 offlSQL> drop tablespace UNDOTBS02 including contents and datafiles; --ÔÙÉ??ýundotbs1