Shulou(Shulou.com)05/31 Report--

This article is about how to use the switch command in RMAN. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Detailed explanation of SWITCH command

Purpose: purpose, what can be done with the switch command

Use the SWITCH command to perform either of the following operations:

Update the file names for a database, tablespace, or data file to the latest image copies available for the specified files

1. Updates the file name of the database, table space, or data file to the latest image copy available for the specified file

Update the file names for data files and temp files for which you have issued a SET NEWNAME command

two。 Updates the specified filename information in the control file after set netname is issued in the run block

A SWITCH is equivalent to the SQL statement ALTER DATABASE RENAME FILE: the names of the files in the RMAN repository are updated, but the database does not rename the files at the operating system level.

Prerequisites (prerequisite)

RMAN must be connected to a target database. When switching tablespaces, data files, or temp files, the files must be offline. When switching the whole database, the database must not be open.

RMAN must connect to the target database. When switching tablespaces, data files, or temporary files, these files must be offline. When switching the entire database, the database must not be opened.

Usage Notes (instructions for use)

The SWITCH command deletes the RMAN repository records for the data file copy from the recovery catalog and updates the control file records to status DELETED.

The SWITCH command removes the RMAN repository record for the copy of the data file from the recovery directory and updates the control file record to the DELETED status.

If RMAN is connected to a recovery catalog, and if the database is using a control file restored from backup, then SWITCH updates the control file with records of any data files known to the recovery catalog but missing from the control file.

If RMAN connects to the recovery directory and the database is using the control file restored from the backup, SWITCH updates the control file and records any data files that are known to the recovery directory but are missing from the control file.

Execute SWITCH... TO COPY only at the RMAN prompt. Use SWITCH without TO COPY only within a RUN block.

Execute SWITCH only at the RMAN prompt... TO COPY . Use SWITCH only within RUN blocks, not TO COPY.

Syntax:

SWITCH {DATABASE | DATAFILE datafileSpec [, datafileSpec]... | | TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']...} TO COPY |

SWITCH {DATAFILE ALL | DATAFILE datafileSpec [TO DATAFILECOPY {'filename' | TAG ['] tag_name [']}] | TEMPFILE ALL | TEMPFILE tempfileSpec [TO 'filename']}

Semantics semantics

Switch

This subclause switches file names for a database, tablespace, or data file to the latest image copies available for the specified files. By executing this command, you avoid restoring data files from backups. Execute SWITCH... TO COPY only at the RMAN prompt.

This clause switches the file name of the database, table space, or data file to the latest image copy that can be used for the specified file. By executing this command, you can avoid restoring data files from the backup. Execute SWITCH only at the RMAN prompt... TO COPY .

Switch File

This subclause updates the names for data files and temp files for which you have issued a SET NEWNAME command. Use this clause only within a RUN block.

This clause updates the names of the data files and temporary files for which you issued the SET NEWNAME command. Use this clause only in RUN blocks.

Examples usage example

Example 3-55 Switching to Image Copies to Avoid Restoring from Backup (switch to image copy to avoid recovery from backup)

Assume that a disk fails, rendering all data files in the users tablespace inaccessible. Image copies of all data files in this tablespace exist in the fast recovery area. After starting RMAN and connecting to the database as TARGET, you can run SWITCH to point to the control file to the new data files and then run RECOVER as follows: suppose a disk fails and all data files in the user's tablespace will not be accessible. There are image copies of all data files in this tablespace in the quick recovery area. After you start RMAN and connect to the database as TARGET, you can run SWITCH's control file that points to the new file, and then run RECOVER as shown below.

SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE"

SWITCH TABLESPACE users TO COPY

RECOVER TABLESPACE users

SQL "ALTER TABLESPACE users ONLINE"

Example 3-56 Switching Data File File Names After a Restore to a New Location (switch datafile name after restore to a new location)

Assume that a disk fails, forcing you to restore a data file to a new disk location. After starting RMAN and connecting to the database as TARGET, you can use the SET NEWNAME command to rename the data file, then RESTORE to restore the missing data file. You run SWITCH to point to the control file to the new data file and then RECOVER. This example allocates both disk and tape channels. Suppose the disk fails, forcing you to restore the data file to the new disk location. After you start RMAN and connect to the database as TARGET, you can use the SET NEWNAME command to rename the data file, and then use RESTORE to restore the missing data file. You run SWITCH to point the control file to the new data file, and then restore it. This example allocates disk and tape channels.

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK

ALLOCATE CHANNEL dev2 DEVICE TYPE sbt

SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE"

SET NEWNAME FOR DATAFILE'/ disk1/oradata/prod/users01.dbf'

TO'/ disk2/users01.dbf'

RESTORE TABLESPACE users

SWITCH DATAFILE ALL

RECOVER TABLESPACE users

SQL "ALTER TABLESPACE users ONLINE"

}

The first use of the experiment is as follows:

Update the file names for a database, tablespace, or data file to the latest image copies available for the specified files

Updates the file name of the database, table space, or data file to the latest image copy available for the specified file

1. Query tablespace file location and size

SQL > select f.file#

2 t.name tablespace

3 f.name

4 trunc (f.bytes / 1048576, 2) size_mb

5 to_char (f.creation_time, 'yyyy-mm-dd') creation_time

6 status

7 from v$datafile f, v$tablespace t

8 where f.ts# = t.ts#

9 order by f.creation_time

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS

-

1 SYSTEM + DATA/dbrac/datafile/system.256.957563979 900 2013-08-24 SYSTEM

2 SYSAUX + DATA/dbrac/datafile/sysaux.257.957563979 610 2013-08-24 ONLINE

4 USERS + DATA/dbrac/datafile/users.259.957563981 171.25 2013-08-24 ONLINE

3 UNDOTBS1 + DATA/dbrac/datafile/undotbs1.258.957563981 100 2013-08-24 ONLINE

5 EXAMPLE + DATA/dbrac/datafile/example.264.957564205 346.25 2017-10-16 ONLINE

6 UNDOTBS2 + DATA/dbrac/datafile/undotbs2.265.957564601 100 2017-10-16 ONLINE

7 PERSON_LOB + DATA/dbrac/datafile/person_lob.269.957699435 10 2017-10-18 ONLINE

8 TEST + DATA/dbrac/datafile/test.270.957699617 20 2017-10-18 ONLINE

9 TEST_IND + DATA/dbrac/datafile/test_ind.271.957699627 20 2017-10-18 ONLINE

10 TEST_BLOB + DATA/dbrac/datafile/test_blob.273.957699641 20 2017-10-18 ONLINE

11 TEST_IND + DATA/dbrac/datafile/test_ind.274.957699781 400 2017-10-18 ONLINE

12 TEST_BLOB + DATA/dbrac/datafile/test_blob.275.957699833 400 2017-10-18 ONLINE

13 TEST + DATA/dbrac/datafile/test.276.957699883 400 2017-10-18 ONLINE

13 rows selected.

-- query the remaining space of the asm disk group:

[grid@dbrac1] $asmcmd-p

ASMCMD [+] > lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED NORMAL N 512 4096 1048576 30720 22892 0 11446 0 N DATA/

MOUNTED NORMAL N 512 4096 1048576 10240 4610 0 2305 0 N FRA/

MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCRVOTE/

ASMCMD [+] >

[grid@dbrac2 ~] $sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 19 18:40:22 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL > select status from v$instance

STATUS

-

STARTED

SQL > set lines 200

SQL > col name for A15

SQL > select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS,VOTING_FILES from v$asm_diskgroup

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS VO

1 DATA MOUNTED NORMAL 30720 22892 0 11446 0 N

2 FRA MOUNTED NORMAL 10240 4610 0 2305 0 N

3 OCRVOTE MOUNTED NORMAL 6144 5218 2048 1585 0 Y

SQL > select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 1 Pol 2

GROUP_NUMBER DISK_NUMBER MOUNT_STATUS HEADER_STA MODE_STATUS STATE REDUNDANCY TOTAL_MB FREE_MB NAME FAILGROUP PATH

--

1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11446 DATA_0000 DATA_0000 / dev/asm_data1

11 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11446 DATA_0001 DATA_0001 / dev/asm_data2

20 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2305 FRA_0000 FRA_0000 / dev/asm_fra1

2 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2305 FRA_0001 FRA_0001 / dev/asm_fra2

3 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1740 OCRVOTE_0000 OCRVOTE_0000 / dev/asm_ocrvote1

3 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0001 OCRVOTE_0001 / dev/asm_ocrvote2

3 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0002 OCRVOTE_0002 / dev/asm_ocrvote3

7 rows selected.

Simulate the migration of data files from test disks from DATA disk groups to FRA disk groups:

Check the archive:

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 65

Next log sequence to archive 66

Current log sequence 66

SQL >

SQL > show parameter recover

NAME TYPE VALUE

-

Db_recovery_file_dest string + FRA

Db_recovery_file_dest_size big integer 4407M

Db_unrecoverable_scn_tracking boolean TRUE

Recovery_parallelism integer 0

SQL >

SQL > show parameter log_archive_dest

NAME TYPE VALUE

-

Log_archive_dest string

Log_archive_dest_1 string

Log_archive_dest_10 string

Log_archive_dest_11 string

Log_archive_dest_12 string

Omit.

Check test tablespace and data file size and location:

SQL > select f.file#

2 t.name tablespace

3 f.name

4 trunc (f.bytes / 1048576, 2) size_mb

5 to_char (f.creation_time, 'yyyy-mm-dd') creation_time

6 status

7 from v$datafile f, v$tablespace t

8 where f.ts# = t.ts#

9 and t. Nameplate tests

10 order by f.creation_time

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS

-

8 TEST + DATA/dbrac/datafile/test.270.957699617 20 2017-10-18 ONLINE

13 TEST + DATA/dbrac/datafile/test.276.957699883 400 2017-10-18 ONLINE

SQL > select s.file_name

2 s.tablespace_name

3 s.bytes / 1024 / 1024 size_m

4 s.ONLINE_STATUS

5 d.status

6 d.contents

7 from dba_data_files s, dba_tablespaces d

8 where s.TABLESPACE_NAME = d.TABLESPACE_NAME and s.tablespaceNameplate tests

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

-

+ DATA/dbrac/datafile/test.270.957699617 TEST 20 ONLINE ONLINE PERMANENT

+ DATA/dbrac/datafile/test.276.957699883 TEST 400 ONLINE ONLINE PERMANENT

SQL >

-- use rman to back up the tablespace test, with the mode of image copy copy:

Sql "alter tablespace test offline"

Backup as copy datafile 8 format'+ fra'

Backup as copy datafile 13 format'+ fra'

Switch datafile 8 to copy

Switch datafile 13 to copy

-- recover datafile 8

-- recover datafile 13

Sql "alter tablespace test online"

Note: recover can be omitted because offline is as follows

OFFLINE NORMAL Specify NORMAL to flush all blocks in all data files in the tablespace out of the system global area (SGA). You need not perform media recovery on this tablespace before bringing it back online. This is the default.

The operation record is omitted.

Query after execution:

SQL > l

1 select f.file#

2 t.name tablespace

3 f.name

4 trunc (f.bytes / 1048576, 2) size_mb

5 to_char (f.creation_time, 'yyyy-mm-dd') creation_time

6 status

7 from v$datafile f, v$tablespace t

8 where f.ts# = t.ts#

9 and t. Nameplate tests

10 * order by f.creation_time

SQL > /

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS

8 TEST + FRA/dbrac/datafile/test.264.957814459 20 2017-10-18 ONLINE

13 TEST + FRA/dbrac/datafile/test.362.957812657 400 2017-10-18 ONLINE

SQL >

Perform the migration of test tablespace files from the FRA disk group to the DATA disk group again:

Sql "alter tablespace test offline"

Backup as copy datafile 8 format'+ DATA'

Backup as copy datafile 13 format'+ DATA'

Switch datafile 8 to copy

Switch datafile 13 to copy

Sql "alter tablespace test online"

Do the following:

RMAN > sql "alter tablespace test offline"

Sql statement: alter tablespace test offline

RMAN > backup as copy datafile 8 format'+ DATA'

Starting backup at 2017-10-19 19:49:23

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00008 name=+FRA/dbrac/datafile/test.264.957814459

Output file name=+DATA/dbrac/datafile/test.278.957815365 tag=TAG20171019T194923 RECID=8 STAMP=957815367

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 2017-10-19 19:49:31

RMAN > backup as copy datafile 13 format'+ DATA'

Starting backup at 2017-10-19 19:49:37

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00013 name=+FRA/dbrac/datafile/test.362.957812657

Output file name=+DATA/dbrac/datafile/test.277.957815379 tag=TAG20171019T194937 RECID=9 STAMP=957815430

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

Finished backup at 2017-10-19 19:50:33

RMAN > switch datafile 8 to copy

Datafile 8 switched to datafile copy "+ DATA/dbrac/datafile/test.278.957815365"

RMAN > switch datafile 13 to copy

Datafile 13 switched to datafile copy "+ DATA/dbrac/datafile/test.277.957815379"

RMAN > sql "alter tablespace test online"

Sql statement: alter tablespace test online

RMAN >

Finally, verify:

SQL > l

1 select s.file_name

2 s.tablespace_name

3 s.bytes / 1024 / 1024 size_m

4 s.ONLINE_STATUS

5 d.status

6 d.contents

7 from dba_data_files s, dba_tablespaces d

8 * where s.TABLESPACE_NAME = d.TABLESPACE_NAME and s.tablespaceNameplate tests

SQL > /

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

-

+ DATA/dbrac/datafile/test.278.957815365 TEST 20 ONLINE ONLINE PERMANENT

+ DATA/dbrac/datafile/test.277.957815379 TEST 400 ONLINE ONLINE PERMANENT

SQL >

1 select f.file#

2 t.name tablespace

3 f.name

4 trunc (f.bytes / 1048576, 2) size_mb

5 to_char (f.creation_time, 'yyyy-mm-dd') creation_time

6 status

7 from v$datafile f, v$tablespace t

8 where f.ts# = t.ts#

9 and t. Nameplate tests

10 order by f.creation_time

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS

8 TEST + DATA/dbrac/datafile/test.278.957815365 20 2017-10-18 ONLINE

13 TEST + DATA/dbrac/datafile/test.277.957815379 400 2017-10-18 ONLINE

SQL > select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 1 Pol 2

GROUP_NUMBER DISK_NUMBER MOUNT_STATUS HEADER_STA MODE_STATUS STATE REDUNDANCY TOTAL_MB FREE_MB NAME FAILGROUP PATH

--

10 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11023 DATA_0000 DATA_0000 / dev/asm_data1

11 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11023 DATA_0001 DATA_0001 / dev/asm_data2

20 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2967 FRA_0000 FRA_0000 / dev/asm_fra1

2 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2967 FRA_0001 FRA_0001 / dev/asm_fra2

3 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1740 OCRVOTE_0000 OCRVOTE_0000 / dev/asm_ocrvote1

3 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0001 OCRVOTE_0001 / dev/asm_ocrvote2

3 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0002 OCRVOTE_0002 / dev/asm_ocrvote3

7 rows selected.

SQL > select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS,VOTING_FILES from v$asm_diskgroup

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS VO

1 DATA MOUNTED NORMAL 30720 22046 0 11023 0 N

2 FRA MOUNTED NORMAL 10240 5934 0 2967 0 N

3 OCRVOTE MOUNTED NORMAL 6144 5218 2048 1585 0 Y

[grid@dbrac1 ~] $asmcmd lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED NORMAL N 512 4096 1048576 30720 22046 0 11023 0 N DATA/

MOUNTED NORMAL N 512 4096 1048576 10240 5934 0 2967 0 N FRA/

MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCRVOTE/

[grid@dbrac1 ~] $

Thank you for reading! This is the end of the article on "how to use switch commands in RMAN". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.