Monday, July 20, 2015

How to avoid boring work

Over the past few years working as an IT consultant, I've learned that having easy access to a robust lab where I can learn or test solutions is critical. But my approach to building a test lab has changed over the years.
These days I prefer not to "build" a lab but to "define" one — it is a better use of my time.
Building requires a long step-by-step process of installing all the necessary components: OS, databases, app servers, one by one based on the documentation. 
Instead, I can define a lab using tools such as Ansible, Chef, Puppet, Oracle Enterprise Manager, on top of tools that manage Virtual Machines like Vagrant or libvirt.

I presented my thoughts during Irish Oracle User Group conference in March 2015 ( How to avoid boring workand now I would like to start a series of blog post about this.


Here is a list of topics I plan to cover in the next few weeks:
  • Creating and managing a Virtual Machine using Vagrant (create a new box, add storage, use KVM or AWS EC2 as providers )
  • Installing and configuring Oracle Database using Ansible ( Thanks to great work of Mikael Sandstr├Âm on his Github Ansible repository )
  • Integration of Delphix "Data as a Service" product with an automated test environment build

regards,
Marcin
  

Monday, December 22, 2014

Data Guard and Oracle Restart, aka relink always !!!

During my early years with PC I love to play "Leisure Suit Larry" (especially part 1 to 3 which has running on CGA or Hercules graphics card). Author of this games, Al Lowe had a good sense of humor and every time you failed your mission or stuck in a place - there was an pop up on the screen "Al says: save early, save often". After today fight with Oracle I should remember a next saying - "Relink after, relink always".

Let me explain what what happen today.

There used to be old Oracle bug in 11.2g which I described here. Basically Oracle Restart was not aware of role changes and could open a database in wrong mode after role switch. I created a workaround for it but I was really waiting for a patch. During a UKOUG 2014 I was speaking with Oracle Data Guard Product Manager - Larry Carpenter - and I learned that patch is ready. I asked @UweHesse for details and I got my numbers back - patch 15986647 and it is included in latest PSU 11.2.0.4.4.

After I got this information I was happy - I just applied this PSU last week, so let's test it.

The first run looked OK - after switchover Oracle Restart displayed proper role and start-up option on new primary
agent/ohasd/oraagent_grid/oraagent_grid.log
2014-12-22 09:02:36.511: [    AGFW][2057]{0:0:44372} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:02:36.513: [    AGFW][9207]{0:0:44372} Command: res_attr_modified for resource: ora.apppre11.db 1 1 completed with status: SUCCESS
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Attribute: ROLE for resource ora.apppre11.db modified to: PRIMARY
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre11.db modified to: open
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372}  config version updated to : 69 for ora.apppre11.db 1 1
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.663: [    AGFW][2057]{0:0:44374} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.apppre_adf.svc 1 1] ID 4355:617914
but only proper role on the new standby.
agent/ohasd/oraagent_grid/oraagent_grid.log

2014-12-22 09:03:10.420: [    AGFW][2057]{0:0:11369} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
2014-12-22 09:03:10.423: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:03:10.424: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:03:10.424: [    AGFW][2412]{0:0:11369} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369}  config version updated to : 142 for ora.apppre21.db 1 1
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
Start up option was still open instead of mount. Hmmm, I was sure that both nodes are same but again I reconfigure Data Guard Broker and Oracle Restart - no change - issue was resolved on the one server only.  Then I decided to double check if I have this PSU installed, and I found out that everything was in order in terms of PSU.
-bash-3.2$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-12-22_15-08-59PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-12-22_15-08-59PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (3) :

Patch  18235390     : applied on Mon Dec 22 14:54:02 GMT 2014
Unique Patch ID:  18349043
   Created on 4 Dec 2014, 18:50:09 hrs PST8PDT
   Bugs fixed:
     18235390

Patch  19121549     : applied on Mon Nov 17 14:06:54 GMT 2014
Unique Patch ID:  18060349
Patch description:  "OCW Patch Set Update : 11.2.0.4.4 (19121549)"
   Created on 7 Oct 2014, 03:38:04 hrs PST8PDT
   Bugs fixed:
     18328800, 18691572, 14525998, 18187697, 18348155, 17516024, 17387214
     17750548, 17617807, 17551223, 14671408, 14207615, 18272135, 18180541
     17292250, 17378618, 17500165, 18875012, 18464784, 17065496, 18848125
     13991403, 17955615, 14693336, 17273020, 17238586, 17089344, 17405605
     17531342, 17155238, 17159489, 18053580, 16543190, 17039197, 16317771
     17947785, 10052729, 16281493, 18346135, 17481314, 18199185, 18399991
     18024089, 18428146, 18352845, 18352846, 17391726, 18414137, 17001914
     17927970, 14378120, 16346413, 17305100, 15832129, 15986647, 16901346
     18068871, 17985714, 18536826, 16206997, 18752378, 16876500, 16429265
     18343490, 18336452, 16613232, 17273003, 19276791, 12928658, 18226143
     17172091, 18229842, 18053631, 16867761, 18231837, 15869775, 17483479
     18729166, 17405302, 15920201, 18709496

Patch  19121551     : applied on Mon Nov 17 14:05:34 GMT 2014
Unique Patch ID:  17971200
Patch description:  "Database Patch Set Update : 11.2.0.4.4 (19121551)"
   Created on 9 Oct 2014, 13:06:55 hrs PST8PDT
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     16929165, 16220077, 17235750, 17468141, 18191164, 17006183, 16315398
     17501491, 13955826, 17288409, 12905058, 17446237, 17375354, 16992075
     16855292, 17050888, 17546973, 18554871, 17726838, 17614134, 9756271
     18673325, 17227277, 17232014, 13853126, 17545847, 17390160, 18096714
     12747740, 17016369, 17786518, 19271443, 10136473, 16785708, 17311728
     18018515, 16268425, 17610798, 18280813, 17082359, 17783588, 14245531
     18094246, 13866822, 17477958, 16943711, 18673304, 18031668, 19463897
     19463893, 19211724, 13498382, 16450169, 17397545, 17786278, 17767676
     14458214, 19289642, 17622427, 17824637, 17716305, 16399083, 18744139
     14852021, 19727057, 17242746, 17174582, 18277454, 13645875, 14084247
     17551709, 17393683, 17614227, 17705023, 17883081, 16042673, 18996843
     16285691, 17393915, 16228604, 17655634, 17596908, 17600719, 16180763
     17754782, 17323222, 18264060, 17325413, 17343514, 17865671, 16613964
     17811447, 16069901, 17390431, 16494615, 16043574, 17006570, 17027426
     14602788, 17080436, 18673342, 17186905, 17011832, 17394950, 13944971
     17752121, 17284817, 17811456, 17238511, 17239687, 17042658, 14764829
     17602269, 17672719, 17891946, 17205719, 18262334, 16721594, 17071721
     14565184, 17265217, 17389192, 17761775, 16360112, 17982555, 17842825
     19121551, 16837842, 18139690, 17313525, 18203837, 18203838, 18203835
     18436307, 17546761, 17721717, 17344412, 17787259, 16472716, 17346671
     17588480, 13364795, 14657740, 11733603, 17811438, 19466309, 17040527
     17037130, 17088068, 17612828, 18180390, 17449815, 19458377, 19554106
     18973907, 18230522, 19544839, 17811429, 16863422, 17237521, 17951233
     17752995, 16392068, 17437634, 14338435, 13609098, 17332800, 18199537
     17465741, 17441661, 18522509, 18061914, 14133975, 17570240, 16692232
     18247991, 18328509, 16956380, 17587063, 19049453, 16618694, 17586955
     18009564, 14285317, 16542886, 17341326, 17571306, 17036973, 18641419
     16524926, 17297939, 17478145, 17040764, 17299889, 16912439, 15861775
     14054676, 13951456, 17267114, 16850630, 17648596, 14010183, 17296856
     17478514, 16875449, 18681862, 14829250, 17385178, 17443671, 16731148
     16314254, 17165204, 15979965, 19584068, 17381384, 17892268, 16198143



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

OPatch succeeded.
 
But on one server I was testing patch for OEM (18235390) to allow it to monitor tablespaces without ORA-1000 errors.Is it possible that my OEM patch add anything special ?
I was thinking about that for a while but I was not sure. So I did simple test - installed this patch on standby server and ... yes - Oracle Restart issue has been fixed. 
 
Strange, very strange - what this patch did that Data Guard was fixed ? Well it did one important thing - it relinked oracle binaries again.
 
Lucky enough I got one more pair with latest PSU ready for test.I run test with PSU installed and bug was still there. OK - so let's relink - and it was it
 

2014-12-22 14:33:40.942: [    AGFW][2057]{0:0:12827} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
2014-12-22 14:33:40.944: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify {
2014-12-22 14:33:40.945: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify }
2014-12-22 14:33:40.945: [    AGFW][9494]{0:0:12827} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre21.db modified to: mount
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827}  config version updated to : 153 for ora.apppre21.db 1 1
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
 
This is quite strange and a little bit scary. I have installed all PSU using opatch auto from Grid Infrastructure Home and there was no errors but relinking Oracle Home actually fix a problem which was included in the patch. 
 
So remember "Relink after (patch), relink always"
 
regards,
Marcin

Sunday, August 24, 2014

AWR Warehouse

I just noticed last week that there is a new patch for Enterprise Manager and it is enabling AWR Warehouse feature. There is a note ID 1901202.1 which describe bundle patch for OEM 12c release 4.

Today I had a chance to install it in my lab and now I can start testing new OEM feature.
There is some documentation here and on Kellyn's blog.


It is not configured so first task is to configure AWR Warehouse repository. In my case I will use same database which is used for OEM repository.



Retention period and staging area for snapshot files has to be configured as well.


 After these two steps AWR Warehouse configuration job is started and when it will be finished AWR Warehouse will be ready to use.
 

When repository is ready we can start adding databases which will be a source of AWR data.
 

To add a new database to warehouse it has be already configured in OEM and has a default credentials.


If all conditions are met database has been successfully added.



Now it's time to play with these new feature and see what we can achieve using it.

regards,
Marcin




Crossplatform transportable tablespaces - part 2

It took some time since I wrote a first post about TTS migration but I finished that project literally hours before my summer break. Now after couple of days while I enjoyed thermal waters and good wine of Hungary it's time to write next post.

As I described in my previous post I had to migrate database from HP-UX into Linux and also upgrade it from 10g into 12c. This time it was only PoC but my goal was to minimize downtime of production database.

Source database datasheet:
- version 10.2.0.4 
- OS - HP-UX 
- existing backup using data files copy 
- there is a one backup set per data file 
- daily incremental backups are recovered into data files and keep in FRA

On target server a new version of Oracle 12.1.0.1 has been installed and configured with ASM. New database with same character set as source database has been created as well.


Target database datasheet:
- version 12.1.0.1
- OS -Linux 64 bit
- storage - ASM

Transportable tablespaces (TTS) allow us to migrate data between databases but it is DBA responsibility to migrate rest of objects like views and PL/SQL code using for example DataPump. Before I have started a work on TTS I did the following preparation steps:
  1. On source database identify list of tablespaces and it's datafiles to move to new server
  2. On source database identify owners of objects included in TTS
    select distinct owner from dba_tables where tablespace_name like ('LIST','OF','TABLESPACES','TO','MIGRATE');
    
  3. On source database verify that tablespaces are self contained
    begin
       SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'LIST,OF,TABLESPACES,TO,MIGRATE', full_check => TRUE);
    end;
    /
    
    select * from SYS.TRANSPORT_SET_VIOLATIONS;
    
  4. On target database create owners for all objects included in TTS 

This is list of steps I performed to achieve my goal.
  1. Copy existing data files copies into new server - if other location is used on new server change script in point 2
  2. Create a script to convert data file from data file copy into data file in new location
    select 'convert datafile ''' || b.name || ''' format ''+DATA/POCDB/TTS/' || REGEXP_REPLACE(f.name,'(/disk\d/oradata/XXX/)','') || ''' from platform ''HP-UX IA (64-bit)'';' from V$BACKUP_COPY_DETAILS b, v$datafile f where f.file# = b.file#;
    
  3. Convert file using script from point 2. Example output
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_9x3xjcon_.dbf' format '+DATA/POCDB/TTS/reports01.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_aas24412_.dbf' format '+DATA/POCDB/TTS/reports02.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_22ee1445_.dbf' format '+DATA/POCDB/TTS/reports03.dbf' from platform 'HP-UX IA (64-bit)';
    convert datafile '/oracle/fra/o1_mf_pocdb_rep_34ddr545_.dbf' format '+DATA/POCDB/TTS/reports04.dbf' from platform 'HP-UX IA (64-bit)';
    
  4. Copy daily incremental backupsets into new server - if other location is used on new server change script in point 5
  5. Create a script to apply incremental backupset into new files
    set linesize 600 pagesize 999 feedback off head off trimspool on
    select 'recover from platform ''HP-UX IA (64-bit)'' foreign datafilecopy ''' || name || ''' from backupset ''' || handle || ''';'
    from V$BACKUP_DATAFILE bd, v$datafile d, V$BACKUP_PIECE bp where bd.file# = d.file#
    and bp.set_count = bd.set_count and handle is not null
    and bp.COMPLETION_TIME > sysdate -1
    order by bp.set_count;
    
  6. Recover data files copies
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports01.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkp6w_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports02.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkxg5_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports03.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppk4w9_.bkp';
    recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy '+DATA/POCDB/TTS/reports04.dbf' from backupset '/oracle/fra/POCDB/backupset/2014_07_20/o1_mf_nnnd1_TAG20140720T065649_9wppkbws_.bkp';
    
  7. Run steps 4 to 6 until cut over date
  8. Run incremental backup on source
  9. Switch all required tablespace into read only mode
  10. Export transportable tablespaces using DataPump  using parameter file like this
    directory=XXX_DPDUMP
    dumpfile=tts_aws1.dmp
    logfile=tts.log
    TRANSPORT_TABLESPACES='TABLESPACES', 'TO', 'EXPORT'
    TRANSPORT_FULL_CHECK=y
    
    EXPDP command
    expdp parfile=tts.par
    
  11. Run incremental backup on source
  12. Copy backupsets from point 8 and 11 into new server
  13. Create a script to apply incremental backupset into new files (like in point 5)
  14. Import transportable tablespaces using dump file from point 9 and all converted files. In my case first attempt took very long as I didn't excluded stats and Oracle was gathering stats during importing process. This operation can be postponed to next phase using EXCLUDE option. Example IMPDP parameter file
    directory=AWS
    dumpfile=tts_aws1.dmp
    logfile=aws_tts_import1.log
    exclude=TABLE_STATISTICS,INDEX_STATISTICS
    TRANSPORT_DATAFILES=+DATA/POCDB/TTS/reports01.dbf,
    +DATA/POCDB/TTS/reports02.dbf,
    +DATA/POCDB/TTS/reports03.dbf,
    +DATA/POCDB/TTS/reports04.dbf
    
    Run IMPDP command
    impdp parfile=imp.par
    
  15. Export source database code and users
    expdp directory=DPDUMP dumpfile=code.dmp exclude=TABLE_DATA full=y
    
  16. Import PL/SQL code - quick, dirty approach - but it was enough fot that case
    impdp directory=AWS TABLE_EXISTS_ACTION=SKIP dumpfile=code.dmp log=code_import.log full=y
    
  17. Perform backup of new database and gather new statistics

 
Performing all steps above allow me to migrate 1 TB database from HP-UX into Linux with 30 min downtime on source database. As it was POC I left source database working as main production database. For real migration time it's necessary to add time to recover last incremental backup and import TTS on new platform and also resolve issue with time necessary to gather statistics on new platform. Probably copy existing stats using PL/SQL will be solution there but it has to be check in next phase.

This post is long enough so I leave lesson learned to the next one.

regards,
Marcin



Saturday, July 26, 2014

Beauty of command line - OEM 12c

Why all software should have a command line and automation plugin ? Answer is simple - if you have to repeat number of operation for different targets - scripts can help you save your precious time.

I really enjoy a fact that Oracle added a command line to Oracle Enterprise Manager line, and now you can script lot of boring tasks like adding new administrator to list of users who can access Named Credentials.

To add new admin (przepiorom) it's enough to run the following script
 
add_privs.sh przepiorom

This is first draft of this script (no error handling but it's doing his work)
#!bin/bash

NEW_ADMIN=$1

TPID=$$
PRIV_LIST=`emcli list_named_credentials | awk '{ print $1; }' | grep -v Credential  > /tmp/priv_$TPID`


while read LINE ; do
        echo $LINE
        emcli grant_privs -name="${NEW_ADMIN}" -privilege="FULL_CREDENTIAL;CRED_NAME=${LINE}:CRED_OWNER=sysman"
done > /tmp/priv_$TPID

rm /tmp/priv_$PPID


The next example is an another script which is refreshing a Weblogic domain components.
When a new version of application is deployed a previous one are still registered as a targets and you will see it as down in your OEM.



There is a domain refresh command in OEM menu but if you have more systems going through all of those is not what you want. Using a command line and configuration file you can be done with one line.
emcli login -username=sysman -password=xxxxxxx -force
emcli refresh_wls -input_file=domain_refresh_file:/home/oracle/bin/domain_refresh_file.csv –debug

Content of domain_refresh_file.csv looks like this:

/xxx_soa_mot_domain_soa/soa,R
/xxx_soa_mot_domain_soa/soa,E

There is a one line per target split into two parts.

First part of line is a target name and domain name, ex. /xxx_soa_mot_domain_soa/soa Second part is operation: 
R - remove target which doesn't exist in domain anymore 
E - enable refresh of domain (aka. add monitoring targets)


regards,
Marcin

Wednesday, July 16, 2014

Crossplatform transportable tablespaces - part 1

There is couple of way to do heterogeneous migration of Oracle databases but staring with 12c there is whole set of new RMAN commands to transport data across different platforms.
I was looking for a best method of move tablespaces from HP-UX to Linux and after some research I found this presentation by Martin Bach from Enkitec (you can watch it online here). Martin describing Oracle Perl script (MOS ID 1389592.1) which allows you to convert tablespaces on 11g database including Exadata. At first sigh it looks like a solution for me but to use that script I need to create a new backup of all tablespaces I want to move. That could be an option but I already had a daily updated copy of all data files in FRA. So in next step I started to investigate who script is working and how to convert a backupset from HPUX to Linux and apply incremental backup into files not registered into database. Well solution was easy to predict - use PL/SQL RMAN interface - DBMS_BACKUP_RECOVERY package. 

It was not a first time when I was looking into it and it remembered me a Oracle 8i database with corrupted control file without RMAN catalog which had to be recovered. For those who forget RMAN in Oracle 8i had not a catalog functionality so you had to treat all control files or RMAN catalog with extra care. But using knowledge about files and backup names, PL/SQL and DBMS_BACKUP_RECOVERY it was possible to restore everything manually.

But let's come back to current problem - I was keen to use PL/SQL but before that I decided to check what new Oracle introduced in 12c and nice surprise - now all operations described used by Oracle perl script are possible from RMAN interface. So in next step I decided to do a little test with existing copies of data files from smallest tablespace called 'USERS'.
This small test was successful and now I need to document and describe all steps and this is material for a next post. It's working a little bit better than Oracle script as there is no need to convert backupset - recover can apply and do conversion on the fly

New syntax to learn in investigate more:
RMAN> recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy 'C:\TEMP\USERS01.DBF' from backupset 'c:\temp\inc15_2.bkp';

Starting restore at 15-JUL-14
using channel ORA_DISK_1

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 foreign file C:\TEMP\USERS01.DBF
channel ORA_DISK_1: reading from backup piece c:\temp\inc15_2.bkp
channel ORA_DISK_1: foreign piece handle=C:\TEMP\INC15_2.BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUL-14


regards
Marcin

Tuesday, July 1, 2014

Don't delete your flashback logs manually

What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance. 
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.

Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 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 option

SQL> select status from v$instance;

STATUS
------------------------------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

OK open database doesn't work. So what happen when I disable a flashback logging
SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Well still doesn't work - but what is a flashback state ?
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------------------------
RESTORE POINT ONLY

SQL> select * from v$restore_point;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 33 seq 476 thread 1:
"/u01/app/oracle/fast_recovery_area/DEV/flashback/o1_mf_9nq1wbon_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
OK at least there is some information about root cause - it is looking for missing flashback files. Information about flashback database is keep inside control file so let's try to recreate control file using trace file
SQL> alter database backup controlfile to trace as '/tmp/control.ctl';

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1336176640 bytes
Fixed Size                  2253024 bytes
Variable Size             822087456 bytes
Database Buffers          503316480 bytes
Redo Buffers                8519680 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Backup of control file has been created in trace file and edited as follow
[oracle@dev-6 tmp]$ vi control.ctl
CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/dev/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/dev/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/dev/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/dev/system01.dbf',
  '/u01/app/oracle/oradata/dev/sysaux01.dbf',
  '/u01/app/oracle/oradata/dev/undotbs01.dbf',
  '/u01/app/oracle/oradata/dev/users01.dbf',
  '/u01/app/oracle/oradata/dev/USER.dbf',
  '/u01/app/oracle/oradata/dev/DATA.dbf',
  '/u01/app/oracle/oradata/dev/DATA_INDEX.dbf',
  '/u01/app/oracle/oradata/dev/REFERENCE.dbf',
  '/u01/app/oracle/oradata/dev/REFERENCE_INDEX.dbf',
  '/u01/app/oracle/oradata/dev/ARCHIVE.dbf',
  '/u01/app/oracle/oradata/dev/apex01.dbf'
CHARACTER SET AL32UTF8
;

VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/nim_backup/backup/oracle/db/dev/%F''');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');

RECOVER DATABASE;

-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/app/oracle/oradata/dev/bct_01.log' REUSE;

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dev/temp01.dbf' REUSE;
Let's try to create new control files and open database
[oracle@dev-6 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:43:45 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 option

SQL> @control.ctl

Control file created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Media recovery complete.

Database altered.


System altered.


Database altered.


Tablespace altered.


SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------------------------
YES

SQL>
Database has been started and opened - all done. 

I blogged about that cause I have to solve this problem several time when due to space restriction flashback logs has been deleted by other DBA manually instead of disabling and enabling flashback on database. Just keep in mind if you need to release space in FRA don't delete flashback logs manually but turn off flashback on the database 

regards, 
Marcin