Từng bước cài đặt Fedora 7

http://www.tuoitre.com.vn/Tianyon/Index.aspx?ArticleID=207001&ChannelID=67

Step by step document to create Standby Database

Step by step document to create Standby Database
Step by step document to create Standby Database

Standby database are very critical for disaster recovery. This article takes you step by step to setup Oracle Standby Database.
1 - Data Guard Operational Prerequisites

# Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
# The Primary Database must run in ARCHIVELOG mode.
# The hardware and Operating system architecture on primary and standby location must be same.
# Each primary and standby database must have its own control file.
# If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
# Primary database must be FORCE LOGGING mode.
2 - Preparing Primary Database for Standby Database creation

2.1 Ensure the primary database in ARCHIVELOG mode

Ensure the primary database in ARCHIVELOG mode using following command.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11


2.2 Enable database in FORCE LOGGING mode

Place primary database in FORCE LOGGING mode using following SQL statement:

SQL> alter database force logging;
Database altered.


3 - Identify the primary database Datafiles

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF

10 rows selected.


4 - Make a copy of Primary Database

Make a closed backup copy of primary database by performing following steps:

4.1 Shutdown the Primary Database

Issue the following statement to shutdown the primary database.

SQL> shutdown immediate;


4.2 Copy the Datafiles to standby location

Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while coping the files.

5 - Restart the Primary Database

Execute following command to restart the Primary Database.

SQL> startup;

6 - Create Control file for Standby Database

Issue the following command on primary database to create control file for the standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2 'D:\oracle\oradata\stby\control_sb01.ctl';

Database altered.

The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.

7 - Create pfile from for standby database from the primary database
Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.

SQL> CREATE PFILE='C:\oracle\ora92\database\initstby.ora' from spfile;

File created.

8 - Set initialization parameters on physical standby database

Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database, 9.2.0.0.0.

control_files - Specify the path name and filename for the standby control file.

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.

db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.

log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.

log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)

standby_file_management - Set to AUTO.

remote_archive_enable - Set to TRUE.

instance_name - If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host.

lock_name_space - Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.

Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.

(Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service

If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window

C:\>oradim -new -sid stby -intpwd stby -startmode manual

10 - Configure listeners & tnsnames for standby and primary databases

Configure listeners in listeners.ora as follows

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Orcl)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = Orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = stby)
)
)

Restart the listeners using LSNRCTL utility.

% lsnrctl stop
% lsnrctl start

Also make an entry into tnsnames.ora for standby database.

stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

11 - Start Physical standby database

Start up the stand by database using following commands

C:\>set oracle_sid=stby

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;
Database altered.

12 - Enabling archiving to Physical Standby Database

To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.

Issue following commands from primary database session:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

System altered.

13 - Initiate Log apply services

The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> recover managed standby database cancel;
Media recovery complete.

Now go to primary database prompt

SQL> alter system switch logfile;

Go to stand by database prompt

SQL> alter database open read only;

Database altered.

14 - Verifying the Standby Database

On standby database query the V$ARCHIVED_LOG view to verify that redo log received.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52


Archive the current log on the primary database using following statement.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

On standby database query the V$ARCHIVED_LOG view

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00

Now connect scott/tiger@orclc on primary database and create table or insert row in any table.

Now connect as sys on primary database and execute following SQL statement

SQL> alter system switch logfile;

On standby database execute following SQL statements

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open read only;

Database altered.

And check whether the changes applied on the standby database or not.
15 - Annexure

15.1 Parameter file for Primary Database

*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\Orclc\bdump'
*.compatible='9.2.0.0.0'
*.control_files='C:\oracle\oradata\Orclc\CONTROL01.CTL',
'C:\oracle\oradata\Orclc\CONTROL02.CTL','C:\oracle\oradata\Orclc\CONTROL03.CTL'
*.core_dump_dest='D:\oracle\admin\Orclc\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='Orclc'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=C:\oracle\ora92\database\archive\ORCLC mANDATORY'
*.log_archive_dest_2='SERVICE=stby'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\Orclc\udump'

15.2 Parameter file for Standby Database

*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\stby\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\stby\CONTROL_SB01.CTL'
*.core_dump_dest='D:\oracle\admin\stby\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='stby'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=D:\oracle\admin\stby\archive'
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='NONE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\stby\udump'
*.standby_archive_dest='C:\oracle\ora92\database\archive\ORCLC'
*.db_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.log_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby

Delete And Truncate

Question:
What is the difference between TRUNCATE and DELETE?
Answer:
The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

Script to enable/disable Constraints

REM: Script to enable/disable Constraints
REM:
REM: Author: Jyoti
REM: Date Submitted: 04-Mar-2005
REM: Date Posted: 04-Mar-2005
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************



Prompt Enter Owner:
ACCEPT i_owner;

Prompt Enter TABLE_NAME:
ACCEPT i_tab_name;

Prompt Enter Column_name:
ACCEPT i_col_name;

Prompt Enter Constraint_Type (U -UNIQUE, N-not null , P - PRIMARY KEY, R-Reference, C-CHECK) :
ACCEPT i_cons_type;

Prompt Do you want ENABLE(Y) OR DISABLE (N) the constraint ?
ACCEPT i_enable_disable;

Prompt Do you want VALIDATE (Y) OR NOVALIDATE (N) Clause ?
ACCEPT i_validate;

DECLARE
BEGIN
EXECUTE Immediate ' Drop table LONG_TEST';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

DECLARE
l_type varchar2(1) := 'C';
BEGIN
IF upper('&&i_cons_type') IN ('C','N') THEN

EXECUTE Immediate ' Create table LONG_TEST as select a.constraint_name,
to_lob(search_condition) as search_condition
from All_Cons_Columns a,
All_Constraints b
WHERE a.owner = b.Owner
AND a.constraint_name = b.constraint_name
AND a.table_name = b.table_name
AND a.owner = upper('||'''&&i_owner'''||')
AND a.table_name = upper('||'''&&i_tab_name'''||')
AND a.column_name = upper('||'''&&i_col_name'''||')
AND b.constraint_type = '||'''C''' ;
ELSE
EXECUTE Immediate ' Create table LONG_TEST as select a.constraint_name,
to_lob(search_condition) as search_condition
from All_Cons_Columns a,
All_Constraints b
WHERE a.owner = b.Owner
AND a.constraint_name = b.constraint_name
AND a.table_name = b.table_name
AND a.owner = upper('||'''&&i_owner'''||')
AND a.table_name = upper('||'''&&i_tab_name'''||')
AND a.column_name = upper('||'''&&i_col_name'''||')
AND b.constraint_type = upper('||'''&&i_cons_type'''||')';
END IF;
END;
/

DECLARE
CURSOR c_constraint_name IS
SELECT a.constraint_name
FROM All_Cons_Columns a,
All_Constraints b
WHERE a.owner = b.Owner
AND a.constraint_name = b.constraint_name
AND a.table_name = b.table_name
AND a.owner = upper('&&i_owner')
AND a.table_name = upper('&&i_tab_name')
AND a.column_name = upper('&&i_col_name')
AND b.constraint_type = upper('&&i_cons_type');

TYPE RefCurTyp IS REF CURSOR;
c_cons_name RefCurTyp;
l_enable_disable VARCHAR2(10);
l_constraint All_Cons_Columns.constraint_name%TYPE;
l_query Varchar2(2000);
l_validate VARCHAR2(30);

BEGIN
IF upper( '&&i_enable_disable') = 'Y' THEN
l_enable_disable := 'enable';
ELSE
l_enable_disable := 'disable';
END IF;

IF upper('&&i_validate') = 'Y' THEN
l_validate := ' validate ';
ELSE
l_validate := ' novalidate ';
END IF;

IF upper('&&i_cons_type') IN ('C','N') THEN
IF upper('&&i_cons_type') = 'N' THEN
l_query := 'Select constraint_name
from LONG_TEST
where search_condition '||' LIKE '||'''%IS NOT NULL%''';
ELSIF upper('&&i_cons_type') = 'C' THEN
l_query := 'Select constraint_name
from LONG_TEST
where search_condition '||' NOT LIKE '||'''%IS NOT NULL%''';
END IF;

OPEN c_cons_name FOR l_query;
FETCH c_cons_name INTO l_constraint;
IF c_cons_name%FOUND THEN
EXECUTE IMMEDIATE 'Alter table '||'&&i_tab_name'||' '||l_enable_disable||l_validate||' constraint '|| l_constraint;
END IF;
CLOSE c_cons_name;

ELSE
OPEN c_constraint_name;
FETCH c_constraint_name INTO l_constraint;
IF c_constraint_name%FOUND THEN
EXECUTE IMMEDIATE 'Alter table '||'&&i_tab_name'||' '||l_enable_disable||l_validate||' constraint '|| l_constraint;
END IF;
CLOSE c_constraint_name;
END IF;
EXECUTE Immediate ' Drop table LONG_TEST';
END;
/

Script to Get Os user name with terminal name

REM: Script to Get Os user name with terminal name
REM:
REM: Author: Shahid Azizi
REM: Date Submitted: 02/17/2004
REM: Date Posted: 02/17/2004
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;

Script to report constraints in for a table

REM: Script to report constraints in for a table
REM:
REM: Author: Vigyan Kaushik
REM: Date Submitted: 22-Sep-2005
REM: Date Posted: 22-Sep-2005
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

COL column_name format a30
SET linesize 999

SELECT c1.constraint_name, c2.column_name, c1.constraint_type, c1.delete_rule
FROM user_constraints c1, user_cons_columns c2
WHERE c1.table_name = UPPER ('&Table_Name')
AND c1.constraint_name = c2.constraint_name;

The Basics of Oracle Architecture

The Basics of Oracle Architecture


By: Budi Raharjo
http://mbraharjo.blogspot.com


As an Oracle DBA, you must be understand the concepts of Oracle architecture clearly. It is a basic step or main point that you need before you go to manage your database. By this article, I will try to share my knowledge about it. Hope it can be useful for you.

What is An Oracle Database?
________________________________________
Basically, there are two main components of Oracle database �� instance and database itself. An instance consists of some memory structures and the background processes, whereas a database refers to the disk resources. Figure 1 will show you the relationship.




Figure 1. Two main components of Oracle database

Instance
________________________________________

As we cover above, the memory structures and background processes contitute an instance. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA), and an optional area �� Software Area Code. In the other hand, the mandatory background processes are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monitor (PMON). And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc. Figure 2 will illustrate the relationship for those components on an instance.



Figure 2. The instance components
System Global Area

SGA is the primary memory structures. When Oracle DBAs talk about memory, they usually mean the SGA. This area is broken into a few of part memory �� Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Shared Pool

Shared pool is broken into two small part memories �� Library Cache and Dictionary Cache. The library cache is used to stores information about the commonly used SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statemens among users. In the other hand, dictionary cache is used to stores information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc.

The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization parameter file.

Redo Log Buffer

Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program Global Area

Although the result of SQL statemen parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Software Area Code

Software area code is a location in memory where the Oracle application software resides.

Oracle Background Processes

Oracle background processes is the processes behind the scene that work together with the memories.

DBWn

Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.

LGWR

Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.

CKPT

Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.

SMON

System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles.

PMON

Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.

Database
________________________________________

The database refers to disk resources, and is broken into two main structures �� Logical structures and Physical structures.

Logical Structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data effeciently. The logical units are tablespace, segment, extent, and data block. Figure 3 will illustrate the relationships between those units.



Figure 3. The relationships between the Oracle logical structures
Tablespace

A Tablespace is a grouping logical database objects. A database must have one or more tablespaces. In the Figure 3, we have three tablespaces �� SYSTEM tablespace, Tablespace 1, and Tablespace 2. Tablespace is composed by one or more datafiles.

Segment

A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are Temporary Segment and Rollback Segment.

Extent

A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes.

Physical Structures

The physical structures are structures of an Oracle database (in this case the disk files) that are not directly manipulated by users. The physical structure consists of datafiles, redo log files, and control files.

Datafiles

A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles.

Redo Log Files

Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes.

Control Files

Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc.

Script to Show Used/free space by tablespace name

REM: Script to Show Used/free space in Meg by tablespace name
REM:
REM: Author: Sanjay Tiwari
REM: Date Submitted: 03-13-2007
REM: Date Posted: 03-11-2007
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************




tti \"Space Usage for Database in Meg\"

SELECT Total.name \"Tablespace Name\",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/

tti off

Script to convert Report to Excel

REM: Script to convert Report to Excel
REM:
REM: Author: Shadab Ali
REM: Date Submitted: 07-Jan-2006
REM: Date Posted: 08-Jan-2006
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

*************-*********************
PACKAGE RPT2XLS IS
-- Font style constants
BOLD constant binary_integer := 1;
ITALIC constant binary_integer := 2;
UNDERLINE constant binary_integer := 4;

-- Horizontal alignment constants
SUBTYPE xlHAlign IS binary_integer;
CENTER CONSTANT xlHAlign := -4108;
CENTERACROSSSELECTION CONSTANT xlHAlign := 7;
DISTRIBUTED CONSTANT xlHAlign := -4117;
FILL CONSTANT xlHAlign := 5;
GENERAL CONSTANT xlHAlign := 1;
JUSTIFY CONSTANT xlHAlign := -4130;
LEFT CONSTANT xlHAlign := -4131;
RIGHT CONSTANT xlHAlign := -4152;

PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null,
Align in xlHAlign DEFAULT null
);
PROCEDURE new_line;
PROCEDURE move2Line(line in NUMBER);
PROCEDURE run;
PROCEDURE release_memory;

END;

************************------------*********************************
PACKAGE BODY RPT2XLS IS

TYPE ExcelCell IS RECORD(RowNo binary_integer,
ColNo binary_integer,
Val varchar2(2000),
FontName varchar2(20),
FontSize binary_integer,
FontStyle binary_integer,
FontColor binary_integer,
BgrColor binary_integer,
Format varchar2(60),
Align xlHAlign
);
TYPE ExcelCells IS TABLE OF ExcelCell;
Cell ExcelCells := ExcelCells();

CurrentRow binary_integer := 1;

PROCEDURE new_line IS
BEGIN
CurrentRow := CurrentRow + 1;
END;

PROCEDURE move2Line(line in NUMBER) IS
BEGIN
CurrentRow:=CurrentRow+line;
END;


PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null,
Align in xlHAlign DEFAULT null
) IS
BEGIN
Cell.Extend;
Cell(Cell.Last).RowNo := CurrentRow;
Cell(Cell.Last).ColNo := ColNo;
Cell(Cell.Last).Val := CellValue;
Cell(Cell.Last).FontName := FontName;
Cell(Cell.Last).FontSize := FontSize;
Cell(Cell.Last).FontStyle := FontStyle;
Cell(Cell.Last).FontColor := FontColor;
Cell(Cell.Last).BgrColor := BgrColor;
Cell(Cell.Last).Format := Format;
Cell(Cell.Last).Align := Align;
END;

PROCEDURE run IS
Application OLE2.OBJ_TYPE;
Workbooks OLE2.OBJ_TYPE;
Workbook OLE2.OBJ_TYPE;
Worksheets OLE2.OBJ_TYPE;
Worksheet OLE2.OBJ_TYPE;
WorkCell OLE2.OBJ_TYPE;
WorkColumn OLE2.OBJ_TYPE;
WorkFont OLE2.OBJ_TYPE;
WorkInterior OLE2.OBJ_TYPE;
ArgList OLE2.LIST_TYPE;
BEGIN
Application := OLE2.create_obj('Excel.Application');
OLE2.set_property(Application, 'Visible', 1);
Workbooks := OLE2.get_obj_property(Application, 'Workbooks');
Workbook := OLE2.invoke_obj(WorkBooks, 'Add');
Worksheets := OLE2.get_obj_property(Workbook, 'Worksheets');
Worksheet := OLE2.get_obj_property(Application, 'ActiveSheet');

for i in Cell.First .. Cell.Last
loop
if Cell(i).Val is not null then
ArgList := OLE2.create_arglist;
OLE2.add_arg(ArgList, Cell(i).RowNo);
ole2.add_arg(ArgList, Cell(i).ColNo);
WorkCell := OLE2.get_obj_property(Worksheet, 'Cells', ArgList);
ole2.destroy_arglist(ArgList);
ole2.set_property(WorkCell, 'Value', Cell(i).Val);
ole2.set_property(WorkCell, 'NumberFormat', Cell(i).Format);

if Cell(i).Align is not null then
ole2.set_property(WorkCell, 'HorizontalAlignment', Cell(i).Align);
end if;

WorkFont := OLE2.get_obj_property(WorkCell, 'Font');
WorkInterior := ole2.Get_Obj_Property(WorkCell, 'Interior');

if Cell(i).FontName is not null then
OLE2.set_property(WorkFont, 'Name', Cell(i).FontName);
end if;
if Cell(i).FontSize is not null then
OLE2.set_property(WorkFont, 'Size', Cell(i).FontSize);
end if;
if mod(Cell(i).FontStyle, 2) = 1 then
OLE2.set_property(WorkFont, 'Bold', 1);
end if;
if mod(Cell(i).FontStyle, 4) > 2 then
OLE2.set_property(WorkFont, 'Italic', 1);
end if;
if mod(Cell(i).FontStyle, 8) > 4 then
OLE2.set_property(WorkFont, 'Underline', 2);
end if;
if Cell(i).FontColor is not null then
OLE2.set_property(WorkFont, 'ColorIndex', Cell(i).FontColor);
end if;
if Cell(i).BgrColor is not null then
OLE2.set_property(WorkInterior, 'ColorIndex', Cell(i).BgrColor);
end if;

OLE2.release_obj(WorkInterior);
OLE2.release_obj(WorkFont);
OLE2.release_obj(WorkCell);
end if;
end loop;

ArgList := ole2.create_arglist;
ole2.add_arg(ArgList, 'A:Z');
WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', ArgList);
ole2.destroy_arglist(ArgList);
ole2.invoke(WorkColumn, 'AutoFit');

OLE2.release_obj(WorkColumn);
OLE2.release_obj(Worksheet);
OLE2.release_obj(Worksheets);
OLE2.release_obj(Workbook);
OLE2.release_obj(Workbooks);
OLE2.release_obj(Application);

END;

PROCEDURE release_memory IS
BEGIN
Cell := ExcelCells();
SYS.DBMS_SESSION.free_unused_user_memory;
END;


END;
/

Quá nhiều và Một chút

Quá nhiều ghen tuông làm cho Tình yêu chết trong nghi ngờ
Quá nhiều giận dỗi làm cho Tình yêu chết trong mệt mỏi
Quá nhiều nước mắt làm cho Tình yêu chết trong đau khổ
Quá nhiều lãng mạn làm cho tình yêu chết trong ảo ảnh
Quá nhiều thực tế làm cho Tình yêu chết trong toan tính
Quá nhiều đam mê làm cho Tình yêu chết trong mộng mị
Chỉ một chút, chỉ một chút thôi …
Một chút giận dỗi để thấy mình được vỗ về yêu thương
Một chút ích kỷ để biết mình thuộc về ai đó
Một chút hờn ghen để biết mình đang yêu thật nhiều

Oralce

You can visit here for the OLAP question:
http://forums.oracle.com/forums/thread.jspa?threadID=519250&tstart=45

Secondly, you should visit here:
http://www.oracle.com/technology/documentation/index.html

Remember this is what I have learned read+practice. The more you read the better and practice as much as you can.

You can also visit for interviews:
http://www.dba-oracle.com/bp/bp_book2_jobs.htm
http://forums.oracle.com/forums/thread.jspa?messageID=1888990&#1888990
http://forums.oracle.com/forums/thread.jspa?messageID=1859100&#1859100
Here is my list of DBA job skills:

http://www.dba-oracle.com/oracle_tips_dba_job_skills.htm

Here are notes on becoming a DBA:

http://www.dba-oracle.com/t_how_to_become_an_oracle_dba.htm

Thống kê vui

Thống kê vui

Hôm nay, lên Google search vài ba từ chơi, hồi trước hình như người ta cũng có search rồi, nhưng toàn bằng tiếng Anh, hôm nay, mình search bằng tiếng Việt . Mấy bạn đọc chơi vui.

hama

Thành công: 1.650.000
Thất bại: 680.000

Tại sao lại không thử khi sự thất bại chỉ là "tên tép riu" so với thành công nhỉ?

Vui: 7.940.000
Buồn: 2.460.000

Thế gian này thật lắm nỗi buồn, nhưng nó vẫn không thể đánh bại được niềm vui. Vậy tại sao chúng ta cứ hay buồn nhỉ?

Hạnh phúc: 1.410.000
Đau khổ: 338.000

Đau khổ thường đi kèm với hạnh phúc nhưng dường như nó chỉ là cái bóng của hạnh phúc mà thôi!

Tiến lên: 163.000
Thụt lùi: 26.800

Có quá nhiều sự tiến lên, thụt lùi chắc chắn sẽ đi đến sự tuyệt chủng mà thôi. Vì vậy nếu bạn không tiến lên có nghĩa là bạn sắp... "tuyệt chủng".

Ánh sáng: 872.000
Bóng tối: 164.000

Có đủ chỗ cho ánh sáng và bóng tối trên thế gian này. Nhưng dường như bóng tối chỉ là anh chàng hay khép nép đi kèm với cô nàng ánh sáng đầy tự tin nhỉ.

Ban ngày: 240.000
Ban đêm: 263.000

Ban ngày không dài hơn ban đêm và ngược lại. Tuy nhiên, ngày cũng có thể dài hơn đêm nếu bạn... ngủ nướng (tức là nằm trên giường chờ hoài mà hông thấy trời tối để... ngủ tiếp luôn) đêm có thể dài hơn ngày nếu bạn... thất tình.

Hoàng hôn: 178.000
Bình minh: 430.000

Có bao nhiêu cái hoàng hôn mà bạn đã trải qua? Liệu có nhiều như những ngày bình minh hay không? Người ta thường không có khả năng chờ cho hết hoàng hôn, vì họ không biết rằng sau hoàng hôn u ám là bình minh rực rỡ.

Yêu: 12.600.000
Ghét: 568.000

Người ta thích yêu nhiều hơn thích ghét nhỉ, mà chúng ta chẳng có lý do nào đi ngược lại với xu hướng của thời đại. Yêu đi bạn ơi!

Mưa: 2.520.000
Nắng: 1.600.000

Ở Việt Nam chắc là mưa nhiều hơn nắng. Vậy mà sao hạn hán vẫn giăng đầy? Có vấn đề cốt yếu nào mà ta chưa nhìn thấu nhỉ? Hãy suy nghĩ kỹ trước khi kết luận về cái gì đó bạn nhé.

Nóng: 3.850.000
Lạnh: 2.250.000

Việt Nam ở vùng nhiệt đới mà, nên nóng nhiều hơn lạnh là phải.

Ăn chơi: 510.000
Làm việc: 1.510.000

Hình như người ta thích làm việc hơn là ăn chơi, nhưng mà "làm không chơi đánh rơi tuổi trẻ, chơi không làm bán rẻ tương lai".

Màu trắng: 235.000
Màu đen: 230.000

Đúng là trên thế gian này nhiều khi trắng đen chẳng rõ ràng gì!

Từ bỏ: 328.000
Tiếp tục: 1.820.000

Tại sao bạn phải từ bỏ khi người khác vẫn tiếp tục? Phải chăng bạn chưa tìm thấy ý nghĩa đích thực của sự hợp tác và lòng tin?

Thất tình: 116.000
Đang yêu: 128.000

Đúng là người ta yêu nhiều mà thất tình cũng nhiều. Nhưng không vì sợ thất tình mà không yêu. Đơn giản chỉ là bạn đang thử xem trái tim bạn có khả năng chịu đựng như thế nào hay thôi. Hèn chi mà người ta nói "yêu nhiều ốm".

14-06-2007

- Cuộc sống có nhiều cái bất công thật khó hiểu??!!