Flashback Database, Backup and Recovery in
Oracle Database 12c Release 2
DOAG Regionaltreffen 25.01.2018, Jörg Sobottka, Karlsruhe
Die Datenbank-Spezialisten.
Zahlen und FaktenRobotron Datenbank-Software GmbH
Gründungsjahr 1990
Geschäftsform GmbH (8 Gesellschafter)
Mitarbeiterzahl ~500 (Stand 06/2017)
Stammkapital 2,4 Mio. EUR
Umsatz 2016 36,8 Mio. EUR
Umsatz 2017 43,8 Mio. EUR
Technologie-Partner
ISO 9001 zertifiziert
Hauptsitz
Schulungs- und Kongresszentrum
Die Datenbank-Spezialisten.
Robotron-Firmengruppe
Tschechien: Robotron Database Solutions s.r.o.
Schweiz: Robotron SchweizGmbH
Österreich: Robotron Austria GmbH
Russland: Robotron Rus GmbH
Deutschland: SASKIA® Informations-Systeme GmbH
Die Datenbank-Spezialisten.
Oracle Services & Support - Kompetenzen
Kernkompetenz Oracle Produkte und
Infrastrukturen
Ausgeprägte Erfahrungen im Bereich komplexer Hochverfügbarkeitsprojekte (ODA, RAC, Dataguard, MAA, Failsafe, Failovercluster, Dbvisit, Stretched Environments)
Kompetenzen in weiteren Oracle Methoden und Technologien
Replikationen (GoldenGate, Streams, Dbvisit)
Linux, Solaris und VM
Upgrades und Migrationen
Datenbank Tuning
Datenbank Security
Backup und Recovery Konzepte
Fusion Middleware Stack
Oracle Engineered Systems
Die Datenbank-Spezialisten.
Oracle Services & Support - ManagedServices
Rundum-Betreuung oder teilweise Übernahme von Services Ihrer Oracle-Systeme, remote oder vor Ort
Dienstleistungen, Unterstützung und systemtechnische
Projekte (z. B. Infrastruktur, HA, Tuning, Coaching)
individuelle und an Ihre Anforderungen angepasste Service Level Agreements inklusive 24x7
kürzeste Reaktionszeiten im Desaster-Fall durch
fortlaufendes proaktives System-Monitoring
planbare Kosten durch vordefinierte Leistungspakete unseres Service-Katalogs
Reduktion Ihrer Betriebskosten durch Konsolidierung,
Standardisierung und Automatisierung des IT-Betriebs
Die Datenbank-Spezialisten.
Select profile_details from oracle_communitywhere name = ‘Joerg Sobottka‘;
ORACLE DBSINCE 1991
OCP DBA (9i-12c)
TuningHigh
Availability
DevelopmentSW Design
Licensing
Enterprise Manager
IT Strategy
Blog: https://a-different-view-by-js.blogspot.com
Die Datenbank-Spezialisten.
Topics
Architecture Non-CDB
New default architecture CDB with PDBs (Single-Tenant, Multi-Tenant)
New Features for Flashback Database, Backup and Recovery (12.2 and a little bit 12.1)
Detailed view into Flashback Database in a CDB-/PDB environment
Do‘s and don‘ts Flashback PDB
Detailed view into Backup and Recovery with RMAN in a CDB-/PDB environment
Do‘s and don‘ts Backup and Recovery with RMAN
Live demo
Die Datenbank-Spezialisten.
Architecture Non-CDB
One database with
typically one, sometimes more instances (RAC, Standby, …) and
one system tablespace, one sysaux tablespace,
typically one undo tablespace and one temp tablespace with
one or more user schemas and therefore
one or more user tablespaces with
one or more files per tablespace, shared redo logs, flashback area, control files, archive redo logs, etc.
managed as one database with
one set of parameters and a central data dictionary
But different applications normally don‘t share resources
Die Datenbank-Spezialisten.
Architecture CDB/PDB - Singletenant
Container-based database
Available since 12.1, NEW: default architecture in 12.2
ONE Container (CDB$ROOT) with own data files (System, SysAux, Temp, Undo, possible – but bad design: application tablespaces)
Root container manages redo logs, flashback logs, memory, processes…
ONE pluggable database with System, SysAux, Temp – Tablespaces as blueprint (PDB$SEED-Container)
ONE pluggable user/application database with System, SysAux, Temp and Undo Tablespace (Application Container)
Difference to 12.1: Every PDB now has its own UNDO (local undo)!
Managed typically as ONE database
Different dictionaries for CDB and PDB (metadata)
Singletenant has 3 containers (CDB$ROOT, PDB$SEED, 1 Apps.PDB)
Die Datenbank-Spezialisten.
Architecture CDB/PDB - Multitenant
Container-based database
Available since 12.1
ONE Container (CDB$ROOT) with own data files (System, SysAux, Temp, Undo, possible – but bad design: application tablespaces)
Root container manages redo logs, flashback logs, memory, processes…
ONE pluggable database with System, SysAux, Temp – Tablespaces as blueprint (PDB$SEED-Container)
ONE OR MANY pluggable user/application databases with System, SysAux, Temp and Undo Tablespace (Application Containers)
Difference to 12.1: Every PDB now has its own UNDO (local undo)!
Managed typically as ONE OR MANY databases
Different dictionaries for CDB and PDB (metadata)
Multitenant has > 3 containers (CDB$ROOT, PDB$SEED, x Apps.PDB)
Die Datenbank-Spezialisten.
CDB/PDB States/File states
Container based database
Pluggable database
PDB state depends on CDB state (=> Controlfile owned by CDB)
PDB MAY be opened together with CDB open or stays mounted
If CDB is open, it’s possible to put PDB in Mount or Open state
Can’t work with a PDB when CDB is closed or unmounted (nomount)
Close Nomount Mount Open
Close Mount Open
Shutdown
Shutdown / alter pluggabledatabase close
Die Datenbank-Spezialisten.
Flashback Database and RMAN outstanding New Features 12.1
Support for single-/multitenant databases:- Backup, restore and recover CDBs and PDBs as whole db- Point-in-time recovery of a CDB or PDB requires point-in-time recovery of the (shared) undo tablespace- Duplicate to standby databases as whole db- Flashback whole databases- Recover using backup sets from physical standby db
Useful in a CDB environment:
Point-in-time table recovery
SYSBACKUP privilege for separation of duty (no view into data)
“Native” sql interface enhancements
Cross platform data transport using full and incremental backup sets
Die Datenbank-Spezialisten.
Flashback Database and RMAN outstanding New Features 12.2
Single PDB can be flashed back (rewind) to a point-in-time without influencing other PDBs or the CDB
Easier point-in-time recovery of a PDB due to local undo (no restore ofshared undo necessary)
Create standby database using dbca instead of rman
Point-in-time table recover to a new tablespace or new schema with “REMAP_TABLESPACE” and “REMAP SCHEMA”
Point-in-time table recover checks for sufficient disk space
Recover nonlogged data blocks from standby or primary database
Recover UNTIL AVAILABLE REDO
Die Datenbank-Spezialisten.
Flashback Database in 12.1 and 12.2
Prerequisites: - local undo must be enabled (default in 12.2, owner CDB and each PDB)- Fast recovery area must be configured (owner CDB)- Flashback logging enabled: “alter database flashback on” (owner CDB)- Archive logging enabled (owner CDB)- Connect as SYSDBA or SYSBACKUP- Doesn’t work with Nologging, direct-path inserts, shrunken data files, …
Flashback a CDB with ALL included PDBs
Connect to CDB
Shutdown immediate
Startup mount
Flashback database to <timestamp/SCN/Restore Point/…>;
Alter database open resetlogs;
Useful for test environments, upgrades, automatic build systems,…
Die Datenbank-Spezialisten.
Flashback Database in 12.2
Flashback a PDB without influencing CDB
Connect to the CDB
Alter session set container=pdb1r2;
Create restore point PDB1R2_RP1 guarantee flashback database;
Make a “fault” at PDB1R2: Truncate table mytab;
Connect to the CDB
Alter pluggable database PDB1R2 close; #PDB must be in mount state
Flashback pluggable database PDB1R2 to restore point PDB1R2_RP1; # or to scn/before timestamp/…
Alter pluggable database PDB1R2 open resetlogs; #can not make an open reset logs (because the owner of redo logs is the CDB), but does something=>alert.log/v$pdb_incarnation
Later on: drop restore point PDB1R1_RP1;
Die Datenbank-Spezialisten.
Do’s and Dont’s Flashback PDB
Check connections TWICE! Connect could be to CDB or any PDB!
Avoid to flashback single PDBs to timestamp or SCN (not guaranteed)
Better: use predefined guaranteed restore points with DIFFERENT NAMES for different targets (each PDB/CDB) – Namespace for restore points is each PDB/CDB
Attention: If a PDB restore point is not found, it is interpreted as a CDB restore point!
Helpful:- Do all at CDB$ROOT using FOR PLUGGABLE DATABASE syntax, e.g. create restore point MyRP FOR PLUGGABLE DATABASE PDB1R2;Use ALTER SESSION SET CONTAINER clause to switch to PDB, if needed.- RMAN: LIST RESTORE POINT ALL; don’t show PDB information- SELECT * FROM V$RESTORE_POINT; does show PDB information
Die Datenbank-Spezialisten.
Backup with RMAN in a CDB/PDB environment
Backup and Restore of CDB with PDBs, only CDB or PDBs without CDB is possible
Backing up a PDB is nearly the same as with Non-CDB databases
If a backup of a PDB is done for later use, don’t forget to backup controlfileand archive logs from CDB$ROOT
Syntax/Execution is depending on the connection to a container
Not all RMAN syntax is working everywhere or works different:- CDB$ROOT> backup database plus archivelog; #CDB, PDBs, arch. logs- CDB$ROOT> backup database root; #CDB$ROOT only- CDB$ROOT> backup pluggable database pdb1r2; #PDB- CDB$ROOT> backup pluggable database pdb1r2 plus archivelog; #PDB/logs- PDB1R2> backup database plus archivelog; # no backup of archivelog, no error is thrown- PDB1R2> backup datafile 27; #does not make an autobackup controlfile!- CDB$ROOT> backup datafile 27; #does make an autobackup controlfile!
Die Datenbank-Spezialisten.
Restore and Recover with RMAN in a CDB/PDB environment (1)
Remember all time: Owner of control files, spfile, flashback logs, online and archive redo logs is only CDB$ROOT
Some tablespaces do exist very often (System, Sysaux, Temp, Undo), but in different containers (CDB$ROOT, PDB$SEED, user PDBs)
Same syntax, different output example:REPORT SCHEMA- reports connected to a PDB only Data-/Tempfiles owned by the PDB- reports connected to a CDB all Data-/Tempfiles for the CDB$ROOT and all PDBs (including PDB$SEED)
Same syntax, not understandable error:ALTER DATABASE DATAFILE 25 OFFLINE;- works in the right PDB container- CDB$ROOT: ORA-01516: nonexistent file "25" in the current container=> Set container for a session is in RMAN not allowed
The solution is…
Die Datenbank-Spezialisten.
Restore and Recover with RMAN in a CDB/PDB environment (2)
PDB:rman target=sys/oracle@pdb1r2rman> run {
alter database datafile 25 offline; restore datafile 25;recover datafile 25;alter database datafile 25 online; }
CDB:rman target=sys/oracle@orcl12c
rman> run {alter pluggable database pdb1r2 close;restore datafile 25;recover datafile 25;alter pluggable database pdb1r2 open;}
Die Datenbank-Spezialisten.
Restore and Recover with RMAN in a CDB/PDB environment (4)
CDB:rman target=sys/oracle@orcl12c
rman> run {sql 'PDB1R2' 'alter database datafile 25 offline';restore datafile 25;recover datafile 25;sql 'PDB1R2' 'alter database datafile 25 online';
}
RMAN does not allow to reconnect
RMAN does not allow to set datafiles owned by a PDB offline from CDB withintegrated SQL syntax
RMAN can run SQL(!) statements using the old SQL keyword in a specifiedcontainer environment
Die Datenbank-Spezialisten.
Restore and Recover with RMAN in a CDB/PDB environment (5)
Where will this work? CDB? PDB1R2? PDB2R2?... PDB145R2?rman> run {
alter tablespace users offline;restore tablespace users;recover tablespace users;alter tablespace users online;}
CDB should not have a users tablespace (but it is possible)
Connected to every PDB where a datafile/users tablespace exists
Ensure: Which PDB is affected by an error and connect to this!
Restore and recover a PDB-tablespace with tablespace syntax connected tothe CDB is not implemented => restore/recover the whole PDB
Die Datenbank-Spezialisten.
Restore and Recover with RMAN in a CDB/PDB environment (6)
Data Recovery Advisor
Diagnosis of failures
Generates scripts to repair failures
Easy to use for detected failures: List failure; advise failure; repair failure;
Per default does not show all failures at all time (only already detected and only with high or critical priorities)
Works connected to CDB only
Does work in a Non-CDB or single-tenant environment very well
Is not aware of any SLAs we do have on consolidated db with high number ofPDBs
Can restore and recover to original location (no relocation of data files)
Target DRA: Have EVERYTHING online with as LESS I/O as possible
Die Datenbank-Spezialisten.
Do’s and Dont’s RMAN in a CDB/PDB environment (1)
More chances to do it wrong and to lose data or time – if you don‘t knowexactly, where you are and what you do – than in a non-CDB environment
For backups of single PDBs – don‘t forget to backup the archivelogsseparately, if you are connected to a PDB – New RMAN session needed asPDB connection does not backup archivelogs
Autobackup of controlfiles and spfile are done only after backup fromCDB$ROOT level, not, if you are connected to a PDB
Don‘t use „delete input“ while backing up archivelogs, especially when usingflashback database or with a high number of PDBs (as it is more likely thanbefore that they may be used – keep them e.g. at least 2 or 3 days on disk)
With a rising number of PDBs, backups are getting huge in size. Think aboutincremental backup strategies or backup on PDB level
Die Datenbank-Spezialisten.
Do’s and Dont’s RMAN in a CDB/PDB environment (2)
Use an external RMAN catalog!- It‘s cost free (no additional license needed, as long as the database isunder support: http://docs.oracle.com/database/121/DBLIC/editions.htm#BABDFJIC)- Don‘t loose backup metadata while creating, moving, dropping pluggabledatabases- Allows to report schema as of timestamp- Helps storing scripts for backup and recovery
Do not drop a PDB from a container unless a new backup was taken from the new CDB where the database has been plugged in (CDB$ROOT, PDB and archive logs) and use “backup controlfile to trace” before and after structural changes
Reduce the risk of losing data by doing all, that can be done, from the CDB$ROOT container
Create predefined scripts and use them whenever possible
Die Datenbank-Spezialisten.
Do’s and Dont’s RMAN in a CDB/PDB environment (3)
Ugly: Startup of a CDB may work (Opens the database without any errormessage) – but one or more PDB can remain in mount status
Consult (monitor) the ALERT.LOG often – all known errors with data files, control files, etc. are reported in there (even if the CDB opens normally)
Make yourself comfortable with all the new CDB and PDB views- OPEN_MODE from v$containers or v$pdbs shows mount/open status- STATUS from dba_pdbs is for Normal/Plugin/Convertion/upgrade states
Trust the Data Recovery Advisor LESS than in a Non-CDB or Single PDB environment – it tries its best, but it doesn’t know your SLAs and it may does not know all errors
BUT: use the Data Recovery Advisor with REPAIR FAILURE PREVIEW to see in combination with the ALERT.LOG, what really happened, to challenge your restore and recover strategy and to learn something about the syntax but be aware, that errors marked “low” are not shown by default and there may be some errors not found yet
Die Datenbank-Spezialisten.
Do’s and Dont’s RMAN in a CDB/PDB environment (4)
„restore tablespace system“ can be run on the CDB$ROOT, PDB$SEED and all user PDB containers. Use data file names/data file numbers instead of tablespaces or at least check your connections TWICE
If you need to restore/recover the CDB oracle recommends to restore andrecover ALL included PDBs because of possible metadata inconsistencies
Train yourself regularly, e.g. create an environment, make and write down CONCEPTS and REVIEW them often:More necessary than before, to write down backup AND restore and recover concepts separately for every (pluggable) database or at least for groups of (pluggable) database with same SLA levels:WHAT? WHEN? HOW OFTEN? WHERE FROM? WHERE TO? KEEP HOW LONG? HOW TO RESTORE? HOW TO RECOVER? WHICH SLA? WHICH USERS?
Use environment to exercise test scenarios again and again for backup and recovery from time to time (also with different database versions)
Die Datenbank-Spezialisten.
Do’s and Dont’s RMAN in a CDB/PDB environment (5)
For dataguard environments:- Do backups from standby and restore and recover the primary to move thebackup workload from your primary to your standby system- Be aware, that with 12.2 the standby system does NOT need to have all PDBs included running in your production (as it was with 12.1)- Nevertheless, ALL archive logs are shipped (and used for recovery) to thestandby database system (so you can backup them there)
With active data guard (EE extra cost option), e.g. block restore and recoverybetween primary and standby database are done without the need ofinteraction with a database administrator
Die Datenbank-Spezialisten.
Your feelings: Still confused, but on a higher level?Start with your concepts!
Die Datenbank-Spezialisten.
Fragenkontakt
Jörg [email protected]
Telefon: +41 71 225 78 04Mobil: +41 79 555 97 93
Blog: https://a-different-view-by-js.blogspot.com