Enhancements in Oracle 12c Data Guard & Recovery
Oracle 12c Release 2 introduced several new features for Data Guard and Recovery. Some of the key changes include the introduction of SYSDG administrative privilege for better separation of duties, real-time cascaded standby for efficient redo transport, far sync instances for remote syncing, and online relocation of primary database data files. These enhancements aim to improve the operational efficiency, performance, and disaster recovery capabilities of Oracle databases.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Oracle 12c Data Guard & Backup and Recovery Workshop
Question Question Can you please recap the main new features introduced in Oracle 12c Release 2 for the Broker as well as Data Guard what has changed compared to say Oracle 11g ?
Data Guard New Features Oracle 12c Release 1 and Release 2
SYSDG Administrative Privilege for Oracle Data Guard SYSDG Administrative Privilege for Oracle Data Guard Operations Operations Oracle Data Guard-specific administration privilege Enforcing better separation of duty Perform standard administration duties for an Oracle Data Guard environment Earlier on required SYSDBA privilege STARTUP,SHUTDOWN, ALTER DATABASE, SELECT ANY DICTIONARY, CREATE RESTORE POINT etc No access to application data either via DML or DDL operations
Real Real- -time Cascaded Standby time Cascaded Standby Cascaded redo transport destination receives redo from primary database indirectly from another standby database (cascading standby) rather than directly from a primary database The cascaded standby is referred to as the terminal destination Offloads the overhead associated with redo transport from primary database of having to ship redo to multiple standby destinations Real-time cascading requires a license for the Oracle Active Data Guard option Cascading standby database can cascade primary database redo to up to 30 terminal destinations Multiple standby destinations one for Disaster Recovery and others for real- time reporting
Far Sync Far Sync
Far Sync Instance Components Far Sync Instance Components
Online relocation of primary database data file Online relocation of primary database data file Prior to Oracle 12c file had to be taken offline or database was not open Online move of datafile to different physical location while database is open ALTER DATABASE MOVE DATAFILE Standby database is not affected when the primary database datafile is moved Also move datafile on ADG enabled standby database while redo apply and recovery is in progress
DML operations on Active Standby DML operations on Active Standby DML operations allowed on Global Temporary tables on Active Data Guard enabled standby database Undo on Global Temporary tables stored in temp tablespace and not undo tablespace Redo-less changes to Global Temporary tables Read-mostly reporting applications as opposed to only read-only reporting applications
Resume a switchover after a failure Resume a switchover after a failure If switchover fails because of a configuration issue, the Data Guard Broker reports any problems it encounters in the alert log files or in the broker log files Review log files and fix the problem (if possible) Retry the switchover operation
VALIDATE Database VALIDATE Database Performs a comprehensive set of database checks prior to a role change Will report if a database is ready for either a Switchover or Failover Provides a summary of the database Reports and errors or warnings that are detected Obtains information from both Data Guard related dictionary views a well as Automatic Diagnostic Repository (ADR) Checks if temp datafiles exist, adequate groups of standby redo log files, block corruptions, system or user data files are offline or corrupted as well as flashback database status
Data Guard at the Pluggable Database Level Data Guard at the Pluggable Database Level Prior to Oracle Database 12c Release 2 Data Guard was configured either for all pluggable databases in a container database or for none ENABLED_PDBS_ON_STANDBY initialization parameter Specify a subset of pluggable databases (PDB s) in a container database with a corresponding physical standby database Database role defined at the pluggable database level and not the container level If the parameter is not specified, then all PDBs in the CDB are created on the standby
In In- -Memory Column Store on standby database Memory Column Store on standby database Supported in an Active Data Guard environment Offload reporting workloads to standby Also offload analytical query type workload to standby In-Memory column store can exist on both Primary and Standby database In-Memory column store can also exist exclusively on the Standby database INMEMORY_SIZE=0 on Primary INMEMORY_SIZE=10G on Standby
Automatic Password File Change Propagation Automatic Password File Change Propagation Prior to 12.2 password files had to be manually synchronized between the primary and standby databases ORA-16191: Primary log shipping client not logged on standby type errors if password was changed Password file changes done on the primary database are now automatically propagated to standby databases Password file updates on primary now included in redo stream Redo apply on standby updates password file on standby
Keeping Physical Standby Sessions Connected During Role Keeping Physical Standby Sessions Connected During Role Transition Transition Prior to 12.2, Data Guard role transitions like switchover would disconnect user sessions Standby database can be used for reporting purposes (ADG) User sessions connected would not be disrupted during a role transition from standby to primary STANDBY_DB_PRESERVE_STATES initialization parameter NONE, ALL or SESSION NONE is default and no sessions retained during switchover or failover SESSION or ALL - user sessions are retained when a read-only physical standby database is converted to primary
Using Oracle Diagnostic Pack to Tune Oracle Active Data Guard Using Oracle Diagnostic Pack to Tune Oracle Active Data Guard Standbys Standbys Active Data Guard standby is open in read-only mode Being used for supporting real-time queries and read-only reporting applications Also can be used for storing In-Memory columnar data Ability to identify and tune costly SQL being executed on ADG standby Capture performance data to the Automatic Workload Repository (AWR) for an Oracle Active Data Guard standby Run Automatic Database Diagnostic Monitor (ADDM) analysis on the AWR data
Multiple observers for Fast Multiple observers for Fast- -Start Failover Start Failover If one observer fails, backup observer takes over automatically so configuration is never in an unobserved state Register up to three observers to monitor a single Data Guard broker configuration At any given time only one Observer is active and designated as the master START OBSERVER <NAME> SET MASTEROBSERVER TO <NAME>
Create Standby Database via DBCA Create Standby Database via DBCA [oracle@host02 dbs]$ dbca -silent -createDuplicateDB -gdbName amer - primaryDBConnectionString host01:1521/amer -sid amer -createAsStandby - dbUniqueName amer_sb Enter SYS user password: Listener config step 33% complete Auxiliary instance creation 66% complete RMAN duplicate 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/amer_sb/amer1.log" for further details.
Data Guard Broker Miscellaneous 12.2 New Features Data Guard Broker Miscellaneous 12.2 New Features DGMGRL @ command allows you to run DGMGRL command scripts VALIDATE DATABASE DATAFILE command performs validation of data files across both the primary database and standby databases which will include detecting any lost writes SHOW DATABASE VERBOSE command shows the locations of the Oracle alert log file and of the broker log file SPOOL command is available to record the output of the commands issued on the DGMGRL console / (slash) command available which repeats the last command entered on the DGMGRL console.
Part Two Backup and Recovery
Why Backup and Recovery Why Backup and Recovery Protect the database against media failure, site failure, human error, natural disaster, terrorism etc In the event of a loss reconstruct or recover the database Objective is to recover as fast as possible (RTO or Recovery Time Objective) Objective is to recover with minimum data loss (RPO or Recovery Point Objective)
Types of Failures Types of Failures Media Failures Failure to read or write to a database file located on disk User or Human errors Data in database is changed incorrectly by mistake or wilfully Application error Application logic or code error has adverse effect on data in the database Software malfunction or logical errors
Backup related DBA activities and duties Backup related DBA activities and duties Planning a backup strategy Backup strategy should have minimum impact to the business Configuring a backup schedule Monitoring the backup environment Testing recovery from different failure scenarios Data archival Data transfer
Physical Backup and Logical Backup Physical Backup and Logical Backup Physical backups are physical copies of database files data files, control files, archive redo log files, parameter and password files Physical copy is taken to disk or offline media like tape library or Oracle Recovery Appliance (Zero Data Loss Appliance) Foundation for any sound backup and recovery strategy Logical backups supplement physical backups Data pump export of tables, subset of tables or schema level Data pump export of stored procedures and packages Database backups are physical backups
User User- -managed backups and RMAN managed backups and RMAN Cold backups or hot backups Copy files at the O/S level after database has been shutdown in a consistent manner via SHUTDOWN IMMEDIATE/NORMAL/TRANSACTIONAL option Or after tablespace to be backed up is taken offline User has to ensure backup is taken at transactionally consistent database checkpoint SCN Online backup requires BEGIN BACKUP and END BACKUP ALTER TABLESPACE BEGIN|END BACKUP ALTER DATABASE BEGIN|END BACKUP V$BACKUP view has information about which data file is in hot backup mode
RMAN Components RMAN Components RMAN client connects to target database using Oracle Net Target database is the database that the RMAN client connects to for backup or restore operations Recovery Catalog database or schema is a separate database/schema which contains the metadata required by RMAN for backup and recovery operations. Recovery catalog can contain backup metadata for not just one but numerous databases In the absence of a Recovery Catalog RMAN will use database control file to obtain required metadata Fast Recovery Area disk-based location for storing RMAN backups and recovery-related files like control files, online and archive redo log files, flashback log files. Media Management Software enables vendor-specific backup application to integrated with RMAN for backup to storage systems like tape media
Connecting with RMAN Connecting with RMAN rman target '"/ as sysdba rman target '"/ as sysbackup rman target / rman target bkupoper@pdb1 RMAN> connect catalog rco@catdb rman target / auxiliary sys@aux
RMAN Commands, scripts, substitution variables RMAN Commands, scripts, substitution variables RMAN> CONNECT TARGET RMAN> BACKUP DATABASE; rman TARGET / @cmdfile1 RMAN> @cmdfile1 Make backup scripts dynamic using SQL*PLUS like substitution variables Invoke substitution variables via USING command
Using Substitution Variables Using Substitution Variables testbkp.rcv connect target sys/oracle run{ backup incremental level &1 database tag &2; } bkp_db.sh #! /bin/ksh export level=$1 export tag=$2_`date +%d%b%y` rman @testbkp.rcv using $level $tag $ ./bkp_db.sh 1 inc_lev1
RMAN Configuring Environment RMAN Configuring Environment RMAN out of the box default values may be adequate in most environments Can override default parameters SHOW or SHOW ALL to view default parameters CONFIGURE to change defaults Configure backup retention policy, encryption, default destinations for backups, tape backups
RMAN Configuration RMAN Configuration SHOW ALL; RMAN configuration parameters for database with db_unique_name PROD1 are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
RMAN Configuration RMAN Configuration CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_DEVICE=tape1)'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/disk1/oracle/dbs/snapcf_ev.f'; # default
RMAN Retention Policies RMAN Retention Policies How long to retain backups Would depend on SLAs related to data recovery REDUNDANCY-based retention policy RECOVERY WINDOW-based retention policy Defined via CONFIGURE RETENTION POLICY command Redundancy based retention policy specifies how many full or level 0 backups of data file and control file which RMAN will retain Default is 1 If number of full or level 0 backups for specific data file or control file is exceeded then RMAN considers additional backups as OBSOLETE OBSOLETE backups can be deleted by RMAN automatically
REDUNDANCY REDUNDANCY- -based retention policy based retention policy CONFIGURE RETENTION POLICY TO REDUNDANCY 2 Full backups are performed every Monday, Tuesday, Wednesday, and Thursday As part of Friday backup, Monday and Tuesday backups become obsolete Run DELETE OBSOLETE command to remove redundant backups
WINDOW WINDOW- -based retention policy based retention policy CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS Recovery window is a period that begins with the current time and extends backward in time to the earliest time a point-in-time recovery is possible in the event of a media failure Full backups and required incremental backups and archived logs are retained by RMAN so that the database can be recovered up to 7 days in the past Backups taken outside of the recovery window (later than 7 days) may be required to satisfy the point-in-time recovery objective
RMAN Channels RMAN Channels An RMAN channel is a connection to a database server session CONFIGURE CHANNEL command to configure options for disk or tape-based backup channels Override and specify one-time options with the ALLOCATE CHANNEL command CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT /backup/%U; CONFIGURE DEVICE TYPE sbt PARALLELISM 2; ALLOCATE CHANNEL c1 DEVICE TYPE DISK; ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
Archived Redo Log Deletion Policy Archived Redo Log Deletion Policy CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo log files are eligible for deletion Archived redo logs can be deleted automatically by the database or via user-initiated RMAN commands Archive log files in the fast recovery area can be deleted automatically by the database under situation of space pressure By default archive redo log policy is set to NONE Archived redo log files in the recovery area are eligible for deletion if they have been backed up at least once to disk or SBT CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP 2 TIMES TO DEVICE TYPE CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY
Advanced Configuration Advanced Configuration Configuring Backup Duplexing CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2 Configuring Tablespaces for Exclusion from Whole Database Backups CONFIGURE EXCLUDE FOR TABLESPACE example; Configure backup compression CONFIGURE COMPRESSION ALGORITHM 'BASIC ; CONFIGURE COMPRESSION ALGORITHM HIGH ; Configuring Backup Encryption CONFIGURE ENCRYPTION FOR DATABASE ON; CONFIGURE ENCRYPTION ALGORITHM TO 'AES256';
Backup Optimization Backup Optimization BACKUP command skips backing up a particular file when the identical file has already been backed up to the specified device type CONFIGURE BACKUP OPTIMIZATION ON Data file is considered identical if it has the same DBID or checkpoint SCN as the data file already backed up Archived log file is considered identical if it has the same DBID, thread number, sequence number and RESETLOGS SCN
RMAN Backups RMAN Backups RMAN> BACKUP DATABASE; RMAN uses the configuration settings stored in the control file or recovery catalog and the records of previous backups to determine an efficient set of steps for the backup Determines the destination device type and backup location as well as any advanced options like encryption or compression RMAN> BACKUP DEVICE TYPE DISK DATABASE; RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE; RMAN> BACKUP DATABASE FORMAT "/backup/bkp_full_%U";
RMAN Backup Set and Backup Pieces RMAN Backup Set and Backup Pieces Backup data stored by RMAN in a logical structure called a backup set Smallest unit of an RMAN backup A backup set contains the data from one or more data files, archived redo logs, control files, or server parameter file A backup set contains one or more binary files in an RMAN-specific format called backup piece Backup multiple files into one backup set with one backup piece SECTION SIZE parameter of BACKUP command will produce multiple backup pieces Backup a large file in parallel with multiple channels each producing an individual backup piece
Image Copy Image Copy An image copy is an exact copy of a database file Not stored in RMAN format Similar to making a copy using operating system commands RMAN BACKUP AS COPY command Image copies are chosen over backupsets because of the additional overhead of reading through an entire backupset trying to identify the file which is needed to be restored Can perform a SWITCH DATAFILE TO COPY command to use the image copies already existing on disk as a replacement for the datafile which needs to be restored followed by a recovery of the data file copy No need to perform a restore of data file first before performing a recovery Control file is updated with new location of the data file
Multisection Backup Multisection Backup Multisection backup enables backups of large data files to be divided into sections Sections backed up in parallel across multiple channels Faster backup performance and better recovery times New in 12c is Multisection Incremental Backups BACKUP INCREMENTAL LEVEL 1 SECTION SIZE 1024M DATAFILE /u01/app/oracle/users01.dbf';
Multiplexed RMAN Backup Sets Multiplexed RMAN Backup Sets RMAN can simultaneously read multiple files from disk and then write their blocks into the same backup set Image copies of files are not multiplexed Multiplexing based on FILESPERSET and MAXOPENFILES FILESPERSET parameter of the BACKUP command determines how many data files to put in each backup set as well as how many files are read by a channel - default is 64 MAXOPENFILES defines how many data files RMAN can read from simultaneously default is 8 The level of multiplexing is the minimum of MAXOPENFILES and the number of files in each backup set If FILESPERSET is lower then the level of multiplexing, then multiplexing will be FILESPERSET value Backup 12 data files with one channel when FILEPERSET is set to 4. The level of multiplexing is the lesser of this number and 8 The channel simultaneously writes blocks from 4 data files into each backup piece
RMAN can simultaneously read multiple files from disk and then write their blocks into the same backup set
Parallelizing a database backup Parallelizing a database backup RUN { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT /u01/%d_backups/%U'; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT /u02/%d_backups/%U'; ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT /u03/%d_backups/%U'; BACKUP AS COPY DATABASE; }
Full backups Full backups Perform a complete database backup when database is either mounted or open Issue BACKUP DATABASE command Can exclude certain tablespaces from backup (maybe read-only tablespaces) SKIP READONLY clause Can also backup archive log files along with data files BACKUP DATABASE PLUS ARCHIVELOG Performs a log switch and archives current redo log file which is also included in the full backup
Incremental backups Incremental backups Incremental backup copies only data file blocks that have changed since a specified previous backup Cumulative incremental backup or a Differential incremental backup BACKUP DATABASE and BACKUP INCREMENTAL LEVEL 0 DATABASE are different A full backup is not usable as part of an incremental strategy Level 0 incremental backup is the basis of an incremental strategy
Incremental backups Incremental backups Avoid daily full backups reducing disk space requirements for backup storage Reduced network traffic caused by transmitting backup data Faster daily backups if combined with Block Change Tracking Faster recovery times Synchronize a physical standby database with the primary database in case of a lag on the standby via BACKUP INCREMENTAL FROM SCN command
Incremental backups Incremental backups Differential Backup RMAN looks for last level 1 or level 0 backup and only captures those data block changes that were made after those backups at the same level Is the default BACKUP INCREMENTAL LEVEL 1 DATABASE Cumulative Backup RMAN backups up all data block changes that are made after a level 0 backup or level 1 backup at the same or lower level BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE
