| Backup a particular partition [message #198610] |
Tue, 17 October 2006 23:40  |
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  |
 |
Mahesh Rajendran
Messages: 10707
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
