Flashback in a Data Guard Environment but what are the steps

The Setup of an Data Guard environment with Physical Standby via Data Guard Broker was done including an Switchover. Everything fine and works as expected. (Oracle Release is 12.1.0.2 and PSU Jan 2017).

The Dev Team asked for a Flashback Database if possible a few days ago. Okay no problem. But what do I have to do exactly.

What are the steps to do? ( The requested timestamp for Flashback on the Primary DB is „07.09.2017 17:15“ )

Stop Apply on the Standby

DGMGRL> connect sys@gtdb10_11_DG
Password:
Connected as SYSDBA.
DGMGRL> edit database gtdb10_11 set state='APPLY-OFF';

To get the Primary easily after the Flashback in Sync with the Standby you need to flashback the Standby a short time before you flashback the Primary.

Primary =   „07.09.2017 17:15“

Standby =  „07.09.2017 17:00“

sqlplus / as sysdba

flashback standby DATABASE TO TIMESTAMP(to_date('07.09.2017 17:00','dd.mm.yyyy hh24:mi'))

Now to the Primary Database and I describe the easiest way

srvctl stop db -d gtdb10_12
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 11 13:12:32 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount

SQL> flashback DATABASE TO TIMESTAMP(to_date( '07.09.2017 17:15','dd.mm.yyyy hh24:mi'))

After Flashback the Primary open the DB with resetlogs

SQL> alter database open resetlogs;

# Stop the mounted database
SQL> shutdown immediate

# Restart the Primary
srvctl start db -d gtdb10_12

Flashback done

# Now I restarted the Standby is normally not needed
Standby
srvctl stop db -d gtdb10_11
srvctl start db -d gtdb10_11

# Start the Apply on the Standby Database
DGMGRL> connect sys@gtdb10_11_DG
Password:
Connected as SYSDBA.
DGMGRL> edit database gtdb10_11 set state='APPLY-ON';
Succeeded.

The Dev Team can go on :-)

Remarks

Important is that you have to start with Flashback on the Standby Site not on the Primary Site. Otherwise you need to recreate the whole Standby Database and that could be very time intensiv by having the risk that you work without a Physical Database during the Setup. Also the Switchover has to be redone because whole Standby Database was newly created.

Restore Points

If you work with „restore points“ than keep also in mind that you create the first restore point on the Standby Database and then the Restore Point on the Primary Site.

 

 

 

 

 

 

 

Advertisements

Problems with Adaptive Features in 12.1.0.2 What’s to do?

The Adaptive Features is divided in two parts

• Adaptive Plans
  • the possibility to change plans during execution
• Adaptive Statistics
  • the statistics allow plans to be built from the results of previous executions

Version 12.1 

In 12.1 both components  will be managed by the parameter „optimizer_features_enable“ and is per default on „TRUE“.

Version 12.2

In 12.2 the parameter „optimizer_features_enable“ obsolete. The adaptive feature is now controlled by two parameters

• optimizer_adaptive_statistics default „FALSE“
• optimizer_adaptive_plans default „TRUE“

What are the recommendation for 12.1.0.2 if you do a upgrade. You should directly adopt the 12.2 default  by applying the following Patches:

• Patch 22652097 splits the parameter optimizer_adaptive_features into two, as above, and disables adaptive statistics.
• Patch 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STAT_EXTENSIONS is set to ON.

Summary

If you Upgrade to 12.1..2 set directly the Default for Oracle 12.2.

Yes, the next Upgrade to Release 12.2 came sooner or later.

 

 

 

Oracle RU is available

Today Oracle released a very large „monster“ Upgrade. This July 2017 Update includes the first time the new RU „Release Upgrade“ and RUR „Release Update Revision“ Patches.

To be up to date read the following My Oracle Support Doc-ID’s

• MOS Note:742060.1 – Release Schedule for Current Database Releases
• MOS Note: 2285040.1 – Release Update and Release Update Revisions for Database Proactive Patch Program

Actually I can’t find a FAQ but I think this will come in a few weeks.

Stay tuned :-)

Oracle ACE

Vor zwei Jahren als Oracle ACE Associate gestartet, wurde ich nun zum Oracle ACE „befördert“.  Vielen Dank

Two years ago I started as Oracle ACE Associate and now I was „promoted“ to the Oracle ACE status. Many Thanks

 

 

 

 

 

 

Oracle RAC 12.1 Summary Deployment and Admin

If you need a good overview and introduction in Oracle 12.1 RAC take a look to the following document.

RAC-12.1-Deployment-Admin

 

 

 

 

 

How to fix ‚PLAN_TABLE‘ is old version

During a lot of SQL Performance activities I saw all the time the following message but why?

Note
-----
- 'PLAN_TABLE' is old version

Yes, I did a few days before an Upgrade from 11.2.0.4 -> 12.1.0.2. Now I start to check out some important SQL Queries but I the „old version“ message

sqlplus

set autotrace traceonly explain

select * from dual;

and again

Note ------

'PLAN_TABLE' is old version

Problem is that this message came up while the plan_table came from the previous version in my case 11.2.0.4. So the solution for this is very easy

In the new environment here 12.1.0.2
SQL> connect sys as sysdba

SQL> drop table plan_table$;

SQL> @?/rdbms/admin/catplan

This shoud fix the problem and if not?

Okay it could be possible that you should drop the plan_table as Schema User  and then run the the SQL again.

 

Here an example output:
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
- parallel scans affinitized for buffer cache

 

 

 

40 Years of Oracle Database Innovation

Happy birthday Oracle :-)

 

rman auxiliary (for duplicate)

I just do some tests with the Oracle 12.2 Release and while doing tests it is all the time better to have a second database and that is the time for rman. So I looked around and found an easy way to duplicate a database from a Backup. Here the details:

My environment

OL 7.3 with an Oracle 12.2 + DATABASE BUNDLE PATCH: 12.2.0.1.170516 (25862693)

I setup an database via dbca and use Standards including the fast recovery area. Oh yes the FRA is a standard now

Next I did a backup and here I did a few tests with image copied and „backup as copy database“. While doing this I had a full backup on disk and that is the point to start with the duplicate.

What are the steps?

First I created a initdb2.dup

Yes, for testing I create only one controlfile
initdb2.dup
*.audit_file_dest='/u01/app/oracle/admin/db2_s1/adump'
*.audit_trail='NONE'
*.db_name='db2'
*.db_unique_name='db2_s1'
*.control_files='/u01/app/oracle/fast_recovery_area/db2_s1.ctl'
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest_size=8G
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.enable_pluggable_database=true
*.large_pool_size=256M
*.sga_target=2G

Next I created a few directories and then I started the new instance

sqlplus / as sysdba

startup nomount pfile=/u01/app/oracle/admin/db2/pfile/initd2.dup'

finally I start the duplicate from „BACKUP LOCATION“

 

rman auxiliary /

RMAN>duplicate database to db2 backup location '/u01/app/oracle/fast_recovery_area';

 

That’s it finished :-)

Keep in mind that I set my environment before starting the rman duplicate and the connection is done against the auxiliary instance. Yes, it is very easy to do a duplicate to a new database when you have a full backup on disc.

 

 

 

Oracle Engineered Systems Production

I found a old video concerning the „real“ production of an Oracle Engineered System but it’s really good and cool stuff.

https://vimeo.com/46778003

Have fun

 

neue DOAG Webseite ist online

Die neue DOAG Webseite ist Online.

https://www.doag.org/de/home/