Shulou(Shulou.com)06/01 Report--

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.