Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

What's the best way to perform data archiving on an Oracle database?

Tags:

oracle

archiving

I'd like to figure out the best way to archive the data that is no needed anymore, in order to improve the application performance and also to save disk space. In your experience what is the best way to implement this, what kind of tools can I use? It is better to develop an specific in house application for that purpose?

like image 692
Rulas Avatar asked Nov 11 '09 20:11

Rulas

People also ask

What is the best way to archive data?

Disk storage has become the biggest challenger to tape as the media of choice for data archiving. The availability of SATA drives with capacity of up to 2 TB and that can cost less than $150 definitely position the technology to compete with tape.

What is Oracle archival process?

"Archiving" is defined as the operation in which the archiver background process copies filled online redo log files to offline destinations. An offline copy of an online redo log is called an archived redo log. You must operate the database in ARCHIVELOG mode to archive redo log files.

What are the different archiving modes available in the Oracle database?

The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving. An archived redo log file is a copy of one of the filled members of a redo log group.

How do you archive a database?

Select Database | Manage Database Backup and Restore. In the upper right Databases pane, select the full STORAGE database you wish to archive. The database must have reached its maximum size, and a new database must be started. Right-click the STORAGE database and select Archive.

1 Answers

One way to manage archiving:

  1. Partition your tables on date range, e.g. monthly partitions
  2. As partitions become obsolete, e.g. after 36 months, those partitions can now be moved to your data warehouse, which could be another database of just text files depending upon your access needs.
  3. After moving, the obsolete partitions can be removed from your primary database, so always maintaining just (e.g.) 36 months of current data.
  4. All this can be automated using a mix of SQL/Shell scripts.
like image 198
Sam Avatar answered Sep 23 '22 10:09

Sam
Sign in to Comment

Related questions

pull access denied for container-registry.oracle.com/database/enterprise
Java OR PHP Server Side Web App and Why? [closed]
Column headings keep appearing throughout Oracle output
set serveroutput on in oracle procedure
Why is there a maximum length for stored procedure names? [duplicate]
Intermittent ORA-22288 error - command length incorrect
Is this a bug in Oracle when joining a table to a view that depends on the table
Does Oracle 12 have problems with local collection types in SQL?
ORA-12704: character set mismatch when performing multi-row INSERT of nullable NVARCHAR's
How to get cursor from Oracle using Groovy?
Is this a PL/SQL bug?
Entity Framework Can't Update Data In Table With Composite Key (Oracle)
ORA_ROWSCN - old/restored blocks don't have a real SCN assigned?
Is it possible to know what triggers would fire given a query?
Does DataGrip by JetBrains support PL/SQL development?
Left join on a table with condition on others table
Hibernate Join two unrelated table when both has Composite Primary Key

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!