“Dumping current patch information” in Alert Log 12c can lead to a misinterpretation

Oracle Database 12.1 shows information on applied patches in the alert log during instance startup.

A question come up: The msg info in alert.log (below) is about patch applied on OH only or this information is related to patch applied on Database?

alert.log
...
2016-09-01 18:07:52.476000 -03:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 23144544
Patch Description: DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
Patch Apply Time: 2016-08-31 17:44:09 GMT-03:00
...

To get the answer I installed a OH 12.1 without create a database and apply the DATABASE BUNDLE PATCH: 12.1.0.2.160719.

I created a CDB database  with DBCA using template General purpose or transaction processing.

During a database creation  was looking at alert log and I noticed two things.

First the alert log show:

===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================

Then at end of database creation  alert log show:

...
2016-09-01 18:07:52.476000 -03:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 23144544
Patch Description: DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
Patch Apply Time: 2016-08-31 17:44:09 GMT-03:00
...

The patch has been applied on my database during database creation?

The anwser is NO.
To my surprise, the “Dumping current patch information” is about Patches applied on OH only.

Why  “No Patches” the after some time “Show Patches”? 

The patch information is populated by calling DBMS_QOPATCH. It is populated if the database was started in a open mode, as it’s not possible to execute any package with database not opened in read-write.
At first moment database was started in a non-open mode , then NO PATCHES, when database was started in OPEN mode then alertlog was pouplated with the info.

Why this can lead a misinterpretation? 

DBMS_QOPATCH was introduced in 12c to query about Patch information  applied on database, but the info on alert.log is not about patch applied on database, the info is about patch applied on OH only, it can lead us to think wich database have listed patch on alertlog applied.

(I think this is some bug or something else, because it makes no sense to me)

What is the safe mode to check if database have a patch applied?

 

conn as sysdba

SQL> set serverout on
SQL> exec dbms_qopatch.get_sqlpatch_status;

PL/SQL procedure successfully completed.

No outuput then No Patch applied.

After create database I checked if patch was applied and none patch was applied, but the “Dumping current patch information”  show all applied patch in the alertlog at startup of instance.

 

How to apply a BP or PSU on Database 12c?

By executing datapatch utility.

./datapatch -h
SQL Patching tool version 12.1.0.2.0 on Thu Sep 1 19:35:49 2016
Copyright (c) 2016, Oracle. All rights reserved.

sqlpatch usage:
All arguments are optional, if there are no arguments sqlpatch
will automatically determine which SQL scripts need to be run in
order to complete the installation of any SQL patches.

Optional arguments:
-db <db name>
 Use the specified database rather than $ORACLE_SID
-bundle_series <bundle_series>
 Specify if the patch is a bundle patch
 Should also be accompanied by -force option
 if -bundle_series option is specified,only 1 patch will
 be considered by the -force command
-apply <patch1,patch2,...,patchn>
 Only consider the specified patch list for apply operations
-rollback <patch1,patch2,...,patchn>
 Only consider the specified patch list for rollback operations
-upgrade_mode_only
 Only consider patches that require upgrade mode
-force
 Run the apply and/or rollback scripts even if not necessary
 per the SQL registry
-pdbs <pdb1,pdb2,...,pdbn>
 Only consider the specified list of PDBs for patching. All
 other PDBs will not be patched
-prereq
 Run prerequisite checks only, do not actually run any scripts
-oh <oracle_home value>
 Use the specified directory to check for installed patches
-verbose
 Output additional information used for debugging
-help
 Output usage information and exit
-version
 Output build information and exit

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)

Let’s  apply the patch.

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Suprise: When execute ./datapatch -verbose it has no warning or confirmation, the datapatch automatically  will apply patch in  all CDBs and in all opened PDBs of current OH environment.

The same concern that we have about “dd” command, we must have about datapatch command. Hope Oracle fix it.

Output:

./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Sep 1 18:28:47 2016
Copyright (c) 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/
sqlpatch_23003606_2016_09_01_18_28_47/sqlpath_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
 ID 160719 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED
 Nothing to roll back
 The following patches will be applied:
 23144544 (DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544))

Installing patches...
Patch installation complete. Total patches installed: 2

Validating logfiles...
Patch 23144544 apply (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_
PRDCDB_CDBROOT_2016Sep01_18_29_26.log (no errors)
Patch 23144544 apply (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_
PRDCDB_PDBSEED_2016Sep01_18_32_36.log (no errors)
SQL Patching tool complete on Thu Sep 1 18:34:54 2016

datapatch always validate if a BP or PSU already was applied  as show below:

./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Sep 1 18:35:21 2016
Copyright (c) 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/
sqlpatch_23593462_2016_09_01_18_35_21/sqlpatch_invocation.log

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
 ID 160719 in the binary registry and ID 160719 in PDB CDB$ROOT, 
 ID 160719 in PDB PDB$SEED

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED
 Nothing to roll back
 Nothing to apply

SQL Patching tool complete on Thu Sep 1 18:35:53 2016

 

Checking what patches have been applied (or rolled back) on database

SQL> set serverout on
SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 23144544
 Action : APPLY
 Action Time : 01-SEP-2016 18:34:46
 Description : DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
 Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/23144544/20355564/23144544_apply_PRDCDB_CDB
ROOT_2016Sep01_18_29_26.log
 Status : SUCCESS

PL/SQL procedure successfully completed.

 

Some notes:

  • Patch Information in Alert Log is about OH only
  • Creating a Database a CDB or Non-CDB database using a default Template it does not apply any Patch, you must apply patch manually after database creation.
  • datapatch utility is dangerous because has no warning or confirmation when executed.

 


PRCR-1079 CRS-5017 ORA-01017 – DBCA Fails to create a Database in Oracle Restart Environment 12c.

The Oracle Grid Infrastructure software owner (typically, grid) must be a member of the OSDBA group. Membership in the OSDBA group enables access to the files managed by Oracle ASM. If you have a separate OSDBA group for Oracle ASM, then the Oracle Restart software owner must be a member of the OSDBA group for each database and the OSDBA group for Oracle ASM.

http://docs.oracle.com/database/121/LADBI/usr_grps.htm#BABFECII

Issue:

If grid user is not a member of dba os group, then DBCA will fails at end of database creation and DBCA will perform rollback of database creation (database will not be created).

The error below will be raised:

srvctl start database -d prdcdb
PRCR-1079 : Failed to start resource ora.prdcdb.db
ORA-01017: invalid username/password; logon denied
CRS-5017: The resource action "ora.prdcdb.db start" encountered the following
error: ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/db-oracle/crs/
trace/ohasd_oraagent_grid.trc".

 

Solution:

To fix the issue you must add  user “grid” to OSDBA group “dba” and relink RDBMS Binaries.

# id grid
uid=205(grid) gid=202(oinstall) groups=208(asmdba),209(asmadmin),210(asmoper)
# usermod -a -G dba grid (linux)
# id grid
uid=205(grid) gid=202(oinstall) groups=203(dba),208(asmdba),209(asmadmin),
210(asmoper)


# su - oracle
oracle@db-oracle:/home/oracle> export AIXTHREAD_SCOPE=S
oracle@db-oracle:/home/oracle> export ORACLE_BASE=/u01/app/oracle
oracle@db-oracle:/home/oracle> export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/
dbhome_12102
oracle@db-oracle:/home/oracle> export PATH=$ORACLE_HOME/bin:$PATH

oracle@db-oracle:/home/oracle> relink all
writing relink log to: /u01/app/oracle/product/12.1.0/dbhome_12102/
install/relink.log


If there are no errors reported in $ORACLE_HOME/install/relink.log, then retry to create the database with dbca.

 

 

 


What Patch to Apply? PSU ? GI PSU ? Proactive Bundle Patch?

For those who unfamiliar with Oracle Patch is little confusing what patch to apply when get  a table with different patch in the same version.

patch

I will try clarify some doubts.

Note: You must provide a valid My Oracle Support login name in order to access below Links.

Patch version numbering changed

In November 2015 the version numbering for new Bundle Patches, Patch Set Updates and Security Patch Updates for Oracle Database changed the format from  5th digit of the bundle version with a release date in the form “YYMMDD” where:

  • YY is the last 2 digits of the year
  • MM is the numeric month (2 digits)
  • DD is the numeric day of the month (2 digits)

More detail can be found here: Oracle Database, Enterprise Manager and Middleware – Change to Patch Numbering from Nov 2015 onwards (Doc ID 2061926.1)

 

Changes on Database Security Patching from 12.1.0.1 onwards

Starting with Oracle Database version 12.1.0.1 , Oracle will only provide Patch Set Update (PSU) patches to meet the Critical Patch Update (CPU) program requirements for security patching. SPU (Security Patch Update) patches will no longer be available. Oracle has moved to this simplified model due to the popularity of the PSU patches. PSUs are Oracle’s preferred proactive patching vehicle since their inception in 2009.

Database Security Patching from 12.1.0.1 onwards (Doc ID 1581950.1)

 

Where to find last Patches for Database?

Use the Patch Assistant: Assistant: Download Reference for Oracle Database PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

 

What Patch to apply PSU, GI PSU,Proactive Bundle Patch, Bundle Patch (Windows 32bit & 64bit)?

When using the Patchset Assistant the assistant show below table:
In this case I search for last patch for 12.1.0.2.

PSU_Bundle_Patch

Understanding the Patch Nomenclature :
New Patch Nomenclature for Oracle Products (Doc ID 1430923.1)

Note: As of April 2016, the Database Patch for Engineered Systems and Database In-Memory has been renamed from “Bundle Patch (BP) ” to “Database Proactive Bundle Patch”.

Note: Windows Platform must use “Bundle Patch (Windows 32bit & 6bit)”.

Database patch content:

  • SPU contains only the CPU program security fixes
  • PSU contains the CPU program security fixes and additional high-impact/low-risk critical bug fixes
  • Proactive Bundle Patch (PBP) includes all PSU fixes along with fixes targeted at the specific Bundle Patch environment.

They are cumulatives,so  if you have a OH (12.1.0.2) in base release (i.e no fix)  and apply the last PSU or PBP it will fix all bugs from base release until current version of patch.

Where to apply each Patch?

  • PSU –  Can be applied on Database Servers, Client-Only and  Instant Client.
  • GI PSU – Can be applied on GI Home (Oracle Restart or Oracle Clusterware) in conjunction with RAC, RACOne,  Single Instance home, Client-Only and  Instant Client.
  • Proactive Bundle Patch – Can be applied on GI Home in conjunction with RAC, RACOne, or Single Instance home, Client-Only and  Instant Client.

 

An installation can only use one of the SPU, PSU or Proactive Bundle Patch patching methods.

 

How to choose between them?

The “Database Proactive Bundle Patch” requires a bit more testing than a Patch Set Update (PSU) as it delivers a larger set of fixes.

If you are installing a new fresh installation you should to apply Database Proactive Bundle Patch.

PSU is addressed to environments sensitive to changes, because it required less testing.

I have Applied “Database PSU” how to move to “Database Proactive Bundle Patch”? 

Moving from “Database PSU” to “Database Proactive Bundle Patch”

  • Back up your current setup
  • Fully rollback / deinstall “Database PSU”
    • If using OJVM PSU that is likely to require OJVM PSU to be rolled out too
  • Apply / install the latest “Database Proactive Bundle Patch”
  • Apply any interim patches also rolled out above (including OJVM PSU if that was installed)

Note from Oracle: It is not generally advisable to switch from “Database PSU” to “Database SPU” method.

The below note can clarify any doubt on this post.
Oracle Database – Overview of Database Patch Delivery Methods (Doc ID 1962125.1)

 

OPLAN Support

GI PSU and Proactive Bundle Patch are supported by OPlan.

OPlan is a utility that facilitates the patch installation process by providing you with step-by-step patching instructions specific to your environment.
In contrast to the traditional patching operation, applying a patch based on the README requires you to understand the target configuration and manually identify the patching commands relevant to your environment. OPlan eliminates the requirement of identifying the patching commands by automatically collecting the configuration information for the target, then generating instructions specific to the target configuration.

Oracle Software Patching with OPLAN (Doc ID 1306814.1)

Useful Notes:

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)

Frequently Asked Questions (FAQ): Patching Oracle Database Server (Doc ID 1446582.1)

12.1.0.2 Database Proactive Bundle Patches / Bundle Patches for Engineered Systems and DB In-Memory – List of Fixes in each Bundle (Doc ID 1937782.1)


ACFS-9459: ADVM/ACFS is not supported on this OS version: ‘unknown’

Oracle Grid Infrastructure is supported on Operation System, but ACFS isn’t. Why?

OLYMPUS DIGITAL CAMERA

The Grid Infrastructure is a Software that use OS Library, so Grid Infrastructure isn’t Kernel dependent but OS Package dependent.

ACFS is a module of Grid Infrastructure that have drivers installed/configured  into OS Kernel, then ACFS is Kernel Dependent.

An Oracle ACFS file system is installed as a dynamically loadable vendor operating system (OS) file system driver and tool set that is developed for each supported operating system platform. The driver is implemented as a Virtual File System (VFS) and processes all file and directory operations directed to a specific file system.

The ACFS  is composed by three components:

The Oracle ACFS, Oracle Kernel Services (OKS) and Oracle ADVM drivers, they are dynamically loaded when the Oracle ASM instance is started.

  • Oracle ACFS
    This driver processes all Oracle ACFS file and directory operations.
  • Oracle ADVM
    This driver provides block device services for Oracle ASM volume files that are used by file systems for creating file systems.
  • Oracle Kernel Services Driver (OKS)
    This driver provides portable driver services for memory allocation, synchronization primitives, and distributed locking services to Oracle ACFS and Oracle ADVM.

Before upgrade your OS Kernel you must place into your check list the ACFS Drivers, as you do with others OS Drives.

How to Oracle support the ACFS into future Kernels?

By releasing Patch Set Updates (PSUs), they are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule.

How it’s works?

Check this EXAMPLE below:

acfs_release_date

Note: This table is only a example where these kernel and dates are fictitious.

On image above we can see.

In jan/2016 the  GI 11.2.0.4 and 12.0.1.2 don’t need apply PSU because already have supported drives into base release, but 11.2.0.3 need PSU 11.2.0.3.2 to get supported drivers to kernel 2.6.32-100.

In Feb/2016 was released the kernel 2.6.65-30, so ACFS Deployment take some time until developers build new ACFS Drives. So, in FeB/2016 none release is supported for the kernel 2.6.65-30.

In Mar/2016 Oracle release the PSU where all ACFS versions was supported under that PSU.

With example above we can conclude: Does not matter what Grid Infrastructure base version  (such as 11.2.0.4 or 12.0.1.2 ) you are using,  what matter is, the Grid Infrastructure must have supported  ACFS Drivers for that Kernel.

Where to find Certification Matrix for ACFS ?

Oracle Support have a detailed MOS Note : ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

You need ALWAYS check above mos note before any kernel updates for environments that have ACFS Configured.

 


RHEL/OEL 7 – NTPD replaced by Chrony

Despite configuring NTP during the installation process, NTPD is not installed /configured/running after the installation completes.

date_time

New fresh installation of  RHEL/OEL 7 the default NTP Client is the CHRONY.

What is the CHRONY?
Chrony was introduced as new NTP client provided in the chrony package. Chrony does not provides all features available in old ntp client (ntp). So ntp is still provided due to compatibility.

During Oracle Grid Infrastructure Installation the runInstaller will   fail during  prerequisite  check due ntp not configured.

NTP not configured. Network Time Protocol (NTP)  - Failed 
PRVF-7590 : "ntpd" is not running on node "xxx"

Oracle recommend to use NTPD and disable CHRONYD.

Just follow step below to Install/Configure/Enable NTPD

1. Check Chronyd service

# systemctl status chronyd.service
● chronyd.service - NTP client/server
 Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled)
 Active: active (running) since Mon 2016-05-30 01:15:33 BRT; 5s ago
 Process: 19464 ExecStartPost=/usr/libexec/chrony-helper update-daemon (code=exited, status=0/SUCCESS)
 Process: 19456 ExecStart=/usr/sbin/chronyd $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 19459 (chronyd)
 CGroup: /system.slice/chronyd.service
 └─19459 /usr/sbin/chronyd

May 30 01:15:33 node1 systemd[1]: Starting NTP client/server...
May 30 01:15:33 node1 chronyd[19459]: chronyd version 2.1.1 starting (+CMDMON +NTP +REFCLOCK +RTC +PRIVDROP +DEBUG +ASYNCDNS +IPV6 +SECHASH)
May 30 01:15:33 node1 chronyd[19459]: Frequency -23.731 +/- 0.023 ppm read from /var/lib/chrony/drift
May 30 01:15:33 node1 systemd[1]: Started NTP client/server.

2. Stop and disable Chronyd service

# systemctl stop chronyd.service
# systemctl disable chronyd.service
Removed symlink /etc/systemd/system/multi-user.target.wants/chronyd.service.

3. Install ntpd package

# yum install ntp -y

4. Add “-x ” option into  the “/etc/sysconfig/ntpd” file.

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

5. Enable and Start NTPD

#systemctl enable ntpd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.


# systemctl start ntpd.service

# systemctl status ntpd.service
● ntpd.service - Network Time Service
 Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
 Active: active (running) since Mon 2016-05-30 01:23:09 BRT; 9s ago
 Process: 23048 ExecStart=/usr/sbin/ntpd -u ntp:ntp $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 23050 (ntpd)
 CGroup: /system.slice/ntpd.service
 └─23050 /usr/sbin/ntpd -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid

May 30 00:27:07 node1 ntpd[2829]: Listen normally on 10 virbr0 192.168.122.1 UDP 123
May 30 00:27:07 node1 ntpd[2829]: Listen normally on 11 lo ::1 UDP 123
May 30 00:27:07 node1 ntpd[2829]: Listen normally on 12 eno16777984 fe80::250:56ff:fe90:21e9 UDP 123
May 30 00:27:07 node1 ntpd[2829]: Listen normally on 13 eno33559296 fe80::250:56ff:fe90:d421 UDP 123
May 30 00:27:07 node1 ntpd[2829]: Listen normally on 14 eno50338560 fe80::250:56ff:fe90:f203 UDP 123
May 30 00:27:07 node1 ntpd[2829]: Listening on routing socket on fd #31 for interface updates
May 30 00:27:07 node1 systemd[1]: Started Network Time Service.
May 30 00:27:08 node1 ntpd[2829]: 0.0.0.0 c016 06 restart
May 30 00:27:08 node1 ntpd[2829]: 0.0.0.0 c012 02 freq_set ntpd 0.000 PPM
May 30 00:27:08 node1 ntpd[2829]: 0.0.0.0 c011 01 freq_not_set

Known issues that are solved using above procedure.

# ntpq -p
localhost: timed out, nothing received
***Request timed out
# ntpstat 
Unable to talk to NTP daemon.  Is it running? 

 

 

 


Long-Term RMAN Backups (Online Backup Without Keep All Logs)

102091053-short-term-long-term.1910x1000

Short-term backups takes periodic images of active data in order to provide a method of recovering data that have been deleted or destroyed.
These backups are retained only for a few days or weeks. The primary purpose of short-term backup is to recover recent data or if a media failure or disaster occurs, then you can use it to restore your backups and bring your data available.

Most of Backup System have two different setup for Short-Term and Long-Term backups.

Short-Term Backups are classic backup which have short retention and are stored on  pool of data active, often these data are always available on Tape Library or Disk.

Long-Term Backups are archival backups which have long retention and often are stored only on tapes, usually these tapes are off-site or in safes.

Will try explain in few lines the purpose of Long-Term backups.

You can back up the database on the first day of every year to satisfy a regulatory requirement and store the media offsite.
Years after you make the archival backup, you could restore and recover it to query the data as it appeared at the time of the backup. We have a Data Presevation.

Data preservation is related to data protection, but it serves a different purpose.
For example, you may need to preserve a copy of a database as it existed at the end of a business quarter.
This backup is not part of the disaster recovery strategy. The media to which these backups are written are often unavailable after the backup is complete.
You may send the tape into fire storage or ship a portable hard drive to a testing facility.

RMAN provides a convenient way to create a backup and exempt it from your backup retention policy. This type of backup is known as an archival backup.

Why this introduction about short-term or long-term backups?

I manage some environments and I see that some DBA fail to understand this difference and end up making incorrect configurations for archival backup and classic backups.

IMHO, the main villain is RDBMS 10.2 and earlier version, because there is no long-term backup concept when take online backup.

In 10.2  to take a archival backup we must shutdown database to not keep all archivelogs (many envs this is not possible), if we take online backup with long retention (KEEP LOGS) you need keep all archivelogs until first backup be obsolete. On this version there is no difference between a classic backup or archival backups. (in fact have diference, but it’s a slight difference)

So, because that many DBA still stuck on this concept of this version.

In 11.1 this changed, you can create a consistent archival backup without need keep all archivelogs.

When to use this rman command ( “backup database keep logs forever/ until sysdate + XXX”) on 11.1 version or above?
It should be only used if the DBA have a requirement to keep ALL changes of database forever or until some date and need recover ANY point-in-time for a database life. If you don’t need it, this setup is insane and increase cost and management of its environment.

Let’s start with some tests. I’m using rdbms 11.2.0.4.

How to take a Long-Term Backup in 11.1 or higher version.

Best practices:  3 important things.

  1. Use RESTORE POINT to easy the restore.RESTORE POINT clause that creates a normal restore point, which is a label for an SCN to which the backup must be recovered to be made consistent. The SCN is captured just after the datafile backups complete. RMAN resynchronizes restore points with the recovery catalog and maintains the restore points as long as the backup exists.
    More detail on documentation below.
    https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV89551
  2. Use non-default TAG this will easy  manage backup later.
  3. Use CHECK LOGICAL Option to avoid backup logical corrupted blocks.By default, RMAN does not check for logical corruption. If you specify CHECK LOGICAL on the BACKUP command, however, then RMAN tests data and index blocks for logical corruption, such as corruption of a row piece or index entry, and log them in the alert log located in the Automatic Diagnostic Repository (ADR). If you use RMAN with CHECK LOGICAL option when backing up or restoring files, then it detects all types of block corruption that are possible to detect.What a disappointment you try to restore a backup taken on last year and find out which there are  many logical blocks corrupted.

P.S: I always use CHECK LOGICAL for my all backups (short or long-term backups)

 

Performing a Long-Term Backup

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 18 15:56:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTDB (DBID=2633899005)
 connected to recovery catalog database

RMAN> run {
 2> ALLOCATE CHANNEL tape1 
    DEVICE TYPE 'SBT_TAPE' PARMS 
    'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
 3> BACKUP CHECK LOGICAL 
    FILESPERSET 10 
    FULL DATABASE 
    TAG 'MONTHLY_FULL_20160218' 
    FORMAT 'RMAN_MONTHLY_FULL_%d_%s_%p_%T_%c' 
    KEEP UNTIL TIME 'SYSDATE + 400' 
    RESTORE POINT BKP_MONTHLY_20160218_1556;
 4> RELEASE CHANNEL tape1;
 5> }
 6> exit
 allocated channel: tape1
 channel tape1: SID=241 device type=SBT_TAPE
 channel tape1: Data Protection for Oracle: version 5.5.2.0

Starting backup at Feb 18 2016 15:57:07
 current log archived

backup will be obsolete on date Mar 24 2017 15:57:15
 archived logs required to recover from this backup will be backed up
 channel tape1: starting compressed full datafile backup set
 channel tape1: specifying datafile(s) in backup set
 input datafile file number=00001 name=+TSM/testdb/datafile/system.501.901826545
 input datafile file number=00006 name=+TSM/testdb/datafile/users.487.901826545
 input datafile file number=00002 name=+TSM/testdb/datafile/sysaux.542.901826547
 input datafile file number=00003 name=+TSM/testdb/datafile/undotbs1.442.901826547
 input datafile file number=00004 name=+TSM/testdb/datafile/users.314.901826547
 input datafile file number=00005 name=+TSM/testdb/datafile/users.510.901826547
 channel tape1: starting piece 1 at Feb 18 2016 15:57:15
 channel tape1: finished piece 1 at Feb 18 2016 15:58:40
 piece handle=RMAN_MONTHLY_FULL_TESTDB_328_1_20160218_1 tag=MONTHLY_FULL_20160218 
 comment=API Version 2.0,MMS Version 5.5.2.0
 channel tape1: backup set complete, elapsed time: 00:01:25

backup will be obsolete on date Mar 24 2017 15:58:40
 archived logs required to recover from this backup will be backed up
 channel tape1: starting compressed full datafile backup set
 channel tape1: specifying datafile(s) in backup set
 including current SPFILE in backup set
 channel tape1: starting piece 1 at Feb 18 2016 15:58:41
 channel tape1: finished piece 1 at Feb 18 2016 15:58:42
 piece handle=RMAN_MONTHLY_FULL_TESTDB_329_1_20160218_1 tag=MONTHLY_FULL_20160218 
 comment=API Version 2.0,MMS Version 5.5.2.0
 channel tape1: backup set complete, elapsed time: 00:00:01
 current log archived
 backup will be obsolete on date Mar 24 2017 15:58:46
 archived logs required to recover from this backup will be backed up
 channel tape1: starting compressed archived log backup set
 channel tape1: specifying archived log(s) in backup set
 input archived log thread=1 sequence=91 RECID=830 STAMP=904147125
 channel tape1: starting piece 1 at Feb 18 2016 15:58:46
 channel tape1: finished piece 1 at Feb 18 2016 15:58:47
 piece handle=RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1 tag=MONTHLY_FULL_20160218 
 comment=API Version 2.0,MMS Version 5.5.2.0
 channel tape1: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date Mar 24 2017 15:58:47
 archived logs required to recover from this backup will be backed up
 channel tape1: starting compressed full datafile backup set
 channel tape1: specifying datafile(s) in backup set
 including current control file in backup set
 channel tape1: starting piece 1 at Feb 18 2016 15:58:49
 channel tape1: finished piece 1 at Feb 18 2016 15:58:50
 piece handle=RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1 tag=MONTHLY_FULL_20160218 
 comment=API Version 2.0,MMS Version 5.5.2.0
 channel tape1: backup set complete, elapsed time: 00:00:01
 Finished backup at Feb 18 2016 15:58:50

released channel: tape1

Recovery Manager complete.

Change Backup To UNAVAILABLE

If this backup will be off-line in Tape Library  then mark it as UNAVAILABLE to avoid RMAN try use it during normal restore operation. Normal Restore should use short-term backups which usually  is active on Tape Library.

As I used non-default tag it’s easy change it.

RMAN> CHANGE BACKUP TAG MONTHLY_FULL_20160218 UNAVAILABLE;

changed backup piece unavailable
 backup piece handle=RMAN_MONTHLY_FULL_TESTDB_328_1_20160218_1 RECID=311 STAMP=904147035
 changed backup piece unavailable
 backup piece handle=RMAN_MONTHLY_FULL_TESTDB_329_1_20160218_1 RECID=312 STAMP=904147121
 changed backup piece unavailable
 backup piece handle=RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1 RECID=313 STAMP=904147126
 changed backup piece unavailable
 backup piece handle=RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1 RECID=314 STAMP=904147129
 Changed 4 objects to UNAVAILABLE status

How to restore it?

RMAN> shutdown abort;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
 Oracle instance started

Total System Global Area 801701888 bytes

Fixed Size 2250648 bytes
 Variable Size 251660392 bytes
 Database Buffers 536870912 bytes
 Redo Buffers 10919936 bytes

RMAN> run {
 2> SET UNTIL RESTORE POINT BKP_MONTHLY_20160218_1556;
 3> RESTORE CONTROLFILE;
 4> STARTUP MOUNT;
 5> RESTORE DATABASE;
 6> RECOVER DATABASE;
 7> ALTER  DATABASE OPEN RESETLOGS;
 8> };

executing command: SET until clause

Starting restore at 18-02-2016 16:56:32
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: SID=225 device type=SBT_TAPE
 channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=233 device type=DISK

channel ORA_SBT_TAPE_1: starting datafile backup set restore
 channel ORA_SBT_TAPE_1: restoring control file
 channel ORA_SBT_TAPE_1: reading from backup piece RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1
 channel ORA_SBT_TAPE_1: piece handle=RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1 
 tag=MONTHLY_FULL_20160218
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
 output file name=+TSM/testdb/controlfile/current.518.901826451
 Finished restore at 18-02-2016 16:56:42

database is already started
 database mounted
 released channel: ORA_SBT_TAPE_1
 released channel: ORA_DISK_1

Starting restore at 18-02-2016 16:56:48
 Starting implicit crosscheck backup at 18-02-2016 16:56:48
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=225 device type=DISK
 Finished implicit crosscheck backup at 18-02-2016 16:56:49

Starting implicit crosscheck copy at 18-02-2016 16:56:49
 using channel ORA_DISK_1
 Finished implicit crosscheck copy at 18-02-2016 16:56:49

searching for all files in the recovery area
 cataloging files...
 no files cataloged

allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: SID=233 device type=SBT_TAPE
 channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0
 using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: starting datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 channel ORA_SBT_TAPE_1: restoring datafile 00001 to +TSM/testdb/datafile/system.501.901826545
 channel ORA_SBT_TAPE_1: restoring datafile 00002 to +TSM/testdb/datafile/sysaux.542.901826547
 channel ORA_SBT_TAPE_1: restoring datafile 00003 to +TSM/testdb/datafile/undotbs1.442.901826547
 channel ORA_SBT_TAPE_1: restoring datafile 00004 to +TSM/testdb/datafile/users.314.901826547
 channel ORA_SBT_TAPE_1: restoring datafile 00005 to +TSM/testdb/datafile/users.510.901826547
 channel ORA_SBT_TAPE_1: restoring datafile 00006 to +TSM/testdb/datafile/users.487.901826545
 channel ORA_SBT_TAPE_1: reading from backup piece RMAN_MONTHLY_FULL_TESTDB_328_1_20160218_1
 channel ORA_SBT_TAPE_1: piece handle=RMAN_MONTHLY_FULL_TESTDB_328_1_20160218_1 
 tag=MONTHLY_FULL_20160218
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:45
 Finished restore at 18-02-2016 16:58:43

Starting recover at 18-02-2016 16:58:44
 using channel ORA_SBT_TAPE_1
 using channel ORA_DISK_1

starting media recovery

channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=1 sequence=91
 channel ORA_SBT_TAPE_1: reading from backup piece RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1
 channel ORA_SBT_TAPE_1: piece handle=RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1 
 tag=MONTHLY_FULL_20160218
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
 archived log file name=+TSM/testdb/archivelog/2016_02_18/thread_1_seq_91.267.904150729 thread=1 sequence=91
 channel default: deleting archived log(s)
 archived log file name=+TSM/testdb/archivelog/2016_02_18/thread_1_seq_91.267.904150729 RECID=834 STAMP=904150729
 media recovery complete, elapsed time: 00:00:02
 Finished recover at 18-02-2016 16:58:51

database opened
 new incarnation of database registered in recovery catalog
 starting full resync of recovery catalog
 full resync complete

How to migrate old fashion to new fashion to make long-term backup

Many environments inherit old backup configurations  of old DBAs, then is very common to see set up long-term backup environments using  “backup  database keep logs;”.

You can ask: I don’t want to keep all logs and  want to keep only my backups which is not obsolete? It’s possible?

Yes, There is a  workaround to avoid RMAN still keeping all logs forever, if you are using 11.1 or above.

AGAIN, if you are using 10.2 or later version you CAN’T do this below.

 

What you need to do.

First of all you need re-configure your scripts to  long-term backups by using restore point from now.

What to do with old backups.

We can leave this the old setup until the last backup take with “KEEP LOGS” retention be obsolete. All Archivelogs Backups will be obsolete and RMAN will automaticaly deleted it. (recommended)

P.S: If you have a Long Term backup with “KEEP FOREVER”  the Archivelogs Backups never will be obsolete.

There is a workaround (or bug)  to delete all archivelogs and make a “self-contained” backup even using “keep logs”. This can be dangerous if not configured properly,  RMAN will delete all archived logs that not satisfy retention policy. That’s mean, if it go wrong you will have a backup of database without archivelog to recovery it. (i.e you don’t will be able to recovery that backup).

Steps:

  • Find on RMAN Catalog all  Backups with clause keep.
  • Find all Backup of Archivelogs between 24h before and 24h after the backup with  clause “Keep” was taken.
    • e.g  backup was finished at 02/01/2016 10:00, find all backup of archivelogs between 02/01/2016 10:00 and 03/01/2016 10:00
  • Change theses backups (archivelogs, controlfile and datafile) to UNAVAILABLE
  • After change these backups to UNAVAILABLE all Backups of Archivelogs will be orphan and will be obsolete.

 

Eg.

(dd-mm-yyyy hh24:mi)

Backup 1 ( 01/01/2016 12:00)

Backup 2 (01/02/2016 12:00)

Backup 3 (01/03/2016 12:00)

Backup 4 (01/04/2016 12:00)

 

 

Scenario 1:

Change only  Backup 1  and Archivelogs to Restore this Backup to unavailable.

  • All archivelogs that does not have status UNAVAILABLE  until 01/02/2016 12:00 will be obsolete and will deleted on next “delete obsolete”.
  • All futures backups of archivelog will be kept until Backup 2 be obsolete.

Scenario 2:

Change only Backup 2  and Archivelogs to Restore this Backup to unavailable.

  • All archivelogs will be kept.

Scenario 3:

Change only Backup 1,2,3 and 4 to UNAVAILABLE.

  • All archivelogs will be obsolete, if run “delete obsolete” you can restore Backup 1,2,3 or 4, BUT WILL NOT BE ABLE TO RECOVERY IT since there is no Archivelog.

Scenario 4:

Change to unavailable Backup 1,2,3,4   and archivelogs to restore these database.

  • All archivelogs with Status AVAILABLE will be obsolete and all archivelogs will obey default retention policy.

 

It seens to me a bug, because Oracle just ignore retention policy of archivelog log, if Backup with Keep clause is UNAVAILABLE.

 

 

 

 


Starting with Oracle In-Memory (aka IM) new Feature


I recommend that DBA be aware of this feature, because this feature will really bring a HUGE difference in performance matter to our databases.

Note: This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Good to Know

  • Is a Option of Oracle Enterprise Edition (12.1.0.2 and above). - Additional license required
  • It is Not in Memory Database - it's an accelerator to our current database
  • It is Not Column Store Database - it allows keeping some of our data in column store which is non-persistent
  • It has nothing to do with Oracle Times-Ten or Oracle Coherence
  • No additional hardware requirement, except additional OS RAM available

Price: Buy it

 

I'm posting useful videos links from Oracle Learning Library.

 

White paper: When to Use Oracle Database In-Memory (PDF)

White paper: Oracle Database In-Memory Advisor Best Practices (PDF)

Blog Official of In-Memory Feature