Thursday, December 24, 2009

scp - Linux command line tool to copy files over ssh

scp stands for secure cp (copy), which means that you can copy files across an ssh connection that will be encrypted, and therefore secured.

You can this way copy files from or to a remote server, you can even copy files from one remote server to another remote server, without passing through your PC.
 
Syntax:
scp [[user@]from-host:]source-file [[user@]to-host:][destination-file]

Description of options


from-host: Is the name or IP of the host where the source file is, this can be omitted if the from-host is the host where you are actually issuing the command

user: Is the user which have the right to access the file and directory that is supposed to be copied in the cas of the from-host and the user who has the rights to write in the to-host

source-file:Is the file or files that are going to be copied to the destination host, it can be a directory but in that case you need to specify the -r option to copy the contents of the directory

destination-file:Is the name that the copied file is going to take in the to-host, if none is given all copied files are going to maintain its names

Options


-p  Preserves the modification and access times,
    as well as the permissions of the source-file in the destination-file
-q  Do not display the progress bar
-r  Recursive, so it copies the contents of the
source-file (directory in this case) recursively
-v  Displays debugging messages

Example


[oracle@testdb]$ scp java.tar.gz tamim@172.168.0.222:/home/tamim/
The authenticity of host '172.168.0.222 (172.168.0.222)' can't be established.
RSA key fingerprint is 23:b9:a4:b9:93:99:28:1f:4c:08:fa:8a:5f:d7:10:d0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.168.0.222' (RSA) to the list of known hosts.
tamim@172.168.0.222's password:
java.tar.gz                                   100%   35MB  11.7MB/s   00:03

File is successfully transfer to the host 172.168.0.222 in home/tamim directory

To copy a directory user scp –r

Impotent:


To use this command you need to have open-ssh installed in the hosts.

Tuesday, December 22, 2009

Run Script in Linux as a Oracle user

To run script as a oracle user we have to use su command which means change user ID or become super-user

Syntax


su [ - ] [ username [ arg ] ]

Pass the environment along unchanged, as if the user actually logged in as the specified user.

username The name of another username that you wish to log in as.

arg Additional arguments that need to be passed through the su command.

To run a script create_user.sql in oracle user use the following script


su - oracle -c $ORACLE_HOME/bin/sqlplus -s <<!
/ as sysdba
@/home/oracle/create_user.sql
disconnect
!

To run some Java console base application from rc.local as a Oracle User


[oracle@www ~]$ vi /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local
su - oracle /home/oracle/runApp.sh
 

Write a script to run a jar file as a demon process (use &), you can also run a class file in the same way.
[oracle@www ~]$ vi runApp.sh
JAVA_HOME="/usr/java/jre1.6.0_14/"
export JAVA_HOME
export PATH=$PATH:$JAVA_HOME/bin
java -jar /home/oracle/AktelCMP.jar &
 

N.B: You can also run pearl or any other application in the same way.

How to take file backup from Linux Server

This article describes a simple backup method that I use every day to backup my home Linux systems. It's an easy method that non-technical Linux users can use to backup their important data. We'll discuss the decisions you have to make in order to do a thorough backup.

What data and file should backup?


The most important files are least here:



  • System settings -- Many people never touch their system settings -- the settings are created during Linux installation and stay that way. For those people, backing up system settings is less crucial than backing up their personal settings, since a re-installation would fix things. For people who customize their systems -- e.g., changing system configuration files in /etc -- backing up these settings can be at least as important as backing up personal settings.

  • Installed software (and everything else) -- This category includes installed system software (primarily Linux) and application software (such as OpenOffice, Firefox, and the Apache Web server). Such software can usually be restored by reinstalling, but not always.



  • Your files -- This includes documents, spreadsheets, email, calendar data, financial data, downloaded music -- anything that you've created, recorded or received that has meaning and importance to you. These are clearly the most important and hardest to recreate, because you or others created them from imagination and hard work or because you paid for them.



  • Your settings -- This includes changes you've made to personal settings: desktop configuration (e.g., colors, backgrounds, screen resolution, mouse settings, locale) and program options, such as settings for OpenOffice, Gimp, your music player, and your email program. These are easier to recreate than your documents, but you'd hate to lose them -- it takes time to recreate them.


Syntax:


tar [[-]function] [options] filenames...
tar [[-]function] [options] -C directory-name...

Command-line arguments that specify files to add to, extract from,or list from an archive may  be given as shell pattern matching strings.

Backing up with tar:


c Create a new archive.
t List the contents of an archive.
x Extract the contents of an archive.
f The archive file name is given on the command line
  (required whenever the tar output is going to a file)
M The archive can span multiple floppies.
v Print verbose output (list file names as they are processed).
u Add files to the archive if they are newer than the copy in the tar file.
z Compress or decompress files automatically.

A ".tar" file is not a compressed files, it is actually a collection of files within a single file uncompressed. If the file is a .tar.gz ("tarball") or ".tgz" file it is a collection of files that is compressed. If you are looking to compress a file you would create the tar file then gzip the file.

Creating a tar file:


[root@vasappserver1 tamim]# tar -cvvf  rc.local.tar rc.local
-rwxr-xr-x root/root       419 2009-12-22 13:15:38 rc.local
[root@vasappserver1 tamim]# ls -ls
total 1932
   4 -rwxr-xr-x    1 root     root          419 Dec 22 13:15 rc.local
  12 -rw-r--r--    1 root     root        10240 Dec 22 13:16 rc.local.tar

In the above example command the system would create a tar file named TamimDataBackup.tar in the directory you currently are in of the home directory.
[root@vasappserver1 tamim]# tar -cvvf  TamimDataBackup.tar  /home/tamim
………………………………………
[root@vasappserver1 tamim]# ls -ls
total 17584
    4 -rwxr-xr-x    1 root     root          419 Dec 22 13:15 rc.local
  184 -rw-r--r--    1 root     root       184320 Dec 22 13:18 rc.local.tar
5164 -rw-r--r--    1 root     root      5273600 Dec 22 13:18 TamimDataBackup
10316 -rw-r--r--    1 root     root     10547200 Dec 22 13:19 TamimDataBackup.tar

Extracting the files from a tar file:


[root@vasappserver1 tamim]# tar -xvvf  TamimDataBackup.tar
………………………………………
[root@vasappserver1 tamim]# ls -la
………………………………………
-rw-r--r--    1 root     root      5273600 Dec 22 13:18 TamimDataBackup
-rw-r--r--    1 root     root     10547200 Dec 22 13:19 TamimDataBackup.tar
………………………………………

[root@vasappserver1 tamim]# tar -xvvzf TamimDataBackup.tar.gz
………………………………………

Note: There is no "untar" linux / unix command.

Creating a tarred file that is compressed with bzip


[root@vasappserver1 tamim]# tar -cjvf backup.tbz home/
………………………………………

Adding the j option to the tar command enables tar to compress files and/or directories using bzip. In the above example the home directory and all its subdirectories are added to the compressed backup.tbz file.

Take full backup:


The following command will perform a backup of your entire Linux system onto the ``/archive/'' file system, with the exception of the ``/proc/'' pseudo-filesystem, any mounted file systems in ``/mnt/'', the ``/archive/'' file system (no sense backing up our backup sets!), as well as Squid's rather large cache files (which are, in my opinion, a waste of backup media and unnecessary to back up):
 
tar -zcvpf /archive/full-backup-`date '+%d-%B-%Y'`.tar.gz \
    --directory / --exclude=mnt --exclude=proc --exclude=var/spool/squid

[root@vasappserver1 tamim]# tar -cpvzf tamim.tar /usr/local/

Monday, December 21, 2009

How to recover forgotten root Password in RHEL

You have to login in single-user mode and create a new root password. To enter single-user mode, reboot your computer. If you use the default boot loader, GRUB, you can enter single user mode by performing the following:

Stop 01: At the boot loader menu, use the arrow keys to highlight the installation you want to edit and type [A] to enter into append mode.

Stop 02: You are presented with a prompt that looks similar to the following:
grub append> ro root=LABEL=/

Step 03: Press the Space bar once to add a blank space, then add the word single to tell GRUB to boot into single-user Linux mode. The result should look like the following:
ro root=LABEL=/ single

Step04: Press [Enter] and GRUB will boot single-user Linux mode. After it finishes loading, you will be presented with a shell prompt similar to the following:
sh-2.05b#

Step 05: You can now change the root password by typing
passwd root

Source: RedHat Linux official Side.

Wednesday, December 16, 2009

RMAN Retore & Recover

Use the RMAN RESTORE command to restore the following types of files from copies on disk or backups on other media:
· Database (all datafiles)
· Tablespaces
· Control files
· Archived redo logs
· Server parameter files

Automates the procedure for restoring files. When you issue a RESTORE command, RMAN restore the correct backups and copies to either:
· The default location, overwriting the old files with the same name
· A new location, which you can specify with the SET NEWNAME command

RMAN Backup Clause Syntax


   RECOVER [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]
               recoverObject [recoverOptionList];

Steps for media recovery Using RMAN


Step 01:


Mount or open the database. Mount the database when performing whole database recovery, or open the database when performing online tablespace recovery.
STARTUP FORCE MOUNT;

Step 02:


To perform incomplete recovery, use the SET UNTIL command to specify the time, SCN, or log sequence number at which recovery terminates. Alternatively, specify the UNTIL clause on the RESTORE and RECOVER commands.

Step 03:


Restore the necessary files with the RESTORE command.

Step 04:


Recover the datafiles with the RECOVER command.

Step 05:


Place the database in its normal state. For example, open it or bring recovered tablespaces online.

Restore and recover the whole database


RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Script Code:
STARTUP NOMOUNT;
RUN
{
   ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
   RESTORE DATABASE;
   ALTER DATABASE MOUNT;  
RECOVER DATABASE;
}

Restore and recover a tablespace


RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Restore and recover a datafile


RMAN> SQL 'ALTER DATABASE DATAFILE 32 OFFLINE';
RMAN> RESTORE DATAFILE 32;
RMAN> RECOVER DATAFILE 32;
RMAN> SQL 'ALTER DATABASE DATAFILE 32 ONLINE';

Restore and recover the Control file from Backup


Restore the control file, (to all locations specified in the parameter file) then restore the database, using that control file:
STARTUP NOMOUNT;
RUN
{
   ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
   RESTORE CONTROLFILE;
   ALTER DATABASE MOUNT;
   RECOVER DATABASE;
}

Create a new control file


If all control file copies are lost, you can create a new control file using the NORESETLOGS option and open the database after doing media recovery. An existing standby database instance can generate the script to create a new control file by using the following statement
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

Recovery from the Loss of an Online Redo Log File


To add a new member to a redo log group, issue the following statement:
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'log_file_name' REUSE TO GROUP n

Disaster Recovery


In a disaster situation where all files are lost you can only recover to the last SCN in the archived redo logs. Beyond this point the recovery would have to make reference to the online redo logs which are not present. Disaster recovery is therefore a type of incomplete recovery.

Step 01: Connect to RMAN


$rman catalog=rman/rman@orcl target=sys/oracle@orcl

Step 02: Recover the control file if needed.


RMAN> startup nomount;
RMAN> restore controlfile;
RMAN> alter database mount;

Step 03: Collect the last SCN using SQL*Plus as SYS


SQL> SELECT archivelog_change#-1 FROM v$database;

ARCHIVELOG_CHANGE#-1
--------------------
             1203813

Step 04: Restore and Recover database using RMAN.


 
RMAN> run {
        set until scn 1203813;
        restore database;
        recover database;
        alter database open resetlogs;
        }

Restore Validation


Restore Validation confirms that a restore could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.
RMAN> RESTORE DATABASE VALIDATE;

Backup Using RMAN

Types of Files that can be Backup Using RMAN


The BACKUP command can back up of Database, which includes all data files as well as the current control file and current server parameter.

Following type of backup can be perform by RMAN



  • Tablespaces (except for locally-managed temporary tablespaces)

  • Current datafiles

  • Current control file

  • Archived redo logs

  • Current server parameter file

  • Backup sets


RMAN does not back up the following:



  • Online redo logs

  • Transported tablespaces before they have been made read/write

  • Client-side initialization parameter files or noncurrent server parameter files


RMAN Backup Clause Syntax


   BACKUP FULL Options
   BACKUP FULL AS (COPY | BACKUPSET) Options
   BACKUP INCREMENTAL LEVEL [=] integer Options
   BACKUP INCREMENTAL LEVEL [=] integer AS (COPY | BACKUPSET) Options
   BACKUP AS (COPY | BACKUPSET) Options
   BACKUP AS (COPY | BACKUPSET) (FULL | INCREMENTAL LEVEL [=] integer) Options

Database Backup

Back up the database, and then the control file which contains a record of the backup
RMAN> BACKUP DATABASE;
RMAN> BACKUP CURRENT CONTROLFILE;

Data files Backup

RMAN> BACKUP AS BACKUPSET DATAFILE
        'ORACLE_HOME/oradata/users01.dbf',
        'ORACLE_HOME/oradata/tools01.dbf';

Backup all data files in the database

Bit-for-bit copies, created on disk
RMAN> BACKUP AS COPY DATABASE;

Backup archive logs

RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-30' AND 'SYSDATE';

Backup tablespace

RMAN> BACKUP TABLESPACE system, users, tools;

Backup controlfile

RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';

Backup Server parameter file

RMAN> BACKUP SPFILE;

Backup everything

RMAN> BACKUP BACKUPSET ALL;

Create a consistent backup and keep the backup for 1 year:


Exempt from the retention policy
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS;

Backup Validation confirms that a backup could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Multilevel Incremental Backups


RMAN can create multilevel incremental backups. Each incremental level is denoted by an integer, for example, 0, 1, 2, and so forth. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data. The only difference between a level 0 backup and a full backup is that a full backup is never included in an incremental strategy.

If no level 0 backup exists when you run a level 1 or higher backup, RMAN makes a level 0 backup automatically to serve as the base.

The benefit of performing multilevel incremental backups is that RMAN does not back up all block all of the time.

Differential Incremental Backups


In a differential level n incremental backup, RMAN backs up all blocks that have changed since the most recent backup at level n or lower.

For example, in a differential level 2 backup, RMAN determines which level 2 or level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, RMAN copies all blocks changed since the base level 0 backup. If no level 0 backup is available, RMAN makes a new base level 0 backup for this file.

Use Command for incremental Level Backup


RMAN> backup incremental level 0 database tag="SUNDAY";
RMAN> backup incremental level 3 database tag="MONDAY";
RMAN> backup incremental level 3 database tag="TUESDAY";
RMAN> backup incremental level 3 database tag="WEDNESDAY";
RMAN> backup incremental level 2 database tag="THURSDAY";
RMAN> backup incremental level 3 database tag="FRIDAY";
RMAN> backup incremental level 3 database tag="SATURDAY";

Cumulative Incremental Backups


RMAN provides an option to make cumulative incremental backups at level 1 or greater. In a cumulative level n backup, RMAN backs up all the blocks used since the most recent backup at level n-1 or lower.

For example, in cumulative level 2 backups, RMAN determines which level 1 backup occurred most recently and copies all blocks changed since that backup. If no level 1 backups are available, RMAN copies all blocks changed since the base level 0 backup.

Cumulative incremental backups reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups require more space and time than differential backups, however, because they duplicate the work done by previous backups at the same level.

Use Command for Cumulative Level Backup


RMAN> backup incremental level=0 database tag='base';
RMAN> backup incremental level=2 cumulative database tag='monday';
RMAN> backup incremental level=2 cumulative database tag='tuesday';
RMAN> backup incremental level=2 cumulative database tag='wednesday';
RMAN> backup incremental level=2 cumulative database tag='thursday';
RMAN> backup incremental level=2 cumulative database tag='friday';
RMAN> backup incremental level=2 cumulative database tag='saturday';
RMAN> backup incremental level=1 cumulative database tag='weekly'

You can view your incremental Backup Details by using following Query


SQL Code: 
select  incremental_level,
        incremental_change#,
        checkpoint_change#,
        blocks
from   v$backup_datafile;

RMAN Configuration

A complete high availability and disaster recovery strategy requires dependable data backup, restore, and recovery procedures. Oracle Recovery Manager (RMAN), a command-line and Enterprise Manager-based tool, is the Oracle-preferred method for efficiently backing up and recovering your Oracle database. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and backup set compression, and integrates with Oracle Secure Backup and third party media management products for tape backup.

RMAN takes care of all underlying database procedures before and after backup or restore, freeing dependency on OS and SQL*Plus scripts. It provides a common interface for backup tasks across different host operating systems, and offers features not available through user-managed methods, such as parallelization of backup/recovery data streams, backup files retention policy, and detailed history of all backups.

Step 01: Create tablepsace to hold repository


#sqlplus sys/sys_password@orcl AS SYSDBA

SQL> CREATE TABLESPACE "RMAN"
DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\RMAN.DBF' SIZE 6208K REUSE
AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Step 02: Create user RMAN Schema owner for the backup and recovery using RMAN.


SQL>  CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

Step 03: Grant recovery_catalog_owner to RMAN user


SQL>  GRANT connect, resource, recovery_catalog_owner TO rman;

Step 04: Now connect to the RMAN and Create Repository catalog


$rman catalog=rman/rman@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 19:55:32 2009

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

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";

recovery catalog created

Step 05: Register Database Each database to be backed up by RMAN must be registered


$rman catalog=rman/rman@orcl target=sys/oracle@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 20:01:00 2009

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

connected to target database: ORCL (DBID=1223903242)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

We have done!!!! The configuration of RMAN in our Database




Some Basic Parameter of RMAN


Retention Policy:


This instructs RMAN on the backups that are eligible for deletion. For example: A retention policy with redundancy 2 would mean that two backups - the latest and the one prior to that - should be retained. All other backups are candidates for deletion.

Default Device Type:


This can be "disk" or "sbt" (system backup to tape). We will backup to disk and then have our OS backup utility copy the completed backup, and other supporting files, to tape.

Control files Auto backup:


This can be set to "on" or "off". When set to "on", RMAN takes a backup of the control file AND server parameter file each time a backup is performed. Note that "off" is the default

Parallelism:


This tells RMAN how many server processes you want dedicated to performing the backups.

Device Type Format:


This specifies the location and name of the backup files. We need to specify the format for each channel. The "%U" ensures that Oracle appends a unique identifier to the backup file name. The MAXPIECESIZE attribute sets a maximum file size for each file in the backup set.

Control files Auto backup Format:


This tells RMAN where the controlfile backup is to be stored. The "%F" in the file name instructs RMAN to append the database identifier and backup timestamp to the backup filename. The database identifier, or DBID, is a unique integer identifier for the database.

For example, one can turn off control file auto backups by issuing:
RMAN> configure controlfile autobackup off;

Show All Command


Any of the above parameters can be changed using the commands displayed by the "show all" command.
RMAN> show all;

Mechanism of Restore and Recovery operation


RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

Resynchronized Catalog


The recovery catalog should be resynchronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but if you are in doubt you can perform a full resync using the following command.
RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

RECYCLE BIN

Oracle has introduced the RECYCLE BIN which is a logical entity to hold all the deleted objects and works exactly like the recycle bin provided in Windows operating system for example. All the deleted objects are kept n the recycle bin; these objects can be retrieved from the recycle bin or deleted permanently by using the PURGE command. Either an individual object like a table or an index can be deleted from the recycle bin

Related Data Dictionary Objects


·         recyclebin$
·         dba_recyclebin   
·         recyclebin 
·         user_recyclebin



user_recyclebin and dba_recyclebin are use for recovery using flashback
 
SQL> SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';


NAME             VALUE       
----------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
recyclebin       on       
 

How to Use Recycle Bin in Oracle 10g


Starting and stopping the recyclebin
Syntax: 
ALTER SYSTEM SET recyclebin=<OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE>;

By default recyclebin is set to on
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from  dba_recyclebin;
no rows selected

SQL> drop table scott.emp;
Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from dba_recyclebin;
OBJECT_NAME                              ORIGINAL_NAME        TYPE
-----------------------------------------------------------------
BIN$iSsOuMCxThKB65n/ep2Y5g==$0    PK_EMP        INDEX
BIN$JsuRtykaSpOd8XLplx1/vA==$0    EMP           TABLE


To recover the emp table issue the following command

Syntax:
FLASHBACK TABLE <table_name> TO BEFORE DROP {RENAME TO <new_table_name>};

SQL> flashback table scott.emp to before drop;
Flashback complete.

Now emp table is restored from recyclebin.



You can also rename the table during the time of restore
SQL> flashback table scott.emp to before drop
     RENAME TO scott.emp2;
 

To clear the recycle bin issue the following command


Syntax:
PURGE TABLE <recycle_bin_name>;
 
SQL> PURGE TABLE scott.emp;
 
Table purged.

Remove Recycle Bin Objects by Tablespace and User


Syntax:
PURGE TABLESPACE <tablespace_name>
USER <schema_name>;
 
SQL Code:
PURGE TABLESPACE users USER scott;

Clear full recycilebin


Syntax:
PURGE RECYCLEBIN

Empty Everything in All Recycle Bins


SQL Code:
PURGE dba_recyclebin;
 
 

Flashback Recovery

Flashback query is a powerful and useful feature introduced in Oracle 9i, and enhanced greatly in Oracle 10g, that can help us recover data, lost or corrupted, due to human error. One big advantages of using flashback over point-in-time recovery is that for the latter not only transactions from the time of error to the current time would be lost but also the system will be unavailable for the duration of the recovery. For flashback query, on the other hand, there will be no down time needed and repair or recovery is less labor and time intensive than what it used to be in earlier versions of Oracle. With the new features like Recycle Bin, Flashback databases and Flashback Drop in Oracle 10g, the flashback capability introduced in 9i has been improved tremendously now turning a small feature into a powerful tool in the new Oracle releases.

Type of flashback recovery:



  • Flashback Database (We can revert database at a past time)

  • Flashback Drop (Reverses the effects of a DROP TABLE statement)

  • Flashback Table (Reverses a table to its state at a previous point in time)

  • Flashback Query (We can specify a target time and then run queries, viewing results and recover from an unwanted change)

  • Flashback Transaction Query (We can view changes made by a transaction during a period of time.)


Requirement for Flashback:



  • Database must be in Archive log mode

  • Must have flash recovery area enable


According to the Oracle documentation, Flashback technologies are applicable in repairing the following user errors.

  • Erroneous or malicious DROP TABLE statements

  • Erroneous or malicious update, delete or insert transactions

  • Erroneous or malicious batch job or wide-spread application errors


Dependent Objects



  • V_$FLASHBACK_DATABASE_LOG

  • V_$FLASHBACK_DATABASE_LOGFILE

  • V_$FLASHBACK_DATABASE_STAT

  • GV_$FLASHBACK_DATABASE_LOG

  • GV_$FLASHBACK_DATABASE_LOGFILE

  • GV_$FLASHBACK_DATABASE_STAT


Syntax:


Syntax base on SCN: 
SCN FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number>

Syntax base on TIMESTAMP:
TIMESTAMP FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value>

Syntax base on RESTORE POINT:
RESTORE POINT FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>

Flashback Syntax Elements


How to OFF Flashback
Syntax:
ALTER DATABASE FLASHBACK OFF;

How to ON Flashback
Syntax:
ALTER DATABASE FLASHBACK ON;

Start flashback on a tablespace


ALTER TABLESPACE <tablespace_name> FLASHBACK ON;

Stop flashback on a tablespace


ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;

Initialization Parameters


Setting the location of the flashback recovery area
db_recovery_file_dest=/oracle/flash_recovery_area



Setting the size of the flashback recovery area
db_recovery_file_dest_size=2147483648



Setting the retention time for flashback files (in minutes) -- 2 days

db_flashback_retention_target=2880

Set Retention Target
Syntax:
ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
SQL Code:
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;

How to Enable Flashback


Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes) and set recovery size up to 2 GB in file “/recovery/flashback

Step 01: Verify the Database in flash back mode and the retention_target.


SQL> SELECT flashback_on, log_mode
FROM v$database;
FLASHBACK_ON       LOG_MODE
------------------ ------------
NO                 ARCHIVELOG

Step 02: Shutdown the database and start in exclusive mode


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             268438380 bytes
Database Buffers          339738624 bytes
Redo Buffers                2940928 bytes
Database mounted.

Step 03: Enable the Archive log and Set the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.


Flash Recovery Area created by the DBA, is the allocation of space on the disk to hold all the recovery related files in one, centralized place. Flash Recovery Area contains the Flashback Logs, Redo Archive logs, backups files by RMAN and copies of control files. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters.
SQL> alter database archivelog;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=600;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST=
‘C:\oracle\product\10.2.0\flash_recovery_area\ORCL\FLASHBACK’;

System altered.

N.B: For UNIX system issue the following command
alter system set DB_RECOVERY_FILE_DEST=‘/recovery/flashback’;

Step 04: On the Flash back and open the database.


SQL> alter database flashback on;

System altered.

SQL> alter database open;

Step 05: Now Verify the Database flashback mode.


SQL> SELECT flashback_on, log_mode
FROM v$database;
FLASHBACK_ON       LOG_MODE
------------------ ------------
YES                ARCHIVELOG
SQL> SELECT name, value
FROM gv$parameter
WHERE name LIKE ‘%flashback%’;
NAME                              VALUE
---------------------------------------
db_flashback_retention_target      600
SQL> SELECT estimated_flashback_size
FROM gv$flashback_database_log;
ESTIMATED_FLASHBACK_SIZE
------------------------
22835200

How to Recover Database from Flashback recovery area


Step 01: Find the Current SCN and Flashback time.


SQL> SELECT current_scn
  2  FROM v$database;

CURRENT_SCN
-----------
    1143033

SQL> SELECT oldest_flashback_scn,oldest_flashback_time
  2  FROM gv$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
             1141575 16-DEC-09

Step 02: Grant flashback to the user.


So that user can create a restore point and flashback
GRANT flashback any table TO <user_name>;

Step 03: Shutdown the database and start in exclusive mode.


SQL> SHUTDOWN immediate;

SQL> startup mount exclusive;

Step 04: Be sure to substitute your SCN and issue the following command


SQL> FLASHBACK DATABASE TO SCN <SCN Number>;

Flashback complete.

Or If restore point create by the user
FLASHBACK DATABASE TO RESTORE POINT <RESTORE POINT>;


Or flashback using TIMESTAMP
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
FLASHBACK DATABASE TO TIMESTAMP Timestamp ‘2009-11-05 14:00:00’;
FLASHBACK DATABASE TO TIMESTAMP
TO_TIMESTAMP (‘2009-11-11 16:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

Step 05: Now open database using resetlogs


alter database open will fail
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

alter database open resetlogs will be succeed
SQL> alter database open resetlogs;

Database altered.

Step 06: See the flashback status


SELECT *
FROM gv$flashback_database_stat;

INST_ID   BEGIN_TIME  END_TIME  FLASHBACK_DATA   DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------- ------------ --------- --------------- --------- ---------- ------------------------
1         16-DEC-09   16-DEC-09 827392          9797632    130048                         0

Step 06: Now switch the log and use RMAN to clear the archive log following way


alter system switch logfile;

shutdown immediate;

startup mount exclusive;

alter database flashback off;

alter database noarchivelog;

alter database open;

SQL> SELECT flashback_on, log_mode
  2  FROM v$database;

FLASHBACK_ON       LOG_MODE
------------------ ------------
NO                 NOARCHIVELOG

Run rman to delete the archive log

$rman target sys/oracle@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 16 15:06:09 2009

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

connected to target database: ORCL (DBID=1223903242)

RMAN> crosscheck archivelog all;

RMAN> delete archivelog all;

RMAN> list archivelog all;

Note:


In RAC Database, flashback recovery area must be store is clustered file system or in ASM

Default retation target is 1440 (One Days).

If we want to retain flashback logs to perform a 48 hour flashback, set the retention target to 2880 minutes (2 days x 24 hours/day x 60 minutes/hour)

By default, flashback logs are generated for all permanent tablespaces

Important:



  • If we disable Flashback Database for a tablespace, then we must take its datafiles offline before running FLASHBACK DATABASE.

  • We can enable Flashback Database not only on a primary database, but also on a standby database.

Tuesday, December 15, 2009

Htaccess in Linux

How to Configure Your Website Using Htaccess in Linux with Apache


Step 01: For this to work successfully you will have to be logged in as root or using one of the sudo or su options.

Step 02: We will need to create the folder that will have to be authenticated. Since the default location in apache is /var/www/html we will create it here. You will do this by using the mkdir command.
[root@linux ~]# mkdir /var/www/html/testfolder

Restrict web page under /var/www/html/testfolder using basic authentication:

Step 03: Next we need to add the .htaccess & .htpasswd files to the personal folder. We first need to change the directory of the folder we wish to protect.
[root@linux ~]# cd /var/www/html/testfolder

Step 04: Next we can create the .htaccess file.
[root@linux ~]# vi .htaccess

Step 05: Press i to insert and add the following content.
AuthUserFile /var/www/html/testfolder/.htpasswd

AuthGroupFile /www.null

AuthName "Authorization Required"

AuthType Basic

require user USER_NAME

N.B. Change "test folder" to the name of your folder and change "USER_NAME" to the user name you wish to use.

Press your esc button then :wq to save your file in your vi editor.

Step 06: Next we'll create the .htpasswd file. We want to run htpasswd on the path of the folder we want to protect.
[root@linux ~]# htpasswd -c /var/www/html/testfolder/.htpasswd USER_NAME

New password:

Re-type new password:

Adding password for user USER_NAME

Step 07 : Next we will have to edit the apache httpd.conf (on some systems called the apache2.conf) file.
[root@linux ~]# vi /etc/httpd/conf/httpd.conf

Step 08: You will have to scroll all the way to the bottom to add the following directory.
#FOR MY TEST FOLDER

<Directory "/var/www/html/testfolder">

AllowOverride AuthConfig

</Directory>

Step 09: Finally save httpd.conf by typing esc :qw! and restart apache.
[root@linux ~]# service httpd restart

Step 10: To add new users, use the same command without the -c switch. For example, to add the user mahbub, type
# htpasswd .htpasswd mahbub

Step 11: To delete users, open the .htpasswd file, using your favorite unix editor, like vi, and delete the row(s) associated with the specific user(s) that you want to remove.

Restrict web page under /var/www/html/testfolder using Digest authentication:

Step 12: Add the following lines in the htttpd.conf file
<Directory "/var/www/htdocs/testfolder" >
Options None
AllowOverride None
AuthType Digest
AuthName "Protected Area"
AuthDigestFile /usr/local/Apache/conf/digest_passwd
AuthDigestGroupFile /usr/local/apache/conf/groups
Require valid-user
Order deny,allow
Deny from all
</Directory>

Step 13:create a valid user as
# htdigest -c /usr/local/apache/conf/digest_passwd "Protected Area" username

Step 14:Now restart http service

Theory of User authentication


Apache allows us to require user authentication for access to certain directories. The authentication method can be one of two types, Basic or Digest.

Basic authentication


To set up a directory that requires a user to supply a username and password we would use something like the following in our httpd.conf file:
<Directory "/var/www/htdocs/protected" >
Order deny,allow
Deny from all
Allow from 192.168.1.
AuthName "Private Information"
AuthType Basic
AuthUserFile /usr/local/apache/conf/passwd
AuthGroupFile /usr/local/apache/conf/groups
require group <group-name>
</Directory>

Firstly we have denied access to all users but those on our internal network to the directory /var/www/htdocs/protected. To require a password we use the AuthType Basic directive. Our password file is /usr/local/apache/conf/passwd, as specified by the AuthUserFile directive and, similarly, we specify a group file. The last line require group <group-name> means that a user must be a member of <group-name> in order to be allowed access to the directory.

Of course, for this to work, we must set up our password and group files. For the group file simply create a file, /usr/local/apache/conf/groups, containing the line:
group-name: user1 user2

You can specify as many groups as you wish on separate lines. List users separated by a space.

Next we create the password file with the command htpasswd -cm /usr/local/apache/conf/passwd user1. This will prompt for a password and create a user with name user1 in the file /usr/local/apache/conf/passwd. The c option will create the file if it doesn't exist, and the m option will MD5 hash the password (SHA1 and crypt options are also available, but SHA1 does not work with some Apache versions). Subsequent users can be added using htpasswd -m /usr/local/apache/conf/passwd user1.

If you do not want to use groups you could use require valid-user user1 user2 in order to only allow access to certain users.

The disadvantage of Basic Authentication is that passwords are sent as plain text from the client to the server, meaning that it is simple for a malicious user with access to the network can obtain the password using a network traffic analyzer. Digest Authentication tries to prevent this.

Digest Authentication


In digest authentication the password is never transmitted across the network. Instead the server generates a nonce, a one-time random number, and sends it to the client's browser, which then hashes the nonce with the user's password and sends the resulting hash back to the server. The server then performs the same hash and compares the result. This is considerably more secure than Basic Authentication, though not so widely used. One disadvantage of Digest Authentication is that it requires setting up a different password file for each realm on the server, as the realm name is used when creating the necessary hashes. With Basic Authentication, one password file can be used across the board.

To create an area protected by Digest Authentication, we use something like the following.
<Directory "/var/www/htdocs/protected" >
Options None
AllowOverride None
AuthType Digest
AuthName "Protected Area"
AuthDigestFile /usr/local/Apache/conf/digest_passwd
AuthDigestGroupFile /usr/local/apache/conf/groups
Require valid-user
Order deny,allow
Deny from all
</Directory>

This time we set AuthType Digest, and the AuthName "Protected Area" directive is required. In place of AuthUserFile and AuthGroupFile directives we use the AuthDigestFile and AuthDigestGroupFile directives. The group file is the same as previously, but we need to set up the password file using the command htdigest -c /usr/local/apache/conf/digest_passwd "Protected Area" user1. Note the use of the htdigest program in place of htpassword and the AuthName in the command. Again the c option creates the file if it doesn't exist.

Article Written By : Mahabub Bhai.