The Redwood Post

Backup technique of the week - 1 : "The Tablespace Begin backup"

2010-07-11T15:07:00.000-07:00

Here's the first article of the new "Backup technique of the week" series!

This series of posts will show you each week a new way to take a backup of your database. Hot backups, cold backups, using Oracle tool or just a simple script, you will see different ways to prepare for the worst...

Now, let's get into the subject. This first chapter will show you a very common way to take a backup using the BEGIN BACKUP command.

SQL> ALTER TABLESPACE users BEGIN BACKUP;

This command is avaliable since oracle 9i R2 - rel. 9204. What this does is that it flags the datafile headers "for backup" and performs a checkpoint on the tablespace so that no dirty buffer remains from modifications done before that point.

SQL> SELECT name, checkpoint_change#
FROM v$datafile_header;

The SCN of the datafile is frozen and all data modification in this mode will result in the affected data block to be written directly in the redo logs.
Note: Of course, using this mode will generate a lot of redo log activity!

You can then use a filesystem command to make a copy of the necessary files.

SQL> SELECT name FROM sys.v_$datafile;

SQL> SELECT member FROM sys.v_$logfile;

SQL> SELECT name FROM sys.v_$controlfile;

After you have taken your backup, you can put the tablespace back in normal mode with the END BACKUP command.

SQL> ALTER TABLESPACE users END BACKUP;

When putting the tablespace out of the BEGIN BACKUP mode, a record in the redo file will mark the end of the backup, and the "hot backup flag" in the concerned datafile headers is removed. The header SCN is written with the current one.

The backup is clean and can be restored using the archived redo logs.

Useful Bash login script

2010-06-27T11:17:00.000-07:00

Managing multiple Oracle instances on multiple servers can become a hard task if you don't standardize and organize.
How great could it be if you could get informations about your server & database displayed directly when you connect using SSH?
Information such as...

The name of the server you are connected to
The $ORACLE_SID variable
The status of the database

You want it, you have it!... Here is a small script that will test the status of your instance when you connect.

That is the code you have to use in your .bash_profile.

started=`sqlplus -s login/passwd @/home/oracle/test.sql | grep "ACTIVE"`
stop=`sqlplus -s login/passwd @/home/oracle/test.sql | grep "ERROR"`

echo -e ""
echo -e "---------------------------------"
echo -en "Welcome to "
echo $HOSTNAME
echo -e "---------------------------------"
echo -e "Database informations:"
echo -n " - SID: "
echo $ORACLE_SID
echo -n " - Status: "
if test -n "$started"
   then
   echo "Started"
elif test -n "$stop"
   then
   echo "Stopped"
else
   echo "Unknown"
fi

Next, just create a file named test.sql file in the home folder of the user.

The file will contain this query:

SELECT STATUS,

DATABASE_STATUS

FROM V$INSTANCE;

EXIT

And you're done! Just in time for the coffee.

Oracle listener statistics

2010-06-21T11:17:00.000-07:00

When you want to look for some random hacks, the first tool that comes to you mind would obviously be Nmap.

It's a very powerful tool for scanning a remote host for open ports.

I recently found an article about Nmap statistics when looking for open TNS Listeners.

Here is an extract of the article:

For experiment, I and my friends ran nmap with -iR 0 -p1521 options, meaning checking for open port 1521 on randomly generated IP addresses, infinitely.

Result: one working Oracle TNS Listener among nearly 69,000 random IP addresses.

Source: blogs.conus.info

Here is the detail of the experiment:

Top 3 operational systems:

52% - Windows
37% - Linux
6% - Solaris

Oracle TNS Listener versions:

55% - 10.2

23% - 9.2

7% - 11.1

5% - 8.1

4% - 10.1

2% - 8.0

2% - 9.0

So I decided to run the test myself, and tried the Nmap command previously shown.

After 4 hours of random IP scanning, here's my personal contribution to this analysis:

I found 6144 hosts with an Oracle TNS listener.

On the 6144 ports, 4266 were closed, 1780 were filtered and the 98 remaining were simply open.

Downloading a Youtube video using only Safari 5

2010-06-20T07:11:00.000-07:00

To download a Youtube video, there is a plethora of plugins, extensions, javascript...

But why do we need such softwares in order to simply download an internet ressource? In this article, I'll explain you how to achieve this with nothing more than your standard-issued web browser!

Go on the webpage of the video you want to download.

Display the Web Inspector. This option is located under the Developer menu, which must be activated in Safari's preference panel.

The Web Inspector gives you a detailed output of the webpage, along with the location of every element of the page.

Now it shouldn't be any problem finding an element named videoplayback.flv who weights a lot more than every other element on the page. This is the video you want to download.

A simple drag'n drop will give you the address of the resource to get.

The .flv file is readable with VLC, or can be easily converted to the format you prefer.