Search This Blog

Tuesday, March 6, 2007

COLD_BACKUP_linux

Oracle Version : 10.2.0.1.0
RedHat Linux : Ver 3 update 4


shutdown.sql


[oracle@localhost ~]$ cat shutdown.sql
SQL> PROMPT startup force;
startup force
SQL> PROMPT shutdown normal;
shutdown normal

SQL>PROMPT exit;
exit
[oracle@localhost ~]$


coldbackup.sql

[oracle@localhost ~]$ cat coldbackup.sql
SQL> select ' host cp '||name||' /home/oracle/backup/' from v$datafile
2 union all
3 select ' host cp '||name||' /home/oracle/backup/' from v$tempfile
4 union all
5 select ' host cp '||member||' /home/oracle/backup/' from v$logfile
6 union all
7 select ' host cp ' ||name||' /home/oracle/backup/' from v$controlfile;
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/system01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/undotbs01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/sysaux01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/users01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/temp01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo03.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo02.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo01.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control01.ctl /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control02.ctl /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control03.ctl /home/oracle/backup/

SQL> PROMPT host cp /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileoracle.ora /home/oracle/backup

host cp /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileoracle.ora /home/oracle/backup

SQL> PROMPT exit;
exit
[oracle@localhost ~]$


startup.sql

[oracle@localhost ~]$ cat startup.sql
SQL> PROMPT startup
startup

SQL>PROMPT exit;
exit
[oracle@localhost ~]$



coldbkp.sh

[oracle@localhost ~]$ cat coldbkp.sh
#!/bin/bash

ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
export ORACLE_SID
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s sys/oracle as sysdba @/home/oracle/shutdown.sql
sqlplus -s sys/oracle as sysdba @/home/oracle/coldbackup.sql
sqlplus -s sys/oracle as sysdba @/home/oracle/startup.sql


[oracle@localhost ~]$


Automatic Schedule through CRONTAB utility

[oracle@localhost ~]$ crontab -l
24 19 * * * /home/oracle/coldbkp.sh > /home/oracle/backup.log


NOTE:
1.Change ORACLE_SID,ORACLE_HOME,PATH variable according your side.
2.Connect with ORACLE user NOT root.
3.Give 755 premission to COLDBKP.SH script >>>chmod 755 coldbkp.sh<<<
4.Check backup.log file for any errors. >>>/home/oracle/backup.log<<<
5.Must test your backup on TEST SERVER.

hope this helps
Taj

2 comments:

AJ said...

Hey Taj

I created a shell test.sh like this:

test.sh
____________
#!/bin/bash

ORACLE_SID=orcl
export ORACLE_SID
sqlplus sys/catdog as sysdba @/home/oracle/test.sql


but when i run this I get this error

[oracle@jora ~]$ bash ./test.sh
: command not found
': not a valid identifier: `ORACLE_SID

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 10 13:08:01 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SP2-0556: Invalid file name.
SQL

you can mail me at ajmer.kharengra@spps.org

thanks

M.S.Taj said...

Hi,
Use Double Quotes like
1.
sqlplus "/@tns_entry as sysdba"

or

2.
sqlplus "sys/pwd@tns_entry as sysdba"


Hope this helps
Taj