1- determine the dbid
DBID=4233323468
2- check all the files
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_files_all.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Reports on all data files, online redo log files, and control |
-- | files within the database. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a64 HEADING 'Filename'
COLUMN filesize FORMAT 99,999,999,999 HEADING 'File Size'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 99,999,999,999 HEADING 'Next'
COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max'
BREAK ON report
COMPUTE SUM OF filesize ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'[ ONLINE REDO LOG ]'
, a.member
, b.bytes
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2
Generate this script in case you need to revert back to non-asm file.
set serveroutput on;
declare
cursor df is select file#, name from v$datafile;
begin
dbms_output.put_line('run');
dbms_output.put_line('{');
for dfrec in df loop
dbms_output.put_line('set newname for datafile ' ||
dfrec.file# || ' to ''' || dfrec.name ||''' ;');
end loop;
dbms_output.put_line('restore database;');
dbms_output.put_line('switch all;');
dbms_output.put_line('}');
end;
run
{
set newname for datafile 1 to '/ofs/app/oracle/oradata/testasm/system01.dbf' ;
set newname for datafile 2 to '/ofs/app/oracle/oradata/testasm/undotbs01.dbf' ;
set newname for datafile 3 to '/ofs/app/oracle/oradata/testasm/sysaux01.dbf' ;
set newname for datafile 4 to '/ofs/app/oracle/oradata/testasm/users01.dbf' ;
restore database;
switch all;
}
3- change the target db parameter to point to ASM
Also note that the target database uses an SPFILE on the local file system:
$ORACLE_HOME/dbs/spfileTESTDB.ora
Use the following steps to fully migrate an existing Oracle database from a local file system to ASM:
1. With the target database open, edit the initialization parameter control_files and db_create_file_dest to point to the ASM disk group +TESTDB_DATA1. Also configure db_recovery_file_dest to point to the ASM disk group +FLASH_RECOVERY_AREA:
SQL> ALTER SYSTEM SET control_files='+DGRP1' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET db_create_file_dest='+DGRP1' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='+FLASH_RECOVERY_AREA' SCOPE=spfile;
System altered.
SQL> SHUTDOWN immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1977176 bytes
Variable Size 83891368 bytes
Database Buffers 75497472 bytes
Redo Buffers 6406144 bytes
S
bash-2.05$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 5 14:55:14 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: testasm (not mounted)
RMAN> restore controlfile from '/ofs/app/oracle/oradata/testasm/control01.ctl';
S
Starting restore at 05-DEC-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DGRP1/testasm/controlfile/backup.256.640536949
Finished restore at 05-DEC-07
rman > restore controlfile from '/ofs/app/oracle/oradata/testasm/control01.ctl';
sql> alter database mount;
rman> BACKUP AS COPY DATABASE FORMAT '+DGRP1';
Starting backup at 05-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/ofs/app/oracle/oradata/testasm/system01.dbf
output filename=+DGRP1/testasm/datafile/system.257.640537143 tag=TAG20071205T145902 recid=1 stamp=640537194
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/ofs/app/oracle/oradata/testasm/sysaux01.dbf
output filename=+DGRP1/testasm/datafile/sysaux.258.640537199 tag=TAG20071205T145902 recid=2 stamp=640537226
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/ofs/app/oracle/oradata/testasm/undotbs01.dbf
output filename=+DGRP1/testasm/datafile/undotbs1.259.640537235 tag=TAG20071205T145902 recid=3 stamp=640537238
chann
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DGRP1/testasm/datafile/system.257.640537143"
datafile 2 switched to datafile copy "+DGRP1/testasm/datafile/undotbs1.259.640537235"
datafile 3 switched to datafile copy "+DGRP1/testasm/datafile/sysaux.258.640537199"
datafile 4 switched to datafile copy "+DGRP1/testasm/datafile/users.260.640537243"
RMAN>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 555996 generated at 12/05/2007 14:52:53 needed for thread 1
ORA-00289: suggestion :
/ofs/app/oracle/product/10.2.0.1/dbs/arch1_2_640468119.dbf
ORA-00280: change 555996 for thread 1 is in sequence #2
Specify log: {
auto
ORA-00308: cannot open archived log
'/ofs/app/oracle/product/10.2.0.1/dbs/arch1_2_640468119.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/ofs/app/oracle/product/10.2.0.1/dbs/arch1_2_640468119.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
sql> select tablespace_name, file_name, bytes from dba_temp_files;
alter database tempfile
'/ofs/app/oracle/oradata/testasm/temp01.dbf'
drop including datafiles;
alter tablespace temp add tempfile size 512m
autoextend on next 250m maxsize unlimited;
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
TEMP
+DGRP1/testasm/tempfile/temp.263.640537821
536870912
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
BYTES
----------
3
/ofs/app/oracle/oradata/testasm/redo03.log
52428800
2
/ofs/app/oracle/oradata/testasm/redo02.log
52428800
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
BYTES
----------
1
/ofs/app/oracle/oradata/testasm/redo01.log
52428800
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 50m;
Database altered.
SQL> alter system checkpoint global;
System altered.
alter system switch logfile;
alter database drop logfile group 2;
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 50m;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
put the spfile into asm
SQL> create pfile ='/tmp/inittestasm.ora' from spfile;
File created.
SQL> create spfile='+DGRP1/testasm/spfiletestasm.ora' from pfile='/tmp/inittestasm.ora';
File created.
echo "SPFILE='+DGRP1/testasm/spfiletestasm.ora'" > $ORACLE_HOME/dbs/inittestasm.ora
shutdown immediate;
startup
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
6 1 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/system01.dbf
7 2 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/undotbs01.dbf
8 3 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/sysaux01.dbf
9 4 A 05-DEC-07 555996 05-DEC-07 /ofs/app/oracle/oradata/testasm/users01.dbf
RMAN > DELETE copy of database
No comments:
Post a Comment