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

This article mainly introduces ASM and File System data file transfer methods, the article is very detailed, has a certain reference value, interested friends must read it!

1. Environmental introduction

The experiment uses Oracle Linux environment, corresponding to the underlying Oracle 11g single instance + ASM storage scheme.

[root@SimpleLinux ~] # uname-r

2.6.18-128.el5

[root@SimpleLinux ~] # ps-ef | grep pmon

Grid 3214 1 0 13:53? 00:00:00 asm_pmon_+ASM

Oracle 3294 1 0 13:53? 00:00:00 ora_pmon_ora11g

Root 3376 3107 0 13:53 pts/0 00:00:00 grep pmon

The database uses the 11gR2 version.

SQL > select * from v$version

BANNER

-

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

Create a directory of data files.

[oracle@SimpleLinux oracle] $cd $ORACLE_HOME

[oracle@SimpleLinux db_1] $mkdir oradata

[oracle@SimpleLinux db_1] $ls-l | grep oradata

Drwxr-xr-x 2 oracle oinstall 4096 Dec 27 13:56 oradata

The database adopts archiving mode. Note: the following experiments are required to be implemented in archiving mode.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 25

Next log sequence to archive 27

Current log sequence 27

Create a lab file tablespace.

SQL > create tablespace test datafile'/ u01 size

2 extent management local uniform size 1m

3 segment space management auto

Tablespace created

SQL > select file_name, tablespace_name from dba_data_files

FILE_NAME TABLESPACE_NAME

-

+ DATA/ora11g/datafile/users.259.825944329 USERS

+ DATA/ora11g/datafile/undotbs1.258.825944329 UNDOTBS1

+ DATA/ora11g/datafile/sysaux.257.825944327 SYSAUX

+ DATA/ora11g/datafile/system.256.825944325 SYSTEM

+ DATA/ora11g/datafile/example.265.825944513 EXAMPLE

/ u01/app/oracle/product/11.2.0/db_1/oradata/testtb TEST

L.dbf

6 rows selected

2. ASMCMD command

ASMCMD is a command-line tool provided by Oracle to manage ASM. After entering 11g, ASMCMD provides the cp command, modeled on the cp command of the Linux platform. ASMCMD is an option we copied beforehand.

Our experiment is to transfer the TEST file of the file system to + DATA ASM DiskGroup.

First switch the logfile.

SQL > alter system switch logfile

System altered

-- We move online beforehand, only after offline.

SQL > alter database datafile'/ u01 offline App alter database datafile'/ u01 apprenticement produce 11.2.0 apprenticespact 1max oradata

Database altered

SQL > select file_name, online_status from dba_data_files where file_id=6

FILE_NAME ONLINE_STATUS

/ u01/app/oracle/product/11.2.0/db_1/oradata/testtb RECOVER

L.dbf

Enter the ASMCMD command line for processing.

Using grid users, note that this process is accompanied by the process of logging in to the ASM instance.

[oracle@SimpleLinux oradata] $su-grid

Password:

[grid@SimpleLinux ~] $asmcmd

ASMCMD > cp / u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf + DATA/ora11g/datafile/testtbl.dbf

Copying / u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf-> + DATA/ora11g/datafile/testtbl.dbf

In ASM, check to make sure the directory of the file exists.

ASMCMD > ls-l

Type Redund Striped Time Sys Name

DATAFILE UNPROT COARSE DEC 27 13:00:00 Y EXAMPLE.265.825944513

DATAFILE UNPROT COARSE DEC 27 13:00:00 Y SYSAUX.257.825944327

DATAFILE UNPROT COARSE DEC 27 13:00:00 Y SYSTEM.256.825944325

DATAFILE UNPROT COARSE DEC 27 13:00:00 Y UNDOTBS1.258.825944329

DATAFILE UNPROT COARSE DEC 27 13:00:00 Y USERS.259.825944329

N testtbl.dbf = > + DATA/ASM/DATAFILE/testtbl.dbf.268.835279797

In addition, in the control file system of Oracle, modify the file name.

SQL > alter database rename file'/ u01Gap App alter database rename file'/ u01AccessUniverse product 11.2.0GUSTBl dbf'oradata'+ DATA/ora11g/datafile/testtbl.dbf'

Database altered

SQL > select file_name, file_id from dba_data_files

FILE_NAME FILE_ID

(for reasons of space, there are omissions. )

+ DATA/ora11g/datafile/example.265.825944513 5

+ DATA/ora11g/datafile/testtbl.dbf 6

6 rows selected

Due to the online operation, the data file 6 is restored and put online.

SQL > alter database recover datafile 6

Database altered

SQL > alter database datafile 6 online

Database altered

SQL > select file_name, file_id, online_status from dba_data_files

FILE_NAME FILE_ID ONLINE_STATUS

-

(for reasons of space, there are omissions. )

+ DATA/ora11g/datafile/example.265.825944513 5 ONLINE

+ DATA/ora11g/datafile/testtbl.dbf 6 ONLINE

6 rows selected

Just delete the original data file.

[oracle@SimpleLinux oradata] $ls-l

Total 102512

-rw-r- 1 oracle asmadmin 104865792 Dec 27 14:02 testtbl.dbf

[oracle@SimpleLinux oradata] $rm testtbl.dbf

The experiment was successful.

3. DBMS_FILE_TRANSFER packet method

The dbms_file_transfer package in Oracle PL/SQL can also be transferred. The copy_file process can be used to transfer files across ASM and file systems.

Using the dbms_file_transfer package requires that you specify a file directory, which is done through the directory object in Oracle.

SQL > create directory souredir as'+ DATA/ora11g/datafile/'

Directory created

SQL > create directory destdir as'/ u01ActionAccording to an oracle, the product is 11.2.0, dbAction1andoradata'.

Directory created

In this method, we transfer the ASM file from the previous section to File System.

SQL > select file_name, file_id, online_status from dba_data_files

FILE_NAME FILE_ID ONLINE_STATUS

-

(for reasons of space, there are omissions. )

+ DATA/ora11g/datafile/example.265.825944513 5 ONLINE

+ DATA/ora11g/datafile/testtbl.dbf 6 RECOVER-data file that has been offline status

6 rows selected

SQL > begin

2 dbms_file_transfer.copy_file (source_directory_object = > 'souredir'

3 source_file_name = > 'testtbl.dbf'

4 destination_directory_object = > 'destdir'

5 destination_file_name = > 'testtbl.dbf')

6 end

7 /

PL/SQL procedure successfully completed

The file testtbl.dbf is viewed in the file system.

[oracle@SimpleLinux oradata] $ls-l

Total 102512

-rw-r- 1 oracle asmadmin 104865792 Dec 27 14:34 testtbl.dbf

SQL > alter database rename file'+ DATA/ora11g/datafile/testtbl.dbf' to'/ u01 apprenticeship oracle.productUniverse 11.2.0According to the name of the apprenticeship

Database altered

SQL > alter database recover datafile 6

Database altered

Online operation on the data file.

SQL > alter database datafile'/ u01 online App alter database datafile'/ u01 apprenticement produce 11.2.0 apprenticespact 1max oradata

Database altered

SQL > select file_name, file_id, online_status from dba_data_files

FILE_NAME FILE_ID ONLINE_STATUS

-

(for reasons of space, there are omissions. )

+ DATA/ora11g/datafile/example.265.825944513 5 ONLINE

/ u01/app/oracle/product/11.2.0/db_1/oradata/testtb 6 ONLINE

L.dbf

6 rows selected

The experiment was successful.

4. RMAN method

Before the cp command, RMAN was a more common method. Let's offline the file first.

SQL > alter database datafile'/ u01 offline App alter database datafile'/ u01 apprenticement produce 11.2.0 apprenticespact 1max oradata

Database altered

Start the RMAN copy process.

RMAN > connect target /

Connected to target database: ORA11G (DBID=4222144573)

Using target database control file instead of recovery catalog

RMAN > copy datafile'/ u01Gap App copy datafile'/ u01AccessUniverse product 11.2.0GUSTBl dbf'oradata'+ RECO'

2 >

Starting backup at 27-DEC-13

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=41 device type=DISK

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00006 name=/u01/app/oracle/product/11.2.0/db_1/oradata/testtbl.dbf

Output file name=+RECO/ora11g/datafile/test.282.835281667 tag=TAG20131227T144106 RECID=2 STAMP=835281676

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

Finished backup at 27-DEC-13

Change the file name to perform the data file online operation.

SQL > alter database rename file'/ u01Gap App alter database rename file'/ u01AccessUniverse product 11.2.0GUSTBl dbf'oradata'+ RECO/ora11g/datafile/test.282.835281667'

Database altered

SQL > alter database recover datafile 6

Database altered

SQL > alter database datafile 6 online

Database altered

The experiment was successful.

The above is all the contents of the article "what are the methods of transferring ASM and File System data files?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.