12c Migracija baze podataka sa datotečnog sustava na ASM premještanjem datoteka na živo
Wednesday, 18.02.2015 – msuticPostoji mnogo članaka gdje je objašnjeno na koji način migrirati bazu podataka sa datotečnog sustava na ASM. Moglo bi se RMAN-om kopirati datoteke baze podataka na ASM pa početi aktivno koristiti kopirane datoteke, povući datoteke iz sigurnosnih kopija i vratiti na ASM ili kreirati duplikat baze podataka na ASM-u.
Sve je ovo moguće odraditi na Oracle verzijama starijim od 12c.
U ovom postu bi se htio osvrnuti na malo drugačiji pristup – odraditi premještanje datoteka na živo u ASM.
Promjene imena i premještanje živih datoteka je moguće tek od verzije 12c što nam omogućava promjenu ili premještanje datoteka dok je baza otvorena i korisnici pristupaju podacima na datotekama. Time nam je pojednostavljeno upravljanje datotekama i izbjegavanje zastoja u radu baze čak i kad premještamo SYSTEM i UNDO tablespace.
Ovo je 12.1 utična baza podataka sa jednom instancom koju želim migrirati sa datotečnog sustava na ASM:
RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB12C List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 800 SYSTEM YES /u01/app/oracle12/oradata/cdb12c/system01.dbf 2 260 PDB$SEED:SYSTEM NO /u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf 3 740 SYSAUX NO /u01/app/oracle12/oradata/cdb12c/sysaux01.dbf 4 585 PDB$SEED:SYSAUX NO /u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf 5 740 UNDOTBS1 YES /u01/app/oracle12/oradata/cdb12c/undotbs01.dbf 6 5 USERS NO /u01/app/oracle12/oradata/cdb12c/users01.dbf 7 270 PDB:SYSTEM NO /u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf 8 605 PDB:SYSAUX NO /u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf 9 5 PDB:USERS NO /u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf 10 50 PDB:MARKO NO /u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 100 TEMP 100 /u01/app/oracle12/oradata/cdb12c/temp01.dbf 2 62 PDB$SEED:TEMP 32767 /u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf 3 100 PDB:TEMP 100 /u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf
Kreirati skriptu za premještanje datoteka:
$ sqlplus -s / as sysdba set lines 200 set pages 50 set feed off set head off spool /tmp/move_dbfiles.sql select 'ALTER DATABASE MOVE DATAFILE '''||name||''' TO ''+DATA'';' from v$datafile order by con_id; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/system01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/undotbs01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/users01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/sysaux01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf' TO '+DATA'; spool end; exit
Kada bi sada pokrenuli ovu skriptu dobili bi nekoliko grešaka jer nije moguće prebaciti PDB datoteke ako niste u PDB kontejneru. Također PDB baza podataka ne smije biti zatvorena.
Nakon što sam malo izmijenio skriptu ovo je finalna verzija:
$ cat /tmp/move_dbfiles.sql ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/system01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/undotbs01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/users01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/sysaux01.dbf' TO '+DATA'; ALTER SESSION SET CONTAINER=pdb$seed; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf' TO '+DATA'; ALTER SESSION SET CONTAINER=pdb; ALTER DATABASE OPEN; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf' TO '+DATA';
Izvršimo sada skriptu:
$ sqlplus / as sysdba SQL> @/tmp/move_dbfiles.sql Database altered. Database altered. Database altered. Database altered. Session altered. Database altered. Database altered. Session altered. Database altered. Database altered. Database altered. Database altered. Database altered.
Datoteke su premještene u ASM bez ikakvog prekida. Vrlo jednostavno.
Privremeni tablespace-ovi baza se i dalje nalaze na datotečnom sustavu.
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 100 TEMP 100 /u01/app/oracle12/oradata/cdb12c/temp01.dbf 2 62 PDB$SEED:TEMP 32767 /u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf 3 100 PDB:TEMP 100 /u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf
Njih ne mogu prebaciti metodom koja je gore opisana pa ću ovdje upotrijebiti malo drugačiji postupak.
SQL> alter tablespace TEMP add tempfile '+DATA'; Tablespace altered. SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle12/oradata/cdb12c/temp01.dbf'; Tablespace altered. SQL> alter session set container=pdb; Session altered. SQL> alter tablespace TEMP add tempfile '+DATA'; Tablespace altered. SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf'; Tablespace altered.
Zeznut dio je premještanje privremenih datoteka TEMP tablespace-a iz PDB$SEED-a koji se koristi za kreiranje utičnih baza podataka. Postupak sam pronašao na blog postu Jože Senegačnika.
SQL> alter session set container=CDB$ROOT; Session altered. SQL> alter session set "_oracle_script"=TRUE; Session altered. SQL> alter pluggable database pdb$seed close; Pluggable database altered. SQL> alter pluggable database pdb$seed open read write; Pluggable database altered. SQL> alter session set container=pdb$seed; Session altered. SQL> alter tablespace temp add tempfile '+DATA'; Tablespace altered. SQL> alter tablespace temp drop tempfile '/u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf'; Tablespace altered. SQL> alter session set container=CDB$ROOT; Session altered. SQL> alter pluggable database pdb$seed close; Pluggable database altered. SQL> alter pluggable database pdb$seed open read only; Pluggable database altered.
Podatkovne i privremene datoteke su sada migrirane na ASM.
RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB12C List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 800 SYSTEM YES +DATA/CDB12C/DATAFILE/system.259.871936371 2 260 PDB$SEED:SYSTEM NO +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/system.265.871936527 3 740 SYSAUX NO +DATA/CDB12C/DATAFILE/sysaux.263.871936455 4 585 PDB$SEED:SYSAUX NO +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/sysaux.256.871936497 5 740 UNDOTBS1 YES +DATA/CDB12C/DATAFILE/undotbs1.257.871936413 6 5 USERS NO +DATA/CDB12C/DATAFILE/users.258.871936451 7 270 PDB:SYSTEM NO +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/system.269.871936549 8 605 PDB:SYSAUX NO +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/sysaux.266.871936565 9 5 PDB:USERS NO +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/users.270.871936547 10 50 PDB:MARKO NO +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/marko.271.871936543 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 2 100 PDB$SEED:TEMP 32767 +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.264.871937389 4 100 TEMP 32767 +DATA/CDB12C/TEMPFILE/temp.268.871936871 5 100 PDB:TEMP 32767 +DATA/CDB12C/0C38EB3894542836E055000000000001/TEMPFILE/temp.267.871936909 7 100 PDB$SEED:TEMP 32767 +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.262.871937843
Premještanje redo log datoteka na ASM:
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12/oradata/cdb12c/redo01.log
/u01/app/oracle12/oradata/cdb12c/redo02.log
/u01/app/oracle12/oradata/cdb12c/redo03.log
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 '+DATA';
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 '+DATA';
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 '+DATA';
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/CDB12C/ONLINELOG/group_1.261.871938327
+DATA/CDB12C/ONLINELOG/group_2.260.871938351
+DATA/CDB12C/ONLINELOG/group_3.272.871938381
Sve ove radnje su načinjene dok je baza podataka bila podignuta i aktivna.
Sada slijedi dio kada će biti potreban kratak prekid u radu baze – dio kada premještamo kontrolne datoteke na ASM.
$ srvctl stop database -d cdb12c RMAN> startup nomount; Oracle instance started Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 570428144 bytes Database Buffers 260046848 bytes Redo Buffers 5455872 bytes RMAN> restore controlfile to '+DATA' from '/u01/app/oracle12/oradata/cdb12c/control01.ctl'; Starting restore at 17-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: copied control file copy Finished restore at 17-FEB-15 RMAN> restore controlfile to '+FRA' from '/u01/app/oracle12/oradata/cdb12c/control01.ctl'; Starting restore at 17-FEB-15 using channel ORA_DISK_1 channel ORA_DISK_1: copied control file copy Finished restore at 17-FEB-15 ASMCMD [+] > find --type CONTROLFILE +DATA * +DATA/CDB12C/CONTROLFILE/current.273.871940441 ASMCMD [+] > find --type CONTROLFILE +FRA * +FRA/CDB12C/CONTROLFILE/current.256.871940457
SQL> alter system set control_files='+DATA/CDB12C/CONTROLFILE/current.273.871940441','+FRA/CDB12C/CONTROLFILE/current.256.871940457' scope=spfile; System altered. SQL> shutdown abort; ORACLE instance shut down.
Stavljanje parametar datoteke na ASM i startanje baze podatka:
$ srvctl start database -d cdb12c -o mount
RMAN> run
2> {
3> BACKUP AS BACKUPSET SPFILE;
4> RESTORE SPFILE TO "+DATA/CDB12C/spfilecdb12c.ora";
5> }
Starting backup at 17-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-FEB-15
channel ORA_DISK_1: finished piece 1 at 17-FEB-15
piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/backupset/2015_02_17/o1_mf_nnsnf_TAG20150217T214744_bg7b7jkg_.bkp tag=TAG20150217T214744 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-FEB-15
Starting Control File and SPFILE Autobackup at 17-FEB-15
piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-FEB-15
Starting restore at 17-FEB-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/CDB12C/spfilecdb12c.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp tag=TAG20150217T214745
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-15
$ srvctl modify database -db cdb12c -spfile '+DATA/CDB12C/spfilecdb12c.ora';
$ srvctl stop database -db cdb12c
$ srvctl start database -db cdb12c
Posljednje provjere:
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/CDB12C/spfilecdb12c.ora SQL> set lines 200 SQL> set pages 999 SQL> select name from v$datafile union all 2 select name from v$tempfile union all 3 select member from v$logfile union all 4 select name from v$controlfile; NAME ----------------------------------------------------------------------------- +DATA/CDB12C/DATAFILE/system.259.871936371 +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/system.265.871936527 +DATA/CDB12C/DATAFILE/sysaux.263.871936455 +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/sysaux.256.871936497 +DATA/CDB12C/DATAFILE/undotbs1.257.871936413 +DATA/CDB12C/DATAFILE/users.258.871936451 +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/system.269.871936549 +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/sysaux.266.871936565 +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/users.270.871936547 +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/marko.271.871936543 +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.264.871937389 +DATA/CDB12C/TEMPFILE/temp.268.871936871 +DATA/CDB12C/0C38EB3894542836E055000000000001/TEMPFILE/temp.267.871936909 +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.262.871937843 +DATA/CDB12C/ONLINELOG/group_1.261.871938327 +DATA/CDB12C/ONLINELOG/group_2.260.871938351 +DATA/CDB12C/ONLINELOG/group_3.272.871938381 +DATA/CDB12C/CONTROLFILE/current.273.871940441 +FRA/CDB12C/CONTROLFILE/current.256.871940457 19 rows selected.
REFERENCE
http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN012
http://joze-senegacnik.blogspot.com/2015/02/maintaining-tempfile-in-temp-tablespace.html
4 Responses to “12c Migracija baze podataka sa datotečnog sustava na ASM premještanjem datoteka na živo”
Eeeee veoma odlican tekst!! Ustedio si mi dosta vremena 🙂
Trebace mi uskoro, kad pocnem sa migracijom/upgradeom na 12c!
Thumbs up!
By Dejan on Feb 19, 2015
Hvala 🙂
Baš sam se namučio sa prijevodom na hrvatski jezik pa ako primijetiš neku grešku ili bolji prijevod slobodno me ispravi.
Pozdrav,
Marko
By Marko on Feb 19, 2015
Moram priznati da mi je trebalo nekoliko sekundi da shvatim sta znači “utična baza” i “na živo” 🙂
By Dejan on Feb 19, 2015
Pomozi mi ako imaš ideju za bolji prijevod 🙂
Mislim, ja ne znam jel se to tako prevodi inače, ali “utična baza” i “na živo” su mi nekako imali najviše smisla u ovom kontekstu.
Jedina stvar koju sam ostavio neprevedenu je “tablespace” – e to tek ne znam kako bi preveo, a da ljudi shvate o čem se radi.
Uvijek sam otvoren za prijedloge 😉
By Marko on Feb 20, 2015