Moving the logfile?

[owenyuen]

How to move the logfile members from $ORACLE_BASE/oradata/ to $ORACLE_BASE/oradata/test?

Thanks

 

[alexhu]

create the new logs in the new location (adding them to the database, so you now have twice as many)

switch the logfiles until you are using the new set of logs

drop the set of logs in the old location

Alex

 

[owenyuen]

Sorry Alex:
I am very new in Oracle. Don't know anyone can give me more details for step by step?

Thanks

 

[alexhu]

OK

select * from v$logfile will show you the current logfiles and their locations

GROUP# STATUS MEMBER
---------- ------- ------------------------------
1 /u05/oradata/pkms/log1pkms.ora
2 /u05/oradata/pkms/log2pkms.ora
3 /u05/oradata/pkms/log3pkms.ora
1 /u02/oradata/pkms/log1pkms.ora
2 /u02/oradata/pkms/log2pkms.ora
3 /u02/oradata/pkms/log3pkms.ora


select * from v$log will show you the status of each group (i.e the CURRENT group is what we are looking for)

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 36961 10485760 2 NO CURRENT
19383950 21-MAR-03

2 1 36959 10485760 2 YES INACTIVE
19382184 21-MAR-03

3 1 36960 10485760 2 YES INACTIVE
19383112 21-MAR-03

Every time a log switch occurs (either automatically or by 'alter system switch logfile') the current group will change.

You need to add another group to the database in the new location

This is taken from the docs;

ALTER DATABASE stocks
ADD LOGFILE GROUP 4
('/u01/oradata/db1/log3.log' ,
'/u01/oradata/db1/log3.log') SIZE 50K;

You need to add as many groups as you have now in the database (at least 2 or 3 I would hope!)

Once you have added the new groups you can drop the old groups PROVIDED they are not CURRENT (as in select * from v$log)

ALTER DATABASE stocks DROP LOGFILE GROUP 1;

Use 'alter system switch logfile' to move the current group

You will end up with groups 4,5,6 instead of 1,2,3 - if that bothers you then repeat the procedure until you get the numbers you want

Thats it

Alex

 

[AOLEURO]

Hi

Alex answer are online changes to logfiles. If you can close the database then you can move the files in OS and rename filenames in Oracle, like this:

1. start sqlplus
2. connect system/manager@orcl as sysdba
3. shutdown immediate;
4. exit
5. mv cccccc ddddddddd
OS move files
6. start sqlplus
7. connect system/manager@orcl as sysdba
8. startup mount [pfile=?????????]
Maybe pfile parameter is needed
9. select member from v$logfile;
10. alter database rename file 'cccccc' to 'dddddddddd';
Oracle rename files. Oracle check that files exists in new directory
11. alter database open
12. exit

and you still got your files without loss of backup / recovery before/after changes.

In Alex example you have to be carefull because your backups have redo log files you can't use or you have to be carefull using control- and system files from backup.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !