Home » RDBMS Server » Backup & Recovery » Backup a particular partition
Backup a particular partition [message #198610] Tue, 17 October 2006 23:40 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi All,

I want to device a new strategy to backup. We have a very large database, basically it is a Datawarehouse DB. There are few dimesion table , which are very small tables. And there are few tables like fact tables(very few), which are very large in size and these tables are partioned according to the month(1-12).

Now our srategy is daily we take a export backup and weekly Cold backup. At present the logs are not archived.

I want to know can I take a particular tablespace(partition) backup in both export and cold backup. Will there be any issue while restoring??

Thanks in advance.

Brayan
Re: Backup a particular partition [message #198699 is a reply to message #198610] Wed, 18 October 2006 05:10 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> At present the logs are not archived
>> Will there be any issue while restoring??
First issue is
export dumps are NOT equal backups. They are "like" backups, which are primary meant to move data around.
If you have a database of somewhat worth, archive the logs.
For all Oracle versions above 9i, use RMAN to do the cold backups.
>> can I take a particular tablespace(partition) backup in both export and cold backup.
In export , yes.
In "cold" backup, you close the database and perform a backup. So typically, you backup all.
Not a particular partition/tablespace/whatever.


  1  create table another_emp
  2   (ename varchar2(10),
  3   deptno number(2))
  4   partition by range(deptno)
  5   (partition Aemp_deptno_p1 values less than (25),
  6*  partition Aemp_deptno_p2 values less than (45))
scott@9i > /

Table created.

scott@9i > insert into another_emp (select ename,deptno from emp);

14 rows created.

scott@9i > commit;

scott@9i > exec dbms_stats.gather_table_stats('SCOTT','ANOTHER_EMP',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

scott@9i > select table_name,partition_name,num_rows from user_tab_partitions where table_name='ANOTHER_EMP';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
ANOTHER_EMP                    AEMP_DEPTNO_P1                          8
ANOTHER_EMP                    AEMP_DEPTNO_P2                          6

scott@9i > !exp scott/tiger tables=(another_emp:aemp_deptno_p1)

Export: Release 9.2.0.7.0 - Production on Wed Oct 18 06:11:10 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                    ANOTHER_EMP
. . exporting partition                 AEMP_DEPTNO_P1          8 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

scott@9i > !exp scott/tiger tables=(another_emp:aemp_deptno_p2)

Export: Release 9.2.0.7.0 - Production on Wed Oct 18 06:11:25 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                    ANOTHER_EMP
. . exporting partition                 AEMP_DEPTNO_P2          6 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

Previous Topic: create database from a standby database
Next Topic: automate import dumpfile
Goto Forum:
  


Current Time: Wed Dec 07 07:13:37 CST 2016

Total time taken to generate the page: 0.13092 seconds