Set-based Archiving with Oracle

Purpose: This page presents the SQL queries used for set-based maintenance of the UCS database on an Oracle RDBMS.

Prerequisites

See Archiving and Pruning the DB for prerequisites. In particular, before using these queries you must first run archiving from UCS Manager.

Creating the Database Link

1. The tnsname.ora file must refer to the destination database host in order to enable database link creation. The file must do this even if the destination database is on the same server as the main database. Below is an example tnsname file:

   # tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\network\admin\tnsnames.ora
   # Generated by Oracle configuration tools.
   UCS =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = bsgenuscdb.emea.lucent.com)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = UCS)
       )
     )
   UCSARCH =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = bsgenuscdbarch.emea.lucent.com)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = UCSArch)
       )
     )

   In this example, the UCS entry is for the main database and UCSARCH is for the archive database. Note the following:

   (1) These names must match the names of the databases used. (2) Because is no need for a link from archive to main, you do not have to modify the tnsnames.ora on the archive database machine.
2. Ensure that the destination host is reachable from the main machine by pinging the destination host from the main machine.
3. Once the tnsname file is properly configured, the execution of the following SQL command will create the DB Link. Note that you will receive no error message even if the tnsnames.ora file or the parameters of ucsarch are incorrect.

   create database link arch using 'ucsarch';
   

   Replace ucsarch with the name that you configured in the tnsnames.ora file.

   Important

   The queries presented here describe the minimum needed to create the database link between the main and archive UCS databases. Depending on the configuration of your database, you may need to pass more parameters, such as usernames, password, schemas, tablespaces, and so on. Consult your RDBMS documentation for guidance.
4. To test the link, execute the following command, which lists the structure of the interaction table in the archive database:

   desc interaction@arch;
   

5. To drop the link, execute the following command:

   drop database link arch;
   

   Important

   Database links persist through restarts.

Moving the Data to the Archive Database

1. Use the following commands:

   create database link arch using 'ucsarch';
   insert into interaction@arch select * from interaction_arch;
   insert into emailin@arch select * from emailin_arch;
   insert into emailout@arch select * from emailout_arch;
   insert into phonecall@arch select * from phonecall_arch;
   insert into callback@arch select * from callback_arch;
   insert into chat@arch select * from chat_arch;
   insert into ixncontent@arch select * from ixncontent_arch;
   insert into ixnContentSentReceived@arch select * from ixnContentSentReceived_arch;
   insert into document@arch select * from document_arch;
   insert into cobrowseurl@arch select * from cobrowseurl_arch;
   insert into attachment@arch select * from attachment_arch;
   drop database link arch;
   

2. If the move is successful, the temporary tables can be dropped:

   drop table interaction_arch;
   drop table emailin_arch;
   drop table emailout_arch;
   drop table phonecall_arch;
   drop table callback_arch;
   drop table cobrowseurl_arch;
   drop table chat_arch;
   drop table attachment_arch;
   drop table ixncontent_arch;
   drop table ixnContentSentReceived_arch;
   drop table document_arch;
   

End

Archiving is now complete. Return to Archiving and Pruning the DB for descriptions of limitations and failure recovery methods.