Dosya sistemindeki veritabanının ASM içerisine taşınması
Bu makalede , dosya sisteminde dosyaları bulunan bir veritabanının ASM içerisine taşınmasını anlatacağım. Öncelikle Grid Infrastructure kurulumunun yapılması gerekiyor. DATA ve FRA isimli iki disk grubu oluşturuyoruz. Sonrasındaki adımlar aşağıdaki gibidir.
İşe veritabanı kapatarak başlıyoruz ve mevcut spfile dan pfile oluşturuyoruz. Pfile içinde yeni parametreler eklemek gerekiyor. Değişiklik sonrasında ASM içinde spfile oluşturmak gerekecek.
[root@oradb ~]# su - oracle [oracle@oradb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 14:24:52 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> create pfile='/tmp/initORCL.ora' from spfile; File created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
/tmp/initORCL.ora dosyasına aşadaki parametreleri ekliyoruz.
*.control_files=(+DATA, +FRA) *.db_recovery_file_dest=+FRA *.db_recovery_file_dest_size=5368709120 *.db_create_file_dest=+DATA *.db_create_online_log_dest_1=+FRA [oracle@oradb ~]$ vi /tmp/initORCL.ora
$ORACLE_HOME/dbs altındaki mevcut spfile ı yedekleyip, değişiklik yaptığımız pfile dosyasını buraya kopyalıyoruz.
[oracle@oradb ~]$ cd $ORACLE_HOME/dbs [oracle@oradb dbs]$ mv spfileORCL.ora spfileORCL.ora.31082015 [oracle@oradb dbs]$ mv initORCL.ora initORCL.ora.31082015 [oracle@oradb dbs]$ mv /tmp/initORCL.ora . [oracle@oradb dbs]$ ls -ltr total 28 -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r-----. 1 oracle oinstall 605 Aug 26 16:15 initORCL.ora.31082015 -rw-r-----. 1 oracle oinstall 24 Aug 26 16:16 lkORCL -rw-r-----. 1 oracle oinstall 1536 Aug 26 17:07 orapwORCL -rw-r-----. 1 oracle oinstall 3584 Aug 30 22:02 spfileORCL.ora.31082015 -rw-rw----. 1 oracle oinstall 1544 Aug 31 14:24 hc_ORCL.dat -rw-r--r-- 1 oracle oinstall 949 Aug 31 14:27 initORCL.ora
ASM içerisine spfile oluşturuyoruz.
[oracle@oradb dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 14:29:11 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created.
Control file restore işlemi için veritabanını nomount modda açıyoruz.
SQL> startup nomount ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2260088 bytes Variable Size 2466251656 bytes Database Buffers 1795162112 bytes Redo Buffers 12107776 bytes SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oradb dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 31 14:30:41 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore controlfile from '/home/app/oracle/oradata/ORCL/control01.ctl'; Starting restore at 31-AUG-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=398 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATA/ORCL/controlfile/current.256.889194645 output file name=+FRA/ORCL/controlfile/current.256.889194645 Finished restore at 31-AUG-15 RMAN> startup mount database is already started database mounted released channel: ORA_DISK_1 RMAN> configure device type disk parallelism 4; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored
Veritabanının +DATA içerisinde bir kopyasını oluşturuyoruz.
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA'; Starting backup at 31-AUG-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=398 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=399 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=14 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=400 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/home/app/oracle/oradata/ORCL/hope_data01.dbf channel ORA_DISK_2: starting datafile copy input datafile file number=00007 name=/home/app/oracle/oradata/ORCL/hope_index01.dbf channel ORA_DISK_3: starting datafile copy input datafile file number=00002 name=/home/app/oracle/oradata/ORCL/sysaux01.dbf channel ORA_DISK_4: starting datafile copy input datafile file number=00001 name=/home/app/oracle/oradata/ORCL/system01.dbf output file name=+DATA/ORCL/datafile/system.260.889194691 tag=TAG20150831T143129 RECID=2 STAMP=889194743 channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_4: starting datafile copy input datafile file number=00003 name=/home/app/oracle/oradata/ORCL/undotbs01.dbf output file name=+DATA/ORCL/datafile/sysaux.259.889194691 tag=TAG20150831T143129 RECID=3 STAMP=889194749 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:02 channel ORA_DISK_3: starting datafile copy input datafile file number=00005 name=/home/app/oracle/oradata/ORCL/example01.dbf output file name=+DATA/ORCL/datafile/example.262.889194753 tag=TAG20150831T143129 RECID=4 STAMP=889194770 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_3: starting datafile copy copying current control file output file name=+DATA/ORCL/controlfile/backup.263.889194777 tag=TAG20150831T143129 RECID=5 STAMP=889194778 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_3: starting datafile copy input datafile file number=00004 name=/home/app/oracle/oradata/ORCL/users01.dbf output file name=+DATA/ORCL/datafile/undotbs1.261.889194745 tag=TAG20150831T143129 RECID=6 STAMP=889194778 channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_4: starting full datafile backup set channel ORA_DISK_4: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_4: starting piece 1 at 31-AUG-15 output file name=+DATA/ORCL/datafile/users.264.889194781 tag=TAG20150831T143129 RECID=7 STAMP=889194781 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:02 channel ORA_DISK_4: finished piece 1 at 31-AUG-15 piece handle=+DATA/ORCL/backupset/2015_08_31/nnsnf0_tag20150831t143129_0.265.889194781 tag=TAG20150831T143129 comment=NONE channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01 output file name=+DATA/ORCL/datafile/hope_data.257.889194689 tag=TAG20150831T143129 RECID=8 STAMP=889194909 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:47 output file name=+DATA/ORCL/datafile/hope_index.258.889194689 tag=TAG20150831T143129 RECID=9 STAMP=889194932 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:04:07 Finished backup at 31-AUG-15
+DATA içerisinde oluşan kopyaya switch ederek , artık veritabanın asıl dosyalarının +DATA içerisindekilerin olmasını sağlıyoruz.
RMAN> SWITCH DATABASE TO COPY; datafile 1 switched to datafile copy "+DATA/ORCL/datafile/system.260.889194691" datafile 2 switched to datafile copy "+DATA/ORCL/datafile/sysaux.259.889194691" datafile 3 switched to datafile copy "+DATA/ORCL/datafile/undotbs1.261.889194745" datafile 4 switched to datafile copy "+DATA/ORCL/datafile/users.264.889194781" datafile 5 switched to datafile copy "+DATA/ORCL/datafile/example.262.889194753" datafile 6 switched to datafile copy "+DATA/ORCL/datafile/hope_data.257.889194689" datafile 7 switched to datafile copy "+DATA/ORCL/datafile/hope_index.258.889194689" RMAN> alter database open; database opened RMAN> exit Recovery Manager complete.
Veritabanı artık ASM içerisinden çalışmaya başlamış, dosya sistemindeki veritabanı dosyalarını da kopya olarak işaretlemiştir. İlerde dosya sistemindeki kopyaları drop ederek tamamen ASM içerisinde çalışmasını sağlayacağız.
Switch sonrasonda işlemlere başyalabiliriz. Öncelikle ASM içerisinde temp tablespace oluşturup , mevcut temp tablespace’i drop edeceğiz.
[oracle@oradb dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 14:36:20 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select name, bytes from v$tempfile; NAME -------------------------------------------------------------------------------- BYTES ---------- /home/app/oracle/oradata/ORCL/temp01.dbf 66060288 SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M; Tablespace created. SQL> alter database default temporary tablespace temp1; Database altered. SQL> drop tablespace temp including contents; Tablespace dropped. SQL> create temporary tablespace temp tempfile SIZE 1G extent management local uniform size 10M; Tablespace created. SQL> alter database default temporary tablespace temp; Database altered. SQL> drop tablespace temp1 including contents; Tablespace dropped.
Redolog işlemlerini tamamlıyoruz.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 2466251656 bytes
Database Buffers 1795162112 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/ORCL/controlfile/curr
ent.256.889194645, +FRA/dcltes
t/controlfile/current.256.8891
94645
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 5192M
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/app/oracle/oradata/ORCL/redo03.log
/home/app/oracle/oradata/ORCL/redo02.log
/home/app/oracle/oradata/ORCL/redo01.log
SQL> alter database add logfile thread 1 group 21 ('+DATA') size 100M;
Database altered.
SQL> alter database add logfile thread 1 group 22 ('+DATA') size 100M;
Database altered.
SQL> alter database add logfile thread 1 group 23 ('+DATA') size 100M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
Log dosyasını drop ederken aşağıdaki gibi bir hata alırsanız , redo log switch ve checkpoint işlemleriyle bu durumu düzeltebilirsiniz.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance ORCL (thread 1)
ORA-00312: online log 2 thread 1: '/home/app/oracle/oradata/ORCL/redo02.log'
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile thread 1 group 1 ('+DATA') size 100M;
Database altered.
SQL> alter database add logfile thread 1 group 2 ('+DATA') size 100M;
Database altered.
SQL> alter database add logfile thread 1 group 3 ('+DATA') size 100M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 21;
Database altered.
SQL> alter database drop logfile group 22;
Database altered.
SQL> alter database drop logfile group 23;
Database altered.
SQL> alter database add logfile member '+FRA' to group 1;
Database altered.
SQL> alter database add logfile member '+FRA' to group 2;
Database altered.
SQL> alter database add logfile member '+FRA' to group 3;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Son olarak dosya sistemindeki kopyayı siliyoruz.
[oracle@oradb dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 31 15:00:27 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=2455775002) RMAN> DELETE COPY OF DATABASE; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=410 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=418 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=18 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- --------------- 10 1 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/system01.dbf 11 2 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/sysaux01.dbf 12 3 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/undotbs01.dbf 13 4 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/users01.dbf 14 5 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/example01.dbf 15 6 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/hope_data01.dbf 16 7 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/hope_index01.dbf Do you really want to delete the above objects (enter YES or NO)? YES deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/system01.dbf RECID=10 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/sysaux01.dbf RECID=11 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/undotbs01.dbf RECID=12 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/users01.dbf RECID=13 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/example01.dbf RECID=14 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/hope_data01.dbf RECID=15 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/hope_index01.dbf RECID=16 STAMP=889194941 Deleted 7 objects RMAN> exit Recovery Manager complete.
Bundan sonraki işlemler taşıma ile ilgili olmayıp, mevcut veritabanımızın otomatik açılması , pfile dan doğru spfile’ı görmesi için gereklidir.
# spfile pfile işlemleri :
Spfile artık +DATA disk grubu içinde olduğundan dbs altındaki initORCL.ora (pfile) dosyamızın da bu spfile’ı göstermesi gerekiyor.
$ORACLE_HOME/dbs dizini altındaki initORCL.ora dosyasının içeriğini aşağıdaki gibi değiştiriyoruz. Bu sayede bir sonraki restart işleminde spfile olarak +DATA disk grubundakini görecek ve oradan açacak.
SPFILE='+DATA/ORCL/spfileORCL.ora'
# Cluster’a Database bilgilerinin eklenmesi işlemleri :
Veritabanımızın cluster’a dahil edilmesi için aşağıdaki işlemler gerekmektedir. Öncelikle veritabanımızı ekliyoruz.
[oracle@oradb dbs]$ srvctl add database -d ORCL -n oradb -s open -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/ORCL/spfileORCL.ora -a DATA,FRA [oracle@oradb dbs]$ srvctl status database -d ORCL Database is not running. [oracle@oradb dbs]$ srvctl stop database -d ORCL PRCC-1016 : ORCL was already stopped [oracle@oradb dbs]$ exit logout [root@oradb /]# su - oracle [oracle@oradb ~]$ ps -ef|grep pmon oracle 3166 1 0 Aug27 ? 00:00:24 asm_pmon_+ASM oracle 15840 1 0 15:32 ? 00:00:00 ora_pmon_ORCL oracle 16827 16801 0 15:34 pts/0 00:00:00 grep pmon [oracle@oradb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 15:34:44 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@oradb ~]$ srvctl start database -d ORCL [oracle@oradb ~]$ srvctl status database -d ORCL Database is running. [oracle@oradb ~]$ . oraenv ORACLE_SID = [ORCL] ? +ASM The Oracle base remains unchanged with value /u01/app/oracle [oracle@oradb ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.DATA.dg ora....up.type ONLINE ONLINE oradb ora.FRA.dg ora....up.type ONLINE ONLINE oradb ora....ER.lsnr ora....er.type ONLINE ONLINE oradb ora.asm ora.asm.type ONLINE ONLINE oradb ora.cssd ora.cssd.type ONLINE ONLINE oradb ora.ORCL.db ora....se.type ONLINE ONLINE oradb ora.diskmon ora....on.type OFFLINE OFFLINE ora.evmd ora.evm.type ONLINE ONLINE oradb ora.ons ora.ons.type OFFLINE OFFLINE
Artık veritabanımız tamamen ASM içerisinde çalışmaya başlamıştır.
