Catatan kerja

Database beberapa permasalahan yang terjadi di tempat kerja

Moving or renaming Datafile, Logfile and Controlfile October 28, 2008

Filed under: Oracle — aderahman @ 4:25 am

Moving Datafile :

Check the datafiles on your database :

SQL > select name from v$datafile;

There are 3 ways to move or rename datafiles :

1. while the Instance is Mounted

When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut down.

Opening the Database

% sqlplus “/ as sysdba”

SQL> shutdown immediate

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf

SQL> startup mount

SQL> alter database rename file ‘/u05/app/oradata/ORA920/indx01.dbf’ to ‘/u06/app/oradata/ORA920/indx01.dbf’;

Do not disconnect after this step. Stay logged in
and proceed to open the database!

SQL> alter database open;

SQL> exit

2. while the Instance is Open

When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is running.

NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain active ROLLBACK segments or TEMPORARY segments.

% sqlplus “/ as sysdba”

SQL> alter tablespace INDX offline;

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf

SQL> alter tablespace INDX
2 rename datafile ‘/u05/app/oradata/ORA920/indx01.dbf’ to ‘/u06/app/oradata/ORA920/indx01.dbf’;

Do not disconnect after this step. Stay logged in
and proceed to bring the tablespace back online!

SQL> alter tablespace INDX online;

SQL> exit

Moving Online Redo Log Files

% sqlplus “/ as sysdba”

SQL> shutdown immediate

SQL> !mv /u06/app/oradata/ORA920/redo_g03a.log /u03/app/oradata/ORA920/redo_g03a.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03b.log /u04/app/oradata/ORA920/redo_g03b.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03c.log /u05/app/oradata/ORA920/redo_g03c.log

SQL> startup mount

SQL> alter database rename file ‘/u06/app/oradata/ORA920/redo_g03a.log’ to ‘/u03/app/oradata/ORA920/redo_g03a.log’;
SQL> alter database rename file ‘/u06/app/oradata/ORA920/redo_g03b.log’ to ‘/u04/app/oradata/ORA920/redo_g03b.log’;
SQL> alter database rename file ‘/u06/app/oradata/ORA920/redo_g03c.log’ to ‘/u05/app/oradata/ORA920/redo_g03c.log’;

Do not disconnect after this step. Stay logged in
and proceed to open the database!

SQL> alter database open;

SQL> exit

Moving Control File

Moving Control Files

The following method can be used to move or rename a control file(s). A summary of the steps involved follows:

Shutdown the Instance
Move the Control File
Edit the init.ora
Startup the Instance

% sqlplus “/ as sysdba”

SQL> shutdown immediate

SQL> !mv /u06/app/oradata/ORA920/control01.ctl /u03/app/oradata/ORA920/control01.ctl
SQL> !mv /u06/app/oradata/ORA920/control02.ctl /u04/app/oradata/ORA920/control02.ctl
SQL> !mv /u06/app/oradata/ORA920/control03.ctl /u05/app/oradata/ORA920/control03.ctl

Within the init.ora file, there will be an entry for the
“control_files” parameter. Edit this entry to reflect the change(s)
made to the physical control file(s) moved in the previous example.


control_files = (/u03/app/oradata/ORA920/control01.ctl,
/u04/app/oradata/ORA920/control02.ctl,
/u05/app/oradata/ORA920/control03.ctl)

SQL> startup open

SQL> exit

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s