Say I have created a new tablespace recently and did not yet take a backup of the datafile. Now I lose that datafile. Dilemma? No, because I can do an ALTER DATABASE CREATE DATAFILE. Sounds complex? Well even if I wouldn’t be aware of that possibility, a simple RMAN restore will work – as if there were a backup:
RMAN> create table adam.nu tablespace tbs1 as select * from adam.sales where rownum<=10000; Statement processed RMAN> alter system switch logfile; Statement processed RMAN> host 'echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf'; host command complete RMAN> select count(*) from adam.nu; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 10/20/2015 11:50:12 ORA-01115: IO error reading block from file (block # ) ORA-01110: data file 5: '/u01/app/oracle/oradata/prima/tbs1.dbf' ORA-27072: File I/O error Additional information: 4 Additional information: 131 RMAN> alter database datafile 5 offline; Statement processed RMAN> restore datafile 5; Starting restore at 2015-10-20 11:50:43 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=187 device type=DISK creating datafile file number=5 name=/u01/app/oracle/oradata/prima/tbs1.dbf restore not done; all files read only, offline, or already restored Finished restore at 2015-10-20 11:50:45 RMAN> recover datafile 5; Starting recover at 2015-10-20 11:50:52 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2015-10-20 11:50:53 RMAN> alter database datafile 5 online; Statement processed RMAN> select count(*) from adam.nu; COUNT(*) ---------- 10000
Cool isn’t it? Requires that you have all archived logs available since the creation of the tablespace. And besides the SQL commands inside the RMAN shell, it is not a 12c new feature. It works that way since forever, as far as I recall. Don’t believe it, test it! Maybe not on a production system ;-)
RMAN old feature: Restore datafile without backup https://t.co/SIZpDigUH4
— Uwe Hesse (@UweHesse) October 20, 2015
Oracle University has introduced a new format that suits the way many learn today: Oracle Learning Streams
Although I do not expect that this format will replace classroom training, I consider it a great enhancement – coming with a price like most good things…
As you can see, I’m happy to contribute to it in my area of expertise: You can find me here in the Database Stream.
My clips start with A Practical Guide because they all contain brief demonstrations that show how things can be done practically.
This is what I think viewers want to get instead of “Death by PowerPoint” :-)
It is an ongoing process (a stream of content, therefore the name), so I will keep on adding clips to the pool.
Especially if you like my kind of content, so have a look and get back to me to tell what you would like to see there, please!
FASTSYNC is a new LogXptMode for Data Guard in 12c. It enables Maximum Availability protection mode at larger distances with less performance impact than LogXptMode SYNC has had before. The old SYNC behavior looks like this:
The point is that we need to wait for two acknowledgements by RFS (got it & wrote it) before we can write the redo entry locally and get the transaction committed. This may slow down the speed of transactions on the Primary, especially with long distances. Now to the new feature:
Here, we wait only for the first acknowledgement (got it) by RFS before we can write locally. There is still a possible performance impact with large distances here, but it is less than before. This is how it looks implemented:
DGMGRL> show configuration; Configuration - myconf Protection Mode: MaxAvailability Members: prima - Primary database physt - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 26 seconds ago) DGMGRL> show database physt logxptmode LogXptMode = 'fastsync' DGMGRL> exit [oracle@uhesse ~]$ sqlplus sys/oracle@prima as sysdba SQL*Plus: Release 220.127.116.11.0 Production on Sat Aug 1 10:41:27 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="physt", SYNC NOAFFIRM delay=0 optional compression= disable max_failure=0 max_conn ections=1 reopen=300 db_unique _name="physt" net_timeout=30, valid_for=(online_logfile,all_ roles)
My configuration uses Fast-Start Failover, just to show that this is no restriction. Possible but not required is the usage of FASTSYNC together with Far Sync Instances. You can’t have Maximum Protection with FASTSYNC, though:
DGMGRL> disable fast_start failover; Disabled. DGMGRL> edit configuration set protection mode as maxprotection; Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode Failed. DGMGRL> edit database physt set property logxptmode=sync; Property "logxptmode" updated DGMGRL> edit configuration set protection mode as maxprotection; Succeeded.
Addendum: As my dear colleague Joel Goodman pointed out, the name of the process that does the Redo Transport from Primary to Standby has changed from LNS to NSS (for synchronous Redo Transport):
SQL> select name,description from v$bgprocess where paddr<>'00'; NAME DESCRIPTION ----- ---------------------------------------------------------------- PMON process cleanup VKTM Virtual Keeper of TiMe process GEN0 generic0 DIAG diagnosibility process DBRM DataBase Resource Manager VKRM Virtual sKeduler for Resource Manager PSP0 process spawner 0 DIA0 diagnosibility process 0 MMAN Memory Manager DBW0 db writer process 0 MRP0 Managed Standby Recovery TMON Transport Monitor ARC0 Archival Process 0 ARC1 Archival Process 1 ARC2 Archival Process 2 ARC3 Archival Process 3 ARC4 Archival Process 4 NSS2 Redo transport NSS2 LGWR Redo etc. CKPT checkpoint RVWR Recovery Writer SMON System Monitor Process SMCO Space Manager Process RECO distributed recovery LREG Listener Registration CJQ0 Job Queue Coordinator PXMN PX Monitor AQPC AQ Process Coord DMON DG Broker Monitor Process RSM0 Data Guard Broker Resource Guard Process 0 NSV1 Data Guard Broker NetSlave Process 1 INSV Data Guard Broker INstance SlaVe Process FSFP Data Guard Broker FSFO Pinger MMON Manageability Monitor Process MMNL Manageability Monitor Process 2 35 rows selected.
I’m not quite sure, but I think that was even in 11gR2 already the case. Just kept the old name in sketches as a habit :-)