FreeNAS backup solution using rsync and virtualbox

I used the FreeNAS as file server  for about 50 users and the freenas works stable as a rock from 2008 till now,which was based on a hp desktop dc5700 with 1G ram and 140G disk volume.However,with the data increasing and time went by, the stability was the most important stuff I need to consider about,so I made the backup solution to work as primary/standby to shorten the MTTR which use RSYNC,vm tech..
because I only have few days and limited resources, so maybe the solution seems a little rough…anyway,it’s ok,just change the ip will finish the taking over and the bussiness is not so real-time.

 

SGA components explaination

SGA components
database buffer cache: cache blocks of data retrieved from disk
redo log buffer: cache redo information until it can be written to disk
shared pool : cache various constructs that can be shared among different db users
large pool: optional area used for buffering large i/o requests in support of parallel query,shared server,oracle XA,and certain types of backup operations such as RMAN
java pool:holds session-specific java code and data within the java virtual machine
stream pool:used by oracle stream

 

great thanks

今年过年很融洽,也不知是怎的,几天下来竟然没什么争执,这便是我多年一直想要的状态,多亏了姑娘一直陪伴吧。有的时候,你想要的东西在他人看来,其实是很简单的,而其他人何尝不是如此、大家又何尝不是如此。站在旁观的角度,你会发现,你已拥有很多。

Benefits of Index-Organized Tables

from oracle doc,check the benefits of IOTs:

Index-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Presence of nonkey columns of a row in the B-tree leaf block itself avoids an additional block access. Also, because rows are stored in primary key order, range access by the primary key (or a valid prefix) involves minimum block accesses.

In order to allow even faster access to frequently accessed columns, you can use a row overflow segment (as described later) to push out infrequently accessed nonkey columns from the B-tree leaf block to an optional (heap-organized) overflow segment. This allows limiting the size and content of the portion of a row that is actually stored in the B-tree leaf block, which may lead to a higher number of rows in each leaf block and a smaller B-tree.

Unlike a configuration of heap-organized table with a primary key index where primary key columns are stored both in the table and in the index, there is no such duplication here because primary key column values are stored only in the B-tree index.

Because rows are stored in primary key order, a significant amount of additional storage space savings can be obtained through the use of key compression.

Use of primary-key based logical rowids, as opposed to physical rowids, in secondary indexes on index-organized tables allows high availability. This is because, due to the logical nature of the rowids, secondary indexes do not become unusable even after a table reorganization operation that causes movement of the base table rows. At the same time, through the use of physical guess in the logical rowid, it is possible to get secondary index based index-organized table access performance that is comparable to performance for secondary index based access to an ordinary table.

FLASHBACK TABLE

Here are the doc from oracle online document to clarify the FLASHBACK TABLE

Purpose:

Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

Note:

Oracle strongly recommends that you run your database in automatic undo mode by setting the UNDO_MANAGEMENT initialization parameter to AUTO. In addition, set the UNDO_RETENTION initialization parameter to an interval large enough to include the oldest data you anticipate needing. For more information please refer to the documentation on the UNDO_MANAGEMENT and UNDO_RETENTION initialization parameters.
You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.
There are the prerequisites to use FLASHBACK TABLE

Prerequisites:

To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table.

//row movement shoud be enabled except flashback drop, cauze flashback drop is using the recyclebin instead of undo data
Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recyclebin rather than undo data. Please refer to row_movement_clause for information on enabling row movement.

To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.

 

V$OBJECT_USAGE

piece from oracle online doc about v$object_usage

V$OBJECT_USAGE
You can use this view to monitor index usage. The view displays statistics about index usage gathered from the database. All indexes that have been used at least once can be monitored and displayed in this view
.

Column Datatype Description
INDEX_NAME VARCHAR2(30) Index name in sys.obj$.name
TABLE_NAME VARCHAR2(30) Table name in sys.obj$.name
MONITORING VARCHAR2(3) YES| NO
USED VARCHAR2(3) YES| NO
START_MONITORING VARCHAR2(19) Start monitoring time in
sys.object_stats.start_monitoring
END_MONITORING VARCHAR2(19) End monitoring time in
sys.object_stats.end_monitoring

block media recovery

block media recovery是对比datafile media recovery提出来的,下面是摘自oracle文档中的内容。
block media recovery是真对只是丢了几个block的恢复。

Block media recovery is a technique for restoring and recovering individual data blocks while all database files remain online and available. If corruption is limited to only a few blocks among a subset of database files, then block media recovery might be preferable to datafile recovery.

The interface to block media recovery is provided by RMAN. If you do not already use RMAN as your principal backup and recovery solution, then you can still perform block media recovery by cataloging into the RMAN repository the necessary user-managed datafile and archived redo log backups.

Block Media Recovery Using All Available Backups
In this scenario, you identify the blocks that require recovery and then use any available backup to perform the restore and recovery of these blocks.

To recover datablocks by using all available backups:

Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/trgt/users01.dbf’
ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
ORA-01110: data file 2: ‘/oracle/oradata/trgt/undotbs01.dbf’

Assuming that you have preallocated automatic channels, run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks as in the following example:

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19;
Block Media Recovery Using Specific Backups
In this scenario, you identify the blocks that require recovery, and then use only selected backups to perform the restore and recovery of these blocks.

To recover datablocks while limiting the type of backup:

Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/trgt/users01.dbf’
ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
ORA-01110: data file 2: ‘/oracle/oradata/trgt/undotbs01.dbf’

Assuming that you have preallocated automatic channels, execute the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, you can specify what type of backup should be used to restore the blocks:

# restore from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# restore from datafile image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
FROM DATAFILECOPY;

You can indicate the backup by specifying a tag:

# restore from backupset with tag “mondayam”
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
FROM TAG = mondayam;

You can limit the backup candidates to those made before a certain point:

# restore using backups made before one week ago
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
RESTORE UNTIL ‘SYSDATE-7’;
# restore using backups made before SCN 100
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
RESTORE UNTIL SCN 100;
# restore using backups made before log sequence 7024
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
RESTORE UNTIL SEQUENCE 7024;

Note that if you limit the restore of datablocks with the UNTIL clause, then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.

The difference between buffer and cache

piece of information got from wiki to clarify the difference between buffer and cache

The terms “buffer” and “cache” are not mutually exclusive and the functions are frequently combined; however, there is a difference in intent.
A buffer is a temporary memory location that is traditionally used because CPU instructions cannot directly address data stored in peripheral devices. Thus, addressable memory is used as an intermediate stage. Additionally, such a buffer may be feasible when a large block of data is assembled or disassembled (as required by a storage device), or when data may be delivered in a different order than that in which it is produced. Also, a whole buffer of data is usually transferred sequentially (for example to hard disk), so buffering itself sometimes increases transfer performance or reduces the variation or jitter of the transfer’s latency as opposed to caching where the intent is to reduce the latency. These benefits are present even if the buffered data are written to the buffer once and read from the buffer once.
A cache also increases transfer performance. A part of the increase similarly comes from the possibility that multiple small transfers will combine into one large block. But the main performance-gain occurs because there is a good chance that the same datum will be read from cache multiple times, or that written data will soon be read. A cache’s sole purpose is to reduce accesses to the underlying slower storage. Cache is also usually an abstraction layer that is designed to be invisible from the perspective of neighboring layers.

put it simple:buffer is used to slowdown the write behavior such as cpu to disk.cache is used to speedup the read behavior.(buffer是缓冲写,cache是加速读取..)

details refer:http://en.wikipedia.org/wiki/Cache#The_difference_between_buffer_and_cache