Tuesday, September 7, 2010

Oracle data files Hot Backup : A sample


1. See the data files:

SQL> select FILE_NAME from dba_data_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/DATA/dbf/siamst_system.dbf
/DATA/dbf/siamst_undo01.dbf
/DATA/dbf/siamst_undo02.dbf
/DATA/dbf/siamst_undo03.dbf
/DATA/dbf/siamst_undo04.dbf
/DATA/dbf/siamst_sysaux.dbf
/DATA/dbf/siamst_data04.dbf
/DATA/dbf/siamst_data03.dbf
/DATA/dbf/siamst_data02.dbf
/DATA/dbf/siamst_data01.dbf
/DATA/dbf/siamst_data10.dbf
/DATA/dbf/siamst_data09.dbf
/DATA/dbf/siamst_data08.dbf
/DATA/dbf/siamst_data07.dbf
/DATA/dbf/siamst_data06.dbf
/DATA/dbf/siamst_data05.dbf
/DATA/dbf/siamst_data13.dbf
/DATA/dbf/siamst_data12.dbf
/DATA/dbf/siamst_data11.dbf
/DATA/dbf/siamst_data16.dbf
/DATA/dbf/siamst_data15.dbf
/DATA/dbf/siamst_data14.dbf
/DATA/dbf/siamst_data17.dbf
/DATA/dbf/siamst_data20.dbf
/DATA/dbf/siamst_data19.dbf
/DATA/dbf/siamst_data18.dbf
/mnt/app/compressedbackup/siamst_index_8k_1.dbf
/mnt/app/compressedbackup/siamst_data_8k_1.dbf


2. Prepare the statements for taking those data files into backup mode:

SQL> select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name  2    3    4
  5  ;

TSBB
-------------------------------------------------------------
alter tablespace DATA16K begin backup;
alter tablespace DATA8K begin backup;
alter tablespace EIMDATA16K begin backup;
alter tablespace EIMINDEX16K begin backup;
alter tablespace INDEX16K begin backup;
alter tablespace INDEX8K begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace UNDO begin backup;

9 rows selected.

3. Run each SQL and then do the physical copy of the files where you want to copy those.
Once done then run the SQL set below to bring back the datafiles to normal state.


SQL> select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name  2    3    4
  5  ;

TSEB
-----------------------------------------------------------
alter tablespace DATA16K end backup;
alter tablespace DATA8K end backup;
alter tablespace EIMDATA16K end backup;
alter tablespace EIMINDEX16K end backup;
alter tablespace INDEX16K end backup;
alter tablespace INDEX8K end backup;
alter tablespace SYSAUX end backup;
alter tablespace SYSTEM end backup;
alter tablespace UNDO end backup;

9 rows selected.

No comments:

Post a Comment