DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, January 19, 2015

Using In-Database Row Archiving in 12c


    Introduction

    In-database archiving enables you to archive rows within a table by marking them as invisible. These invisible rows are in the database and are optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

    With in-database archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.

    To manage in-database archiving for a table, you must enable ROW ARCHIVAL for the table, manipulate the ORA_ARCHIVE_STATE hidden column of the table, and specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter.

    Scenario

    you first create a copy of the HR.EMPLOYEES table and enable row archival on the copy. Next, you flag the archived rows in the archival enabled table. You then query the table to verify that the archived rows are not displayed unless you enable the rows to be visible for the session.

    Prerequisites

    • Oracle Database 12c should be installed.
    • You need a container database with a pluggable database which has the HR sample schema.
    The environment used in the development of this tutorial was as follows:
    • ORACLE_HOME: /u01/app/oracle/product/12.1.0
    • TNS Listener port: 1521
    • Non-container database info:
      • SID: noncdb
    • Container database info:
      • SID: cdb1, with pluggable databases pdb1, and pdb2
      • SID: cdb2, with no pluggable databases
 

Creating a Table and Enabling Row Archival

    Connect to pdb1 as the HR user.
    . oraenv
    [Enter cdb1 at the prompt.]
    sqlplus hr/hr@localhost:1521/pdb1
    Create a copy of the HR.employees table. Call the copy HR.emp_arch, and only copy over 4 rows.
    create table emp_arch
    as select employee_id, first_name from employees where rownum <= 4;
    Enable row archival on HR.emp_arch.
    alter table emp_arch row archival;
    There is a hidden column in row-archival-enabled tables called ora_archive_state which indicates whether a row is archived or not. The hidden column is only displayed if specified in a query. First, describe the table structure ofHR.emp_arch. Notice that the ora_archive_state column is not listed.
    desc emp_arch
    Now, query the HR.emp_arch table. Display the ora_archive_state column in the query result.
    select employee_id, first_name, ora_archive_state from emp_arch;
    A value of 0 in the ora_archive_state column means the row is not archived; in other words, the row is active and visible via a standard query.
    Confirm that all rows are active by issuing a standard query. You should see all 4 rows.
    select * from emp_arch;
 

Setting the Archive State of Some Rows

    Use the dbms_ilm.archivestatename procedure to update the ora_archive_state value for employee_id 102 and 103.
    update emp_arch
    set ora_archive_state=dbms_ilm.archivestatename(1)
    where employee_id in (102, 103);
    commit;
    Confirm that now only 2 rows are visible via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 rows should be 0.
    select employee_id, first_name, ora_archive_state from emp_arch;
 

Enabling a Session to View Archived Rows

    Set the archival visibility to ALL for the session. This will allow this session to view all rows, archived or not.
    alter session set row archival visibility = all;
    Issue the same query as before, but now you should see all 4 rows.
    select employee_id, first_name, ora_archive_state from emp_arch;
    Set the archival visibility to ACTIVE for the session. This will allow this session to view only active (unarchived) rows.
    alter session set row archival visibility = active;
    Issue the same query as before, but now you should see only the 2 active rows.
    select employee_id, first_name, ora_archive_state from emp_arch;
 

Verifying that Copying a Row-Archival Table Will Not Propagate the Source Table's Archival State to the Target Table

    Use CTAS to make a copy of the HR.emp_arch table.
    create table emp_arch_copy as select employee_id, first_name from emp_arch;
    Verify that HR.emp_arch_copy does not have row archival enabled, and therefore does not contain the ora_archive_state hidden column. You should see an "invalid identifier" error for this query.
    select employee_id, first_name, ora_archive_state from emp_arch_copy;
    Query the table again, but this time omit the hidden column. Notice that all 4 rows are copied even though you set the row archival visibility for this session to active rows only.
    select employee_id, first_name from emp_arch_copy;
    Set the archival visibility to ALL for the session. From this point forward, this session should now see all rows, whether archived or not.
    alter session set row archival visibility = all;
    Enable row archival for HR.emp_arch_copy.
    alter table emp_arch_copy row archival;
    Verify that HR.emp_arch_copy now has row archival enabled, and therefore contains the ora_archive_state hidden column.
    select employee_id, first_name, ora_archive_state from emp_arch_copy;
    Use the dbms_ilm.archivestatename procedure to update the ora_archive_state value for employee_id 102 and 103.
    update emp_arch_copy
    set ora_archive_state=dbms_ilm.archivestatename(1)
    where employee_id in (102, 103);
    commit;
    Confirm that now 2 rows are active, and 2 rows are inactive, via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 active rows should be 0, and the 2 inactive rows should be 1.
    select employee_id, first_name, ora_archive_state from emp_arch_copy;
    Recall that the HR.emp_arch table you created earlier contains 2 active and 2 inactive rows. Confirm this via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 active rows should be 0, and the 2 inactive rows should be 1.
    select employee_id, first_name, ora_archive_state from emp_arch;
    Copy all rows from HR.emp_arch to HR.emp_arch_copy. To distinguish between rows that were previously copied to HR.emp_arch_copy via CTAS, and the rows being copied again now, append '_New' to the values in thefirst_name column values.
    insert into emp_arch_copy select employee_id, first_name || '_New' from emp_arch;
    commit;
    What row archival state do you think the newly inserted rows have? Remember that the archival state is not copied along with the rows.
    Query HR.emp_arch_copy to determine the ora_archive_state value for each row.
    select employee_id, first_name, ora_archive_state from emp_arch_copy;
    You should see that the ora_archive_state values from HR.emp_arch are not propagated to HR.emp_arch_copy. All of the _New rows in HR.emp_arch_copy have ora_archive_state set to the default value of 0 (active). The 2 inactive rows resulted from the update you performed in the HR.emp_arch_copy table in step 7.
 

Resetting Your Environment

    Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
    Drop the tables created in this tutorial.
    drop table emp_arch;
    drop table emp_arch_copy;

No comments: