A small example of redirecting data files under asm (using the toad tool), asmtoad

A small example of redirecting data files under asm (using the toad tool), asmtoad

Original works, from the blog of "Deep Blue,Deep Blue blog: http://blog.csdn.net/huangyanlong/article/details/46863991

 

Scenario: in the production environment, you can use the add data file method to scale up an index tablespace. After a period of time, you will find that the naming rules are incorrect.

Solution: Modify the data file name.

Method: use toad or sqlplus

Operation:

This attempt uses the toad management tool to replace the previous instructions. This record provides a wider experience of using tools. However, in actual work, if the time permits, we recommend that you use the command to complete it.

 

(1) tablespace offline operations

Note:Two tablespaces cannot be taken offline! System tablespace and undo tablespace.

The toad graphic interface is as follows:

Or run the following command:

First, query the table space information.

SQL> select t2.file #, t1.name, t2.name from v $ tablespace t1, v $ datafile t2 where t1.ts # = t2.ts #;

Use the offline command to take the specified tablespace offline.

SQL> alter tablespace users offline;

Tablespace altered.

 

(2) physically copy the data file to the new path and rename it

In ASM, check what file operation commands are available:

Use help.

ASMCMD> help

asmcmd [-V] [-v <errors|warnings|normal|info|debug>] [--privilege <sysasm|sysdba>] [-p] [command]asmcmd_no_conn_str        Starts asmcmd (if no parameters) or executes the command with given parameters        asmcmd [-V] [-v <errors|warnings|normal|info|debug>] [--privilege <sysasm|sysdba>] [-p] [command]        Specify the connect string after the -c option to use the listener to         connect.  By default, ASMCMD asks for a password in a non-echoing         prompt, unless the password is specified as part of the connect string.        The connect identifier is in the form of "host:port:sid", with the        host and the port being option.  Host defaults to "localhost" and port        to 1521.  If the sid is not specified as a part of the connect         identifier, then it is read from the environment variable ORACLE_SID.        If no connect string is used, then environment variables         ORACLE_HOME and ORACLE_SID determine the instance to which the program         connects, and asmcmd establishes a bequeath connection to the it, in         the same manner as a SQLPLUS / AS SYSASM.  The user must be a member         of the SYSDBA group to exercise this option.        Specifying the -V option prints the asmcmd version number and        exits immediately.        Specifying the -v option prints extra information that can help        advanced users diagnose problems.        Specify the --privilege option to choose the type of connection. There are        only two possibilities: connecting as SYSASM or as SYSDBA.        The default value if this option is unspecified is SYSASM:        Specifying the -p option allows the current directory to be displayed         in the command prompt, like so:        ASMCMD [+DATA/ORCL/CONTROLFILE] >        The parameter command specifies one of the following commands, along         with its parameters.        Type "help [command]" to get help on a specific ASMCMD command.        commands:        --------        md_backup, md_restore        lsattr, setattr        cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias        mkdir, pwd, rm, rmalias        chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount        offline, online, rebal, remap, umount        dsget, dsset, lsop, shutdown, spbackup, spcopy, spget        spmove, spset, startup        chtmpl, lstmpl, mktmpl, rmtmpl        chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr        mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr        volcreate, voldelete, voldisable, volenable, volinfo        volresize, volset, volstat

Execute the copy redirection operation:

ASMCMD> cp +OCR_DATA/xzzyk/tbs_xzxt_zyk_index_02.dbf +OCR_DATA/xzzyk/tbs_xzxt_zyk_kksj_index_02.dbf

Copying + OCR_DATA/xzzyk/tbs_xzxt_zyk_index_02.dbf-> + OCR_DATA/xzzyk/tbs_xzxt_zyk_kksj_index_02.dbf

This step cannot be implemented in toad. If the toad interface also prompts, We need to manually copy the file in the operating system.

(3) Redirect new data files

After manually redirecting an asm file, we can use toad to directly modify the path and file information. As follows:

In the above red box, modify the redirection location.

 

Or instructions:

If you have previously used cp to rename the redirection, for example:

[Oracle @ bjbank] $ cp users01.dbf users_01.dbf

Run the rename Command in sqlplus as follows:

SQL> alter database rename file '/home/u01/app/oracle/oradata/bjbank/users01.dbf' to '/home/u01/app/oracle/oradata/bjbank/users_01.dbf ';

Database altered.

 

(4) online

After modifying the path information of the data file in the previous step, run the execute Command to put the tablespace online.

After online, click close to close, as shown below:

Or use the following command:

Assume that datafile 4 was taken offline.

SQL> alter database datafile 4 online;

Database altered.

You can also run the following command:

SQL> alter tablespace users online;

Tablespace altered.

 

(5) delete old physical files

Manually delete the old data file on asm.

ASMCMD> rm tbs_xzxt_zyk_index_02.dbf

 

Summary:

It is convenient to use the tool, but it cannot be blindly dependent on the tool. We recommend that you use the command line method. This is because we can assume that a tool is unavailable when a database is maintained one day. Moreover, tools are easy to generate inertia.

 

**************************************** * ** Blue growth series ********************************* *******************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

Blue growth note-chasing DBA (1): traveling on the road to Shandong

Blue growth notes-Chase DBA (2): Install! Install! Long-lost memories have aroused my new awareness of DBAs.

Blue growth note-chasing DBA (3): importing and exporting data on antiques becomes a problem

Blue growth note-chasing DBA (4): recalling the sorrow of teenagers, and exploring oracle Installation (10g and 11g in Linux)

Blue growth note-chasing DBA (5): Not talking about technology or business, annoying Application Systems

Blue growth note-chasing DBA (6): doing things and being human: Small technology, great human

Blue growth note-Chase DBA (7): Basic commands, foundation stone

Blue growth notes-chasing DBA (8): repicking SP reports and recalling oracle's STATSPACK Experiment

Blue growth note-chasing DBA (9): Chasing DBA, new planning, new departure

Blue growth note-chasing DBA (10): Flying knife defense, familiarity rather than expertise: Playing with middleware Websphere

Blue growth note-chasing DBA (11): It's easy to go home and wake up.

Blue growth notes-Chase DBA (12): seven days and seven gains of SQL

Blue growth note-chasing DBA (13): Coordinating hardware manufacturers, six stories: what you see as "servers, storage, switches ......"

Blue growth note-chasing DBA (14): An unforgettable "Cloud" end, started hadoop deployment

Blue growth note-chasing DBA (15): Who thinks FTP is "simple" and thinks it is a trigger

Blue growth note-chasing DBA (16): DBA also drank alcohol and was rejected

Blue growth note-Chase DBA (17): whether to share or consume, learn to grow in the Post-IOE Era

**************************************** **************************************** **********************************

 

**************************************** ******************************** *****************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

Football and oracle series (1): 32-way zhoudianbing, overall view of group A Brazil smon process of oracle32 process Alliance

Football and oracle series (2)

Football and oracle series (3): oracle process rankings, the World Cup round is about to fight!

Football and oracle series (4): from Brazil to Germany, think of the different RAC topology comparison! 

Football and oracle series (5): The directX library missing in the voda14 game is similar to the oracle rpm package!

Football and oracle series (6): Asian Cup with database creation-come on, Chinese Team

**************************************** **************************************** **********************************

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.