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

File system how to migrate to ASM, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Database complete is only a complete databse, without any additional functions, only

The level 0 incremental backup is not only the complete backup of the database, but also the level 0 incremental backup. On this basis, the incremental backup can be carried out later. Therefore, it is recommended to use the level 0 incremental backup to complete the database.

It is best to use grid users to enter the asm environment

[oracle@sink ~] $su-grid

Password:

[grid@sink ~] $asmcmd

Backup files after level 0 incremental backup

ASMCMD > pwd

+ data/sink

ASMCMD > ls

BACKUPSET/

CONTROLFILE/

DATAFILE/

PARAMETERFILE/

Check the level 0 incremental backup you just made

RMAN > list copy of database

List of Datafile Copies

=

Key File S Completion Time Ckp SCN Ckp Time

-

9 1 A 06-JAN-18 1218041 06-JAN-18

Name: + DATA/sink/datafile/system.258.964712643

Tag: ORA_ASM_MIGRATION

8 2 A 06-JAN-18 1218040 06-JAN-18

Name: + DATA/sink/datafile/sysaux.257.964712643

Tag: ORA_ASM_MIGRATION

4 3 A 06-JAN-18 1218045 06-JAN-18

Name: + DATA/sink/datafile/undotbs1.261.964712645

Tag: ORA_ASM_MIGRATION

6 4 A 06-JAN-18 1218050 06-JAN-18

Name: + DATA/sink/datafile/users.263.964712657

Tag: ORA_ASM_MIGRATION

7 5 A 06-JAN-18 1218043 06-JAN-18

Name: + DATA/sink/datafile/example.260.964712643

Tag: ORA_ASM_MIGRATION

3 6 A 06-JAN-18 1218042 06-JAN-18

Name: + DATA/sink/datafile/tbssss.259.964712643

Tag: ORA_ASM_MIGRATION

RMAN >

Dump spfile to

RMAN > restore spfile to'+ DATA/spfilesink.ora'

Starting restore at 06-JAN-18

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=18 device type=DISK

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: restoring SPFILE

Output file name=+DATA/spfilesink.ora

Channel ORA_DISK_1: reading from backup piece + DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657

Channel ORA_DISK_1: piece handle=+DATA/sink/backupset/2018_01_06/nnsnn0_ora_asm_migration_0.264.964712657 tag=ORA_ASM_MIGRATION

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 06-JAN-18

View the status of the path in ASM after dumping the spfile parameter file

ASMCMD > pwd

+ data

ASMCMD > ls

ASM/

SINK/

Spfilesink.ora

ASMCMD >

Modify and view the content in pfile so that its content points to the path to the spfilesink.ora of spfile in ASM. Interesting!

[oracle@sink dbs] $pwd

/ u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@sink dbs] $ls

Afiedt.buf hc_test.dat initsink.ora lkSINK orapwsink snapcf_sink.f

Hc_sink.dat init.ora inittest.ora lkTEST orapwtest spfilesink.ora

[oracle@sink dbs] $vim initsink.ora

[oracle@sink dbs] $cat initsink.ora

Spfile='+DATA/spfilesink.ora'

[oracle@sink dbs] $

Enter the SQL environment again, start to nomount, set the spfile path to point, and check whether the relevant parameters take effect! Succeed!

[oracle@sink dbs] $! sql

Sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 16:30:19 2018

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 Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

16:30:19 SYS @ sink > startup force nomount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 553651280 bytes

Database Buffers 276824064 bytes

Redo Buffers 2371584 bytes

16:30:38 SYS @ sink > alter system set db_create_file_dest='+DATA'

System altered.

Elapsed: 00:00:00.01

16:31:23 SYS @ sink > show parameter create

NAME TYPE VALUE

-

Create_bitmap_area_size integer 8388608

Create_stored_outlines string

Db_create_file_dest string + DATA

Db_create_online_log_dest_1 string

Db_create_online_log_dest_2 string

Db_create_online_log_dest_3 string

Db_create_online_log_dest_4 string

Db_create_online_log_dest_5 string

16:31:33 SYS @ sink >

Since the previous step, check the database startup status, and then satrtup force, boot it to opened state

16:31:33 SYS @ sink > select status from v$instance

STATUS

-

STARTED

1 row selected.

Elapsed: 00:00:00.00

16:35:24 SYS @ sink > alter database open

Alter database open

*

ERROR at line 1:

ORA-01507: database not mounted

Elapsed: 00:00:00.00

16:35:39 SYS @ sink > startup force

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 553651280 bytes

Database Buffers 276824064 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

16:36:24 SYS @ sink >

When oracle starts to a certain state, it starts satrtup nomount

Alter database mountalter database openstartup mountalter database open--startup (open)-after oracle starts to a certain state, it can only be started step by step until the state of open, and subsequent startup cannot be carried out beyond the level.

Query the file# and name of data files here to facilitate the migration of data files.

SYS @ sink > r

1 * select file#,name from v$datafile

FILE# NAME

1/ u01/app/oracle/oradata/sink/system01.dbf

2 / u01/app/oracle/oradata/sink/sysaux01.dbf

3 / u01/app/oracle/oradata/sink/undotbs01.dbf

4 / u01/app/oracle/oradata/sink/users01.dbf

5 / u01/app/oracle/oradata/sink/example01.dbf

6 + DATA/sink/datafile/tbssss.256.963504823

6 rows selected.

Elapsed: 00:00:00.01

Failed to dump the control file, see the error message: it means that the to statement is not used to execute this statement when the database is mount or open

RMAN > restore controlfile from'/ u01qqappAccording to oradataOnsinkUniverse control01.ctl'

Starting restore at 06-JAN-18

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=42 device type=DISK

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of restore command at 01/06/2018 16:45:57

RMAN-06496: must use the TO clause when the database is mounted or open

It was not possible to add the to statement this time, but after entangling for a while, I found that the dump control file found that the oralce must be in the nomount state.

RMAN > restore controlfile from'/ u01According to an oracle control 01.ctl' to'+ data/'

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "to": expecting one of: "archivelog, channel, check, controlfile, database, datafile, device, force, from, high, preview, primary, skip readonly, spfile, standby, tablespace, to restore point, until restore point, until, validate, (;"

RMAN-01007: at line 1 column 71 file: standard input

After you find a clue, start the database to nomount state

[oracle@sink dbs] $! sql

Sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 16:48:57 2018

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 Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

16:48:57 SYS @ sink > select status from v$instance

STATUS

-

OPEN

1 row selected.

Elapsed: 00:00:00.01

16:49:10 SYS @ sink > startup nomount

ORA-01081: cannot start already-running ORACLE-shut it down first

16:49:17 SYS @ sink > startup force nomount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 553651280 bytes

Database Buffers 276824064 bytes

Redo Buffers 2371584 bytes

16:49:33 SYS @ sink > select status from v$instance

STATUS

-

STARTED

1 row selected.

Elapsed: 00:00:00.01

16:49:57 SYS @ sink >

Well, after a lot of entanglement, it finally succeeded, and the control file dump was successful!

RMAN > restore controlfile from'/ u01qqappAccording to oradataOnsinkUniverse control01.ctl'

Starting restore at 06-JAN-18

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=23 device type=DISK

Channel ORA_DISK_1: copied control file copy

Output file name=+DATA/sink/controlfile/current.266.964717197

Output file name=+DATA/sink/controlfile/current.267.964717197

Finished restore at 06-JAN-18

RMAN >

View the effect of control files after migration

ASMCMD > pwd

+ data/sink/controlfile

ASMCMD > ls

Backup.262.964712653

Current.266.964717197

Current.267.964717197

Cut the database to the mount state in the RMAN environment, switch... Modify the control file to use datafile copy for the datafile use of the current DB

RMAN > alter database mount

Database mounted

Released channel: ORA_DISK_1

RMAN > switch database to copy

Using target database control file instead of recovery catalog

Datafile 1 switched to datafile copy "+ DATA/sink/datafile/system.258.964712643"

Datafile 2 switched to datafile copy "+ DATA/sink/datafile/sysaux.257.964712643"

Datafile 3 switched to datafile copy "+ DATA/sink/datafile/undotbs1.261.964712645"

Datafile 4 switched to datafile copy "+ DATA/sink/datafile/users.263.964712657"

Datafile 5 switched to datafile copy "+ DATA/sink/datafile/example.260.964712643"

Datafile 6 switched to datafile copy "+ DATA/sink/datafile/tbssss.259.964712643"

Directly exit exits the rman environment, and then directly! sql enters the SQL environment. Note that the database status is still mount, and check the path of dataifle.

RMAN > exit

Recovery Manager complete.

[oracle@sink dbs] $! sql

Sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 17:42:31 2018

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 Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

17:42:41 SYS @ sink > col name for A55

17:42:57 SYS @ sink > r

1 * select name from v$datafile

NAME

+ DATA/sink/datafile/system.258.964712643

+ DATA/sink/datafile/sysaux.257.964712643

+ DATA/sink/datafile/undotbs1.261.964712645

+ DATA/sink/datafile/users.263.964712657

+ DATA/sink/datafile/example.260.964712643

+ DATA/sink/datafile/tbssss.259.964712643

6 rows selected.

Elapsed: 00:00:00.01

17:42:58 SYS @ sink >

Recover databse (application) open multiple channel to speed up the recover, the production of DB is very large, the recovery time is very long, this is a good way

RMAN > run {

2 > allocate channel dev1 device type disk

3 > allocate channel dev2 device type disk

4 > allocate channel dev3 device type disk

5 > allocate channel dev4 device type disk

6 > recover database

7 >}

Released channel: ORA_DISK_1

Allocated channel: dev1

Channel dev1: SID=25 device type=DISK

Allocated channel: dev2

Channel dev2: SID=26 device type=DISK

Allocated channel: dev3

Channel dev3: SID=27 device type=DISK

Allocated channel: dev4

Channel dev4: SID=28 device type=DISK

Starting recover at 06-JAN-18

Starting media recovery

Media recovery complete, elapsed time: 00:00:00

Finished recover at 06-JAN-18

Released channel: dev1

Released channel: dev2

Released channel: dev3

Released channel: dev4

RMAN >

Exit, enter the sql environment, open to open, see the temporary file name, temporary tablespace name

RMAN > exit

Recovery Manager complete.

[oracle@sink dbs] $! sql

Sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 17:57:16 2018

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 Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

17:59:16 SYS @ sink > alter database open

Database altered.

Elapsed: 00:00:02.44

17:59:50 SYS @ sink > select name from v$tempfile

NAME

/ u01/app/oracle/oradata/sink/temp01.dbf

1 row selected.

Elapsed: 00:00:00.02

18:00:05 SYS @ sink > select tablespace_name,contents from dba_tablespaces

TABLESPACE_NAME CONTENTS

SYSTEM PERMANENT

SYSAUX PERMANENT

UNDOTBS1 UNDO

TEMP TEMPORARY

USERS PERMANENT

EXAMPLE PERMANENT

TBSSSS PERMANENT

7 rows selected.

Elapsed: 00:00:00.01

Add the temporary file temp01.dbf in the temporary tablespace temp to the + date directory in ASM

18:00:24 SYS @ sink > alter tablespace temp add tempfile'+ data'

Tablespace altered.

Elapsed: 00:00:00.11

18:01:27 SYS @ sink > select name from v$tempfile

NAME

/ u01/app/oracle/oradata/sink/temp01.dbf

+ DATA/sink/tempfile/temp.268.964720887

2 rows selected.

Elapsed: 00:00:00.01

Because there is only one temporary file in a temporary tablespace, it cannot be deleted, and multiple files can be successfully deleted, until the last one cannot be deleted in this way.

18:12:22 SYS @ sink > alter tablespace temp drop tempfile'/ u01Accord oradataUniplex temp01.dbf'

Tablespace altered.

Elapsed: 00:00:00.12

18:13:35 SYS @ sink > select name from v$tempfile

NAME

/ u01/app/oracle/oradata/sink/temp01.dbf

+ DATA/sink/tempfile/temp.268.964720887

2 rows selected.

Elapsed: 00:00:00.01

In the previous step, DB is in open. When the temporary file is deleted to 1 in the above operation, you cannot continue to delete it. You need to put DB in mount state.

18:14:03 SYS @ sink > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

18:19:51 SYS @ sink > startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 553651280 bytes

Database Buffers 276824064 bytes

Redo Buffers 2371584 bytes

Database mounted.

18:20:08 SYS @ sink > alter database drop tempfile'/ u01Accord oradataUniplex temp01.dbf'

Alter database drop tempfile'/ u01qqapp.oracle.oradata.temp01.dbf'.

*

ERROR at line 1:

ORA-01900: LOGFILE keyword expected

Elapsed: 00:00:00.00

18:21:45 SYS @ sink > edit

Wrote file afiedt.buf

1 * alter database tempfile'/ u01 drop App drop oracleUniple oradataUniplicatesinkUniverse

18:22:16 SYS @ sink > r

1 * alter database tempfile'/ u01 drop App drop oracleUniple oradataUniplicatesinkUniverse

Database altered.

Elapsed: 00:00:00.03

18:22:17 SYS @ sink > alter database open

Database altered.

Elapsed: 00:00:02.34

18:23:19 SYS @ sink > col tablespace_name for A10

18:23:38 SYS @ sink > col file_name for A55

18:23:54 SYS @ sink > r

1 * select tablespace_name,file_name from dba_temp_files

TABLESPACE FILE_NAME

TEMP + DATA/sink/tempfile/temp.268.964720887

1 row selected.

Elapsed: 00:00:00.00

Migration log files to ASM, the details will not be explained, so long, tired, go to dinner

18:23:55 SYS @ sink > select group#,status,sequence#,bytes from v$log

GROUP# STATUS SEQUENCE# BYTES

--

1 INACTIVE 16 52428800

2 CURRENT 17 52428800

3 INACTIVE 15 52428800

3 rows selected.

Elapsed: 00:00:00.01

18:29:19 SYS @ sink > col member for A55

18:29:37 SYS @ sink > select group#,member from v$logfile

GROUP# MEMBER

3 / u01/app/oracle/oradata/sink/redo03.log

2 / u01/app/oracle/oradata/sink/redo02.log

1/ u01/app/oracle/oradata/sink/redo01.log

3 rows selected.

Elapsed: 00:00:00.01

18:29:57 SYS @ sink > alter database add logfile'+ data' size 50m

Database altered.

Elapsed: 00:00:01.41

18:30:32 SYS @ sink > alter database add logfile'+ data' size 50m

Database altered.

Elapsed: 00:00:00.62

18:30:54 SYS @ sink > select group#,member from v$logfile

GROUP# MEMBER

3 / u01/app/oracle/oradata/sink/redo03.log

2 / u01/app/oracle/oradata/sink/redo02.log

1/ u01/app/oracle/oradata/sink/redo01.log

4 + DATA/sink/onlinelog/group_4.269.964722631

5 + DATA/sink/onlinelog/group_5.270.964722653

5 rows selected.

Elapsed: 00:00:00.00

18:31:13 SYS @ sink > select group#,status,sequence#,bytes from v$log

GROUP# STATUS SEQUENCE# BYTES

--

1 INACTIVE 16 52428800

2 CURRENT 17 52428800

3 INACTIVE 15 52428800

4 UNUSED 0 52428800

5 UNUSED 0 52428800

5 rows selected.

Elapsed: 00:00:00.02

18:33:37 SYS @ sink > alter database drop logfile group 1

Database altered.

Elapsed: 00:00:00.01

18:35:03 SYS @ sink > alter database drop logfile group 3

Database altered.

Elapsed: 00:00:00.01

18:35:20 SYS @ sink > select group#,status,sequence#,bytes from v$log

GROUP# STATUS SEQUENCE# BYTES

--

2 CURRENT 17 52428800

4 UNUSED 0 52428800

5 UNUSED 0 52428800

3 rows selected.

Elapsed: 00:00:00.00

18:35:56 SYS @ sink > alter system switch logfile

System altered.

Elapsed: 00:00:00.01

18:36:31 SYS @ sink > r

1 * alter system switch logfile

System altered.

Elapsed: 00:00:00.01

18:36:37 SYS @ sink > select group#,status,sequence#,bytes from v$log

GROUP# STATUS SEQUENCE# BYTES

--

2 ACTIVE 17 52428800

4 ACTIVE 18 52428800

5 CURRENT 19 52428800

3 rows selected.

Elapsed: 00:00:00.01

18:37:00 SYS @ sink > alter system checkpoint

System altered.

Elapsed: 00:00:00.01

18:37:35 SYS @ sink > select group#,status,sequence#,bytes from v$log

GROUP# STATUS SEQUENCE# BYTES

--

2 INACTIVE 17 52428800

4 INACTIVE 18 52428800

5 CURRENT 19 52428800

3 rows selected.

Elapsed: 00:00:00.01

18:38:02 SYS @ sink > alter database drop logfile group 2

Database altered.

Elapsed: 00:00:00.02

18:38:54 SYS @ sink > select group#,status,sequence#,bytes from v$log

GROUP# STATUS SEQUENCE# BYTES

--

4 INACTIVE 18 52428800

5 CURRENT 19 52428800

2 rows selected.

Elapsed: 00:00:00.00

18:39:30 SYS @ sink > select group#,member from v$logfile

GROUP# MEMBER

4 + DATA/sink/onlinelog/group_4.269.964722631

5 + DATA/sink/onlinelog/group_5.270.964722653

2 rows selected.

Elapsed: 00:00:00.00

18:40:00 SYS @ sink > alter database add logfile'+ data' size 50m

Database altered.

Elapsed: 00:00:00.32

18:40:36 SYS @ sink > select group#,member from v$logfile

GROUP# MEMBER

1 + DATA/sink/onlinelog/group_1.271.964723237

4 + DATA/sink/onlinelog/group_4.269.964722631

5 + DATA/sink/onlinelog/group_5.270.964722653

3 rows selected.

Elapsed: 00:00:00.00

All right, the effect of all the results, data migration completed (parameter file, control file, data file, log file)

18:41:47 SYS @ sink > select name from v$datafile

18:41:59 2 union all

18:42:03 3 select name from v$controlfile

18:42:18 4 union all

18:42:21 5 select member name from v$logfile

NAME

+ DATA/sink/datafile/system.258.964712643

+ DATA/sink/datafile/sysaux.257.964712643

+ DATA/sink/datafile/undotbs1.261.964712645

+ DATA/sink/datafile/users.263.964712657

+ DATA/sink/datafile/example.260.964712643

+ DATA/sink/datafile/tbssss.259.964712643

+ DATA/sink/controlfile/current.266.964717197

+ DATA/sink/controlfile/current.267.964717197

+ DATA/sink/onlinelog/group_1.271.964723237

+ DATA/sink/onlinelog/group_4.269.964722631

+ DATA/sink/onlinelog/group_5.270.964722653

11 rows selected.

Elapsed: 00:00:00.01

18:42:35 SYS @ sink >

This is the effect of migrating to ASM.

ASMCMD > pwd

+ data/sink/datafile

ASMCMD > ls

EXAMPLE.260.964712643

SYSAUX.257.964712643

SYSTEM.258.964712643

TBSSSS.256.963504823

TBSSSS.259.964712643

UNDOTBS1.261.964712645

USERS.263.964712657

ASMCMD > cd.. / controlfile

ASMCMD > pwd

+ data/sink/controlfile

ASMCMD > ls

Backup.262.964712653

Current.266.964717197

Current.267.964717197

ASMCMD > cd.. / onlinelog

ASMCMD > pwd

+ data/sink/onlinelog

ASMCMD > ls

Group_1.271.964723237

Group_4.269.964722631

Group_5.270.964722653

ASMCMD >

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.