Beiträge getaggt mit High Availability
Clone Database from Snapshot Standby Database
In 11g, we introduced the new feature Snapshot Standby Database for Data Guard. In my current Data Guard course, one of the attendees asked, whether it is possible to create a Database Clone out of such a Snapshot Standby Database. Maybe you want to preserve the changes that have been done during the time, your Physical Standby Database was turned into a READ-WRITE opened (Test-)Database. I answered that this is mot likely possible, but I did not try yet. Now I did 🙂
DGMGRL> convert database physt to snapshot standby; Converting database "physt" to a Snapshot Standby database, please wait... Database "physt" converted successfully
There is my Snapshot Standby Database. You may reproduce the Data Guard Configuration in place here with the Whitepaper 11g Data Guard on the Command Line from my Downloads page.
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
I continue to prepare a Clone Database by creating an spfile and a password file for it (very similar as described in the Whitepaper for the physt Database). Then I start it into NOMOUNT:
uhesse $ sqlplus sys/oracle@clon as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:00:26 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 209718360 bytes
Database Buffers 96468992 bytes
Redo Buffers 6336512 bytes
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
The version in use is 11gR2, but it should work with 11gR1 the same way. Now RMAN is used to create the Clone from the Snapshot Standby:
RMAN> connect target sys/oracle@physt connected to target database: PRIMA (DBID=1970640575) RMAN> connect auxiliary sys/oracle@clon connected to auxiliary database: CLON (not mounted) RMAN> duplicate target database to clon from active database; Starting Duplicate Db at 21-DEC-10 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=96 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/21/2010 17:03:19 RMAN-05541: no archived logs found in target database RMAN> exit
Oops! I need Archivelogs on the Snapshot Standby. Note that they are in the same directory but with a different Resetlogs Identifier as the Archivelogs created when the Database was a Physical Standby Database.
uhesse $ sqlplus sys/oracle@physt as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 21 17:03:49 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/home/oracle/physt/archive/1_12_738347903.dbf
/home/oracle/physt/archive/1_8_738347903.dbf
/home/oracle/physt/archive/1_7_738347903.dbf
/home/oracle/physt/archive/1_9_738347903.dbf
/home/oracle/physt/archive/1_11_738347903.dbf
/home/oracle/physt/archive/1_10_738347903.dbf
/home/oracle/physt/archive/1_13_738347903.dbf
/home/oracle/physt/archive/1_14_738347903.dbf
/home/oracle/physt/archive/1_15_738347903.dbf
/home/oracle/physt/archive/1_1_738349098.dbf
10 rows selected.
Again we go with the duplicate:
RMAN> connect target sys/oracle@physt
connected to target database: PRIMA (DBID=1970640575)
RMAN> connect auxiliary sys/oracle@clon
connected to auxiliary database: CLON (not mounted)
RMAN> duplicate target database to clon from active database;
Starting Duplicate Db at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PRIMA'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''CLON'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/home/oracle/clon/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PRIMA'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''CLON'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 209718360 bytes
Database Buffers 96468992 bytes
Redo Buffers 6336512 bytes
Starting backup at 21-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_physt.f tag=TAG20101221T172429 RECID=6 STAMP=738350670
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-DEC-10
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/home/oracle/clon/system01.dbf";
set newname for datafile 2 to
"/home/oracle/clon/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/clon/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/clon/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/clon/system01.dbf" datafile
2 auxiliary format
"/home/oracle/clon/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/clon/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/clon/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 21-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/physt/system01.dbf
output file name=/home/oracle/clon/system01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/physt/sysaux01.dbf
output file name=/home/oracle/clon/sysaux01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/physt/undotbs01.dbf
output file name=/home/oracle/clon/undotbs01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/physt/users01.dbf
output file name=/home/oracle/clon/users01.dbf tag=TAG20101221T172436
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 21-DEC-10
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/home/oracle/physt/archive/1_3_738349098.dbf" auxiliary format
"/home/oracle/clon/archive/1_3_738349098.dbf" ;
catalog clone archivelog "/home/oracle/clon/archive/1_3_738349098.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 21-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=3 RECID=12 STAMP=738350751
output file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15
Finished backup at 21-DEC-10
cataloged archived log
archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf RECID=12 STAMP=738350766
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=738350766 file name=/home/oracle/clon/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=738350767 file name=/home/oracle/clon/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=738350767 file name=/home/oracle/clon/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=738350767 file name=/home/oracle/clon/users01.dbf
contents of Memory Script:
{
set until scn 226204;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 21-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK
starting media recovery
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/clon/archive/1_3_738349098.dbf
archived log file name=/home/oracle/clon/archive/1_3_738349098.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-DEC-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''CLON'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 209718360 bytes
Database Buffers 96468992 bytes
Redo Buffers 6336512 bytes
sql statement: alter system set db_name = ''CLON'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 209718360 bytes
Database Buffers 96468992 bytes
Redo Buffers 6336512 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLON" RESETLOGS ARCHIVELOG
MAXLOGFILES 26
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/home/oracle/clon/log_g1m1.rdo' ) SIZE 100 M REUSE,
GROUP 2 ( '/home/oracle/clon/log_g2m1.rdo' ) SIZE 100 M REUSE
DATAFILE
'/home/oracle/clon/system01.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
"/home/oracle/clon/temp01.dbt";
switch clone tempfile all;
catalog clone datafilecopy "/home/oracle/clon/sysaux01.dbf",
"/home/oracle/clon/undotbs01.dbf",
"/home/oracle/clon/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/clon/temp01.dbt in control file
cataloged datafile copy
datafile copy file name=/home/oracle/clon/sysaux01.dbf RECID=1 STAMP=738350787
cataloged datafile copy
datafile copy file name=/home/oracle/clon/undotbs01.dbf RECID=2 STAMP=738350787
cataloged datafile copy
datafile copy file name=/home/oracle/clon/users01.dbf RECID=3 STAMP=738350787
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=738350787 file name=/home/oracle/clon/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=738350787 file name=/home/oracle/clon/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=738350787 file name=/home/oracle/clon/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 21-DEC-10
So this was pretty much the same as with an ordinary Clone from a Production Database – as expected. Now we turn the Snapshot Standby back into Physical Standby, thereby losing all the changes that might be done there by using Flashback Database internally. But the Clone Database will remain, now being an independent Database with another Name and DBID than the Primary resp. Snapshot Standby.
DGMGRL> convert database physt to physical standby;
Converting database "physt" to a Physical Standby database, please wait...
Operation requires shutdown of instance "physt" on database "physt"
Shutting down instance "physt"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Continuing to convert database "physt" ...
Operation requires shutdown of instance "physt" on database "physt"
Shutting down instance "physt"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Database "physt" converted successfully
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
SQL> connect sys/oracle@clon as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;
NAME DBID DATABASE_ROLE
--------- ---------- ----------------
CLON 2202079427 PRIMARY
SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;
NAME DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA 1970640575 PHYSICAL STANDBY
SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select name,dbid,database_role from v$database;
NAME DBID DATABASE_ROLE
--------- ---------- ----------------
PRIMA 1970640575 PRIMARY
Conclusion: With 11g, we can very comfortably transform our Physical Standby into a fully changeable Snapshot Standby, clone that modified Database into an autonomous Database to keep the changes and afterwards transform the Snapshot Standby back into Physical Standby. During the whole action, Redo Protocol from the Primary Database can still be received – but not applied – on the Standby Site.
Database Migration to ASM with short downtime
See how to migrate to ASM with the downtime it takes only to shutdown and restart the instance – in other words, the downtime will be in the range of only minutes on a production system. The example is done on a small Linux server using Oracle Database Enterprise Edition 11.2.0.2, but should work very similar on other platforms and with the 10g version as well.
SQL> select * from v$version; BANNER ---------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> select name from v$datafile union select name from v$tempfile; NAME --------------------------------- /home/oracle/prima/sysaux01.dbf /home/oracle/prima/system01.dbf /home/oracle/prima/temp01.dbt /home/oracle/prima/undotbs01.dbf /home/oracle/prima/users01.dbf SQL> select name from v$controlfile; NAME -------------------------------- /home/oracle/prima/control01.ctl SQL> select member from v$logfile; MEMBER ------------------------------- /home/oracle/prima/log_g1m1.rdo /home/oracle/prima/log_g2m1.rdo
This is my standard demo Database. I have installed already Grid Infrastructure (Marketing name for the combination of Oracle Restart & ASM) for a standalone server. Also, I have already prepared 16 fake ‚Raw Devices‘, each of 250m in size. Yes, my system is tiny – it’s my notebook. Continuing to start the ASM instance and then create the two recommended diskgroups DATA and FRA. We have the option to use the comfortable GUI asmca (11g New Feature, it can also create quorum failgroups meanwhile) or go with the command line:
[oracle@uhesse-pc ~]$ cat /u01/app/11.2.0/grid/dbs/init+ASM.ora #init+ASM.ora instance_type='asm' asm_diskstring='/dev/raw/raw*' remote_login_passwordfile='EXCLUSIVE' diagnostic_dest='/u01/app/oracle/' asm_diskgroups=data,fra
After connecting to the ASM instance and startup , which will produce an error message, because data and fra do not exist yet, we create them like this:
SQL> CREATE DISKGROUP data NORMAL REDUNDANCY FAILGROUP fg1 DISK '/dev/raw/raw1' NAME disk01, '/dev/raw/raw2' NAME disk02, '/dev/raw/raw3' NAME disk03, '/dev/raw/raw4' NAME disk04 FAILGROUP fg2 DISK '/dev/raw/raw5' NAME disk05, '/dev/raw/raw6' NAME disk06, '/dev/raw/raw7' NAME disk07, '/dev/raw/raw8' NAME disk08;
Each file, placed on DATA will be mirrored across the fg1 and fg2 on the stripe layer. All drives in fg1 or in fg2 could fail without losing data.
SQL> CREATE DISKGROUP fra EXTERNAL REDUNDANCY DISK '/dev/raw/raw9' NAME disk09, '/dev/raw/raw10' NAME disk10, '/dev/raw/raw11' NAME disk11, '/dev/raw/raw12' NAME disk12, '/dev/raw/raw13' NAME disk13, '/dev/raw/raw14' NAME disk14, '/dev/raw/raw15' NAME disk15, '/dev/raw/raw16' NAME disk16;
FRA has no redundancy – should one drive fail we would lose all data on FRA. Next is an Online Backup with Image Copies to the DATA diskgroup. No downtime involved.
RMAN> backup as copy database format '+DATA'; Starting backup at 01-DEC-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/home/oracle/prima/system01.dbf output file name=+DATA/prima/datafile/system.256.736599607 tag=TAG20101201T110002 RECID=1 STAMP=736599624 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/home/oracle/prima/sysaux01.dbf output file name=+DATA/prima/datafile/sysaux.257.736599629 tag=TAG20101201T110002 RECID=2 STAMP=736599634 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/home/oracle/prima/undotbs01.dbf output file name=+DATA/prima/datafile/undotbs1.258.736599641 tag=TAG20101201T110002 RECID=3 STAMP=736599646 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/home/oracle/prima/users01.dbf output file name=+DATA/prima/datafile/users.259.736599655 tag=TAG20101201T110002 RECID=4 STAMP=736599655 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy copying current control file output file name=+DATA/prima/controlfile/backup.260.736599657 tag=TAG20101201T110002 RECID=5 STAMP=736599662 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 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 01-DEC-10 channel ORA_DISK_1: finished piece 1 at 01-DEC-10 piece handle=+DATA/prima/backupset/2010_12_01/nnsnf0_tag20101201t110002_0.261.736599665 tag=TAG20101201T110002 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 01-DEC-10
Now we change some dynamic parameters to point to the new Database resp. Recovery Area:
SQL> alter system set db_recovery_file_dest_size=1800m; System altered. SQL> alter system set db_recovery_file_dest='+FRA'; System altered. SQL> alter system set db_create_file_dest='+DATA'; System altered.
Archivelogs will now be created into FRA. We also put our spfile there. The ‚from memory‘ clause is an 11g New Feature.
SQL> create spfile='+DATA/spfileprima.ora' from memory; File created.
We remove our spfile from $ORACLE_HOME/dbs and replace it with a pointer to the new spfile:
SQL> host cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initprima.ora spfile='+DATA/spfileprima.ora'
Attention: Now we need to shutdown and restart the productive instance and have a short downtime:
SQL> startup force nomount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2227072 bytes Variable Size 352322688 bytes Database Buffers 54525952 bytes Redo Buffers 8470528 bytes
The controlfiles should also be (mirrored) on ASM. Therefore:
SQL> alter system set control_files='+DATA','+FRA' scope=spfile; System altered.
Restart to make the modified CONTROL_FILES parameter active:
SQL> startup force nomount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2227072 bytes Variable Size 352322688 bytes Database Buffers 54525952 bytes Redo Buffers 8470528 bytes
We only need to restore controlfiles to the new location, switch to the new datafiles on DATA and recover the latest changes that where done since the online backup:
[oracle@uhesse-pc ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 1 11:13:33 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMA (not mounted) RMAN> restore controlfile from '/home/oracle/prima/control01.ctl'; Starting restore at 01-DEC-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=99 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATA/prima/controlfile/current.263.736600443 output file name=+FRA/prima/controlfile/current.256.736600443 Finished restore at 01-DEC-10 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/prima/datafile/system.256.736599607" datafile 2 switched to datafile copy "+DATA/prima/datafile/sysaux.257.736599629" datafile 3 switched to datafile copy "+DATA/prima/datafile/undotbs1.258.736599641" datafile 4 switched to datafile copy "+DATA/prima/datafile/users.259.736599655" RMAN> recover database; Starting recover at 01-DEC-10 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=99 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 01-DEC-10 RMAN> alter database open;
Downtime is over! End users can connect while we do some additional work:
SQL> select name from v$datafile union select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/prima/datafile/sysaux.257.736599629
+DATA/prima/datafile/system.256.736599607
+DATA/prima/datafile/undotbs1.258.736599641
+DATA/prima/datafile/users.259.736599655
/home/oracle/prima/temp01.dbt
The tempfile was not touched by RMAN during the backup or switch to copy. We need to do that manually:
SQL> alter database tempfile '/home/oracle/prima/temp01.dbt' drop;
Database altered.
SQL> alter tablespace temp add tempfile size 50m;
Tablespace altered.
SQL> select name from v$datafile union select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/prima/datafile/sysaux.257.736599629
+DATA/prima/datafile/system.256.736599607
+DATA/prima/datafile/undotbs1.258.736599641
+DATA/prima/datafile/users.259.736599655
+DATA/prima/tempfile/temp.264.736600915
Everything nice. But our Online Logs are still on the filesystem:
SQL> select member from v$logfile; MEMBER ----------------------------------- /home/oracle/prima/log_g1m1.rdo /home/oracle/prima/log_g2m1.rdo
This can also be fixed online:
SQL> alter database add logfile size 20m; Database altered. SQL> alter database add logfile size 20m; Database altered.
Gave us two new groups mirrored across DATA and FRA. Now we drop the old groups:
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 1; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- +DATA/prima/onlinelog/group_3.264.736612757 +FRA/prima/onlinelog/group_3.257.736612759 +DATA/prima/onlinelog/group_4.265.736612765 +FRA/prima/onlinelog/group_4.258.736612769
That was it. We may now do a backup of the database to FRA:
RMAN> backup database; Starting backup at 01-DEC-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=105 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/prima/datafile/system.256.736608851 input datafile file number=00002 name=+DATA/prima/datafile/sysaux.257.736608873 input datafile file number=00003 name=+DATA/prima/datafile/undotbs1.258.736608885 input datafile file number=00004 name=+DATA/prima/datafile/users.259.736608899 channel ORA_DISK_1: starting piece 1 at 01-DEC-10 channel ORA_DISK_1: finished piece 1 at 01-DEC-10 piece handle=+FRA/prima/backupset/2010_12_01/nnndf0_tag20101201t145715_0.259.736613837 tag=TAG20101201T145715 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 01-DEC-10 channel ORA_DISK_1: finished piece 1 at 01-DEC-10 piece handle=+FRA/prima/backupset/2010_12_01/ncsnf0_tag20101201t145715_0.260.736613853 tag=TAG20101201T145715 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 01-DEC-10
Conclusion: It is relatively simple and only needs a short downtime to migrate your Database to ASM, mainly the process is an RMAN Online Backup with Image Copies.
Addendum: See here for a recent example by Pete Sharman about using Enterprise Manager 12c to do an ASM Migration.
Real-Time Query and Automatic Block Media Recovery in 11gR2
Yesterday, I posted about dealing with Block Corruption in general. Special case is Block Corruption in a Data Guard Environment, where we introduced the New Feature Automatic Block Media Recovery (ABMR). Yes, another cool abbrevation from the the Oracle Realm 🙂
To demonstrate this, I have created a Data Guard Configuration as described in my whitepaper 11g Data Guard on the command line, that you may get from the Downloads page. It’s still 11gR1 but can easily be adopted for 11gR2. Will publish an 11gR2 version of it there soon. My scenario looks like this:
SYS@prima > select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production DGMGRL> show configuration; Configuration - myconf Protection Mode: MaxAvailability Databases: prima - Primary database physt - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database physt; Database - physt Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds Apply Lag: 0 seconds Real Time Query: ON Instance(s): physt Database Status: SUCCESS
The first 11g New Feature related to Block Corruption in a Data Guard Environment is less striking than ABMR and similar as restoring blocks from Flashback Logs: If we have a Physical Standby Database present, the corrupted blocks can be restored from there very fast. I corrupted the emp table of scott in the same way as in the previous posting. After using RMAN to validate as shown also already, I do the Blockrecovery
RMAN> blockrecover corruption list;
Starting recover at 17-NOV-10
using channel ORA_DISK_1
finished standby search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-NOV-10
I need to emphasize that this restore of blocks from the Physical Standby does not require Real-Time Query there – so we don’t need to purchase Active Data Guard for that. But ABMR does. First ABMR scenario: Block Corruption happens on the Primary Database. I do the same steps as shown in the previous posting to corrupt the block containing the emp table of scott. Now scott connects and selects on the emp table. After a short (about 1 sec) break, the select produces the correct result set! The alert log file of the Primary Database records the following:
Wed Nov 17 08:54:29 2010 Hex dump of (file 4, block 131) in trace file /home/oracle/prima/diag/rdbms/prima/prima/trace/prima_ora_5169.trc Corrupt block relative dba: 0x01000083 (file 4, block 131) Bad header found during buffer read Data in bad block: type: 0 format: 2 rdba: 0xffc00000 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000000 check value in block header: 0xff66 block checksum disabled Reading datafile '/home/oracle/prima/users01.dbf' for corruption at rdba: 0x01000083 (file 4, block 131) Reread (file 4, block 131) found same corrupt data (no logical check) Starting background process ABMR Wed Nov 17 08:54:29 2010 ABMR started with pid=33, OS id=5174 Automatic block media recovery service is active. Automatic block media recovery requested for (file# 4, block# 131) Wed Nov 17 08:54:30 2010 Automatic block media recovery successful for (file# 4, block# 131) Automatic block media recovery successful for (file# 4, block# 131) WARNING: AutoBMR fixed mismatched on-disk single block ffc00000 with in-mem rdba 1000083.
An end user will probably not even notice the Block Corruption on the Primary Database. Second ABMR scenario: Block Corruption happens on the Physical Standby Database. I destroy the block of the emp table in the same way as on the Primary before. Scott now connects on the Physical Standby and does a select on the emp table. Again a short break – no error message but the correct result set is returned! Alert Log of the Physical Standby records:
Wed Nov 17 08:58:10 2010 Hex dump of (file 4, block 131) in trace file /home/oracle/physt/diag/rdbms/physt/physt/trace/physt_ora_5281.trc Corrupt block relative dba: 0x01000083 (file 4, block 131) Bad header found during buffer read Data in bad block: type: 0 format: 2 rdba: 0xffc00000 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000000 check value in block header: 0xff66 block checksum disabled Reading datafile '/home/oracle/physt/users01.dbf' for corruption at rdba: 0x01000083 (file 4, block 131) Reread (file 4, block 131) found same corrupt data (no logical check) Automatic block media recovery requested for (file# 4, block# 131) Wed Nov 17 08:58:11 2010 Automatic block media recovery requested for (file# 4, block# 131) Automatic block media recovery successful for (file# 4, block# 131) Automatic block media recovery successful for (file# 4, block# 131)
Conclusion: We have just seen another benefit of Real-Time Query. Apart from it’s main purpose of making it possible to offload Read-Only (or even Read-Mostly) Applications to the Physical Standby Database, it is now in 11gR2 also leveraged to resolve Block Corruption fast and transparently. We call this 11gR2 New Feature Automatic Block Media Recovery (ABMR)
Pickleball spielen auf YouTube
Pickleball spielen 