Mounting an NTFS filesystem on Oracle Enterprise Linux

To access an NTFS file system from Linux (OEL) you need either:

• (a) need to have NTFS support compiled in the kernel or
• (b) need to download and install packages that allow NTFS file systems to be mounted in userspace

Historically, few of the Linux distributors have ship with out-of-the-box support for NTFS file systems. As pointed out here, while there is a lot of public information about about MSDOS and VFAT, Microsoft has not published much details of NTFS, so the implementations vary in stability and features.

Initially you should check whether NTFS modules are already installed on your system.

$ ls -l /lib/modules/2.6.32-35-generic/kernel/fs/ | grep ntfs
drwxr-xr-x 2 root root  4096 2011-11-08 09:12 ntfs

In case no modules are found, you need to download and install NTFS packages.

• NTFS-3G is an open source implementation of Microsoft's NTFS file system that includes read and write support.
• NTFS-3G developers use the FUSE file system to facilitate development and to help with portability

Step 1: Download fuse and ntfs-3g

fuse: get it from http://www.atrpms.net/dist/el5/fuse/ or http://sourceforge.net/projects/fuse/files/fuse-2.X/
ntfs-3g: You can download it from http://pkgs.repoforge.org/fuse-ntfs-3g/

Step 2: Install fuse components

# yum install fuse
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package fuse.i386 0:2.7.4-8.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved
================================================================================================
Package            Arch             Version             Repository           Size
================================================================================================
Installing:
fuse               i386             2.7.4-8.el5         ol5_u6_base          84 k

Transaction Summary
================================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total download size: 84 k
Is this ok [y/N]: y
Downloading Packages:
fuse-2.7.4-8.el5.i386.rpm                                                  |  84 kB     00:00     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: fuse    1/1 
Installed: fuse.i386 0:2.7.4-8.el5                                                                                                      

Complete!
#

• If you can't perform a yum installation, you can try installing fuse from your OEL media or performing a manual installation with the package downloaded from forgesource, as described here.

Step 3: Install fuse-ntfs-3g

# rpm -Uvh fuse-ntfs-3g-2010.5.22-1.el5.rf.i386.rpm 
warning: fuse-ntfs-3g-2010.5.22-1.el5.rf.i386.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6
Preparing...                ########################################### [100%]
1:fuse-ntfs-3g           ########################################### [100%]

Step 4: Mount the NTFS file system.

• Once installed, reconnect the device and OEL should recognize it automatically

# mount.ntfs-3g  

Oracle DBMS_FLASHBACK package

Oracle Flashback technology

Logical Flashback (do not depend on RMAN, rely on undo data) Oracle Flashback Query (Oracle 10g) Oracle Flashback Version Query Oracle Flashback Transaction Query Oracle DBMS_FLASHBACK (Oracle 9i) Oracle Flashback Transaction (New 11g) Oracle Flashback Table - Rewind table to a point back in time Oracle Flashback Drop - Reverse the effects of DROP TABLE Physical flashback Oracle Flashback Database Oracle Flashback Data Archive (Oracle Total Recall)(11g) New on Oracle 11g: Oracle Flashback Transaction Oracle Flashback Data Archive (Oracle Total Recall)

Using DBMS_FLASHBACK package

• Released with Oracle 9i
• Provides the same functionality as Oracle Flashback Query (Oracle 10g)
• Acts as a time machine:
• 1. set time to a previous point in time.
• 2. perform queries without the "AS OF" and "VERSIONS BETWEEN" clauses
• 3 return time to the "present"

Requirements:

• You need EXECUTE privilege on DBMS_FLASHBACK package
• The UNDO_RETENTION parameter defines how far back in time an object can be flashbacked.
• UNDO_RETENTION can be set in the init.ora or with:
• ALTER SYSTEM SET UNDO_RETENTION = num_seconds;

Using the DBMS_FLASHBACK package:

1. Specify a past time with DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.
2. Perform regular queries (queries without flashback-feature syntax such as AS OF).
   Do not perform DDL or DML operations.
3. Return to the present by invoking DBMS_FLASHBACK.DISABLE.

(a) Check current data in the emp table

SQL> select * from emp;

 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900      

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432709                12-08-10 10:58:45  

(b) modify data in emp table

SQL> Insert into emp values (3, 'Mark', 5000);
SQL> commit;

SQL> Update emp set salary = 0 where empno=1;
SQL> commit;

SQL> Delete from emp where empno=2;
SQL> update emp set empname = 'Lenny' where empno=100;
SQL> commit;

SQL> select * from emp;

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       0                      
100                    Lenny      900                    
3                      Mark       5000   

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432739                12-08-10 10:59:30 

To view and restore the original data, you can
(1) create a cursor to hold the original data
(2) go back in time with dbms_flashback.enable_at_system_change_number(n);
(3) Open and fetch the data in the cursor.
(4) return to the present with dbms_flashback.enable;
(5) Insert the data in the cursor in the restore table

(c) Create a table to restore original data

SQL> create table emp_restore
     as select * from emp where 1 = 0;


set serveroutput on
DECLARE
  cursor c_emp is 
     select * from emp;
  v_row c_emp%rowtype;
BEGIN
  dbms_output.put_line('enabling dbms_flashback..');
  dbms_flashback.enable_at_system_change_number(1432709);
  Open c_emp;
  dbms_output.put_line('disabling dbms_flashback..');  
  dbms_flashback.disable;
  
  loop 
    fetch c_emp into v_row;
    exit when c_emp%notfound;
    insert into emp_restore values
      (v_row.empno, 
       v_row.empname,
       v_row.salary);
  end loop;
  close c_emp;
  commit;
END;
/
enabling dbms_flashback..
disabling dbms_flashback..

SQL> select * from emp_restore; 

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900 

Oracle data storage: ROWIDs

About Rowids

• ROWIDs uniquely identifiy a row in the database. (although there are some exceptions).
• ROWID may refer to a datatype and a pseudocolumn. In its different forms it is a representation of the physical location of a row within the database.
• The ROWID for a row can change as the result of dumping and reloading the database.
• For this reason ROWID values should not be used across transaction lines.
• You cannot set the value of a ROWID pseudocolumn.
• ROWID is not physically stored in the database.It is inferred from the file and block address of the data.

ROWIDs are used in the construction of indexes. In addition to this,

• Rowids are the fastest means of accessing particular rows.
• Rowids provide the ability to see how a table is organized.
• Rowids are unique identifiers for rows in a given table.

Types of ROWIDs: Physical Rowid, Logical Rowid and Foreign Rowid

Physical ROWIDs:

• Store the addresses of rows in heap-organized tables, clustered tables, and table and index partitions.
• Provide the fastest access to a row.
• Contain the physical address of a row and allow its retrieval in a single block access.
• Rowid corresponds to the physical address of a row's row piece (or the initial row piece if the row is chained in multiple row pieces).
• In case of clustered tables, rows in different tables that are in the same data block can have the same rowid.
• Physical rowids have two formats: restricted and extended.

• In a logical rowid, a physical guess identifies the block in which a row was stored at the time the guess is made, and the database uses the guess to search the block directly.
• However, as new rows are inserted, guesses can become stale.
• Guess staleness can be monitored with DBMS_STATS (index statistics capture the percentage of rows with valid guesses).
• Statistic stored in the DBA_INDEXES (PCT_DIRECT_ACCESS column).
• To refresh guess information:
• Rebuild secondary indexes (resource intensive).
• ALTER INDEX...UPDATE BLOCK REFERENCES

Logical ROWIDs:

• Strore the addresses of rows in index-organized tables (IOTs).
• Used in index-organized tables (IOT), these are based on the table's primary key.
• Logical rowids change when the IOT's primary key is updated.
• This is because table data is stored in the index leaves and thus do not have a permanent physical address.
• Indexes that use logical rowids include a physical guess, which identifies the block location of the row in the IOT at the time the guess was made.
• Since logical rowids do not indicate the exact physical location of a row, they cannot be used to see how a table is organized.

Restricted x Extended ROWIDs

Restricted ROWIDs:

• Provided for backward compatibility with applications developed with Oracle 7 and earlier
• Until Oracle 7, ROWIDs used 8 bytes
• The old format is known as Restricted rowid format.
• Restricted ROWID format stores (block number, Row number, file number)

Restricted Rowid (8 bytes) Block# (bytes 1-4).Row#(bytes 5-6).File#(bytes 7-8)

Extended ROWIDs

• Starting with Oracle 8 Rowid's format changed.
• Extended rowid: four-piece format.
• Uses a base 64 encodig (A-Z,a-z,0-9,+,/)
• It is now 10 bytes long and includes the ID of the Object to which the row belongs.
• Until Oracle 7 file numbers were unique within a database.
• With the new format, ROWIDs stores the relative file number (the file number within the tablespace in which the object is stored).
• Until Oracle 7, the 2-byte representation of file numbers limited the maximum number of files a database could have to 65533.
• This limit now is for the number of files each tablespace can have.
• An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row.

AAAC9E: The data object number identifies the segment. Note: A data object number is assigned to every database segment (table, index, etc). HOWEVER, Schema objects in the same segment (such as a table cluster) have the SAME Object ID. AAE: The data file number: Tablespace-relative. Identifies the file that contains the row. AAAABX: The data block number Identifies the block that contains the row. Relative to the datafile. AAA: The row number: identifies the row in the block. ObjID(bytes 1-4) . RelativeFN(byte 5 + 4bits) . Block#(4bits + byte 7) . Row#(bytes 9-10)

ROWID Pseudocolumn

• Every Oracle table has a pseudocolumn named ROWID. Its value, however, is not actually stored in the table.
• You can select from pseudocolumns, but you cannot insert, update, or delete their values.
• Values of the ROWID pseudocolumn are strings representing ,in hexadecimal format, the address of each row.
• You can parse the value returned from rowid to understand the physical storage of rows in the database.

(a) Query the extended rowid

SQL> select rowid, last_name from hr.employees where employee_id=100;

ROWID               LAST_NAME
------------------  -----------
AAAR5VAAFAAAADPAAA  King

(b) To see the old rowid format (restricted rowid), you can use the DBMS_ROWID package:

SQL> select dbms_rowid.rowid_to_restricted(rowid,1) old_rowid
  2  from hr.employees
  3* where employee_id = 100


OLD_ROWID
------------------
000000CF.0000.0005

Restriced rowid format:
000000CF: data block that contains the row.
Block numbers are relative to the datafile, not tablespace.
0000: row numbers start with 0.
0005: data file that contains the row.

• With the restricted format, to find out the segment (table, index) a rowid belonged to it was necessary to compare ROWID's block and file numbers with entries in the DBA_EXTENTS view.
• With extended format, you can use DBMS_ROWID as below:

SQL> select first_name, dbms_rowid.rowid_object(rowid) "ObjectID",
  2                     dbms_rowid.rowid_relative_fno(rowid) "File#",
  3                     dbms_rowid.rowid_block_number(rowid) "Block#",
  4                     dbms_rowid.rowid_row_number(rowid)   "Row#"
  5  from hr.employees
  6  where employee_id = 100;

FIRST_NAME        ObjectID      File#     Block#     Row#
-------------------- ---------- ---------- ---------- ----------
Steven     73301   5   207        0

Now query DBA_OBJECTS to find out what database object the row belongs to

SQL> select owner, object_name, object_type, data_object_id, object_id
  2  from dba_objects
  3* where data_object_id =  73301;

OWNER       OBJECT_NAME     OBJECT_TYPE    DATA_OBJECT_ID  OBJECT_ID
----------- --------------- -------------- --------------- ----------
HR          EMPLOYEES       TABLE          73301           73933

When does a ROWID change?

• If row movement is enabled: ROWID can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
• If row movement is disabled: ROWID can change if the row is exported and imported using Oracle Database utilities

Can you use ROWIDs as a table's primary key?

• No. Oracle won't let you create a PK constraint on a rowid column.
• In addition, besides not being always unique (rows in clustered tables may share a rowid), a row's rowid can change (i.e., after and export/import or partition movement operation).

Can I store ROWIDs in a table column?

• Yes. You can create table column using the ROWID data type.
• However, ROWID values should not be used across transaction lines.
• Storing a rowid with the intent of using it latter (i.e. as an audit trail record), may lead to errors and incorrect information, since rowids may change as the result of:
  
  • Export/import operation
  • ALTER TABLE...MOVE
  • ALTER TABLE...SHRINK SPACE
  • FLASHBACK TABLE...
  • Partition operation (split, combination)
  • Data update that results in row being moved to another partition