Skip navigation.

Pythian Group

Syndicate content
Official Pythian Blog - Love Your Data
Updated: 4 hours 16 min ago

When RMAN Validate Creates New Files

Wed, 2014-03-19 07:41

While doing some testing I found something happening with RMAN that was unexpected.

After making an RMAN backup, I would run the VALIDATE RECOVERY FILES command.

When it completed I found there were twice as many backup files as when I started.

Please note that this is Oracle 11.2.0.3 – that will be important later on.

Here is the list of current backup files:

RMAN crosscheck backup;
 using channel ORA_DISK_1
 crosschecked backup piece: found to be 'AVAILABLE'
 backup piece handle=/u01/app/oracle/rman/orcl-rman-db-3ip3dlau_1_1.bkup RECID=112 STAMP=842454367
 crosschecked backup piece: found to be 'AVAILABLE'
 backup piece handle=/u01/app/oracle/rman/orcl-rman-db-3jp3dlcv_1_1.bkup RECID=113 STAMP=842454432
 crosschecked backup piece: found to be 'AVAILABLE'
 backup piece handle=/u01/app/oracle/rman/orcl-rman-arch-3lp3dlgs_1_1.bkup RECID=114 STAMP=842454556
 Crosschecked 3 objects

Following are some pertinent parameters:

12:46:52 SYS@js01 AS SYSDBA show parameter db_recovery_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fra
db_recovery_file_dest_size	     big integer 4G

12:47:00 SYS@js01 AS SYSDBA show parameter log_archive_dest_1

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------------------------------
log_archive_dest_1		     string	 LOCATION=USE_DB_RECOVERY_FILE_DEST

Now see what happens when VALIDATE RECOVERY FILES is run.
Listings may be edited for brevity.

RMAN validate recovery files;

Starting validate at 18-MAR-14
using channel ORA_DISK_1
specification does not match any datafile copy in the repository
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=1212 RECID=581 STAMP=842454820
input archived log thread=1 sequence=1213 RECID=582 STAMP=842454821
...
input archived log thread=1 sequence=1232 RECID=601 STAMP=842531265
input archived log thread=1 sequence=1233 RECID=602 STAMP=842531265
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    1212    OK     0              97494           /u01/app/oracle/fra/JS01/archivelog/2014_03_17/o1_mf_1_1212_9lgwwng0_.arc
1    1213    OK     0              97494           /u01/app/oracle/fra/JS01/archivelog/2014_03_17/o1_mf_1_1213_9lgwwnqx_.arc
...
1    1232    OK     0              13              /u01/app/oracle/fra/JS01/archivelog/2014_03_18/o1_mf_1_1232_9lk7kkvh_.arc
1    1233    OK     0              1               /u01/app/oracle/fra/JS01/archivelog/2014_03_18/o1_mf_1_1233_9lk7kkww_.arc
channel ORA_DISK_1: input backup set: count=114, stamp=842454366, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece /u01/app/oracle/rman/orcl-rman-db-3ip3dlau_1_1.bkup
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140317T150606_9lk8nfr3_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:35
channel ORA_DISK_1: input backup set: count=115, stamp=842454431, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece /u01/app/oracle/rman/orcl-rman-db-3jp3dlcv_1_1.bkup
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140317T150606_9lk8ojtw_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=117, stamp=842454556, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece /u01/app/oracle/rman/orcl-rman-arch-3lp3dlgs_1_1.bkup
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140317T150915_9lk8okwy_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:03
Finished validate at 18-MAR-14

Notice that for each existing backup file an exact copy was made.
This was verified by using md5sum to compare the file check sums.

== as shown by md5sum, these are exact duplicates

[oracle@dev ]$ md5sum /u01/app/oracle/rman/orcl-rman-db-3ip3dlau_1_1.bkup /u01/app/oracle/rman/orcl-rman-db-3jp3dlcv_1_1.bkup /u01/app/oracle/rman/orcl-rman-arch-3lp3dlgs_1_1.bkup
21b1c12d47216ce8ac2413e8c7e3fc6e  /u01/app/oracle/rman/orcl-rman-db-3ip3dlau_1_1.bkup
7524091d41785c793ff7f3f504b76082  /u01/app/oracle/rman/orcl-rman-db-3jp3dlcv_1_1.bkup
974bb354db9eb49770991334c891add5  /u01/app/oracle/rman/orcl-rman-arch-3lp3dlgs_1_1.bkup

[oracle@dev ]$ md5sum /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140317T150606_9lk8nfr3_.bkp /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140317T150606_9lk8ojtw_.bkp /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140317T150915_9lk8okwy_.bkp
21b1c12d47216ce8ac2413e8c7e3fc6e  /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140317T150606_9lk8nfr3_.bkp
7524091d41785c793ff7f3f504b76082  /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140317T150606_9lk8ojtw_.bkp
974bb354db9eb49770991334c891add5  /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140317T150915_9lk8okwy_.bkp

It then occurred to me that maybe this behavior was for some reason due to creating backups outside the FRA, and Oracle for some reason wanted a copy of each file in the FRA. If so this would probably be a bug, but I thought it interesting enough to run a test.

The following shows that all previous backups were removed, new ones created, as well as space consumed in the FRA.

== Delete all backups, and create backups in FRA only

RMAN list backup;
specification does not match any backup in the repository

RMAN crosscheck backup;
using channel ORA_DISK_1
specification does not match any backup in the repository

====== create new backups in FRA

RMAN backup database;

Starting backup at 18-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/JS01/datafile/o1_mf_users_8g69rzg7_.dbf
input datafile file number=00003 name=/u01/oradata/JS01/datafile/o1_mf_undotbs1_8g69rgd1_.dbf
input datafile file number=00002 name=/u01/oradata/JS01/datafile/o1_mf_sysaux_8g69qxt0_.dbf
input datafile file number=00001 name=/u01/oradata/JS01/datafile/o1_mf_system_8g69qb0g_.dbf
input datafile file number=00005 name=/u01/oradata/JS01/datafile/o1_mf_atg_data_8hk7kc7f_.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk90z0k_.bkp tag=TAG20140318T125302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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 18-MAR-14
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140318T125302_9lk91s40_.bkp tag=TAG20140318T125302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-MAR-14

RMAN backup archivelog all delete input;

Starting backup at 18-MAR-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1212 RECID=581 STAMP=842454820
input archived log thread=1 sequence=1213 RECID=582 STAMP=842454821
...
input archived log thread=1 sequence=1233 RECID=602 STAMP=842531265
input archived log thread=1 sequence=1234 RECID=603 STAMP=842532824
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140318T125344_9lk928t8_.bkp tag=TAG20140318T125344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/JS01/archivelog/2014_03_17/o1_mf_1_1212_9lgwwng0_.arc RECID=581 STAMP=842454820
archived log file name=/u01/app/oracle/fra/JS01/archivelog/2014_03_17/o1_mf_1_1213_9lgwwnqx_.arc RECID=582 STAMP=842454821
...
archived log file name=/u01/app/oracle/fra/JS01/archivelog/2014_03_18/o1_mf_1_1233_9lk7kkww_.arc RECID=602 STAMP=842531265
archived log file name=/u01/app/oracle/fra/JS01/archivelog/2014_03_18/o1_mf_1_1234_9lk928kg_.arc RECID=603 STAMP=842532824
Finished backup at 18-MAR-14

RMAN crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk90z0k_.bkp RECID=145 STAMP=842532783
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140318T125302_9lk91s40_.bkp RECID=146 STAMP=842532809
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140318T125344_9lk928t8_.bkp RECID=147 STAMP=842532824
Crosschecked 3 objects

12:54:40 SYS@js01 AS SYSDBA @fra

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                      35.24                         0               3
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

Again there are three backup files, this time in the FRA. The files are using 35% of the FRA space.

Let’s run another VALIDATE RECOVERY FILES and find out what happens.


RMAN validate recovery files;

Starting validate at 18-MAR-14
using channel ORA_DISK_1
specification does not match any archived log in the repository
specification does not match any datafile copy in the repository
channel ORA_DISK_1: input backup set: count=140, stamp=842532782, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk90z0k_.bkp
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk955rv_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:15
channel ORA_DISK_1: input backup set: count=141, stamp=842532808, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140318T125302_9lk91s40_.bkp
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140318T125302_9lk95nvg_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=142, stamp=842532824, piece=1
channel ORA_DISK_1: starting piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140318T125344_9lk928t8_.bkp
piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140318T125344_9lk95oxv_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-MAR-14
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:03
Finished validate at 18-MAR-14

RMAN crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk90z0k_.bkp RECID=145 STAMP=842532783
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk955rv_.bkp RECID=148 STAMP=842532917
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140318T125302_9lk91s40_.bkp RECID=146 STAMP=842532809
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_ncsnf_TAG20140318T125302_9lk95nvg_.bkp RECID=149 STAMP=842532932
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140318T125344_9lk928t8_.bkp RECID=147 STAMP=842532824
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_annnn_TAG20140318T125344_9lk95oxv_.bkp RECID=150 STAMP=842532933
Crosschecked 6 objects

12:54:41 SYS@js01 AS SYSDBA @fra

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                      70.47                     35.24               6
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

That is pretty clear – there are duplicates of each file. This is also shown by the FRA now being 70% consumed by backup pieces, whereas previously on 35% of the FRA was used.

This seems like a bug, and a brief search of My Oracle Support finds this relevant document:

Bug 14248496 RMAN ‘validate recovery files’ creates a piece copy for every execution

This fits the situation pretty well, and the version of this database, 11.2.0.3, is one of the affected versions.
As per the doc this bug is fixed in 11.2.0.4

The next step of course is to try this same operation in 11.2.0.4.
This is also a Linux database running on Linux 6 – the only difference is that the database version is 11.2.0.4.

RMAN crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/rman/rman-db-02p3ggdi_1_1.bkup RECID=1 STAMP=842547637
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/rman/rman-db-03p3gggk_1_1.bkup RECID=2 STAMP=842547732
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/rman/rman-db-04p3ggjt_1_1.bkup RECID=3 STAMP=842547838
Crosschecked 3 objects

RMAN validate recovery files;

Starting validate at 18-MAR-14
using channel ORA_DISK_1
specification does not match any archived log in the repository
specification does not match any datafile copy in the repository
skipping backup sets; RECOVERY FILES, RECOVERY AREA or DB_RECOVERY_FILE_DEST option cannot validate backup set
Finished validate at 18-MAR-14

That wasn’t exactly promising – the VALIDATE RECOVERY FILES now just exits with a message that backup sets cannot be validated with this command.
Apparently ‘fixing’ the bug was just a matter of disabling this bit of functionality.
This is at odds with the Oracle 11g Documentation for RMAN VALIDATE
From the section “RECOVERY FILES”

Validates all recovery files on disk, whether they are stored in the fast recovery area or other locations on disk. Recovery files include full and incremental backup sets, control file autobackups, archived redo log files, and data file copies. Flashback logs are not validated.

The Oracle 12c Documentation for 12c RMAN VALIDATE says the same thing, that is that backup sets are included in the files to be validated.

Clearly the intent seems to have been for this to work with VALIDATE RECOVERY FILES, but for some reason the fix was simply to disable the functionality.

So, what can you use instead?

Now the VALIDATE BACKUPSET command must be used to validate the backups. This is not nearly as convenient as simply issuing the VALIDATE RECOVERY FILES command, as VALIDATE BACKUPSET takes a mandatory argument, which is the primary key of the backup set.

The documentation recommends using the LIST BACKUPSET command, but this is rather inconvenient as the keys must be parsed from report text as seen.

RMAN list backupset;

List of Backup Sets
==================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:04     18-MAR-14
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20140318T170034
        Piece Name: /u01/app/oracle/rman/rman-db-02p3ggdi_1_1.bkup
  SPFILE Included: Modification time: 18-MAR-14
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1014016      Ckp time: 18-MAR-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.07G      DISK        00:01:36     18-MAR-14
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20140318T170212
        Piece Name: /u01/app/oracle/rman/rman-db-03p3gggk_1_1.bkup
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1014604    18-MAR-14 /u02/app/oracle/oradata/orcl/system01.dbf
  2       Full 1014604    18-MAR-14 /u02/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 1014604    18-MAR-14 /u02/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 1014604    18-MAR-14 /u02/app/oracle/oradata/orcl/users01.dbf
  5       Full 1014604    18-MAR-14 /u02/app/oracle/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:02     18-MAR-14
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20140318T170212
        Piece Name: /u01/app/oracle/rman/rman-db-04p3ggjt_1_1.bkup
  SPFILE Included: Modification time: 18-MAR-14
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1014639      Ckp time: 18-MAR-14

This is fine for manually validating just a few files, but is really not a workable solution for programmatically validating backup sets. Fortunately there is a better method – just use the v$backup_set_details view.

  1  select session_key, session_recid, session_stamp, bs_key, recid
  2  from v$backup_set_details
  3* order by session_key
15:58:37 dev.jks.com - jkstill@js01 SQL /

SESSION_KEY SESSION_RECID SESSION_STAMP     BS_KEY	RECID
----------- ------------- ------------- ---------- ----------
	469	      469     842532214        106	  106
	469	      469     842532214        107	  107
	469	      469     842532214        105	  105

3 rows selected.

RMAN> validate backupset 105;

Starting validate at 18-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk90z0k_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/JS01/backupset/2014_03_18/o1_mf_nnndf_TAG20140318T125302_9lk90z0k_.bkp tag=TAG20140318T125302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
Finished validate at 18-MAR-14

-- the same was done for BS_KEY values 106 and 107

It is usually a good idea to investigate when something is found to work differently than expected.
It was certainly beneficial in this case, as I was developing code on 11.2.0.3 that would later run on 11.2.0.4.
While that bit of code would not work as expected on 11.2.0.4, it would also not cause an error, and probably not be noticed until it caused a recovery problem.

Using VALIDATE BACKUPSET is a workable solution, but not nearly as convenient as using VALIDATE RECOVERY FILES.
Perhaps there will be a fix for it in future releases.

Categories: DBA Blogs

Log Buffer #363, A Carnival of the Vanities for DBAs

Fri, 2014-03-14 07:52

This Log Buffer Editions encompasses the wide arena of Oracle, SQL Server, and MySQL databases and what bloggers are saying about them.

Oracle:

David Peake recently announced an updated APEX 5.x Statement of Direction.

Finished reading 12c Concepts manual.

What MLB Season Scheduling Means to Your Supply Chain.

SQL for date ranges, gaps and overlaps.

Mandated Third Party Static Analysis: Bad Public Policy, Bad Security.

SQL Server:

If you need to architect a multi-tenant Analysis Services project, what are your options?

With the right tools, it is possible to rapidly deploy upgraded databases.

Using the Statistical Program R for Running Statistics .

Why I Developed SQL Code Guard: A Fairy Tale from a DBA.

Adding SQL Server AlwaysOn to existing Failover Clusters.

MySQL:

Ghosts of MySQL past, part 8.1: Five Years.

Insert benchmark for InnoDB, MongoDB and TokuMX and flash storage.

Database benchmarks are hard but not useless.

MySQL Workbench 6.1: Performance Schema Reports.

How to use the ClusterControl REST API to automate your Database Cluster.

Categories: DBA Blogs

The 5 Worst Things About Working at Pythian

Thu, 2014-03-13 08:12

Have you ever seen an employee post negatively about their company using his or her real name? If not, then you are about to for the first time — you can count that as number 1 out of the 5 worst things about working at Pythian. There are several great reasons to work at Pythian, but if I write about those things, many would brush it aside as unctuous. I think people want to know the dirt, and I’m here to tell you about it. Let’s look at the remaining 4 worst things about working at Pythian:

2. Like many other companies, Pythian encourages and pays for its employees to attend and present at technical conferences. But how many companies spoil their employees by allowing them to enjoy them at the same time?  For instance, during conferences Pythian provides quality opportunities to socialize and network.

3. If you work at Pythian, then you are very good at what you do, which means you are in big trouble. Why? Pythian will make sure you remain with them even if you switch continents. Here I am—still with Pythian— even after moving from Asia to Australia.

4. When was the last time your boss told you to take a vacation? Not only do my bosses at Pythian insist I take time off, they help me plan my holiday. In a previous job, I worked five straight years without a break — not even statuary holidays let alone a vacation, and nobody noticed. I admit, I’m a terrible workaholic and I brought that habit with me to Pythian. Now I have my boss and my family chalking out the days I should take off.

5. This last one is the worst… I call it Pythian’s zero-gravity culture. There is nothing at Pythian that pulls you down, and nothing that holds you back. Nothing prevents you from being innovative, making changes, and suggesting improvements. You are free to let your inner force propel you.

Some may argue that none of these points are actually negative. Rather, to some they are a dream come true. However, I consider them problematic because now there is no way I could work anywhere else. I could look for something better, or at least similar, but the truth is that there is no place like Pythian.

Categories: DBA Blogs

What is the difference between logical and physical corruption in Oracle?

Wed, 2014-03-12 07:49

When we talk about logical corruption, there are two different failure states that fall under this label:

  1. Accidental or incorrect modification of application data by a user or application.

    In this scenario, a user or application, either by misadventure or resulting from an application bug, changes data in a database to incorrect or inappropriate values. An example would be an engineer who performs an update, but forgets to formulate the predicate such that it updates only a single record, and instead accidentally updates (and commits) changes to thousands of records. When we perform an assessment of a client’s systems, we look carefully at how the client is managing retention of database undo data, archived redo logs and the recycle bin. Many clients assume that physical backups serve all aspects of recoverability for Oracle. On the contrary, effective management of these components can greatly reduce the complexity, RPO and RTO in repairing this type of fault.

  2. Logical (and physical) corruption of data blocks. Block corruptions come in two types:

    Physical corruptions (media corrupt blocks) are blocks that have sustained obvious physical damage. When Oracle detects an inconsistency between the CSN in the block header and the CSN in the block footer, or the expected header and footer structures are not present or are mangled, then the Oracle session raises an exception upon read of the block (ORA-01578: ORACLE data block corrupted…). The call to Oracle fails, and the exception is written to the Oracle alert log and trace files. Physical corruptions are generally the result of infrastructure problems, and can be introduced in a variety of ways. Some possible sources of physical corruption are storage array cache corruption, array firmware bugs, filesystem bugs and array controller battery failure combined with power outage. One can imagine at least a dozen other possible sources of such corruption. Physically corrupt blocks can be repaired using Oracle Recovery Manager’s BLOCKRECOVER command. This operation restores and recovers the block in place in the file without interrupting any other sessions operating against the database.

    Logically corrupt blocks are blocks that have good header and footer CSNs, but that have some other kind of internal inconsistency. For instance, one of the block header structures, which tracks the number of locks associated with rows in the block, differs from the actual number of locks present. Another example would be if the header information on available space differs from the true available space on the block. Upon encountering these types of faults, the calling session generally will raise ORA-00600 (“internal error”) with additional arguments that allow us to diagnose the specific type of defect, and the call will fail. The exception will be written to the alert log and trace files. Like physical corruption, there are a wide range of possible ways that the fault could have been introduced, including all of the ways listed above for physical corruption. However, logically corrupt blocks are much more likely to have been introduced as a result of a failure in the Oracle software, or as a result of an Oracle bug or cache corruption.

    By default, Oracle has features that seek to perform sanity checks on blocks before they are written. However, for highly risk-averse enterprises, additional checks, including checks for logical inconsistencies and block checksum verification can be enabled. These features consume additional resources, so should be used judiciously.

Categories: DBA Blogs

Log Buffer #362, A Carnival of the Vanities for DBAs

Fri, 2014-03-07 09:11

This Log Buffer Editions rounds up, yet again, some cool blog posts from across the planet. Sit back, relax and enjoy.

Oracle:

Marc has completed review of enhancement of SQL regarding RMAN in the 12c.

This weekend we have finalized latest update for our ADF runtime performance audit tool – Red Samurai Performance Audit Tool v 2.4.

This isn’t a SQL Developer post per-se, however if you are using the Oracle Developer Day VirtualBox 12c image, you will find this useful.

aud$ and ASSM – a good combination.

Why You Don’t Want to Code Validation in Before Commit?

SQL Server:

Basit A. Farooq talks about Different approaches of counting number of rows in a table.

The Top 5 SQL Server Features You Aren’t Using And Why.

A CLR Application: Deleting Three Billion Rows.

The simple query to find the database backup status The select statment consists of ServerName dbname BackupStartDate BackupFinishDate BackupAge Size status Type The query will only run from sql 2005 and later version.

Characters you should never ever use in an object or schema name!

MySQL:

Migration of MTR suites to use InnoDB.

50 tips to boost MySQL Performance Webinar follow up .

MySQL Workbench is a great tool when someone asks you to look at their schemas.

MySQL for Excel Main Features Video.

Many-table joins in MySQL 5.6.

Categories: DBA Blogs

Pythian Raises $6 Million to Help Retailers Eliminate Data Security Breaches

Tue, 2014-03-04 15:46

Pythian announced today that it raised $6 million to expand the market reach of Adminiscope, its data security solution, to help retailers eliminate data security breaches. As global corporations continue to collect, store, and monetize massive amounts of sensitive data, they’re increasingly turning to Pythian to secure their infrastructures, especially from internal threats. Investors include the Business Development Bank of Canada (BDC) and Royal Bank of Canada (RBC).

“It’s clear there’s an appetite out in the marketplace for greater data security measures among all organizations, especially retailers who have to protect sensitive customer financial information,” said Paul Vallée, Founder and Executive Chairman at Pythian. “The recent challenges continue to underscore the need for solutions like Adminiscope, and strengthen our resolve to ensure our clients operate with the highest level of security and accountability. Our technology offers organizations and their customers unwavering confidence in their data’s security. With this investment, we will bring our solution to more retailers large and small, and help them increase consumer confidence and protect their brand.”

Adminiscope, a software-as-a-service (SaaS) privileged session management solution, secures access to enterprise systems, cloud infrastructures, databases, servers, and applications, and enables stakeholders to monitor and record in real time all privileged activity performed across an IT infrastructure. Database Trends and Applications magazine recently named Adminiscope to its list of Trend-Setting Products for 2014 in the data management and analysis space.

See the full press release here.

Categories: DBA Blogs

Effective Customer Communications

Mon, 2014-03-03 15:44

How well do you communicate with your customers? Are you listening, does your customer hear what you are saying? Every day we interact with our customers, team mates, colleagues and managers. Is your message getting delivered?

1. Actively listen.

It is easy to be on a call, but no one can see you “nod”. Make sure to acknowledge the person by replying with “I see” or paraphrase what they said. Side benefit: you look impressive!

2. Ask targeted questions.

Once you’ve practiced #1, you can now ask intelligent, pointed questions. Consider what your customer said, and more importantly what they didn’t say.

3. Show respect.

Respect your audience. Stop talking, try not to interrupt and focus. Avoid distractions such as typing on your computer or checking email. Be in the moment. When you demonstrate respect for your audience you show them you respect yourself as well as them!

4. Tell the truth.

“The whole truth, and nothing but the truth…” Are you on a call with a very upset customer because the DB crashed, and we were at fault? Escalate the call to your manager, and then listen. If we are at fault, we’ll always admit it. It’s counterproductive to try to shift blame. Even in times of stress, try not to lose sight of that fact that we’re in the solutions business. The customer can handle the truth, and they will respect you more for delivering it.

5. Understand what your customer values.

What systems or DBs are most important to your customer? Are they cost driven? React appropriately.

6. Be candid.

Be straight forward — don’t cover things up. State facts and avoid excuses. This, in turn, builds trust and a sense of partnership.

7. Be consistent.

“Say what you mean, and mean what you say.” Nothing could be truer when dealing with customers. Enough said.

8. Dedicate yourself.

Relationships aren’t built overnight. Dedicate yourself to the process, and you will reap the benefits in the long term. Patience is key.

9. Ask for feedback.

How do we get better if we don’t know where we need to improve? Asking for feedback will open the doors of honesty, so be open to the response that you’ve requested.

10. Be Persistent.

Still not sure on the requirements? Seek clarification by rephrasing your questions and using paraphrasing. Before the end of the conversation, be sure that you are clear on next steps. Ask until you know.

11. Build rapport.

Find common ground and let the customer know you can relate. Compliment them and focus on areas of agreement. They have lives outside of the office, so try get to know them.

12. Smile.

When you smile while you’re talking, your customer can hear it in your voice.

13. You’re an expert — act like one.

Don’t undermine your expertise by asking questions that can be answered internally. Write a note and ask your team.

14. Be flexible.

Be creative in finding solutions. The only limits are the ones we place on ourselves by thinking small. Customers needs change, we need to change and adapt with it. Don’t get stuck in the past.

15. Maintain constant communication.

Don’t restrict yourself to only talking to your customer when there is bad news. Share the good news, too!

16. Be Careful What You Say.

Customers often take things literally. Avoid words such as outage or crash, etc. Continue to be honest, but these words and ones like them trigger panic. Just use different ones.

Effective communication takes work and patience. Commit yourself to improving every day, not just with your customers but with your peers, managers & even your family! 

What do you think?  Have something to add, let me know what works for you!

Please note: Not all of these ideas were my own, but some are. Still I must give credit to that class I took called “Encouraging Upward Communication “. Author/teacher unknown.

Categories: DBA Blogs

Automating DataPump Export

Mon, 2014-03-03 09:20

What’s the most elaborate thing you have done with DataPump?

So there I was, given the requirement to export multiple partitions for multiple tables where each partition has its own dump file having the format “tablename_partitionanme.dmp”, pondering how this can be done efficiently.

With the following metadata and requirements, what approach would you take?

If you are curious about the I approach I used, then read on.

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MDINH                          A_TAB                          P001
MDINH                          A_TAB                          P002
MDINH                          A_TAB                          P003
MDINH                          A_TAB                          P004
MDINH                          A_TAB                          P005
MDINH                          B_TAB                          P001
MDINH                          B_TAB                          P002
MDINH                          B_TAB                          P003
MDINH                          B_TAB                          P004
MDINH                          B_TAB                          P005
Here’s the demo:
$ nohup sqlplus "/ as sysdba" @exp_api.sql > exp_api.log 2>&1 &

$ cat exp_api.log
nohup: ignoring input

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 26 20:28:07 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@db01):PRIMARY> -- DataPump Export (EXPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31641 ORA-27054 ORA-27037 When The Dump File Is On NFS Mount Point (Doc ID 1518979.1)
ARROW:(SYS@db01):PRIMARY> -- Work around for the above mentioned error
ARROW:(SYS@db01):PRIMARY> alter system set events '10298 trace name context forever, level 32';

System altered.

Elapsed: 00:00:00.00
ARROW:(SYS@db01):PRIMARY> declare
  2      h1 number;
  3      dir_name varchar2(30);
  4  begin
  5      dir_name := 'DPDIR';
  6      for x in (
  7          select table_owner, table_name, partition_name
  8          from   dba_tab_partitions
  9          where  table_owner = 'MDINH' and table_name in ('A_TAB','B_TAB') and regexp_like(partition_name,'[0-4]$')
 10          order  by table_owner, table_name, partition_position
 11      ) loop
 12
 13          h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE');
 14
 15          dbms_datapump.add_file (
 16              handle    => h1,
 17              filename  => x.table_name||'_'||x.partition_name||'.dmp',
 18              reusefile => 1,
 19              directory => dir_name,
 20              filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 21
 22          dbms_datapump.add_file (
 23              handle    => h1,
 24              filename  => 'exp_'||x.table_name||'_'||x.partition_name||'.log',
 25              directory => dir_name,
 26              filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 27
 28          dbms_datapump.set_parameter (
 29              handle => h1,
 30              name   => 'INCLUDE_METADATA',
 31              value  => 0);
 32
 33          dbms_datapump.metadata_filter (
 34              handle => h1,
 35              name   => 'SCHEMA_EXPR',
 36              value  => 'IN ('''||x.table_owner||''')');
 37
 38          dbms_datapump.metadata_filter (
 39              handle => h1,
 40              name   => 'NAME_EXPR',
 41              value  => 'IN ('''||x.table_name||''')');
 42
 43          dbms_datapump.data_filter (
 44              handle      => h1,
 45              name        => 'PARTITION_LIST',
 46              value       => x.partition_name,
 47              table_name  => x.table_name,
 48              schema_name => x.table_owner);
 49
 50          dbms_datapump.start_job (handle => h1);
 51          dbms_datapump.detach (handle => h1);
 52      end loop;
 53  end;
 54  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.92
ARROW:(SYS@db01):PRIMARY> alter system set events '10298 trace name context off';

System altered.

Elapsed: 00:00:00.00
ARROW:(SYS@db01):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Review export log:
$ ls -l exp*.log-rw-r--r--. 1 oracle oinstall 2888 Feb 26 20:28 exp_api.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P001.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P002.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P003.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P004.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P001.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P002.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P003.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P004.log
Review export dump:
$ ls -l *.dmp
-rw-r-----. 1 oracle oinstall   90112 Feb 26 20:28 A_TAB_P001.dmp
-rw-r-----. 1 oracle oinstall   98304 Feb 26 20:28 A_TAB_P002.dmp
-rw-r-----. 1 oracle oinstall  188416 Feb 26 20:28 A_TAB_P003.dmp
-rw-r-----. 1 oracle oinstall 1069056 Feb 26 20:28 A_TAB_P004.dmp
-rw-r-----. 1 oracle oinstall   90112 Feb 26 20:28 B_TAB_P001.dmp
-rw-r-----. 1 oracle oinstall   98304 Feb 26 20:28 B_TAB_P002.dmp
-rw-r-----. 1 oracle oinstall  188416 Feb 26 20:28 B_TAB_P003.dmp
-rw-r-----. 1 oracle oinstall 1069056 Feb 26 20:28 B_TAB_P004.dmp
Review job status:
$ grep "successfully completed" exp*.log
exp_api.log:PL/SQL procedure successfully completed.
exp_A_TAB_P001.log:Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 26 20:28:09 2014 elapsed 0 00:00:01
exp_A_TAB_P002.log:Job "SYS"."SYS_EXPORT_TABLE_03" successfully completed at Wed Feb 26 20:28:10 2014 elapsed 0 00:00:02
exp_A_TAB_P003.log:Job "SYS"."SYS_EXPORT_TABLE_04" successfully completed at Wed Feb 26 20:28:11 2014 elapsed 0 00:00:02
exp_A_TAB_P004.log:Job "SYS"."SYS_EXPORT_TABLE_05" successfully completed at Wed Feb 26 20:28:13 2014 elapsed 0 00:00:02
exp_B_TAB_P001.log:Job "SYS"."SYS_EXPORT_TABLE_06" successfully completed at Wed Feb 26 20:28:14 2014 elapsed 0 00:00:02
exp_B_TAB_P002.log:Job "SYS"."SYS_EXPORT_TABLE_07" successfully completed at Wed Feb 26 20:28:16 2014 elapsed 0 00:00:02
exp_B_TAB_P003.log:Job "SYS"."SYS_EXPORT_TABLE_08" successfully completed at Wed Feb 26 20:28:17 2014 elapsed 0 00:00:03
exp_B_TAB_P004.log:Job "SYS"."SYS_EXPORT_TABLE_09" successfully completed at Wed Feb 26 20:28:19 2014 elapsed 0 00:00:02
Review exported partition:
$ grep "exported" exp*.log
exp_A_TAB_P001.log:. . exported "MDINH"."A_TAB":"P001"                      6.351 KB       9 rows
exp_A_TAB_P002.log:. . exported "MDINH"."A_TAB":"P002"                      14.89 KB      90 rows
exp_A_TAB_P003.log:. . exported "MDINH"."A_TAB":"P003"                      101.1 KB     900 rows
exp_A_TAB_P004.log:. . exported "MDINH"."A_TAB":"P004"                      963.3 KB    9000 rows
exp_B_TAB_P001.log:. . exported "MDINH"."B_TAB":"P001"                      6.351 KB       9 rows
exp_B_TAB_P002.log:. . exported "MDINH"."B_TAB":"P002"                      14.89 KB      90 rows
exp_B_TAB_P003.log:. . exported "MDINH"."B_TAB":"P003"                      101.1 KB     900 rows
exp_B_TAB_P004.log:. . exported "MDINH"."B_TAB":"P004"                      963.3 KB    9000 rows
Example of completed log:
$ cat exp_B_TAB_P001.log
Starting "SYS"."SYS_EXPORT_TABLE_06":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
. . exported "MDINH"."B_TAB":"P001"                      6.351 KB       9 rows
Master table "SYS"."SYS_EXPORT_TABLE_06" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_06 is:
  /tmp/B_TAB_P001.dmp
Job "SYS"."SYS_EXPORT_TABLE_06" successfully completed at Wed Feb 26 20:28:14 2014 elapsed 0 00:00:02
SQL Scripts:

exp_api.sql:

set timing on echo on
-- DataPump Export (EXPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31641 ORA-27054 ORA-27037 When The Dump File Is On NFS Mount Point (Doc ID 1518979.1)
-- Work around for the above mentioned error
alter system set events '10298 trace name context forever, level 32';
declare
    h1 number;
    dir_name varchar2(30);
begin
    dir_name := 'DPDIR';
    for x in (
        select table_owner, table_name, partition_name
        from   dba_tab_partitions
        where  table_owner = 'MDINH' and table_name in ('A_TAB','B_TAB') and regexp_like(partition_name,'[0-4]$')
        order  by table_owner, table_name, partition_position
    ) loop

        h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE');

        dbms_datapump.add_file (
            handle    => h1,
            filename  => x.table_name||'_'||x.partition_name||'.dmp',
            reusefile => 1, -- REUSE_DUMPFILES=Y
            directory => dir_name,
            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

        dbms_datapump.add_file (
            handle    => h1,
            filename  => 'exp_'||x.table_name||'_'||x.partition_name||'.log',
            directory => dir_name,
            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

        -- CONTENT = DATA_ONLY    
        dbms_datapump.set_parameter (
            handle => h1,
            name   => 'INCLUDE_METADATA',
            value  => 0);

        dbms_datapump.metadata_filter (
            handle => h1,
            name   => 'SCHEMA_EXPR',
            value  => 'IN ('''||x.table_owner||''')');

        dbms_datapump.metadata_filter (
            handle => h1,
            name   => 'NAME_EXPR',
            value  => 'IN ('''||x.table_name||''')');

        dbms_datapump.data_filter (
            handle      => h1,
            name        => 'PARTITION_LIST',
            value       => x.partition_name,
            table_name  => x.table_name,
            schema_name => x.table_owner);

        dbms_datapump.start_job (handle => h1);
        dbms_datapump.detach (handle => h1);
    end loop;
end;
/
alter system set events '10298 trace name context off';
exit
Reference: DBMS_DATAPUMP
Categories: DBA Blogs

Tips on TempDB

Mon, 2014-03-03 08:50

Are you curious about TempDB? Allow me to help make your understanding of the subject permanent. TempDB is an interesting system database that doesn’t behave quite like the others. It’s kind of like a quirky friend that you’ve come to rely on when you need a hand with getting something done.

Who is TempDB for?

TempDB is accessible to all users that are connected to an instance of SQL Server.

What is TempDB?

TempDB is a system database that’s used to store temporary objects. It utilizes minimal logging, meaning it only stores the information required to recover a transaction – this means no point-in-time recovery. You can’t perform a backup or restore on TempDB, and each time SQL Server is restarted, TempDB gets re-created with the last configured settings.

How does TempDB get used?

TempDB stores a number of different temporary objects:

  • User-created temporary objects: Such as global (prefixed by ##) or local (prefixed by #) temporary tables, temporary stored procedures and cursors.
  • Internal temporary objects: Like work tables for intermediate results (any sorting, such as GROUP BY or ORDER BY)
  • Version stores: Row versions for data-modification transactions when certain isolation levels are in use.
  • …And certain features like online index operations
Needless to say, TempDB gets used a lot. In certain cases, even READ ONLY operations benefit from the use of TempDB. Size Matters

Think about it like this: I’m 5’7” and 150 pounds. Would you rather have me or Dwayne “The Rock” Johnson helping you move your furniture? If it’s just a chair or two, I’m happy to help, but if you want me to move a sofa and don’t plan on doing any heavy-lifting yourself, you’ll probably want to get a bigger guy.

Just like when you’re moving furniture, when using TempDB, the size configurations are very important.

Common Issues

TempDB typically has three common issues that a DBA can run into: I/O bottlenecks, low disk space, and page contention. These issues and their resolutions are often interrelated.

If you really want my help, and I see that I’m not going to be big enough to move your sofa, I’ll try my hardest to bulk up so that I can perform the task. TempDB feels the same way, and will automatically grow in size (the default autogrowth setting is by 10% of its current size) in order to accomplish whatever job it set out to do. It’s great that TempDB is so willing to help, but in some cases, it can become so concerned with getting bigger that it causes I/O pressure on the disk, and performance can suffer. In even worse scenarios, TempDB might decide that it needs to be so huge that it consumes the whole disk. How am I going to help you move your furniture if my muscles can’t even fit through the doorframe anymore?!

Since TempDB is so handy, everyone wants a piece of it, and TempDB will put a lot of work on its plate, trying to satisfy everyone’s demands.  This can cause page contention, which we witness in the form of the PAGELATCH wait type (Note: This is not PAGEIOLATCH) denoting that a page is protected and already in memory. Queries needing TempDB’s help will have to wait for their turn.

Solving Common Issues

So, how do we keep TempDB from feeling over-worked and checking into the Smack-Down Hotel? The answer lies in capacity planning.

The basic concepts of capacity planning for TempDB include:

  • Set autogrowth to a pre-set amount: If TempDB gets to be the size of The Rock we don’t want it to automatically grow by a percentage of its current size each time. Set the number to a reasonable size to avoid the continuous need for growth, but keep it low enough to avoid wasted space.
  • Capture and replay activity using a trace or run individual queries: Monitor TempDB’s growth as it is used.
  • The tricky part: Estimate the work load (while accounting for concurrent user-activity)

You can use SYS.DM_DB_SESSION_SPACE_USAGE and SYS.DM_DB_TASK_SPACE_USAGE to identify queries which are consuming TempDB

  • Configure the appropriate number of TempDB DATA files:  If you thought having one Dwayne “The Rock” Johnson helping you move furniture was cool, imagine having two of him! Increasing the number of TempDB data files will allocate work in a round-robin form.  This will relieve some of the pressure on TempDB. It’s like having one Dwayne “The Rock” Johnson Feng Shui your living room while another does the same to your kitchen. It is best practice to set the number of TempDB data files equal to the number of logical CPU cores. For example, if you have a dual-core processor, then set the number of TempDB data files equal to two.  If you have more than 8 cores, start with 8 files and add four at a time as needed.
  • All TempDB files are created equal: This isn’t George Orwell’s Animal Farm. Ensure that the size and growth settings for all TempDB data files are configured in the same manner.
  • Disk Placement: If possible, spread TempDB data files across different disks to reduce I/O contention. Putting TempDB data files on a different disk than the user data files will further reduce I/O contention. Be sure to use fast disks whenever possible.

Keep in mind, that TempDB configurations are largely environment-specific. You might not actually need to have the same number of TempDB files as your logical CPU cores. In fact, having too many TempDB data files can cause performance problems due to slowing down the round-robin style allocation. Use your judgement and knowledge of your environment to determine what’s right for your system. If it’s configured properly, then you and your TempDB can be tag-team wrestling (or moving furniture) with queries like heavyweight champions.

For some more information, check out the following links:

Microsoft Technet: tempdb Database

Microsoft Technet: Capacity Planning for tempdb

SQL Skills: Paul Randal’s A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

Categories: DBA Blogs

Log Buffer #361, A Carnival of the Vanities for DBAs

Fri, 2014-02-28 08:44

Winter is slowly receding, but the fragrance of blooming blog posts is already here in this Log Buffer Edition which covers Oracle, SQL Server and MySQL.

Oracle:

Patrick Barel is trying something with the types in Oracle.

Weijun is talking about a bug in Kerberos used by Java’s HTTP.

Paul Anderson has told us that the BIApps 11.1.1.7.1 ODI Cumulative Patch 1 has been released.

Courtney Llamas writes about the fast recovery area for archive destination.

Stand Apart From Your Peers With Oracle Database Performance and Tuning 2015 Certified Implementation Specialist Certification.

SQL Server:

This article will help us identify the backup which was used to restore the database.

Steve Jones is putting out an open call for guest editorial pieces in the spring of 2014.

DBAs Checklist for Designing and Reviewing SQL Server System Architectures.

Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression.

SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning.

MySQL:

Real-time data loading from MySQL to Hadoop.

Upcoming EMEA Events with MySQL 2014.

Repos and Distros: Upstream and Downstream.

One of Michael McLaughlin students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X.

The MySQL client has some functionality some of us never use. Why would you use them, and what is the added value of this?

Categories: DBA Blogs

Explaining Foreign Key Index in One Illustration

Wed, 2014-02-26 08:45

Think of the leash as an index:

fk

Categories: DBA Blogs

Nothing to Blog About? Think Again!

Mon, 2014-02-24 08:46

How often do you run off to your favourite sites and devour information put together by others? It’s quite remarkable how hard stuff is until you’ve done it one or more times. The seemingly insurmountable task becomes second nature once mastered. Hey, Alex Gorbachev or Jonathan Lewis were once beginners just like you and me. In the days when I got started there was no internet, no Twitter, no Metalink (MOS), and little, if no email. We used the good old-fashioned phone … a traditional landline at that. They used to have  round apparatus with holes in them called “dials.”

Something you have done may be a mystery to others and the seemingly most menial tasks (to you) may be like a vertical wall to others. Think back to 10 things you have done in the past few weeks. Estimate the number of those 10 things that would be “news” to others… Got that right… All 10. We owe it to others to blog about what we do on a daily basis and go out of our ways to find time to educate the masses.

To shed some light on an example that went down in the early to mid ’90s, picture the following (purveyors of SQL*Forms 2 and 3 may remember this). Triggers were used in a way similar to they are today. Events occurred as we moved around a screen (character-based at that :)). A common trigger was called POST-CHANGE and I became frustrated as we went to Forms 3 remembering that this trigger would not fire until the cursor left a field. I needed a way to execute a trigger while the cursor still resided in a field. Along comes a developer with 3 months experience. She suggests coding an ON-VALIDATE-FIELD trigger. Swell I said to myself knowing well that this trigger as well would not fire until the cursor left the field. So as not to offend here, I did just that. She also suggested placing the text “ENTER;” in the trigger code and all would proceed exactly as hoped.

I tried it out after chuckling to myself, based on what I already knew about Forms and it WORKED. There is the rub… No matter how little you may know, your expertise may lie in a corner of technology, others have not experienced yet. Your experiences are valuable to others and it is your obligation to blog. Nothing to blog about — think again.

Categories: DBA Blogs

Log Buffer #360, A Carnival of the Vanities for DBAs

Mon, 2014-02-24 08:40

This week, there are some announcements from the database arena and then there are some tips and tricks coupled with some cool techniques, and this Log Buffer covers some of them.

Oracle:

startCD options to install Oracle E-Business Suite Release 12.2.

IDC Big Data in the Enterprise: When Worlds Collide.

Friday prank: select from join join join.

Friday Spotlight: Vitual Sysadmin Day and Hands On Labs

Are You “Millennial Ready”?

SQL Server:

Show the results for a date even if there is no data.

Hosting SQL Server in Hyper-V Replica Environment.

SQL Saturday is coming to Vienna on March 6 with a full-day of technical training and networking, featuring international speakers.

Understanding Graphical Execution Plans – Part 2: How to Create One.

SQL Server security related tasks can be divided into four main categories.

MySQL:

Before every release: A glimpse into Percona XtraDB Cluster CI testing.

MySQL Connector/Python v1.1.6 GA

No Hadoop Fun for Me at SCaLE 12X :(

FromDual.en: Why FromDual customers are using Galera Cluster for MySQL

Percona XtraDB Cluster 5.6.15-25.4 is now available.

Categories: DBA Blogs

What Happens When Active DB Duplication Goes Wrong?

Tue, 2014-02-18 13:57

There are many blog posts out there about active database duplication. However, they were all tested in an ideal environment or condition. What happens when a tablespace is created during the middle of active duplication and how to resolve the error? Read on if you would like to know.

For my test case, I created database db01 using OMF and will perform active duplication to db02 using OMF as well on the same host. While duplication was running, I created a new tablespace. Here are the details of the steps performed:

Review files and pfile for TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ ll
total 7692
-rw-rw----. 1 oracle oinstall    1544 Feb 14 13:06 hc_db01.dat
-rwxr-x---. 1 oracle oinstall     590 Feb 13 08:14 initdb01.ora
-rwxr-x---. 1 oracle oinstall     590 Feb 13 08:14 initdb02.ora
-rw-r-----. 1 oracle oinstall      24 Feb 13 08:18 lkDB01
-rw-r-----. 1 oracle oinstall       0 Feb 14 13:06 lkinstdb01
-rw-r-----. 1 oracle oinstall    2048 Feb 11 14:00 orapwdb01
-rw-r-----. 1 oracle oinstall    2048 Feb 11 15:48 orapwdb02
-rw-r-----. 1 oracle oinstall 7847936 Feb 11 18:06 snapcf_db01.f
-rw-r-----. 1 oracle oinstall    3584 Feb 14 13:05 spfiledb01.ora

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ cat initdb02.ora
*.audit_file_dest='/u01/app/oracle/admin/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_name='db02'
*.db_recovery_file_dest='/oradata/fra'
*.db_recovery_file_dest_size=4g
*.diagnostic_dest='/u01/app/oracle'
*.event='10795 trace name context forever, level 2'
*.fast_start_mttr_target=300
*.java_pool_size=0
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'
*.pga_aggregate_target=268435456
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS'

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ diff ./initdb01.ora ./initdb02.ora
7c7
< *.db_name='db01' --- > *.db_name='db02'
[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$

Notice there is only one difference between in the pfile for db01 and db02

Create same directory structures for TARGET database:

[oracle@arrow:]/oradata
$ ls DB*
DB01:
controlfile  datafile  onlinelog

DB02:
controlfile  datafile  onlinelog
[oracle@arrow:]/oradata
$

Startup NOMOUNT TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:11:13 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@db02> startup nomount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             222301584 bytes
Database Buffers          570425344 bytes
Redo Buffers                6717440 bytes
SYS@db02>

Start active database duplication:

[oracle@arrow:db01]/media/sf_linux_x64/rman
$ rman @dupdbomf.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 14 13:12:04 2014

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

RMAN> connect target *
2> connect auxiliary *
3> run {
4> allocate channel c1 type disk maxopenfiles 1;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database to db02
7>   from active database nofilenamecheck
8>   spfile
9>   PARAMETER_VALUE_CONVERT ('DB01','DB02')
10> ;
11> }
12> exit;
connected to target database: DB01 (DBID=1470673955)

connected to auxiliary database: DB02 (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=14 device type=DISK

allocated channel: a1
channel a1: SID=96 device type=DISK

Starting Duplicate Db at 14-FEB-2014 13:12:07

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb01.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''";
}
executing Memory Script

Starting backup at 14-FEB-2014 13:12:08
Finished backup at 14-FEB-2014 13:12:09

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.c                                                                              tl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DB01'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB02'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   restore clone controlfile to  '/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl' from
 '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DB01'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB02'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

Starting backup at 14-FEB-2014 13:12:55
channel c1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_db01.f tag=TAG20140214T131255 RECID=1 STAMP=839509978
channel c1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-FEB-2014 13:13:03

Starting restore at 14-FEB-2014 13:13:03

channel a1: copied control file copy
Finished restore at 14-FEB-2014 13:13:04

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   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 14-FEB-2014 13:13:21

----------------------------------------------------------------------
-- While duplication was running, create new tablespace at source
--
[oracle@arrow:db01]/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:13:22 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@db01):PRIMARY> create tablespace mdinh;

Tablespace created.

ARROW:(SYS@db01):PRIMARY>
----------------------------------------------------------------------

channel c1: starting datafile copy
input datafile file number=00001 name=/oradata/DB01/datafile/o1_mf_system_9hsw4shz_.dbf
output file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:01:26
channel c1: starting datafile copy
input datafile file number=00002 name=/oradata/DB01/datafile/o1_mf_sysaux_9hsw63d2_.dbf
output file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00003 name=/oradata/DB01/datafile/o1_mf_undotbs_9hsw75h4_.dbf
output file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00004 name=/oradata/DB01/datafile/o1_mf_users_9hsw880k_.dbf
output file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 14-FEB-2014 13:16:32

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_10_9hx1xkn1_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-FEB-2014 13:16:34
channel c1: starting archived log copy
input archived log thread=1 sequence=10 RECID=2 STAMP=839510193
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-FEB-2014 13:16:35

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
  RMAN-07518: Reason: Foreign database file DBID: 1470537681  Database Name: DB01

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=839510196 file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=839510196 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=839510197 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=839510197 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

contents of Memory Script:
{
   set until scn  227291;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-2014 13:16:39
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/14/2014 13:17:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06094: datafile 5 must be restored

Recovery Manager complete.
[oracle@arrow:db01]/media/sf_linux_x64/rman
$

Remove spfile and misc files for TARGET database:

Startup NOMOUNT TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ rm spfiledb02.ora lkDB02 hc_db02.dat
[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:18:43 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@db02> startup nomount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             222301584 bytes
Database Buffers          570425344 bytes
Redo Buffers                6717440 bytes
SYS@db02> exit

RESTART active database duplication:

[oracle@arrow:db01]/media/sf_linux_x64/rman
$ rman @dupdbomf.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 14 13:18:52 2014

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

RMAN> connect target *
2> connect auxiliary *
3> run {
4> allocate channel c1 type disk maxopenfiles 1;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database to db02
7>   from active database nofilenamecheck
8>   spfile
9>   PARAMETER_VALUE_CONVERT ('DB01','DB02')
10> ;
11> }
12> exit;
connected to target database: DB01 (DBID=1470673955)

connected to auxiliary database: DB02 (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=14 device type=DISK

allocated channel: a1
channel a1: SID=10 device type=DISK

Starting Duplicate Db at 14-FEB-2014 13:18:54

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb01.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''";
}
executing Memory Script

Starting backup at 14-FEB-2014 13:18:54
Finished backup at 14-FEB-2014 13:18:55

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DB01'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB02'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   restore clone controlfile to  '/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl' from
 '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DB01'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB02'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

Starting backup at 14-FEB-2014 13:19:11
channel c1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_db01.f tag=TAG20140214T131912 RECID=2 STAMP=839510353
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-FEB-2014 13:19:15

Starting restore at 14-FEB-2014 13:19:15

channel a1: copied control file copy
Finished restore at 14-FEB-2014 13:19:16

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

database mounted

Using previous duplicated file /oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf for datafile 1 with checkpoint SCN of 226956
Using previous duplicated file /oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf for datafile 2 with checkpoint SCN of 227250
Using previous duplicated file /oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf for datafile 3 with checkpoint SCN of 227262
Using previous duplicated file /oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf for datafile 4 with checkpoint SCN of 227275

contents of Memory Script:
{
   set newname for datafile  1 to
 "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf";
   set newname for datafile  2 to
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf";
   set newname for datafile  3 to
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf";
   set newname for datafile  4 to
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   set newname for clone datafile  5 to new;

   backup as copy reuse
   datafile  5 auxiliary format new
   ;
   
   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

executing command: SET NEWNAME

Starting backup at 14-FEB-2014 13:19:31
channel c1: starting datafile copy
input datafile file number=00005 name=/oradata/DB01/datafile/o1_mf_mdinh_9hx1qqko_.dbf
output file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf tag=TAG20140214T131931
channel c1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-FEB-2014 13:19:47

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_10_9hx1xkn1_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_%u_.arc"   archivelog like
 "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_11_9hx23s3n_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_%u_.arc"   ;
   catalog clone recovery area;
   catalog clone datafilecopy  "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf",
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf",
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf",
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   switch clone datafile  1 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf";
   switch clone datafile  2 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf";
   switch clone datafile  3 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf";
   switch clone datafile  4 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-FEB-2014 13:19:53
channel c1: starting archived log copy
input archived log thread=1 sequence=10 RECID=2 STAMP=839510193
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
channel c1: starting archived log copy
input archived log thread=1 sequence=11 RECID=3 STAMP=839510393
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-FEB-2014 13:19:56

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
  RMAN-07518: Reason: Foreign database file DBID: 1470537681  Database Name: DB01

cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf RECID=2 STAMP=839510398
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf RECID=3 STAMP=839510398
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf RECID=4 STAMP=839510399
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf RECID=5 STAMP=839510399

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=839510398 file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=839510398 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=839510399 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=839510399 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=839510401 file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf

contents of Memory Script:
{
   set until scn  227620;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-2014 13:20:02

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
archived log for thread 1 with sequence 11 is already on disk as file /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
archived log file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc thread=1 sequence=10
archived log file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:09
Finished recover at 14-FEB-2014 13:20:14
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' 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

sql statement: alter system set  db_name =  ''DB02'' 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     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB02" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES       30
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 100 M ,
  GROUP   2  SIZE 100 M ,
  GROUP   3  SIZE 100 M
 DATAFILE
  '/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf'
 CHARACTER SET AL32UTF8

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf",
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf",
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf",
 "/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/DB02/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf RECID=1 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf RECID=2 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf RECID=3 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf RECID=4 STAMP=839510429

datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=839510429 file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=839510430 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=839510431 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=839510432 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-FEB-2014 13:21:48
released channel: c1
released channel: a1

Recovery Manager complete.
[oracle@arrow:db01]/media/sf_linux_x64/rman
$

Did you noticed duplication reused previous duplicated file versus duplicating it again?

Categories: DBA Blogs

SQL Server Integrity Check – A Necessary Routine

Fri, 2014-02-14 15:04

Today I’ll be discussing Integrity Check on SQL Server – we have some very good info in the community, but I’d like to summarize my thoughts here. When a DBA talks about Integrity Check, the conversation is usually redirected to one subject: CheckDB. And yes, we are going to talk about it in today’s post. :)

The DBCC CheckDB is a medicine of sorts that can prevent headaches, and remedy possible damages. But it should be used with caution: Without a well-planned backup strategy you can lose data. Even with all of the warnings about the importance of Integrity Check, it’s not uncommon to see instances without it.

“I’m a Manager just trying to understand this… Could you explain this at a high level?”

Let’s cut the blah, blah, blah and go ahead with this… I’ll introduce the CheckDB for those who have never heard of it – probably less technical individuals: The CheckDB command is a Database Console Command, or simply DBCC, categorized as a Validation DBCC. This is a necessary step in the entire instance maintenance, where we can detect, and in some cases repair a possible problem with the database integrity. Running the DBCC CheckDb regularly will help to anticipate problems, and even a unexpected shutdown of the instance. We’ll go into more detail a bit later…

“What’s the objective of the CheckDB?”

CheckDB has two roles: The main objective is the integrity check itself, and the second is the correction of the findings. Please not that the CheckDB verifies the database structure and not the data consistency.

Integrity Check: The command CheckDB follows a few steps on its execution, passing from the following phases:

  • Verification of the allocated structures, i.e. GAM, SGAM, PFS, IAM…
  • Verification of table consistency, including all of its indexes.
  • Verification of the system catalogs.

Correction: After the CheckDB “standard execution” as described above, we will have information about possible damaged pages. The CheckDB itself can fix these damages.

CheckDB has an option that allows the correction of the database structure, but the data allocated into the repaired page will be lost. This option is “REPAIR_ALLOW_DATA_LOSS”, and this option changes the damaged page for a new one. This way, we save the integrity of the database. Note that we are talking about the physical structure – not about data integrity, which is why we lose data. After the execution of this command, a verification based on backups should be made in order to identify and recover the lost data.

Another option is “REPAIR_REBUILD” used to correct non clustered indexes. On this option we don’t lose data. Even though we have options to correct pages, there are few limitations:

  • PFS pages, system tables and data purity detected errors aren’t fixable by CheckDB.
  • To repair using CheckDB, the database should be on SINGLE_USER mode.

“How is the CheckDB executed?”

Not too many people are curious to look deeper and discover more about this, but the CheckDB doesn’t run directly over the database. A hidden database snapshot is created and the process runs based on this snapshot. Many people only realize this when the CheckDB execution fails, and once they begin digging on it, they find out about “some snapshot” that failed the creation.

While we’re on the topic, do you know what to do if the snapshot creation fail? I can see 3 options:

  • Fix the permission to create the snapshot.
  • Execute the CheckDB with the TABLOCK option. This can bring problems, as the DB will be a target of locks.
  • Create a process (job) doing the following steps:
    1. Create a snapshot of the database.
    2. Run the DBCC CheckDB on this snapshot. Yes, the DBCC CheckDB can run over databse snapshots. And it’s obvious that a snapshot of a snapshot won’t be created.

“When and where should we execute an Integrity Check?”
It’s recommended to check  the integrity of all the databases, without exception! The best approach is to execute the CheckDB everyday. But we understand that on busy systems (instances with hundreds of databases, VLDBs, etc.) this can be nearly impossible.

Some interesting facts:

  • SQL Server maintenance plan ignores the TempDB, but if the TempDB becomes corrupt the instance will shutdown. Use caution with this.
  • Executing the CheckDB on the master database will cause the execution on the mssqlsystemresource database as well.

Execution Options: There are few execution options for CheckDB:

  • NO_INFOMSGS: Avoid the output of info messages.
  • ALL_ERRORMSDGS: Allow the output of error messages.
    • Default since SQL Server 2008 SP1.
  • NOINDEX:  Skips nonclustered indexes verification.
  • DATA_PURITY: Validates de data based on the column characteristcs.
    • Default since SQL Server 2005.
  • ESTIMATEONLY: Estimates the space needed on TempDB.
    • As per Paul Randal, this option is broken on newer versions of SQL Server.
  • TABLOCK: Uses locks instead of database snapshots.
    • Useful when the snapshot creation is failing.
  • EXTENDED_LOGICAL_CHECKS: Allows the validation on XML columns and Spatial indexes.
    • This can be costly.
  • PHYSICAL_ONLY: Skips most of the logical verifications.
    • Need less resources to run, but a “full” execution should be sone periodically.

Other Approaches to execute: As CheckDB is a resource-intensive process, in some cases the execution may take a long time to complete. Depending on the environment, the Integrity Check on all the databases (or even in one very large database) may not finish on time, namely, the integrity check will exceed the maintenance window limit. To work around this, we can reproduce a full CheckDB executing its subset commands. As follows:

  • CHECKALLOC
  • CHECKTABLE
  • CHECKCATALOG
  • CHECKFILEGROUP

By executing those commands, one per day, we can cover a full CheckDB execution in phases. Another possible approach is execute the backups with the “CHECKSUM” option. This way we will be able to restore using the option “VERIFY ONLY” and do an extra level of verification. Anyway, we will still need the CheckDB.

So, that’s all about CheckDB. There are more information around there, but the best place to get info from CheckDB is on SQL Skills blog, where the CheckDB guru writes few articles about it. See you in another post ;)

Categories: DBA Blogs

RMAN 12c : Say goodbye to your backup when dropping your PDB

Fri, 2014-02-14 14:46

I was working on my presentations for IOUG Collaborate, and I came upon this strange behaviour in RMAN 12c (12.1.0.1.0) which to me, shouldn’t happen. Seems that when you do a DROP PLUGGABLE DATABASE , it is the equivalent of DROP DATABASE INCLUDING BACKUPS. This means that if you need to restore your PDB later on, you won’t have this registered – just be careful when dropping them.

Here we go: So I took a backup of my CDB and all of its PDBs, and kept an eye on this TAG 20140212T191237  (I removed a couple of lines for better reading)


oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rman target sys/oracle@cdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:12:06 2014

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

connected to target database: CDB1 (DBID=808250731)

RMAN> backup database plus archivelog ;

Starting backup at 12/02/2014 19:12:31

current log archived

...

Starting backup at 12/02/2014 19:12:37

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf

channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:12:38

channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:15:23

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/<strong>o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_</strong>.bkp tag=TAG20140212T191237 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

channel ORA_DISK_1: starting piece 1 at 12/02/2014 19:15:23

channel ORA_DISK_1: finished piece 1 at 12/02/2014 19:16:08

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=<b>TAG20140212T191237</b> comment=NONE

..

Finished backup at 12/02/2014 19:16:37

Starting Control File and SPFILE Autobackup at 12/02/2014 19:16:37

piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_02_12/o1_mf_s_839358997_9hrbr5vr_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 12/02/2014 19:16:38

So First I want to show you that I was able to recoup the DB in case I were to lose my datafiles of my PDB, so I will first delete them

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ sqlplus / as sysdba

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set pages 999

SQL> COLUMN PDB_ID FORMAT 999

COLUMN PDB_NAME FORMAT A8

COLUMN FILE_ID FORMAT 9999

COLUMN TABLESPACE_NAME FORMAT A10

COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME

FROM DBA_PDBS p, CDB_DATA_FILES d

WHERE p.PDB_ID = d.CON_ID

ORDER BY p.PDB_ID;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4

PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME

------ -------- ------- ---------- ---------------------------------------------

2 PDB$SEED       5 SYSTEM   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf

2 PDB$SEED       7 SYSAUX   /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

3 PDB1      9 SYSAUX   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

3 PDB1      8 SYSTEM   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

3 PDB1     10 USERS   /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf

oracle@oracleenespanol2.localdomain[cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin

oracle $ rm /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf

Now that I have deleted the datafiles, I will now proceed to restore and recover the PDB, and keep an eye on the tag 20140212T191237  used for the restore.

oracle@oracleenespanol2.localdomain [cdb1] /u01/app/oracle/product/12.1.0/db_1/network/admin
oracle $ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Feb 12 19:19:46 2014

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

connected to target database: CDB1 (DBID=808250731)

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 12/02/2014 19:20:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbovmw_.bkp tag=TAG20140212T191237
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 12/02/2014 19:20:48

RMAN> RECOVER PLUGGABLE DATABASE PDB1;

Starting recover at 12/02/2014 19:21:06
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 12/02/2014 19:21:07

RMAN> alter pluggable database pdb1 open;

Statement processed

As you can see, I was able to restore and recover my PDB without a problem. But what happens if I decide to drop my PDB, and later on decided that the PDB was needed? So I tried to go back to my backup, it will no longer be there, and it doesn’t report on the backup tag 20140212T191237


RMAN> alter pluggable database pdb1 close;

Statement processed

RMAN> drop pluggable database PDB1;

Statement processed

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 13/02/2014 11:18:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/13/2014 11:18:27
RMAN-06813: could not translate pluggable database PDB1

RMAN> list backup tag TAG20140212T191237;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
67 Full 1.88G DISK 00:02:41 12/02/2014 19:15:19
 BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbjrqd_.bkp
 List of Datafiles in backup set 67
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 1 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf
 3 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf
 4 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf
 6 Full 3220602 12/02/2014 19:12:38 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
69 Full 680.13M DISK 00:00:24 12/02/2014 19:16:32
 BP Key: 69 Status: AVAILABLE Compressed: NO Tag: TAG20140212T191237
 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/EDDDB886A1191F07E043344EB2C0BE27/backupset/2014_02_12/o1_mf_nnndf_TAG20140212T191237_9hrbq8lm_.bkp
 List of Datafiles in backup set 69
 Container ID: 2, PDB Name: PDB$SEED
 File LV Type Ckp SCN Ckp Time Name
 ---- -- ---- ---------- ------------------- ----
 5 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf
 7 Full 1732663 18/12/2013 22:52:25 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf

As you can see, that backup is no longer registered. I still don’t know if this is normal behaviour for PDBs backup, or a bug – but for now just be careful when dropping a PDB. Your backup will not be reliable. Scary stuff isn’t it ?

Categories: DBA Blogs

SQL Server Statistics: Maintenance and Best Practices

Fri, 2014-02-14 12:58
What are Statistics?

There are multiple paths a database can use to answer a query, some of them being faster and more efficient than others. It is the job of the query optimizer to evaluate and choose the best path, or execution plan, for a given query. Using the available indexes may not always be the most efficient plan. For example, if 95% of the values for a column are the same, an index scan will probably be more efficient than using the index on that column. Statistics are SQL Server objects which contain metrics on the data count and distribution within a column or columns used by the optimizer to help it make that choice. They are used to estimate the count of rows.

Index statistics: Created automatically when an index (both clustered and non-clustered) is created. These will have the same name as the index and will exist as long as the index exists.

Column statistics: Created manually by the DBA using the ‘CREATE STATISTICS’ command, or automatically if the “Auto Create Statistics” option is set to “True”. Column statistics can be created, modified and dropped at will.

Statistics contain two different types of information about the data; density and distribution. Density is simply the inverse of the count of distinct values for the column or columns. The distribution is a representation of the data contained in the first column of the statistic. This information is stored in a histogram; the histogram contains up to 200 steps with a lower and upper limit and contains the count of values that fall between both limits. To view this histogram, go to the details tab of the statistic’s properties or use the command DBCC SHOW_STATISTICS. The screenshot below shows the histogram of an index statistic; the RANGE_HI_KEY is the upper limit of the step, the RANGE_HI_KEY of the previous step + 1 is the lower limit, and the RANGE_ROWS is the count of rows between the limits.

stats1 Statistics Maintenance and Best Practices

When the data in the database changes the statistics become stale and outdated. When examining a query execution plan, a large discrepancy between the Actual Number of Rows and the Estimated Number of Rows is an indication of outdated stats. Outdated statistics can lead the optimizer in choosing inefficient execution plan and can dramatically affect overall performance. Steps must therefore be taken in order to keep statistics up to date.

stats2

Keep Auto Create Statistics enabled: This database property allows SQL Server to automatically create stats for a single non-indexed column if they are missing when it is used in a where or join condition. This ensures the optimizer will have the necessary information to choose a plan. The statistics automatically created by SQL Server will start with _WA_ in their name.

Keep Auto Update Statistics enabled: This database property allows SQL Server to automatically update the statistics when they are deemed outdated. The update occurs before executing a query if certain conditions are met, or after the query is executed if Auto Update Statistics Asynchronously is used instead. The three conditions that will trigger an update if one is met are:

-Table had 0 rows and increases to one or more rows.

-Table had less than 500 rows and there is an increase of 500 rows or more since the last update

-Table has over 500 rows and there is an increase of 500 + 20% of the table size since the last update

stats3

Maintenance plan: You can also proactively update the statistics yourself using TSQL (sp_updatestats for all stats in a database or UPDATE STATISTICS for a single one) or a maintenance plan task. Scheduling the statistics maintenance during off hours will help reduce the need to update statistics during peak times. The need and frequency of this proactive maintenance will depend on your environment; frequent data changes causes the statistics to become outdated more quickly. You can also specify the sample size used to update the statistic;

Ex:

UPDATE STATISTICS TableName(StatsName) WITH FULLSCAN: Costs more time and resources but will ensure that statistics are accurate.

UPDATE STATISTICS TableName(StatsName) WITH SAMPLE 50 PERCENT: Will only use half the rows and extrapolate the rest, meaning the updating will be faster, but the statistics may not be accurate.

Rebuilding an index will also update index statistics with full scan (column stats will not be updated, and an index reorg will do the update). Note however that updating statistics forces queries to recompile; you must therefore decide when the cost of the overhead for the recompiles is worth having the latest statistics.

Unused Statistics: Statistics comes with a cost, and just as with indexes, too many of them can lead to issues like increasing the cost of statistics maintenance, and can make the optimizer’s job more difficult. Updating statistics for a large database can easily take hours, even days, to complete. When Auto Create Statistics is enabled, stats can be created even for a one time query. A table could end up having a large number of statistics that serve no purpose. It is wise to review and clean up the statistics as part of general maintenance. Identifying unused statistics can be difficult since, unlike indexes, SQL Server does not record statistics usage. However you can identify the statistics that satisfy one of the thresholds for the automatic update above but still hasn’t been updated; this is a good indication of unused statistics.

In addition to unused stats, you may find overlapping stats which are covered by other statistics. The following script from Kendal Van Dyke will identify all single column statistics that are covered by an existing index statistic (share the same leading column) in a database and generates the TSQL commands to drop them.

WITH    autostats ( object_id, stats_id, name, column_id )

AS ( SELECT   sys.stats.object_id ,

sys.stats.stats_id ,

sys.stats.name ,

sys.stats_columns.column_id

FROM     sys.stats

INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id

AND sys.stats.stats_id = sys.stats_columns.stats_id

WHERE    sys.stats.auto_created = 1

AND sys.stats_columns.stats_column_id = 1

)

SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,

sys.columns.name AS [Column] ,

sys.stats.name AS [Overlapped] ,

autostats.name AS [Overlapping] ,

'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)

+ '].[' + OBJECT_NAME(sys.stats.object_id) + '].['

+ autostats.name + ']'

FROM    sys.stats

INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id

AND sys.stats.stats_id = sys.stats_columns.stats_id

INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id

AND sys.stats_columns.column_id = autostats.column_id

INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id

AND sys.stats_columns.column_id = sys.columns.column_id

WHERE   sys.stats.auto_created = 0

AND sys.stats_columns.stats_column_id = 1

AND sys.stats_columns.stats_id != autostats.stats_id

AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0

Source: http://www.kendalvandyke.com/2010/09/tuning-tip-identifying-overlapping.html

Common Mistakes

Statistics update after Index Rebuild: As mentioned previously, the index rebuild (not reorg) will also update index statistics using full scan. Scheduling stats maintenance after the index maintenance will cause duplicate work. In addition, if the stats maintenance is using a small sample size, the new updated stats will overwrite the ones that were just updated with full scan, meaning their values will be less accurate. Scheduling it after an index reorg however is fine.

Relying on Auto Update: As seen above, the threshold which triggers the auto update is around 20% of the total row count. This is fine for small tables, but larger tables require a lot of data changes before the update is triggered, during which the stats can become outdated.

Not specifying the sample size: While updating, choosing the right sample size is important to keep statistics accurate. While the cost of using full scan is higher, in some situations it is required, especially for very large databases. Running EXEC sp_updatestats @resample = ‘resample’ will update all statistics using the last sample used. If you do not specify the resample, it will update them using the default sample. The default sample is determined by SQL server and is a fraction of the total row count in a table. We have recently run into an issue where a DBA executed “EXEC sp_updatestats” on a 1 terabyte database, which caused all statistics to be updated with the default sample. Due to the size of the database, the default sample is simply not enough to represent the data distribution in the database and caused all queries to use bad execution plans which caused major performance issues. Only a full scan update of the statistics provided accurate statistics for this database, but it takes a very long time to run.  Luckily there was a QA server where the database was restored before the stats update and with almost identical data. We were able to script the statistics from the QA server and recreate them on production using their binary representation (see WITH STATS_STREAM).  This solution is not recommended and was only used as a last resort. This incident shows the importance of statistics and implementing proper maintenance appropriate for the environment.

Updating too often: Not only is there a cost in updating statistics, remember that it also causes queries to recompile. Updating statistics should be done only as required, and a schedule appropriate for your environment should be used. The frequency depends on the amount of data changes in the database, more changes require more frequent stats update.

Conclusion

Statistics are a crucial element in the overall performance of a database and require proper maintenance and attention. In addition, each environment is unique and has different needs regarding statistics maintenance. For more information regarding statistics, see http://technet.microsoft.com/en-us/library/ms190397.aspx.

Categories: DBA Blogs

Log Buffer #359, A Carnival of the Vanities for DBAs

Fri, 2014-02-14 10:47

On this Valentine’s Day, what is the most romantic thing you could do as database professional? Why, yes — you could read (and then share) this scintillating and lovely Log Buffer Edition!

Oracle:

Oracle is kicking off a 17-city North American event series on how running Oracle Applications on Oracle hardware can help customers deliver maximum value and achieve dramatically better business results.

Five guidelines to follow as you begin building and employing mobile applications – plus Oracle technologies and products that support your move to mobility in your enterprise.

When you run R functions in the database, especially functions involving multiple R engines in parallel, you can monitor their progress using the Oracle R Enterprise datastore as a central location for progress notifications, or any intermediate status or results.

In the era of big data, data warehousing is becoming even more relevant for enterprises which are eager to become more information-driven.

The License Definitions and Rules (LDRs) are a critical part of every software license that Oracle grants to a customer.

SQL Server:

An examination into how the various transaction isolation levels affect locking (and blocking.)

What to do if you need to push the limits of your disk subsystem, in order to determine whether the hardware’s I/O capacity meets the needs of a database application.

An Overview of SSIS Parameters – Level 15 of the Stairway to Integration Services.

With the new SQL Server 2014 In-Memory OLTP tables, stored procedures can be natively compiled and can improve performance.

Excel spreadsheets are useful for distributing data generated by SQL Server, but SSIS lacks direct support for writing Excel files.

MySQL:

The Sign: row-based binary logging and integer signedness in MySQL and MariaDB.

Delayed Transaction Durability: A new feature in SQL Server 2014 CTP2.

Getting Started with the Spider Storage Engine.

Shard-Query is now much faster for some aggregate functions.

One of the most expensive database operations is performing Data Definition Language (DDL, e.g. CREATE, DROP, ALTER, etc.) statements, specially, the ALTER statements because MySQL blocks the entire table for both reads and writes while modifying the table.

Categories: DBA Blogs

Creating a single-node EBS 12.1.3 Vision instance from OVM templates

Thu, 2014-02-13 08:50
“Seriously John, do you blog about anything else?”

Yeah, well… Evidence is strongly against me so far. :)

One of the more common questions I’ve received as a followup to my Build an E-Business Suite 12.1.3 Sandbox In VirtualBox in One Hour post has been, “Can I do this in a single node instead of creating two VMs?” The answer of course, is yes, but it never seemed like a good topic for a full blog post. Given the number of requests, however (and the patience and persistence of one reader in particular — hi Sandip!), I’m using this post to present quick notes on how to create a single-node EBS 12.1.3 Vision instance from the OVM templates, instead of the two-node system for which they’re designed.

In addition to the normal complete-lack-of-support caveats listed in the original post, please be aware that this post contains pointers and rough notes, not detailed instructions. Basically, I’ve just wrapped some formatting around some notes from a presentation I gave on this topic last summer. If you don’t understand what’s happening in the original set of instructions, these notes will not be useful to you at all. Please read the original post carefully before asking questions about this one.

System specs

Since we’re running apps and the database in a single node we need to configure a slightly more powerful single VM. Here’s partial output from ‘vboxmanage showvminfo’ that illustrates the important points (more memory, more CPU, and an extra disk for the Apps software). Otherwise, the configuration (network interfaces, rescue boot image setup, etc) is the same as in the original post.

Memory size: 3072MB
Number of CPUs: 2
Storage Controller Name (1): SATA
Storage Controller Type (1): IntelAhci
SATA (0, 0): /Volumes/Valen/OVM_1213/EBS121RootDisk.vdi (UUID: ebd87cd3-2620-49b6-b24d-c64158b183da)
SATA (1, 0): /Volumes/Valen/OVM_1213/EBS121DB.vdi (UUID: 0ae2f4dc-bd40-4299-82f7-eebea2c34de7)
SATA (2, 0): /Volumes/Valen/OVM_1213/EBS121Apps.vdi (UUID: 7fc14a42-f4bc-4741-8ba7-a33341ac73ea)

Still the same

The following steps are almost the same as in the original post:

  1. Download the software
  2. Extract the templates
  3. Convert the disk images to .vdi format (though you can skip the Apps server System.img disk, you won’t need it, only ebs1211apps.img). Of course, you’ll only need to create 1 VM at this step, attaching the Apps vdi as the third disk.
  4. Boot the database server VM in rescue mode from the install CD — the steps to install the new kernel and run mkinitrd remain the same

Things change a bit before moving on to step 5, “Reboot and prepare for next steps,” as described below.

What’s different?

Apart from the obvious “no second VM to create,” here are the essential changes I made to my build process for a single-node Vision instance:

  • Before rebooting, add another line to /etc/fstab to attach the apps software volume:
    /dev/sdc1 /u02 ext3 defaults 1 0
  • Before rebooting, do not edit the /etc/sysconfig/oraclevm-template script. I found it to be easier to just let the script execute at boot time, although it did require me to be a bit more careful about my inputs.
  • After rebooting, the template configuration script will guide you through the configuration of the network interfaces and the Vision database tier, as described in the original post

Once the database is started, you’ll need to make a few changes to the scripts that configure, start, and stop the applications tier. First, log in to the VM as root, and then adjust the scripts to account for the new mount point. To save your sanity, it’s also necessary to comment out ovm_configure_network from the ebiz_1211_reconfig.sh script:

# cd /u02
# perl -pi.old -e 's/u01/u02/g' startapps.sh stopapps.sh ebiz_1211_reconfig.sh
# vi ebiz_1211_reconfig.sh
# diff ebiz_1211_reconfig.sh ebiz_1211_reconfig.sh.old
47c47
< #ovm_configure_network "static"
---
> ovm_configure_network "static"
61c61
< su oracle -c "perl /u02/E-BIZ/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier"
---
> su oracle -c "perl /u01/E-BIZ/apps/apps_st/comn/clone/bin/adcfgclone.pl appsTier"

After the scripts have been adjusted, you’re ready to configure the apps tier. Again, as root, run the /u02/ebiz_1211_reconfig.sh script, which will invoke AutoConfig and ask you all the necessary questions. Your answers will differ from the two-node process in two important ways:

  1. There is only one hostname for the environment now
  2. All references to the apps software locations will point to /u02, not /u01

Here’s an excerpt of the Autoconfig run, with only the important/changed bits included:

 cd /u02
[root@gkar u02]# ./ebiz_1211_reconfig.sh
Configuring Oracle E-Business Suite...

Target System Hostname (virtual or normal) [gkar] :

Target System Database SID : VIS

Target System Database Server Node [gkar] :

Target System Database Domain Name [local.org] :

Target System Base Directory : /u02/E-BIZ

Target System Tools ORACLE_HOME Directory [/u02/E-BIZ/apps/tech_st/10.1.2] :

Target System Web ORACLE_HOME Directory [/u02/E-BIZ/apps/tech_st/10.1.3] :

Target System APPL_TOP Directory [/u02/E-BIZ/apps/apps_st/appl] :

Target System COMMON_TOP Directory [/u02/E-BIZ/apps/apps_st/comn] :

Target System Instance Home Directory [/u02/E-BIZ/inst] :

Do you want to preserve the Display [atgtxk-09:0.0] (y/n)  : n

Target System Display [gkar:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 42

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /usr/tmp
3. /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/outbound/VIS_gkar
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node [1] : 1

Do you want to startup the Application Services for VIS? (y/n) [y] :  y
Cleanup items and other reminders

To prevent annoyances when starting/stopping services, and logging in as oracle:

  • touch /home/oracle/.passchanged
  • rm /u02/E-BIZ/apps/apps_st/appl/*mydb*

Also, since our root disk came from the database server VM template, only database services will stop and start automatically upon server shutdown and boot. You will need to use the startapps.sh and stopapps.sh scripts in /u02 to manage the applications tier services.

That should be enough to get you going. Good luck!

Categories: DBA Blogs

Making Oozie Logs A Little Easier On The Eyes

Thu, 2014-02-13 08:47

Today we’re having a quick one.

Earlier during the day, I had to peruse an Oozie log for the first time. And it looked like:


2014-02-11 20:13:14,211  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@:start:] Start action [0004636-140111040403753-oozie-W@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:14,212  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@:start:] [***0004636-140111040403753-oozie-W@:start:***]Action status=DONE
2014-02-11 20:13:14,212  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@:start:] [***0004636-140111040403753-oozie-W@:start:***]Action updated in DB!
2014-02-11 20:13:14,271  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] Start action [0004636-140111040403753-oozie-W@a-first-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:15,079  WARN HiveActionExecutor:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] credentials is null for the action
2014-02-11 20:13:18,306  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] checking action, external ID [job_201401070500_217582] status [RUNNING]
2014-02-11 20:13:18,408  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] [***0004636-140111040403753-oozie-W@a-first-action***]Action status=RUNNING
2014-02-11 20:13:18,409  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] [***0004636-140111040403753-oozie-W@a-first-action***]Action updated in DB!
2014-02-11 20:13:34,367  INFO CallbackServlet:539 - USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] callback for action [0004636-140111040403753-oozie-W@a-first-action]
2014-02-11 20:13:34,424  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] action completed, external ID [job_201401070500_217582]
2014-02-11 20:13:34,443  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@a-first-action] action produced output
2014-02-11 20:13:34,653  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] Start action [0004636-140111040403753-oozie-W@some-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:35,418  WARN HiveActionExecutor:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] credentials is null for the action
2014-02-11 20:13:38,628  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] checking action, external ID [job_201401070500_217583] status [RUNNING]
2014-02-11 20:13:38,731  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] [***0004636-140111040403753-oozie-W@some-action***]Action status=RUNNING
2014-02-11 20:13:38,731  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] [***0004636-140111040403753-oozie-W@some-action***]Action updated in DB!
2014-02-11 20:13:57,659  INFO CallbackServlet:539 - USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] callback for action [0004636-140111040403753-oozie-W@some-action]
2014-02-11 20:13:57,712  INFO HiveActionExecutor:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] action completed, external ID [job_201401070500_217583]
2014-02-11 20:13:57,729  WARN HiveActionExecutor:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.HiveMain], exit code [10044]
2014-02-11 20:13:57,895  INFO ActionEndXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@some-action] ERROR is considered as FAILED for SLA
2014-02-11 20:13:57,964  INFO ActionStartXCommand:539 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@fail] Start action [0004636-140111040403753-oozie-W@fail] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-02-11 20:13:57,965  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@fail] [***0004636-140111040403753-oozie-W@fail***]Action status=DONE
2014-02-11 20:13:57,965  WARN ActionStartXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[0004636-140111040403753-oozie-W@fail] [***0004636-140111040403753-oozie-W@fail***]Action updated in DB!
2014-02-11 20:13:58,036  WARN CoordActionUpdateXCommand:542 - USER[running_user] GROUP[-] TOKEN[] APP[some-big-job-workflow] JOB[0004636-140111040403753-oozie-W] ACTION[-] E1100: Command precondition does not hold before execution, [, coord action is null], Error Code: E1100
Finished: FAILURE

wut?

Okay, Java peeps have a predilection for verbose logs, but surely there is a way to make the whole thing a little more readable for poor, poor human eyes… So I quickly hacked the following:


use 5.10.0;

use strict;

my @lines = map { parse_line($_) } <>;

# the job id is big, and doesn't give us much, remove
for my $useless ( map { $_->{JOB} } @lines ) {
    for ( @lines ) {
        $_->{msg} =~ s/\Q$useless//g;
        $_->{ACTION} =~ s/^\Q$useless//;
    }
}

my %previous;
for my $l ( @lines ) {
    # we'll only print metadata that changed
    my @changes = grep { $l->{$_} ne $previous{$_} } 
                       qw/ USER GROUP TOKEN APP JOB ACTION /;

    say join ' ', map { $_ . "[" . $l->{$_} . "] " } @changes if @changes;

    say "\t", $l->{time}, " ", $l->{msg};
    %previous = %$l;
}

sub parse_line {
    my $line = shift;

    # try to parse the line as a typical log line
    my( $time, $info ) = /^\d{4}-\d{2}-\d{2}\s*  # the date. Don't care
                           (\d+:\d\d:\d\d)       # the time, More interesting
                           ,\d+\s*.*?-           # log level and stuff. Meh
                           (.*)                  # the message itself
                         /x
        or return ();

    my %data = ( time => $time );

    # capture some repeated metadata
    for my $k ( qw/ USER GROUP TOKEN APP JOB ACTION / ) {
        $data{$k} = $1 if $info =~ s/$k\[(.*?)\]\s*//;
    }

    # useless and long, scrap it
    $info =~ s/\[\*{3}.*?\*{3}\]//;

    $data{msg} = $info;

    return \%data;
}

And there we go,  a log trace that is a mite easier on the eyes…


$ perl filter oozie_mess.log
USER[running_user]  GROUP[-]  APP[some-big-job-workflow]  JOB[0004636-140111040403753-oozie-W]  ACTION[@:start:] 
    20:13:14  Start action [@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:14  Action status=DONE
    20:13:14  Action updated in DB!
ACTION[@a-first-action] 
    20:13:14  Start action [@a-first-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:15  credentials is null for the action
    20:13:18  checking action, external ID [job_201401070500_217582] status [RUNNING]
    20:13:18  Action status=RUNNING
    20:13:18  Action updated in DB!
USER[-]  TOKEN[-]  APP[-] 
    20:13:34  callback for action [@a-first-action]
USER[running_user]  TOKEN[]  APP[some-big-job-workflow] 
    20:13:34  action completed, external ID [job_201401070500_217582]
    20:13:34  action produced output
ACTION[@some-action] 
    20:13:34  Start action [@some-action] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:35  credentials is null for the action
    20:13:38  checking action, external ID [job_201401070500_217583] status [RUNNING]
    20:13:38  Action status=RUNNING
    20:13:38  Action updated in DB!
USER[-]  TOKEN[-]  APP[-] 
    20:13:57  callback for action [@some-action]
USER[running_user]  TOKEN[]  APP[some-big-job-workflow] 
    20:13:57  action completed, external ID [job_201401070500_217583]
    20:13:57  Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.HiveMain], exit code [10044]
    20:13:57  ERROR is considered as FAILED for SLA
ACTION[@fail] 
    20:13:57  Start action [@fail] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
    20:13:57  Action status=DONE
    20:13:57  Action updated in DB!
ACTION[-] 
    20:13:58  E1100: Command precondition does not hold before execution, [, coord action is null], Error Code: E1100
Categories: DBA Blogs