Using FLASHBACK DATABASE without losing current data.

Hello Oracle freaks. Welcome back to my blog. I had this thought in my mind where DBA’s generally configure flashback in their databases but then they never really use it in any recovery scenarios. Flashback is a real gift of Oracle to save us from those human mistakes and if we know how to cleverly use it in our real time environments it will be an added advantage. I know most of us think why should I use flashback to restore user data  when I know that it will restore back the entire database to previous point in time loosing all current data. For example , suppose your application team has truncated some of its tables mistakenly and asks you to restore it back. You are in a scenario where application team has not taken the backup of the tables and there would be no other option but to restore an auxiliary instance and do tablespace point in time recovery. This is an immense time taking exercise for the DBA’s and also on top your head you need to estimate additional storage, time and knowledge  that will be required to accomplish this task. But with the method that I will show, you can easily bring back the data with FLASHBACK and what more do you need when you know that the database will be intact and will not require it to be opened with RESETLOGS option to a previous flashback time. So without wasting any more time lets go ahead.

Please Note : To implement the below steps you should have the database flashback feature on (which we generally do for Production/DR databases). If you know that you have a restore point created before that would also suffice.

In the below database I have the flashback feature on in the database .

In the above database I have the flashback retention set to 1440 minutes. This means that the database will hold the flashback logs for 24 hours by default. So logically we can rollback the database to any time or SCN that falls within the last 24 hours.

Now lets create a test table in “pulakesh”  schema of our database. Since this is 12c I will be creating the objects in the pluggable database “TESTPL1”.

Cool we got the table created . Now lets see what is the current SCN of the database. Later if we rollback the database to this SCN we should get back the table rows.

.

Got it..! Now lets assume that something went wrong and the table is truncated.

Aahh..!! never want to be in this situation without a backup. Now think in a scenario where you do not have additional storage to create an auxiliary and do tablespace point in time restore to export the table and import it back to original. Also you have time constraints and the table is the most important table of the application. Well at this point you have an option that is pretty easy and does not really require the expertise to do other stuff. Simply as usual next step would be to flashback the database to a point before the truncate. It could be time before truncate or scn before truncate. In our case lets take the SCN we recorded before the truncate.

Now the trick is at this point. We generally do a open RESETLOGS at this point to open the database. However do not do RESETLOGS here. Instead lets do a open readonly here. Remember readonly does not require the online logs and hence no resetlogs here. Cool, lets see how it works.

Perfect..! Now lets verify if we have got the table rows back.

Good as expected. For the example I have taken a small table. Now let’s assume that the rows were of GB’s and we need to get the rows exported out of this table from the READ ONLY database. But hold on the database is READ ONLY so can we do an export here ? Definitely NO. Because the moment you initiate datapump it will try to create the datapump related internal JOB table in the database and since it is in read only you will get an error. So we have passed all the hurdles except the datapump internal table creation in the database. Do not worry we have the famous database link option.  Yes it can read data of another database from any other local database. So in our case we need to get a database that is in read/write mode and the TESCON database can be reached via database link. So for this purpose lets create a database link to TESTCON from another database (EXADEV).

Here you can create the database link connecting to system user of testcon. The connect description has to use the pluggable “TESTPL1” database service name where our table was created. Prior to 12c it would be straight forward database service name.

Now since the database link is working,  we can now export the table out of the database using NETWORK_LINK option in Oracle :). Remember the role of local database “EXADEV” is only to allow datapump master table to be created on it. Lets go ahead..

Cool..! Table is exported successfully and we will need to now roll forward the original “TESTCON” database. Remember we did not do RESETLOGS, so we can recover the database upto the latest online log available before the shutdown. This would mean that all sequences are still in place and database will be as it is without losing any data (But we know how much we played with it :D).

Lets do a roll forward and see..

Remember since we are doing a complete recovery it is just “recover database” to roll forward

Bang on..! Lets see if it is in the poor state, the way we left it before flashback 🙂

Just as expected. Now is the time to import the dump back here and we are done..! Create the import directory in the database pointing to the dump location and do as below. In my case I will need to import into the pluggable database “TESTPL1”.

There you go. The rows are back and at the cost of zero data loss while flashback was still in use. This method is very simple and really does not require the expertise to do a tablespace point in time recovery on an auxiliary instance spending hours. It saves both time and storage. So until next post bye bye..!!. Happy Learning.

Either win or guide..!!

Deep dive into RMAN Active Duplicate – Manual Method

Welcome back everyone. Lets do something very interesting today. In my last post I have shown you a practical example on how the Oracle TAF configuration works. Well today its completely different. Lets take some time to understand how an active RMAN duplicate works. I am pretty sure most of us just trigger the “duplicate” clause and do some other stuff ( may be a cup of coffee or round table conference with our friends – of course not on official matters 🙂 ). While I understand that Oracle has made the database refresh activities very easy with just the single “duplicate” clause, it is also important for us as a DBA to understand how it works internally. This greatly helps in troubleshooting any issue that arises out of duplicate and for a good reason allows us to continue a failed duplicate sometimes when 70% of the task is done by RMAN. I am sure most of us simply scrap everything and start a fresh duplicate if at all it fails at a certain stage due to unforeseen circumstances. So lets get started.

So all in all do you know how many stages are involved in a RMAN duplicate ? Well to segregate below are the important stages:

1. Start up an auxiliary instance (Manual)
2. Configure static entry for the auxiliary instance  in the listener and copy password file (Manual)
3. Connect to target and auxiliary via RMAN (Manual)
4. Copy controlfile from target (RMAN duplicate)
5. Mount the auxiliary instance (RMAN duplicate)
6. Set newname for the datafiles to be copied from target to auxiliary (RMAN duplicate)
7. Copy datafiles from target (RMAN duplicate)
8. Copy archive logs from target to auxiliary that will be required for recovery of auxiliary datafiles (RMAN duplicate)
9. Rename datafiles in the auxiliary controlfile to the location of copied datafiles (RMAN duplicate)
10. Recover the auxiliary instance (RMAN duplicate)
11. Recreate auxiliary controlfile with the auxiliary database name (RMAN duplicate)
12. Open the auxiliary instance with RESETLOGS option. (RMAN duplicate)

Well thats it for the duplicate. From step number 4 till step number 11, all have been automated in the “duplicate” clause and we find it magical..hahahaha…!! However, very true..! Tremendous automation for a person who just triggers the “duplicate” and leaves it, waiting for the action to complete (Meanwhile he can do chit-chat with his friends 🙂 ). Job made easy!! So after reading this post you will be able  to perform all these steps manually.

Now for each of the above steps lets have a closer look :

• Starting up auxiliary instance

In my example the auxiliary database name will be “exadev”. Below is the content of the pfile that I created with minimum parameters –

cat /tmp/pfile.ora

Its time to create the spfile first in dbs location and make the auxiliary instance to start via spfile. Remember once the duplication is over we will create the spfile in ASM location with instance in at least mount state. If we create the spfile now in ASM it will create it under “unknown” directory as the database name is not known using a controlfile.

• Configure static entry in listener for auxiliary instance and copy password file

This step is mainly required because duplicate automatically shuts down and starts up the instance during the entire process and RMAN needs a service in the listener to be connected. If it would have been a dynamic service, during shutdown the service would have been removed by PMON from the listener and RMAN will exit out throwing error since it no longer knows to what it is connected.

cat /u01/app/grid/network/admin/listener.ora

After starting the LISTENER_DG below is the status –

Password file from target is copied to auxiliary instance $ORACLE_HOME/dbs location.

• Connect to TARGET and AUXILIARY via RMAN

Cool..! now that we are able to connect to both target and auxiliary via RMAN lets get straightaway  into duplicate action but of course our intention is to learn it manually 🙂 . Note here that in the above connect method I am using full description in place of alias because that gives me the freedom where I do not need to add the aliases in tnsnames.ora file of both target and auxiliary. Remember if you are using RMAN connect method with full description it should not contain any white spaces as you can see above I have trimmed down the description.

Action begins from here on. In conventional method straight after this connection we simply trigger “duplicate target database to exadev from active database” and this will trigger all the steps from step number 4 to step number 11 automatically. But hold on..! My post is not that simple. You are in this post to learn how you can do it manually the same set of steps. So here it goes.

• Copy controlfile from target (RMAN duplicate)

After the above target and auxiliary connection do as below –

Controlfile copied to +DATA/exadev/controlfile/backup.444.947185867. You can verify it by navigating to the location

• Mount the auxiliary instance (RMAN duplicate)

Controlfile is now copied and we can go ahead to mount the database. But hold on, to mount the database the db_name should be same as the name present in controlfile. Since the controlfile came from target whose name is “testcon”, lets make the changes in auxiliary spfile and mount the database.

***Remember at this point you need to set the db_unique_name in spfile to exadev since the datafiles are copied to the db_unique_name location in ASM.

Ok cool..!! so we have got the auxiliary instance mounted with the target controlfile that was copied. Next step..

• Set newname for the datafiles to be copied from target to auxiliary (RMAN duplicate)

Well the “set newname” clause is required because the auxiliary instance controlfile has reference to the datafile path of the target database. By using this clause we are telling RMAN to copy the datafiles of the target database to auxiliary instance over its ASM location with the db_unique_name directory (***Remember ? Just a while ago I told to set the db_unique_name parameter to original database name after setting the db_name to target database in auxiliary instance). RMAN duplicate writes a new statement for each datafile with “set newname for datafile…..” clause. However, here I will simply use the “set newname for database” clause that considers all the datafiles of the auxiliary instance to +DATA diskgroup.

• Copy datafiles from target (RMAN duplicate)

……………………………..

…………………………….

• Copy archive logs from target to auxiliary that will be required for recovery of auxiliary datafiles (RMAN duplicate)

Now lets copy the archive logs from target that will be required to recover the auxiliary instance. Note here that since RMAN uses online copies during duplicate so its logic is to copy all the archives from the minimum checkpoint that was recorded in the controlfile when it was being copied from target to auxiliary in the first step of duplication. So that makes sense. Lets find out the minimum checkpoint from the auxiliary controlfile (Remember – the minimum checkpoint has to be taken from the auxiliary controlfile)

Cool..!! Lets go ahead and copy the archivelogs from target to auxiliary with starting scn 1741639.

*************************

***********************

• Rename datafiles in the auxiliary controlfile to the location of copied datafiles (RMAN duplicate)

Remember the “set newname” clause during copy of datafiles ? Now is the time to let the auxiliary controlfile know the correct location of its copied datafiles. To do this we will simply connect as  “/” to auxiliary instance and catalog the datafiles. Once it is cataloged just use the “switch” command in RMAN to rename the datafiles in controflile.

After cataloging the files just trigger switch command in the same RMAN session.

RMAN > switch database to copy;

Lets verify now –

Perfect the controlfile now knows the copied location of the datafiles and is reflecting right there. But hold on, before recovery the controlfile should also know the location of the archivelogs that we copied in step number 8. For that just catalog the location where it copied and you are good to go.

• Recover the auxiliary instance (RMAN duplicate)

So all set..!! We have the datafiles and archivelogs in place. Lets recover it in rman duplicate style ;). Since RMAN duplicate from active recovers upto latest archive available in target hence we need to know the latest checkpoint of the target database which we can refer as “set until SCN” during the recovery of auxiliary.

****************

***************

Note here that you will need to use recover “clone” keyword to tell rman that he needs to recover the auxiliary instance 🙂

Bingoo..!! done till here. Now its the time to recreate the controlfile with original database name “exadev”.

• Recreate auxiliary controlfile with the auxiliary database name (RMAN duplicate)

Remember we changed the db_name in auxiliary to target database name and kept the db_unique_name to original ? Since all our files are now copied under db_unique_name directory in ASM and ready for controlfile re-creation with original database name “exadev”, so will revert those changes and yes in RMAN duplicate style 😉 –

Perfect..! Now is the time to recreate the controlfile and lets do it in the duplicate style again

Instance is in no mount state and cluster_database should be false. Lets do it in RMAN duplicate style

Remember since the controlfile is being recreated using RMAN method you will need to put one white space before each of the keywords from MAXLOGFILES until CHARACTER SET otherwise you will get some weird error messages (Now you know why in RMAN duplicate you will see a lot of white spaces in the create controlfile command 🙂 ). Also note the single quotes that are to be used two times. When you are triggering sql statement inside RMAN command line it has to be done to preserve the original single quote.

Ohh..!! Now you might be thinking why did I only add the system datafile and not all the datafiles during re-creation. Thats for your convenience. When you will have hundreds of datafiles its not very manageable to put them in the command line or in the script and is time consuming. Instead lets do it in the RMAN duplicate style where while recreating controlfile you generally need only the system datafile and the rest you can just catalog. Optionally, you can just put all the rest of the datafile names in the create controlfile statement in which case the below step is not required. Lets see below how to proceed with RMAN duplicate style –

Remember the “set newname” clause is required for us again. In duplicate process it does in the beginning and is within the run block so for the subsequent operation it refers that.

Lets now verify in the controlfile –

Cant expect more. Perfect..!! Now lets use the RMAN duplicate style for one last time. Yes you guessed it right. The irreversible “RESETLOGS” 🙂

Thats it. You have just nailed the RMAN duplicate and now you know what it exactly does. So next time if a duplicate fails do not scrap the entire process and redo it specially if it has completed over 50 % of its process. Its a pain to restart the process for huge restores having Terabytes of data. You will know exactly from which stage to continue manually after reading this post. Well this post was done referring active duplicate but the other options such as  duplicate from backup and without target are 99% similar. Instead of  “backup as copy” it does “restore”. Happy learning !!

*** Either win or guide ***

Basic Failover in Oracle RAC using TAF

Today I am going to give some insight on the Oracle failover capability with live examples. Well what inspired me to post this blog is that I have heard from a lot of people around me that if database is configured as Oracle RAC with at least 2 instances failover of sessions with SELECT statement happens automatically. I think people are just confused between failover and database availability. Remember failover of sessions mean if you have been doing some stuff in the database connected via a particular instance your session should automatically redirect to other available instance and continue doing the stuff whereas in case of availability any new connection will automatically pick up the available instance configured in the cluster. I will show you below how that works. In my post I will be more specific to Oracle TAF (Transparent Application Failover) feature that helps to failover sessions in case it is required during node eviction or instance termination.

Lets take the case of a 2 node Oracle RAC. In the below example, I will be connected to instance 1 . While being connected to instance 1 lets terminate the instance from another session and lets see what we get

[oracle@testd1]exadba1:$tnsping exa_taf

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 19-JUN-2017 14:14:42

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = EXADBA))) OK (0 msec)

Here the above SERVICE_NAME is the default service of the database without TAF capability.

[oracle@testd1]exadba1:$sqlplus sys@exa_taf as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 19 14:15:47 2017

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

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options

SYS@exa_taf>set time on

14:15:52 SYS@exa_taf>select instance_name from v$instance;

INSTANCE_NAME

—————-

exadba1

14:15:56 SYS@exa_taf>  <<<<———————————— ( keep this session open)

Now move over to a different session of the same instance and terminate the instance.

 [oracle@testd1]exadba1:$sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 19 14:16:52 2017

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options

SYS@exadba1>set time on

14:16:54 SYS@exadba1>select instance_name from v$instance;

INSTANCE_NAME

—————-

exadba1

14:16:55 SYS@exadba1>shut abort

ORACLE instance shut down.

14:17:10 SYS@exadba1>exit

Now that we have terminated the instance we should be getting error on the previous session which we kept open. Let see if that happens.

14:17:18 SYS@exa_taf>select instance_name from v$instance

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel Process ID: 351752 Session ID: 454 Serial number: 10174

14:17:21 SYS@exa_taf>

14:18:22 SYS@exa_taf>exit

Perfect exactly what we expected. Now that we have tested this on a service that does not have TAF capability lets do the other way round. For this I will create a TAF enabled service using the srvctl command line utility.

[oracle@testd1]exadba1:$srvctl add service -d exadba -s exa_taf -r “exadba1,exadba2” -m BASIC -e SELECT -q TRUE

***(Remember for select session to failover you will need to specify the FAILOVER_TYPE ( -e ) as SELECT) ***. For each options you can check “srvctl add service -h” to understand the description of each option and their functionality.

[oracle@testd1]exadba1:$srvctl start service -d exadba -s exa_taf [oracle@testd1]exadba1:$srvctl status service -s exa_taf -d exadba

Service exa_taf is running on instance(s) exadba1,exadba2

[oracle@testd1]exadba1:$vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora

[oracle@testd1]exadba1:$tnsping exa_taf

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 19-JUN-2017 14:26:05

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = EXA_TAF))) OK (0 msec)

Now lets connect to the database using the TAF service that I created.

[oracle@testd1]exadba1:$sqlplus sys@exa_taf as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 19 14:26:21 2017

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

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options

SYS@exa_taf>select instance_name from v$instance;

INSTANCE_NAME

—————-

exadba2

SYS@exa_taf>

SYS@exa_taf>set time on

14:27:34 SYS@exa_taf><<<<——————————————- (keep the session open)

Finally lets terminate the instance 2 and see if our failover works without interrupting our previous connection.

[oracle@testd2]exadba2:$s

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 19 14:26:55 2017

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options

SYS@exadba2>select instance_name from v$instance;

INSTANCE_NAME

—————-

exadba2

SYS@exadba2>set time on

14:27:15 SYS@exadba2>

14:27:16 SYS@exadba2>shut abort

ORACLE instance shut down.

14:27:28 SYS@exadba2>exit

Lets now go to our previous session that we kept open and do some stuff….

14:27:34 SYS@exa_taf>

14:27:36 SYS@exa_taf>select instance_name from v$instance;

INSTANCE_NAME

—————-

exadba1

14:27:40 SYS@exa_taf>exit

BINGOO…!! This time I am still connected but as you can see the instance I am connected is instance 1 (exadba1) but I kept session open for instance 2 (exadba2). This proves that I was silently redirected to instance 1 since I terminated the instance 2. This is called the actual failover of session. Now if you would have been in the middle of a select statement that was retrieving records this TAF enabled service would have continued to retrieve records even after current instance failure. This is called “SELECT” failover and is possible only when the service to which you are connected is a TAF enabled service. Starting 11g all this is possible at the server end using srvctl command line utility and is called Oracle RAC TAF configuration. Hope this helps. Happy learning…!!

*** Either win or guide ***