Backup SQL Server 2008 using Symantec Netbackup 7.0(Windows)

Hello all,

Today we will discuss how to backup SQL Server 2008 using Symantec Netbackup.

It is assumed that Netbackup Server is installed and you already have set up connection between Master and Media servers. If it is so, let’s start.

First of all, you should install Netbackup Client on the server where SQL database is located.

Client Server Configuration

1. Download installation file from the internet(I will provide download link later)

2. Run Setup.exe

Click Next-> Check I agree….-> Next ->

image

enter Master Server Name

Let’s stop here! and add more actions before we do this.

* Add the following entries in C:\Windows\System32\drivers\etc\hosts

192.168.1.110  netBackupMaster

Master server ip and hostname .

Also you should have already opened connection between client server and master server (bidirectional TCP port 13724).

So my window look like this :

image

There may appear the window like this:

image

Click Yes –> Then Install-> Finish.

Prepare Backup Script

* Open  NetBackup MS SQL Client (Start->All Programs->Symantec NetBackup->NetBackup Agents).

* Fill the section Userid and Passwod for SQL Server Standard or Mixed Security with the correct credentials.

*From the menu bar choose File->Backup SQL Server objects

** select databases
**
choose Save instead of Launch immediately
**
click Backup, save the .bch file in your desired location.

Master Server Configuration

In NetBackup Server Administration Console go to the Clients-> in the menu bar Actions-> Configure Client->

image

Enter the ip address of the client, where SQL Server is installed.

In the Client Properties window  go to the section Servers –> Click first Add button and enter master server name(for me it will be netBackupMaster) , select entry in Additional servers  and click Make Master. After this you should see in  Master Server and Additional server  text boxes the name of the master you entered now.
In the section Client Name check if the value is correct. Click OK.

Create Backup Policy

In Policies->
* Attributes
** Policy type: MS-SQL-Server
* Schedules
** indicate your desired schedule
* Clients
** Click New button and enter ip or hostname  of the client, indicate its operating system…it is simple
* Backup Selection
** Click New button and find our created .bch script, click OK

Do Manual Backup to test it .

That is all.

In addition, create folder dbclient in C:\Program Files\Veritas\NetBackup\logs\ for troubleshooting purposes. There will be located log files, which is very useful for problem solving with Netbackup.

Advertisements

Oracle recycle bin

There exits two recycle bins USER_RECYCLEBIN  the same as RECYCLEBIN and DBA_RECYCLEBIN.

PREPARING DEMO…

–Create a test table

CREATE TABLE testtb (testcol NUMBER);

–Insert row

INSERT  INTO testtb VALUES(1);

COMMIT;

–Drop it

DROP TABLE  testtb;

–Query recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 
FROM   RECYCLEBIN;

–Output

OBJECT_NAME                     | ORIGINAL_NAME | DROPTIME

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

BIN$nNV1TgjzTeeusBT1fqxeyw==$0  | TESTTB        | 2011-04-05:11:14:46

As you can see Oracle renames table to a system generated name which starts with BIN$.

PURGING….DELETING

This table is not actually dropped, it is just renamed and is still taking the place in the tablespace.

To relieve the space there is several ways:

–Globally clear the recycle bin

PURGE DBA_RECYCLEBIN;

–Clear recycle bin by tablespace

PURGE TABLESPACE users;

–Clear recycle bin in you schema

 PURGE RECYCLEBIN;
or
 USER_RECYCLEBIN;

–Purge individual object

PURGE TABLE "BIN$nNV1TgjzTeeusBT1fqxeyw==$0"

IMPORTANT NOTE!!!

In one of the Oracle tests, there is the question which says that you cannot purge object from the recycle bin by specifying its original name.

Like that:

PURGE TABLE testtb

BUT it works!!!

–Check it

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 
FROM   RECYCLEBIN;

–Output

OBJECT_NAME  |  ORIGINAL_NAME | DROPTIME 

Sometimes Oracle purges objects itself from recycle bin in the following cases:

* When user hits its quota limit on the tablespace
      At that point oracle purges objects from the recycle bin using FIFO method. So purges the oldest one until it’s enough.

* Before a datafile is autoextended .

SELECTING…

I am using table which is created in DEMO section.

–Query

SELECT * FROM "BIN$nNV1TgjzTeeusBT1fqxeyw==$0"; 

–Output

TESTCOL 

1

FLASHBACKING…RECOVERING

FLASHBACK TABLE testtb TO BEFORE DROP

–Query

SELECT * FROM  testtb

–Output

TESTCOL 

1 

Now let’s do the following:

–Create a test table

CREATE TABLE testtb (testcol NUMBER);

–Drop it

DROP TABLE  testtb;

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 
FROM   RECYCLEBIN;

–Output

OBJECT_NAME                    | ORIGINAL_NAME | DROPTIME

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

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0 | TESTTB        | 2011-04-05:14:07:08

Again repeat these steps:

–Create a test table

CREATE TABLE testtb (testcol NUMBER);

–Drop it

DROP TABLE  testtb;

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 
FROM   RECYCLEBIN;

–Output

OBJECT_NAME                   | ORIGINAL_NAME | DROPTIME 

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

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0| TESTTB        | 2011-04-05:14:07:08 

BIN$ZdI0ZO98R8mLymiv96CALA==$0| TESTTB        | 2011-04-05:14:07:12

If you flashback the TESTTB table , the most resent one will be flashbacked. Let’s check it:

–Flashback table

FLASHBACK TABLE testtb TO BEFORE DROP

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME 
FROM   RECYCLEBIN;

–Output

OBJECT_NAME                    | ORIGINAL_NAME | DROPTIME 

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

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0 | TESTTB        | 2011-04-05:14:07:08

Look at the DROPTIME, it shows that the oldest entry was left in the recycle bin.

But if you want to flashback that one, not the current one, do it:

Assume that we have not flashbacked any table and we have the following entries in the recycle bin:

OBJECT_NAME                   | ORIGINAL_NAME | DROPTIME 

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

BIN$wkk5qTDzS7OsaI+LHOVv6g==$0| TESTTB        | 2011-04-05:14:07:08 

BIN$ZdI0ZO98R8mLymiv96CALA==$0| TESTTB        | 2011-04-05:14:07:12

And now we want to flashback the table which was dropped for the first time.

FLASHBACK TABLE "BIN$wkk5qTDzS7OsaI+LHOVv6g==$0" TO BEFORE DROP

–Check the recycle bin

SELECT OBJECT_NAME,ORIGINAL_NAME,DROPTIME
FROM   RECYCLEBIN;

–Output

OBJECT_NAME                    | ORIGINAL_NAME | DROPTIME 

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

BIN$ZdI0ZO98R8mLymiv96CALA==$0 | TESTTB        | 2011-04-05:14:07:12

But if the table by this name already exist, then you will need to rename it before flashbacking:

FLASHBACK TABLE "BIN$wkk5qTDzS7OsaI+LHOVv6g==$0" 
TO BEFORE DROP RENAME TO testtb1

When the table is dropped and placed into the recycle bin the following objects that refers to that table also are placed in the recycle bin:

* Indexes

* LOG segments

* Triggers etc…

BUT the following objects are not placed into the recycle bin:

* Bitmap join indexes

* Materialized view logs

* Referential integrity constraints

So when you flashback the table the following objects such as bitmap join indexes, materialized view logs, referential integrity constraints will not be recovered.

If you drop index before dropping a table, this index will not be recovered during flashbacking the table. The same is with other objects that should be located into the recycle bin.

DISABLING RECYCLING…

The initialization parameter RECYCLEBIN identifies if the dropped object should be placed into the recycle bin or not.

The default value is ON, means that it should.

You can disable it at the system or session level.

ALTER SESSION SET RECYCLEBIN=OFF

After that no object will be placed into the recycle bin, consequently they can’t be flashbacked. If there were objects into the recycle bin before setting RECYCLEBIN  parameter to OFF you are able to flashback them.

There is another way to delete object without locating it into the recycle bin:

DROP TABLE testtb;
;

!!!IMPORTANT

Recycle bin exists for the tables only in non-SYSTEM, locally managed tablespaces. 

So you can’t flashback the table from recycle bin in SYS schema, because this schema  is located in SYSTEM tablespace and doesn’t have any recycle bin.

Changing SYSMAN password(can cause problems)

After changing SYSMAN password from PL/SQL Developer, Enterprise Manager showed me: Agent Unreachable. I started to drill down and checked status of the agent on Oracle server. It showed me that agent was started. But after running the following:

emctl upload

It showed me the exact error: ORA-28000: the account is locked.

Let’s clarify: I’ve changed SYSMAN password, but EM stores and uses the old password during connecting(because changes from PL/SQL developer was not reflected to EM repository) . That’s why SYSMAN account was going to lock.

If you try to go to the EM URL it will show some unusual messages, that database is in shutdown or needs recovery. Don’t do anything! (this is caused because of the SYSMAN password has been changed, neither database is down nor needs recovery)

Let’s just solve it.

1. Stop dbconsole

emctl stop dbconsole

2. Go to the file $ORACLE_HOME/<hostname_sid>/sysman/config/emoms.properties and change the following lines to:

oracle.sysman.eml.mntr.emdRepPwd=<your_new_password>

oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

Don’t worry about plain text password , it will be encrypted automatically after you save the file.

3. Start dbconsole

emctl start dbconsole

Enjoy!

Tablespace Backup Using Cron on Linux

Let’s do all things, step by step:

1. Create directories for Backup, Script and Logs.

mkdir -p /u02/Backup/Backup

mkdir -p /u02/Backup/Scripts

mkdir -p /u02/Backup/Logs

2. Create backup script.

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export ORACLE_SID=my_sid

$ORACLE_HOME/bin/rman target / msglog /u02/Backup/Logs/BackupTBS_MYTABLESPACE.log append << EOF

run{

backup tablespace MYTAVLESPACE format '/u02/Backup/Backup/BackupTBS_MYTABLESPACE_%T.bkp';

delete noprompt backup of tablespace MYTABLESPACE completed before 'SYSDATE-1';

}

EOF

save this script as  MYTABLESPACE_disk_backup.sh

3. Change permissions and owner of this file.

chown oracle:oinstall MYTABLESPACE_disk_backup.sh

chmod a+x MYTABLESPACE_disk_backup.sh

4. Create cron job.

su - oracle

crontab -e

--add the following line(this script will run at 8:00 PM everyday)

00 20 * * * /u02/Backup/Scripts/MYTABLESPACE_disk_backup.sh

cron format is the following:

minute (0-59), hour (0-23, 0 = midnight), day (1-31), month (1-12), weekday (0-6, 0 = Sunday)
*                    *                                    *                *                   *

asterisk means –> every

5. Reload cron service

su -

service crond reload

That is all.