Rac reverts to a single instance
1. Transfer the backup files of rac backed up by rman to a single node in a remote location
[oracle@ora0 ~] $ls / data_back/racbk/
20120514_3snav1p2_1_1.bk 40nav1sh_1_1_20120514.arc 43nav1sh_1_1_20120514.arc 48nav1vu_1_1_20120514.ctl
20120514_3tnav1p2_1_1.bk 41nav1sh_1_1_20120514.arc 44nav1t7_1_1_20120514.arc
20120514_3unav1pa_1_1.bk 42nav1sh_1_1_20120514.arc 46nav1uq_1_1_20120514.spfil
2. Solve pfile
[oracle@ora0 ~] $rman target /
RMAN > startup
Startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file'/ u01qqoracle.productUniverse 10.2.0OnDbsUniplex initora10g.ora'
Starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
RMAN-00571: =
RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =
RMAN-00571: =
RMAN-03002: failure of startup command at 05/14/2012 13:17:11
ORA-00205: error in identifying control file, check alert log for more info
# report an error, ignore it # #
Restore spfile
RMAN > restore spfile from'/ data_back/racbk/46nav1uq_1_1_20120514.spfil'
Connect to the sql interface
SQL > conn / as sysdba
Connected.
SQL > select status from v$instance
STATUS
-
STARTED
SQL > create pfile from spfile
File created.
Delete spfile and modify pfile
Create a parameter file from the RAC database, and if RAC uses PFILE, you can modify it directly after COPY.
Modified parameters include: audit_file_dest, background_dump_dest, core_dump_dest,user_dump_dest, log_archive_dest_1, etc.
Change the parameters related to the path to the corresponding path of your single instance environment. Such as:
Background_dump_dest=/u01/oracle/ora/9.2.0/admin/DEV_dbtest/bdump
Important: remove the cluster_database_instances,cluster_database parameter because you are restoring to a single instance environment.
Remove a UNDO_TABLESPACE setting, assuming that APPS_UNDOTBS1 is retained
# #
* .audit_file_dest='/u01/oracle/admin/db/adump'
* .background_dump_dest='/u01/oracle/admin/db/bdump'
* .compatible='10.2.0.1.0'
* .control_files='/u01/oracle/product/10.2.0/dbs/control_01.ctl'
* .core_dump_dest='/u01/oracle/admin/db/cdump'
* .db_block_size=8192
* .db_domain=''
* .db_file_multiblock_read_count=16
* .db_name='db'
* .job_queue_processes=10
* .log_archive_dest_1='LOCATION=/home/oracle/arc'
* .open_cursors=300
* .pga_aggregate_target=94371840
* .processes=150
* .remote_login_passwordfile='exclusive'
* .sga_target=283115520
* .undo_management='AUTO'
Undo_tablespace='UNDOTBS1'
* .user_dump_dest='/u01/oracle/admin/db/udump'
# # #
SQL > startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2020224 bytes
Variable Size 92277888 bytes
Database Buffers 188743680 bytes
Redo Buffers 2170880 bytes
SQL >
3. Restore the control file and start the database to mount status
RMAN > restore controlfile from'/ data_back/racbk/48nav1vu_1_1_20120514.ctl'
RMAN > alter database mount
Database mounted
Released channel: ORA_DISK
4. Modify the path of data file and log file
Select 'set newname for datafile' | | chr (39) | | name | | chr (39) | |'to'| | chr (39) | | replace (name,'+DG0/db/datafile/','/home/oracle/data2/') | | chr (39) | |'; 'from v$datafile |
# # #
SQL > select 'set newname for datafile' | | chr (39) | | name | | chr (39) | |'to'| | chr (39) | | replace (name,'+DG0/db/datafile/','/home/oracle/data2/') | | chr (39) | |'; 'from v$datafile |
'SETNEWNAMEFORDATAFILE' | | CHR (39) | | NAME | | CHR (39) | |' TO' | | CHR (39) | | REPLACE (NAME,'+DG0/DB/DATAFILE/','/HOME/ORACLE/DATA2/') | | CHR (39) | |';'
- -
Set newname for datafile'+ DG0/db/datafile/sysaux.257.780159407' to'/ home/oracle/data2/sysaux.257.780159407'
Set newname for datafile'+ DG0/db/datafile/system.256.780159407' to'/ home/oracle/data2/system.256.780159407'
Set newname for datafile'+ DG0/db/datafile/timelineweb01.dbf' to'/ home/oracle/data2/timelineweb01.dbf'
Set newname for datafile'+ DG0/db/datafile/undotbs1.258.780159409' to'/ home/oracle/data2/undotbs1.258.780159409'
Set newname for datafile'+ DG0/db/datafile/undotbs2.264.780159511' to'/ home/oracle/data2/undotbs2.264.780159511'
Set newname for datafile'+ DG0/db/datafile/users.259.780159409' to'/ home/oracle/data2/users.259.780159409'
6 rows selected.
# #
5. Restore the database to a new directory (my environment is originally RAW restore to the file system)
Select 'alter database rename file' | | chr (39) | | member | | chr (39) | |'to'| | chr (39) | | replace (member,'+DG0/db/onlinelog/','/home/oracle/data2/') | | chr (39) | |'; 'from v$logfile |
SQL > select 'alter database rename file' | | chr (39) | | member | | chr (39) | |'to'| | chr (39) | | replace (member,'+DG0/db/onlinelog/','/home/oracle/data2/') | | chr (39) | |'; 'from v$logfile |
'ALTERDATABASERENAMEFILE' | | CHR (39) | | MEMBER | | CHR (39) | |' TO' | | CHR (39) | | REPLACE (MEMBER,'+DG0/DB/ONLINELOG/','/HOME/ORACLE/DATA2/') | | CHR (39) | |';'
- -
Alter database rename file'+ DG0/db/onlinelog/group_2.262.780159469' to'/ home/oracle/data2/group_2.262.780159469'
Alter database rename file'+ DG0/db/onlinelog/group_1.261.780159467' to'/ home/oracle/data2/group_1.261.780159467'
Alter database rename file'+ DG0/db/onlinelog/group_3.265.780159539' to'/ home/oracle/data2/group_3.265.780159539'
Alter database rename file'+ DG0/db/onlinelog/group_4.266.780159539' to'/ home/oracle/data2/group_4.266.780159539'
Execute the statements related to the log file in the sql interface:
Alter database rename file'+ DG0/db/onlinelog/group_2.262.780159469' to'/ home/oracle/data2/group_2.262.780159469'
Alter database rename file'+ DG0/db/onlinelog/group_1.261.780159467' to'/ home/oracle/data2/group_1.261.780159467'
Alter database rename file'+ DG0/db/onlinelog/group_3.265.780159539' to'/ home/oracle/data2/group_3.265.780159539'
Alter database rename file'+ DG0/db/onlinelog/group_4.266.780159539' to'/ home/oracle/data2/group_4.266.780159539'
Execute the following script in the rman interface:
RMAN > catalog start with'/ data_back/racbk/'
Find out the scn number of catalog
Rman > list backup # find the largest scn
Then run the following script
Run {
Set newname for datafile'+ DG0/db/datafile/sysaux.257.780159407' to'/ home/oracle/data2/sysaux.257.780159407'
Set newname for datafile'+ DG0/db/datafile/system.256.780159407' to'/ home/oracle/data2/system.256.780159407'
Set newname for datafile'+ DG0/db/datafile/timelineweb01.dbf' to'/ home/oracle/data2/timelineweb01.dbf'
Set newname for datafile'+ DG0/db/datafile/undotbs1.258.780159409' to'/ home/oracle/data2/undotbs1.258.780159409'
Set newname for datafile'+ DG0/db/datafile/undotbs2.264.780159511' to'/ home/oracle/data2/undotbs2.264.780159511'
Set newname for datafile'+ DG0/db/datafile/users.259.780159409' to'/ home/oracle/data2/users.259.780159409'
Set until scn 1385018
Restore database
Switch datafile all
Recover database
}
6. Open the database
SQL > alter database open resetlogs
Database altered.
7 、
Working with temporary files:
First create a temporary tablespace temp1
SQL > create temporary tablespace temp1 tempfile'/ home/oracle/data2/temp2.dbf' size 512m autoextend on next 1m maxsize unlimited
Tablespace created.
SQL > alter database default temporary tablespace temp1
Database altered.
Delete unused undo tablespaces and files
SQL > select tablespace_name,status from dba_data_files
TABLESPACE_NAME STATUS
USERS AVAILABLE
SYSAUX AVAILABLE
UNDOTBS1 AVAILABLE
SYSTEM AVAILABLE
UNDOTBS2 AVAILABLE
TIMELINEWEB AVAILABLE
6 rows selected.
SQL > show parameter undo
NAME TYPE VALUE
-
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1
SQL > drop tablespace undotbs2 including contents and datafiles
Tablespace dropped.
SQL >
Delete temporary files that have been replied through RMAN. Complete the whole process of temporary files.
SQL > drop tablespace temp including contents and datafiles
Tablespace dropped.
Delete unused logs
Select thread#,status,enabled from vThread;-- find thread, which can be deleted
SQL > select thread#,status,enabled from v$thread
THREAD# STATUS ENABLED
1 OPEN PUBLIC
2 CLOSED PUBLIC
SQL > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
-
1 1 0 52428800 1 YES UNUSED 0
2 1 1 52428800 1 NO CURRENT 1385019 14-MAY-12
3 2 0 52428800 1 YES UNUSED 0
42 1 52428800 1 NO CURRENT 1385019 14-MAY-12
SQL > select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
2 ONLINE / home/oracle/data2/group_2.262.780159469 NO
1 ONLINE / home/oracle/data2/group_1.261.780159467 NO
3 ONLINE / home/oracle/data2/group_3.265.780159539 NO
4 ONLINE / home/oracle/data2/group_4.266.780159539 NO
SQL > ALTER DATABASE DISABLE THREAD 2
Database altered.
SQL > alter database drop logfile group 3
2
Database altered.
SQL > alter database drop logfile group 4
Alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance db2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2:'/ home/oracle/data2/group_4.266.780159539'
SQL > alter database clear unarchived logfile group 4
Database altered.
SQL > alter database drop logfile group 4
Database altered.
SQL > select * from v$log
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
-
1 1 0 52428800 1 YES UNUSED 0
2 1 1 52428800 1 NO CURRENT 1385019 14-MAY-12
SQL > select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
2 ONLINE / home/oracle/data2/group_2.262.780159469 NO
1 ONLINE / home/oracle/data2/group_1.261.780159467 NO
SQL >
