Конвертация Oracle10g non-ASM database в ASM database (oracle database)
Ключевые слова: oracle , database , (найти похожие документы )
From: Jason Waghorn <j_waghorn@yahoo.com. yahoo.com
Newsgroups: email
Date: Mon, 18 Nov 2004 14:31:37 +0000 (UTC)
Subject: Конвертация Oracle10g non-ASM database в ASM database
Державец Борис <dba477@list.ru. list.ru прислал комментарий к англоязычной статье:
--------------------------------------------------------------
В техническом плане статья просто следует документации по RMAN в версии 10g.
Однако, сама процедура гораздо легче для понимания когда читаешь статью,
чем когда работаешь с документацией.Автор опускает процедуру перемещения
Flash Recovery Area в ASM,либо просто педпочитает оставить область на файловой
системе.Техническиий английский для понимания не сложен.
В отношении ASM в версии Oracle10g, мне известна только англоязычная статья
см. http://www.opennet.me/opennews/art.shtml?num=4589 пункт 2.
--------------------------------------------------------------
Статью также можно найти по адресу http://www.linuxgazette.com/node/view/9598
Converting Oracle10g non-ASM database to ASM
This article is technical exercise following general guidelines
of Oracle 10g Rman's Reference Manual.However,two times I was unable to
reproduce instructions from Reference and had to manage on my own .
The new Recovery Manager features in version 10g providing ability to migrate
database from file system into AMS looks very impressive.
Make sure ASM instance is up. Suppose also ASM volume group +DATA1
has been already created. Create database "convdata" with "dbca" in /u02/oradata.
Copy corresponding init.ora file to $ORACLE_HOME/dbs.
$cp /u01/app/oracle/admin/convdata/pfile/init.ora.929200412032 \
> $ORACLE_HOME/dbs/initconvdata.ora
$cd $ORACLE_HOME/dbs
Add following entries to initconvdata.ora :-
CONTROL_FILES='+DATA1/controlf01'
DB_CREATE_FILE_DEST='+DATA1'
DB_CREATE_ONLINE_LOG_DEST_1='+DATA1'
$ mv spfileconvdata.ora spfileconvdata.ora.orig
$ export ORACLE_SID=convdata
$ $ORACLE_HOME/bin/rman
RMAN> connect target
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 188743680 bytes
Fixed Size 778036 bytes
Variable Size 162537676 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
RMAN> restore controlfile from '/u02/oradata/convdata/control01.ctl';
Starting restore at 02-NOV-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=270 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
output filename=+DATA1/controlf01
output filename=+DATA1/controlf02
output filename=+DATA1/controlf03
Finished restore at 02-NOV-04
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA1';
Starting backup at 02-NOV-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=270 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u02/oradata/convdata/system01.dbf
output filename=+DATA1/convdata/datafile/system.317.1 tag=TAG20041102T133452
recid=2 stamp=541172167
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u02/oradata/convdata/sysaux01.dbf
output filename=+DATA1/convdata/datafile/sysaux.318.1 tag=TAG20041102T133452
recid=3 stamp=541172205
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u02/oradata/convdata/example01.dbf
output filename=+DATA1/convdata/datafile/example.319.1 tag=TAG20041102T133452
recid=4 stamp=541172249
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:37
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u02/oradata/convdata/undotbs01.dbf
output filename=+DATA1/convdata/datafile/undotbs1.320.1 tag=TAG20041102T133452
recid=5 stamp=541172262
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u02/oradata/convdata/users01.dbf
output filename=+DATA1/convdata/datafile/users.321.1 tag=TAG20041102T133452
recid=6 stamp=541172272
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
copying current controlfile
output filename=+DATA1/convdata/controlfile/backup.322.1
tag=TAG20041102T133452 recid=7 stamp=541172279
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 02-NOV-04
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA1/convdata/datafile/system.317.1"
datafile 2 switched to datafile copy "+DATA1/convdata/datafile/undotbs1.320.1"
datafile 3 switched to datafile copy "+DATA1/convdata/datafile/sysaux.318.1"
datafile 4 switched to datafile copy "+DATA1/convdata/datafile/users.321.1"
datafile 5 switched to datafile copy "+DATA1/convdata/datafile/example.319.1"
SQL> create temporary tablespace temptbl;
Tablespace created.
SQL> alter database convdata default temporary tablespace temptbl;
Database altered.
SQL> drop tablespace temp including contents;
Tablespace dropped.
RMAN> alter database open ;
database opened
RMAN> delete copy of database;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=270 devtype=DISK
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
8 1 A 02-NOV-04 392632 02-NOV-04 /u02/oradata/convdata/system01.dbf
9 2 A 02-NOV-04 392632 02-NOV-04 /u02/oradata/convdata/undotbs01.dbf
10 3 A 02-NOV-04 392632 02-NOV-04 /u02/oradata/convdata/sysaux01.dbf
11 4 A 02-NOV-04 392632 02-NOV-04 /u02/oradata/convdata/users01.dbf
12 5 A 02-NOV-04 392632 02-NOV-04 /u02/oradata/convdata/example01.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/u02/oradata/convdata/system01.dbf recid=8 stamp=541172332
deleted datafile copy
datafile copy filename=/u02/oradata/convdata/undotbs01.dbf recid=9 stamp=541172332
deleted datafile copy
datafile copy filename=/u02/oradata/convdata/sysaux01.dbf recid=10 stamp=541172332
deleted datafile copy
datafile copy filename=/u02/oradata/convdata/users01.dbf recid=11 stamp=541172333
deleted datafile copy
datafile copy filename=/u02/oradata/convdata/example01.dbf recid=12 stamp=541172333
Deleted 5 object
Creating new TEMP tablespace
SQL> create temporary tablespace temptbl;
Tablespace created.
SQL> alter database convdata default temporary tablespace temptbl;
Database altered.
SQL> drop tablespace temp including contents;
Tablespace dropped.
Moving Redo Logs to ASM
SQL> select lf.member, l.bytes
from v$log l, v$logfile lf
where l.group# = lf.group#
and lf.type = 'ONLINE'
/
MEMBER BYTES
-------- -------
/u02/oradata/convdata/redo03.log 10485760
/u02/oradata/convdata/redo02.log 10485760
/u02/oradata/convdata/redo01.log 10485760
SQL> alter database add logfile size 10485760;
Database altered.
SQL> alter database add logfile size 10485760;
Database altered.
SQL> alter database add logfile size 10485760;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select lf.member, l.bytes
from v$log l, v$logfile lf
where l.group# = lf.group#
and lf.type = 'ONLINE'
/
MEMBER BYTES
-------- -------
/u02/oradata/convdata/redo03.log 10485760
/u02/oradata/convdata/redo02.log 10485760
/u02/oradata/convdata/redo01.log 10485760
+DATA1/convdata/onlinelog/group_4.323.1 10485760
+DATA1/convdata/onlinelog/group_5.324.1 10485760
+DATA1/convdata/onlinelog/group_6.325.1 10485760
6 rows selected.
Perform backup of database to be able remove old redo logs
SQL> alter database drop logfile '/u02/oradata/convdata/redo01.log';
Database altered.
SQL> alter database drop logfile '/u02/oradata/convdata/redo02.log';
Database altered.
SQL> alter database drop logfile '/u02/oradata/convdata/redo03.log';
Database altered.
We are done
1 , Державец Борис (? ), 12:09, 20/11/2004 [ответить ]
+ /–
Вместо:-
$cp /u01/app/oracle/admin/convdata/pfile/init.ora.929200412032 \
> $ORACLE_HOME/dbs/initconvdata.ora
$cd $ORACLE_HOME/dbs
Add following entries to initconvdata.ora :-
CONTROL_FILES='+DATA1/controlf01'
DB_CREATE_FILE_DEST='+DATA1'
DB_CREATE_ONLINE_LOG_DEST_1='+DATA1'
$ mv spfileconvdata.ora spfileconvdata.ora.orig
Выполним:-
SQL> create pfile='$ORACLE_HOME/dbs/inittest.ora' from
2 spfile='$ORACLE_HOME/dbs/spfiletest.ora'
3 /
File created.
$cd $ORACLE_HOME/dbs
$vi inittest.ora
Add entries:-
*.CONTROL_FILES='+DATA1/controlf01'
*.DB_CREATE_FILE_DEST='+DATA1'
*.DB_CREATE_ONLINE_LOG_DEST_1='+DATA1'
Save file
$mv spfiletest.ora spfiletest.ora.orig
SQL> shutdown immediate;
SQL> startup nomount
SQL> create spfile='$ORACLE_HOME/dbs/spfiletest.ora' from
2 pfile='$ORACLE_HOME/dbs/inittest.ora'
3 /
File created.
2 , Державец Борис (? ), 12:18, 20/11/2004 [ответить ]
+ /–
Действие JWH:-
Perform backup of database to be able remove old redo logs
Можно избежать если перед ASM конвенртацией активировать "Autoarchiving". В этом случае:-
SQL> alter system switch logfile;
будет приводить к архивированию предыдущего
Redolog файла.