Oracle – Lost user’s password?

Hi everyone,

Sometimes we need to connect to the database using an unusual schema that we don’t even know the password, maybe because it was created through a script in a release or the latest DBA never stored it somewhere public or maybe due to any other reason (whatever, right? you just need to connect there and end of the story), but anyhow, you need to login using this schema specifically (to create/delete synonyms, dblinks, jobs etc…). How would you do that without the password?

shaneharrisonquestionmark1

Continue reading

Flashback – Part 7 (Flashback Data Archive)

Hey everyone,

Finally, the last part of our flashback posts, FLASHBACK DATA ARCHIVE! If you didn’t have a chance to check the previous posts, please do not hesitate to take a look if you need or if you just get curious.

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)

Well, there we go then 🙂

Continue reading

Flashback – Part 6 (Flashback Database)

Hi people,

We’re almost there to finish this Flashback Tutorial 🙂

To check the previous posts, please go through:

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)

Today’s post is gonna be about Flashback Database, a pretty good feature for non-production levels of your structure, I would say.

It’s very unlikely that you are going to rollback your entire production database to a point-in-time in the past, right? But if you need to, this facility is there.

Why do I say that it is great for non-production environments?
Continue reading

Flashback – Part 5 (Flashback Table)

Hi everybody,

Today we are going to discuss about FLASHBACK TABLE. As usual, first, I am tagging here the previous posts about Flashback Technology, so feel free to check it out if you want:

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)

So let’s do it people.

Continue reading

Flashback – Part 4 (Flashback Transaction Query)

Hi all,

If you have missed the previous Flashback posts, please go through these links to find it and read them if you feel like!

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)

And now, we are half way there to the end of the Flashback posts, let’s see a little more about FLASHBACK TRANSACTION QUERY.

Continue reading

Flashback- Part 3 (Flashback Versions Query)

Hi Everyone,

Here we are to continue our Flashback Saga! If you lost our first 2 posts about that and are in the mood for a good reading, please go through the links below:

Flashback Drop

Flashback Query

Today we are going to discuss Flashback Versions Query, which has a strong link with the previous post, the Flashback Query (AS OF). With this feature, we are able to verify all changes made  between 2 points in time in the past, using SCN or a Timestamp. Of course, the Flashback Versions Query will retrieve only the committed data. Just like Flashback Query, the Flashback Versions Query is undo-based, so make sure your undo Tablespace and undo retention period is good enough for you.

Continue reading

Flashback – Part 2 (Flashback Query)

Hey team,

This is the second part of our Flashback Tutorial and today we’re gonna talk about FLASHBACK QUERY. Please check here for the first post about Flashback Drop.

Let’s go:

FLASHBACK QUERY

In the last Flashback post, we learnt about restoring tables that were dropped from the database with the RecycleBin facility. But if you think about it, it’s way more likely that a table suffer an undesirable change, than actually be dropped. Example, when you UPDATE a table with values that are not correct, or delete values (and commit, of course), and so on, wouldn’t it be great if we could come back in the past and see how it was before the change? Thanks to the almighty Oracle Database we can! We can use the Flashback Query to see how a table was at a specific time in the past. And the best part of it, is if you are the owner of your table, you can do it yourself, no need to bother the DBA with that (definetely the best part), and you can correct your own mistakes. Also, please keep in mind that for FLASHBACK QUERY to work, we need to have our undo properly configured. To illustrate that, let’s see an example:

Let’s create our same old table:

CREATE TABLE grepora
( column1 VARCHAR2(30),
 column2 VARCHAR2(40),
 column3 VARCHAR2(20) )
  5   TABLESPACE users;

Table created.

Then, let’s insert some values on it:

SQL> insert into grepora values ('value1', 'value2', 'value3');

1 row created.

SQL> insert into grepora values ('line2', 'line2', 'line2');

1 row created.

SQL> insert into grepora values ('line3', 'line3', 'line3');

1 row created.

SQL> insert into grepora values ('line4', 'line4', 'line4');

1 row created.

SQL> insert into grepora values ('line5', 'line5', 'line5');

1 row created.

SQL> commit;

See how the table is at the moment:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

Get the SYSDATE, to know the exact date where you have this amount of data:

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> 
SQL> select sysdate from dual;

SYSDATE
-------------------
20/06/2016 16:36:07

Now, let’s make some “mistakes” here, try to change the content of the table, deleting and updating values:

SQL> delete from grepora where column1='line5';

1 row deleted.

SQL> update grepora set column1='line1', column2='line1', column3='line1' where column1='value1';

1 row updated.

SQL> commit;

Commit complete.

And see how the table is right now:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

Check that the content data of the table is different from the original version after our changes. How can we revert that if we didn’t know how it was before that?

We use the famous AS OF TIMESTAMP statement, which allow us to see the table in a different time in the past.

With the example below, check that after using the clause AS OF TIMESTAMP and using the date we caught before to DML our table, we can find the same previous data:

SQL> select * from grepora as of timestamp (to_timestamp('20/06/2016 16:36:07', 'dd/mm/yyyy hh24:mi:ss'));

COLUMN COLUMN COLUMN
------ ------ ------
value1 value2 value3
line2  line2  line2
line3  line3  line3
line4  line4  line4
line5  line5  line5

And the current version:

SQL> select * from grepora;

COLUMN COLUMN COLUMN
------ ------ ------
line1  line1  line1
line2  line2  line2
line3  line3  line3
line4  line4  line4

With this feature, we can see how a table was “before the mistake” and do the proper actions to fix it.

I hope it was clear to everyone, if you have any doubt, please get in touch with GrepOra and we’ll be glad to help.

For the next post, we’ll be doing a test case for FLASHBACK VERSIONS QUERY! Stay Tuned!

Rafael.

 

Flashback- Part 1 (Flashback Drop)

Hi everyone!

Flashback is a technology that becomes handy to the DBA when you need to recover the database from logical issues, and it is considered a great feature to use for recovery scenarios, besides RMAN. Comparing with Recovery Manager (RMAN), Flashback is way simpler mode to recover from logical issues (end users, most of the time), when RMAN is better for physical issues. These issues can be like:

  • DELETE operation with a wrong WHERE clause;
  • A table mistakenly DROPPED;
  • Wrong UPDATE commands;
  • Flashback the whole database, to a time in the the past.

And so on… The scenarios are plenty. So in order to understand each of them better, we’ll explain in details, separately, in different posts, so we don’t get tired of reading that much 🙂

The Flashback Types are:

  1. Flashback Drop
  2. Flashback Query
  3. Flashback Versions Query
  4. Flashback Transactions Query
  5. Flashback Table
  6. Flashback Database
  7. Flashback Data Archive

For this Part 1, we’ll discuss about item 1 only, and in the next posts we will continue this saga!

Most of the flashback operations are undo-based, so its up to the DBA to set up a good retention based on his own environment. The steps are:

  • Create the UNDO tablespace
  • Set the undo_retention good enough for your needs
  • Configure the tablespace to be auto-extend

Okay then, enough with the talking and let’s go right to the point.

FLASHBACK DROP

To perform Flashback Drop operations, we must have the RecycleBin enabled on the database. To make sure that your RecycleBin is enable, you can check as:

SQL> show parameter recyclebin

NAME      TYPE  VALUE
----------------------- ----------- ------------------------------
recyclebin      string  on

 

This feature allow us to restore a table that was accidentally dropped, using the RecybleBin as a source. RecybleBin is basically where your tables and associated objects (such as indexes, constraints, triggers, etc…) are sent when they are dropped (yes, they are still in the database somehow, even if you have dropped them). The Flashback Drop is capable of restoring dropped tables based on the RecycleBin. Ok GrepOra, but for how long will we gonna have the dropped objects available on the RecycleBin? They remain available until someone purge it explicitly or due to space pressure.

Here is an example of FLASHBACK DROP operation:

Create table:

SQL> CREATE TABLE grepora
( column1 VARCHAR2(30),
 column2 VARCHAR2(40),
 4 column3 VARCHAR2(20) )
 5 TABLESPACE users;

Table created.

Then drop the table:

SQL> drop table grepora;

Table dropped.

Check in the RecycleBin, with the following command, the dropped table:

SQL> select original_name, object_name, type, droptime from user_recyclebin where original_name='GREPORA';

ORIGINAL_N OBJECT_NAME   TYPE DROPTIME
---------- ------------------------------ ------ -------------------
GREPORA    BIN$NRwjojcna3XgUzvONgooCA==$0 TABLE  2016-06-12:16:06:01

Please, have a look at the OBJECT_NAME column, which now it contains the current name of the dropped table in the database, and the column ORIGINAL_NAME shows the name as it was before the drop. This happens because we can have an object with the same name created and dropped different times, so we can have all its versions available in case we need a specific one.

To prove this is real, we can simply query the dropped table using the RecycleBin’s name:

SQL> select count(*) from "BIN$NRwjojcna3XgUzvONgooCA==$0";

  COUNT(*)
----------
0

Now we have to actually use the flashback command to restore the dropped table and make it available again with the right name. To do that, we have some different ways.

Note: In case we have different versions of the table with the same name on the RecycleBin, Oracle will always choose the most recent one. If you want to restore an older version, you should use the OBJECT_NAME for the operation. 

Examples:

SQL> flashback table grepora to before drop;

Flashback complete.

SQL> select count(*) from grepora;

  COUNT(*)
---------
0

In the example above, we have successfully restored the GREPORA table using its ORIGINAL_NAME.  But what if we had different versions of the same table? 

First, let’s drop the table that we have restored, and check it on the RecycleBin.

SQL> drop table grepora;

Table dropped.

SQL> select original_name, object_name, type, droptime from user_recyclebin where original_name='GREPORA';

ORIGINAL_N OBJECT_NAME   TYPE DROPTIME
---------- ------------------------------ ------ -------------------
GREPORA    BIN$NRxYdbc4hpjgUzvONgrFng==$0 TABLE  2016-06-12:16:20:48

Create the table again, using the same DDL, and then drop it:

SQL> CREATE TABLE grepora
( column1          VARCHAR2(30),
  column2        VARCHAR2(40),
  column3          VARCHAR2(20) )
  5  TABLESPACE users;

Table created.

SQL> drop table grepora;

Table dropped.

Check the RecycleBin. We will find the two versions of our table, in different times.

SQL> select original_name, object_name, type, droptime from user_recyclebin where original_name='GREPORA';

ORIGINAL_N OBJECT_NAME   TYPE DROPTIME
---------- ------------------------------ ------ -------------------
GREPORA    BIN$NRxYdbc4hpjgUzvONgrFng==$0 TABLE  2016-06-12:16:20:48
GREPORA    BIN$NRxYdbc5hpjgUzvONgrFng==$0 TABLE  2016-06-12:16:21:41

Check that the ORIGINAL_NAME for both lines are the same. Now we can flashback any version of the same table, using the OBJECT_NAME:

SQL> flashback table "BIN$NRxYdbc4hpjgUzvONgrFng==$0" to before drop;

Flashback complete.

As we still have the other table and want to restore it as well, we obviously cannot have the same name for both of them, so we can restore it with the RENAME TO clause:

SQL> flashback table "BIN$NRxYdbc5hpjgUzvONgrFng==$0" to before drop rename to grepora_2;

Flashback complete.

And now we have both versions available to the database:

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
GREPORA_2
GREPORA

Please stay tuned for the next Flashback Posts upcoming! We’ll cover it all. I hope it was all clear to everyone. Thanks for reading and have a wonderful week!

Rafael.

Quick guide about SRVCTL

Hi everyone!

Often we caught ourselves trying to remember some simple commands to achieve what we need. And SRVCTL and its variations may be one of them 🙂

Sometimes we need to create a specific service_name to connect to an existing database, and we can, for example, have an application that use a SPECIFIC NODE, so we can configure the service name to use it that way. And we find ourselves looking for the right syntax for that. Ok, we are going to give you guys some basic examples that may be helpful

In order to check ALL the available services already created via SRVCTL we should use:

srvctl status service -d 

it should retrieve an output like that:

dbsrv {/home/oracle}: srvctl status service -d dbgrepora

Service grepora-app1 is running on instance(s) dbgrepora1

Please bear in mind that the does not necessarily match the instance name, so to make sure about the database name, run:

srvctl config database

Example:

dbsrv {/home/oracle}: srvctl config database

dbgrepora

If you have more than one database on that server, it will be returned too.

Ok, now let’s try to create a new service name for your database. In the node that you want to create the service_name, please run the following.

srvctl add service -d  -s 

where follow the rule already described above, and you can create as you wish.

Ok GREPORA, but what if i want to create a service_name to multiple instances ? You got it!

The syntax follows the same idea, but we should include different parameter in there, which is:

-r

Example:

srvctl add service -d dbgrepora -s service_dbg -r dbgrepora1,dbgrepora2

Creating the service_dbg service, and checking the status, you’ll have an output like:

dbsrv {/home/oracle}: srvctl status service -d dbgrepora -s service_dbg

Service service_dbg is running on instance(s) dbgrepora1,dbgrepora2

To stop and remove a created service just use:

srvctl stop service -d  -s 
srvctl remove service -d  -s 

 

Hope it comes to help!

Best Regards,

Rafael.