ORACLE database DBA duties and day-to-day duties
DBA duties and day-to-day duties: Install and upgrade the database , and application tools to build and configure the network environment.
Familiar with the structure of the database system to predict future needs, the development of database storage solutions.
According to the developers to design applications demand to create the database storage structure.
4 according to the demand for applications developers to design, create database objects
According to the developer's feedback, when necessary, to modify the structure of the database
Management database users to maintain the database
Control and monitor user access to the database access
8. Monitor and optimize the performance of the database
9 Develop database backup plan, disaster appears to restore the database information
10 Backup and restore database
Contact database system manufacturers, tracking technology information
12. The middle of the client layer and link
13. Ensure the connection
Method One:
1, create a local Access database, import the Excel data into Access. Can
be directly imported clicking the right mouse button, select Import
file type to import Excel files can also be achieved by creating a macro
with TransferSpreadsheet function.
2, created locally the ODBC, link oracle database. Oracle need to import the data table created in the local Access linked table (do not use the import table).
3, create the insert query in an Access database will be imported good Excel data into oracle.
Method 2:
First save the Excel csv file format, such as the test.csv, write a insert.ctl again
Sqlldr import!
insert.ctl reads as follows:
load data - control file identifies the
infile test.csv - To enter the data file name test.csv
append into table table_name - 3, append records to the table table_name
fields terminated by, - 4, field termination, is a comma
(Field1,
field2,
field3,
...
fieldN) ----- Define columns correspond to the order
Note that the parentheses field order with the corresponding csv file then you can execute the following command:
sqlldr user / password control = insert.ctl
Daily work:
1 Make sure that the server is working normal state and normal state of the various databases
2 Check whether the hard disk space is enough
3 Check the database log to view the trace file, check if there is an error message
4 Check the validity of backups
5
found that the performance of the database server performance parameter
monitoring system performance monitor whether the decline to find the
cause and resolve
6 fill dba log
Weekly work:
A monitoring database object space expansion
2 decide whether to adjust the storage parameters
The system health check
4 Check to delete a data object does not make any sense
5, check the deletion does not make sense constraints and triggers
6 weekly backup check processing
Monthly work:
1 Based on the above inspection, analysis of whether it will affect the performance of the database fragmentation exists
2 According to the historical database performance, to decide how to optimize performance
3 Daily Weekly Monthly backup
4 summarizes the work of the next step of the management plan
DBA daily summary of the work
administrator shall be the Oracle database system to regularly monitor:
1
a day, the operating status of the Oracle database, log files, backup,
database space usage, system resource usage checks to identify and solve
the problem.
The
week expanded space for database objects, data growth monitoring
database health checks, check the state of the database objects.
Monthly
carry out the analyze tables and indexes, check the table space debris,
looking for the chance of database performance tuning, database
performance tuning, proposed the next space management plan. State of the Oracle database to conduct a comprehensive inspection.
Working day
Sure
that all the instance state normal log on to the database or instance,
detection of Oracle background processes: # ps-ef | grep ora
2, check the use of the file system (space). If the file system space is less than 20%, you need to delete unused files to free up space. # Df-k
3, check the log files and trace files record errors in the alert and trace files.
cd $ Oracle_BASE / oradata / <sid> / bdump tail-f alert_ <sid>. log |
4, the effectiveness of the date of the inspection database backup
RMAN backup, check the third-party backup tool backup log to determine whether the backup was successful
EXPORT backup, check the exp log file to determine whether the backup was successful
Other backup, check the appropriate log file
5, check the status of the data file records the state is not "online" data file and recovery.
select file_name from dba_data_files where status = "OFFLINE"
|
select a.tablespace_name, round ((total-free) / total, 3) * 100 pecent from (select tablespace_name, sum (bytes) free from dba_free_space group by tablespace_name) a, (Select tablespace_name, sum (bytes) total from dba_data_files group by tablespace_name) b where a.tablespace_name = b.tablespace_name |
select tablespace_name sum (blocks) as free_blk trunc (sum (bytes) / (1024 * 1024)) as free_m, max (bytes) / (1024) as big_chunk_k in count (*) as num_chunksfrom from dba_free_space group by tablespace_name; |
The run utlbstat.sql / of utlestat.sql generate a system report, or use statspace collect statistics
9, check the database performance record database CPU usage, IO, Buffer Hit rate, use vmstat, iostat, glance, top
10, regular processing of the problems
Weekly working
1, the control space expansion of database objects, check in every day this week to find space quickly expanding database objects, and take appropriate measures.
Delete historical data, extension table space
alter tablespace <name> add datafile '<file>' size <size>
|
2, the amount of monitoring data growth
Amount of data is growing rapidly every day this week, the inspection records found database objects, and take the appropriate measures
Delete historical data, expand the table space
alter tablespace <name> add datafile '<file>' size <size>
|
Check the following:
init <sid>. ora controlfile redo log file archiveing sort area size tablespace (system, temporary, tablespace fragment) datafiles (the autoextend Location) object (number of extent, next extent, index) rollback segment logging & tracing (alert.log, max_dump_file_size, sqlnet) |
select owner, object_name, object_type from dba_objects where status = 'INVALID'
|
select owner, constraint_name, table_name, constraint_type, status from dba_constraints where status = 'DISABLED' and constraint_type = 'p' |
select owner, trigger_name, table_name, status from dba_triggers where status = 'DISABLED' |
1, Analyze tables / indexes / cluster
analyze table <name> estimate statistics sample 50 percent;
2, check the table space debris
Find the appropriate solution based on the month weekly inspection and analysis database fragmentation,
3, looking for database performance tuning opportunities
Compare daily monitoring report on database performance to determine whether it is necessary to adjust the database performance
4, database performance tuning, if necessary performance tuning
5 Next space management plan, according to the weekly monitoring, improved methods of space management
A user following examples all tables in a database of a user's guide to another database,
exp userid = system / manager owner = username1 file = expfile.dmp
imp userid = system / manager fromuser = username1 touser = username2 ignore = y file = expfile.dmp
ORACLE database There are two types of backup methods. The first category is physical backup, the method to achieve the full recovery of the database, but the database must be running the business database archiving mode (non-return block mode), and requires a great deal of external storage devices, such as tape libraries; II backup mode the a logical backup service database used in this way, this method does not require a database to run in return block mode, not only backup is simple, but can not require an external storage device.
Database logical backup method
ORACLE database logical backup is divided into three modes: table backup, backup and full backup.
The table mode backup a user mode specified object (table). Operational databases often use this backup.
If the backup to a local file, use the following command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = exp_icdmain_csd_yyyymmdd.dmp
log = exp_icdmain_csd_yyyymmdd.log
tables = icdmain.commoninformation, icdmain.serviceinfo, icdmain.dealinfo
Direct backup to a tape device, use the following command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = / dev/rmt0
log = exp_icdmain_csd_yyyymmdd.log
tables = icdmain.commoninformation, icdmain.serviceinfo, icdmain.dealinfo
Note: In the case of disk space permits, should be backed up to a local server, and then copied to tape. For speed considerations, try not to be backed up directly to a tape device.
User mode to back up all of the objects in a user mode. Operational databases often use this backup.
If the backup to a local file, use the following command:
exp icdmain / icd owner = icdmain rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = exp_icdmain_yyyymmdd.dmp
log = exp_icdmain_yyyymmdd.log
Direct backup to a tape device, use the following command:
exp icdmain / icd owner = icdmain rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = / dev/rmt0
log = exp_icdmain_yyyymmdd.log
Note: If the disk space, it is recommended to backup to disk, and then copied to tape. If the the database smaller amount of data, this approach can be backed up.
Full mode complete database backup. The business database does not use this backup. The backup command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0 full = y
the file = exp_fulldb_yyyymmdd.dmp (the tape device compared / dev/rmt0 of)
log = exp_fulldb_yyyymmdd.log
Recommended for database backup, incremental backup, which backs up only on the data that has changed since the last backup. Incremental backup command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0 full = y inctype = incremental
the file = exp_fulldb_yyyymmdd.dmp (the tape device compared / dev/rmt0 of)
log = exp_fulldb_yyyymmdd.log
Note: For incremental backups must meet the following conditions:
Only full database backup is valid, and the first time need full = y parameter, after inctype = incremental parameters.
2 user must have system role EXP_FULL_DATABASE.
Traffic is small before the database backup.
(4) If the disk space, it is recommended that backup to disk, and then backed up to tape.
The business database backup methods and backup cycle EXP before the SYS user run CATEXP.SQL file (If you have previously run the file, you do not execute this script).
There is no special instructions, do not allow the client to perform backup command.
Backup backup command in the command reference table mode.
Backed up to tape first backup to a local disk file from a disk file, you need to dump to a tape device.
To view the tape device configured on the host, use the following command:
lsdev-Cc tape
The results shown in the following example shows:
rmt0 Available 30-58-00-2,0 SCSI 4mm Tape Drive
rmt1 Defined 30-58-00-0,0 SCSI 4mm Tape Drive
Available equipment is marked with tape devices available.
To view the contents of tape storage, use the following command:
tar-tvf / dev/rmt0
The results shown in the following example shows:
-Rw-r - r - 300 400 8089 600 Jan 11 14:33:57 2001 exp_icdmain_20010111.dmp
If the display similar to the following, it means that the tape storage backup data directly from the database backup to tape, rather than dump to tape backup files from the local disk, so the operating system does not recognize.
tar: 0511-193 An error occurred while reading from the media.
There is an input or output error.
Or
tar: 0511-169 A directory checksum error on media; -267331077 not equal to 25626.
3 new tape or no tape to retain the existing data, use the following command:
tar-cvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: A. The command will unconditionally overwrite the existing data on the tape.
B. The file name may not contain path information, such as :/ backup / exp_icdmain_yyyymmdd.dmp.
Need to retain the existing data tape, use the following command:
tar-rvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: This command file exp_icdmain_yyyymmdd.dmp appended to the end of the tape does not overwrite existing data.
Particular emphasis on: If the backup directly from the database backup to tape, not further additional copy any other files on the tape, otherwise the backup data failure.
For dump to tape backup files to the local hard disk, use the following command:
A. will copy all the files on the tape to the current directory of the local hard disk
tar-xvf / dev/rmt0
B. the specified file on the tape is copied to the current directory of the local hard disk
tar-xvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup Schedule backup system I / O, so it is recommended that the backup job after 23:00.
Business Database Oracle version recovery, recovery programs to be determined according to the backup program. The business database table and users back up programs combining business database recovery needs table recovery and recovery of combining program according to the actual situation.
The logic of the recovery program database recovery is divided into table recovery, user recovery, fully restored three modes.
This will be based on the table mode to restore the backed-up data in accordance with Table mode.
A. restore backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_cs
d_yyyymmdd.dmp
log = imp_icdmain_csd_yyyymmdd.log
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = / dev/rmt0
log = imp_icdmain_csd_yyyymmdd.log
B. Restoration of the specified table in the backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_cs
d_yyyymmdd.dmp
log = imp_icdmain_csd_yyyymmdd.log
tables = commoninformation, serviceinfo
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = / dev/rmt0
log = imp_icdmain_csd_yyyymmdd.log
tables = commoninformation, serviceinfo
This will be based on the user mode to restore the backed-up data in accordance with the user-mode.
A. restore backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_yy
yymmdd.dmp
log = imp_icdmain_yyyymmdd.log
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = / dev/rmt0
log = imp_icdmain_yyyymmdd.log
B. Restoration of the specified table in the backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_yy
yymmdd.dmp
log = imp_icdmain_yyyymmdd.log
tables = commoninformation, serviceinfo
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = / dev/rmt0
log = imp_icdmain_yyyymmdd.log
tables = commoninformation, serviceinfo
Full mode if the backup mode to full mode, the following recovery methods:
From a local file, use the following command:
imp system / manager rows = y indexes = n commit = y buffer = 65536
feedback = 100000 ignore = y volsize = 0 full = y
file = exp_icdmain_yyyymmdd.dmp
log = imp_icdmain_yyyymmdd.log
Restore from the tape device, use the following command:
imp system / manager rows = y indexes = n commit = y buffer = 65536
feedback = 100000 ignore = y volsize = 0 full = y
file = / dev/rmt0
log = imp_icdmain_yyyymmdd.log
Parameter Description
1 ignore parameters
Oracle in the process of restoring data when restoring a table, the table already exists, is necessary according to the the ignore parameter settings to determine how to do this.
If IGNORE = y, Oracle does not perform CREATE TABLE statement directly inserted into the data table, if the inserted record against constraints, such as primary key constraint error records will not be inserted, but the legal records will be added to the table .
If IGNORE = n, Oracle does not perform a CREATE TABLE statement will not insert data into the table, but ignore the error of the table, continue to recover under a table.
Indexes parameters in the process of restoring data, indexes = n, then the indexes on the table will not be restored, but the primary key corresponding to the unique index unconditional restoration, to ensure data integrity.
Character set conversion for single-byte character set (for example, US7ASCII) is restored, the database automatically converted to the session character set (NLS_LANG parameter); For multi-byte character set (for example, ZHS16CGB231280), the recovery should try to make the character set (to avoid conversion), if you want to convert, the target database character set should be a superset of the output database character set.
The recovery method business database table recovery program. Before recovery IMP, first in the SYS user run CATEXP.SQL file (If you have previously run the file, you do not run this script), and then run the following command:
IMP ICDMAIN / ICD FILE = file names LOG = LOG file name ROWS = Y
COMMIT = Y BUFFER = Y IGNORE = Y TABLES = table name Note: To restore the name of the table with reference to the backup table name. The recovery is in the original table based on the accumulated data. No special instructions, do not allow the client to perform the restore command
To learn Oracle's six summary
Oracle beginners often asked this question, be regarded as a unified response.
2, in order to learn ORACLE, we must first learn ORACLE architecture, now you do not need in-depth understanding of them, but to distinguish between several key concepts:
instance & database,
memory structure, process & those files, such as data file, control file, init parameter file etc
Database, tablespace, Data File and tablespace, segmnet, extent & block the relationship between them.
Of course, to distinguish between the undo tablespace & redo log file, and so on,
For many beginners, these concepts more clear understanding is not an easy thing to do, and these are not Oracle's all.
3,2 some basic concepts about Oracle below to learn some simple practical, is how to manage the Oracle database, of course, is not to manage all Oracle. In this process, you will have a deeper understanding of SQL and Oracle architecture.
4, to this point you basically be initial grasp Oracle, the following will be under your direction to specific learning other ORACLE knowledge. If you're a developer, you need more to learn PL / SQL and Developer, which will be a new course. If you are a DBA, please continue.
5, you can according to their actual situation, there is a choice of learning, which means that the contents of the following requirements in no particular order. Can choose the in-depth study of ORACLE management, backup and recovery, performance tuning, network, etc.. Of course, in the process of learning this knowledge, if the actual work better, so that you can grow up in practice, learn Trouble SHOOTING.
, Of course, in the process of learning, you can with others in the website or forum to communicate, you can see some of the experiences of others articles can also write your own experiences. Technological progress is the accumulation of experience and refining process, I hope you grow together, we exchange.
data warehouse architecture can be divided into three levels:
Data acquisition layer: Database Enterprise ETL Option + Oracle Database Data Quality Option in Oracle Database 10g with a software from all of the features of the design of the data model, data quality management, ETL process design and meta-data management. All the ETL process can be provided by the Oracle data warehouse ETL Tools: Oracle Warehouse Builder generates scripts in Oracle 10g database periodically, in accordance with the requirements of the data warehouse system, data extraction and loaded into the data warehouse system. Since the implementation of the ETL in the Oracle 10g database, you can take full advantage of Oracle 10g database provides a powerful parallel processing capabilities, to ensure efficient and reliable execution of data acquisition.
Data layer: Oracle 10g database to achieve the centralized storage and management of various types of data in a data warehouse system, including a variety of structured data and unstructured data. Oracle 10g database built-in OLAP and data mining capabilities, there is no need for complex data migration, you can complete complex statistical analysis functions directly in a relational database. Oracle 10g database by using the partitioning technology can support the storage of huge amounts of data, the maximum amount of data a database of 8,000 PB (1 petabyte (PB) = 1024TB). Oracle 10g provides a powerful parallel processing capability to meet the requirements of the data warehouse system for performance and scalability. And the system console through a grid (Grid Control) unified management of the data warehouse.
Data Presentation Layer: Oracle offers a new business intelligence to solve the program Oracle BI EE, OLAP analysis and development tools (JDeveloper + BI Beans) and data mining tools (Oracle Data Miner), the results of statistical analyzes show a variety of ways. The Oracle data show program using and HTML in two ways, standards-based J2EE platform. With a single element database, there is no need for the metadata , to minimize the maintenance of the system. At the same time, the the Oracle data show program with powerful analysis capabilities and very easy-to-use analytical dashboard and support through the portal (Portal) technology integration, to provide consistent access interface for different types of users.
Related Reading
The data warehouse is a subject-oriented, integrated, non-renewable, changing with the time of data collection, which is used to support decision analysis and processing of the business or organization.
Data warehouse, the English name for the Data Warehouse, can be abbreviated as DW.
The father of the data warehouse Bill Inmon, "Building the Data Warehouse", a book published in 1991, the proposed definition is widely accepted - the data warehouse (Data Warehouse) is a subject-oriented (Subject Oriented), integrated (Integrated) relatively stable (non-volatile), reflects the historical changes (Time Variant) collection of data used to support management decision-making (Decision Making Support).
Tag: buffer, data warehouse, file, The database
DBA duties and day-to-day duties: Install and upgrade the database , and application tools to build and configure the network environment.
Familiar with the structure of the database system to predict future needs, the development of database storage solutions.
According to the developers to design applications demand to create the database storage structure.
4 according to the demand for applications developers to design, create database objects
According to the developer's feedback, when necessary, to modify the structure of the database
Management database users to maintain the database
Control and monitor user access to the database access
8. Monitor and optimize the performance of the database
9 Develop database backup plan, disaster appears to restore the database information
10 Backup and restore database
Contact database system manufacturers, tracking technology information
12. The middle of the client layer and link
13. Ensure the connection
Method One:
1, create a local Access database, import the Excel data into Access. Can
be directly imported clicking the right mouse button, select Import
file type to import Excel files can also be achieved by creating a macro
with TransferSpreadsheet function.
2, created locally the ODBC, link oracle database. Oracle need to import the data table created in the local Access linked table (do not use the import table).
3, create the insert query in an Access database will be imported good Excel data into oracle.
Method 2:
First save the Excel csv file format, such as the test.csv, write a insert.ctl again
Sqlldr import!
insert.ctl reads as follows:
load data - control file identifies the
infile test.csv - To enter the data file name test.csv
append into table table_name - 3, append records to the table table_name
fields terminated by, - 4, field termination, is a comma
(Field1,
field2,
field3,
...
fieldN) ----- Define columns correspond to the order
Note that the parentheses field order with the corresponding csv file then you can execute the following command:
sqlldr user / password control = insert.ctl
Daily work:
1 Make sure that the server is working normal state and normal state of the various databases
2 Check whether the hard disk space is enough
3 Check the database log to view the trace file, check if there is an error message
4 Check the validity of backups
5
found that the performance of the database server performance parameter
monitoring system performance monitor whether the decline to find the
cause and resolve
6 fill dba log
Weekly work:
A monitoring database object space expansion
2 decide whether to adjust the storage parameters
The system health check
4 Check to delete a data object does not make any sense
5, check the deletion does not make sense constraints and triggers
6 weekly backup check processing
Monthly work:
1 Based on the above inspection, analysis of whether it will affect the performance of the database fragmentation exists
2 According to the historical database performance, to decide how to optimize performance
3 Daily Weekly Monthly backup
4 summarizes the work of the next step of the management plan
DBA daily summary of the work
administrator shall be the Oracle database system to regularly monitor:
1
a day, the operating status of the Oracle database, log files, backup,
database space usage, system resource usage checks to identify and solve
the problem.
The
week expanded space for database objects, data growth monitoring
database health checks, check the state of the database objects.
Monthly
carry out the analyze tables and indexes, check the table space debris,
looking for the chance of database performance tuning, database
performance tuning, proposed the next space management plan. State of the Oracle database to conduct a comprehensive inspection.
Working day
Sure
that all the instance state normal log on to the database or instance,
detection of Oracle background processes: # ps-ef | grep ora
2, check the use of the file system (space). If the file system space is less than 20%, you need to delete unused files to free up space. # Df-k
3, check the log files and trace files record errors in the alert and trace files.
cd $ Oracle_BASE / oradata / <sid> / bdump tail-f alert_ <sid>. log |
4, the effectiveness of the date of the inspection database backup
RMAN backup, check the third-party backup tool backup log to determine whether the backup was successful
EXPORT backup, check the exp log file to determine whether the backup was successful
Other backup, check the appropriate log file
5, check the status of the data file records the state is not "online" data file and recovery.
select file_name from dba_data_files where status = "OFFLINE"
|
select a.tablespace_name, round ((total-free) / total, 3) * 100 pecent from (select tablespace_name, sum (bytes) free from dba_free_space group by tablespace_name) a, (Select tablespace_name, sum (bytes) total from dba_data_files group by tablespace_name) b where a.tablespace_name = b.tablespace_name |
select tablespace_name sum (blocks) as free_blk trunc (sum (bytes) / (1024 * 1024)) as free_m, max (bytes) / (1024) as big_chunk_k in count (*) as num_chunksfrom from dba_free_space group by tablespace_name; |
The run utlbstat.sql / of utlestat.sql generate a system report, or use statspace collect statistics
9, check the database performance record database CPU usage, IO, Buffer Hit rate, use vmstat, iostat, glance, top
10, regular processing of the problems
Weekly working
1, the control space expansion of database objects, check in every day this week to find space quickly expanding database objects, and take appropriate measures.
Delete historical data, extension table space
alter tablespace <name> add datafile '<file>' size <size>
|
2, the amount of monitoring data growth
Amount of data is growing rapidly every day this week, the inspection records found database objects, and take the appropriate measures
Delete historical data, expand the table space
alter tablespace <name> add datafile '<file>' size <size>
|
Check the following:
init <sid>. ora controlfile redo log file archiveing sort area size tablespace (system, temporary, tablespace fragment) datafiles (the autoextend Location) object (number of extent, next extent, index) rollback segment logging & tracing (alert.log, max_dump_file_size, sqlnet) |
select owner, object_name, object_type from dba_objects where status = 'INVALID'
|
select owner, constraint_name, table_name, constraint_type, status from dba_constraints where status = 'DISABLED' and constraint_type = 'p' |
select owner, trigger_name, table_name, status from dba_triggers where status = 'DISABLED' |
1, Analyze tables / indexes / cluster
analyze table <name> estimate statistics sample 50 percent;
2, check the table space debris
Find the appropriate solution based on the month weekly inspection and analysis database fragmentation,
3, looking for database performance tuning opportunities
Compare daily monitoring report on database performance to determine whether it is necessary to adjust the database performance
4, database performance tuning, if necessary performance tuning
5 Next space management plan, according to the weekly monitoring, improved methods of space management
A user following examples all tables in a database of a user's guide to another database,
exp userid = system / manager owner = username1 file = expfile.dmp
imp userid = system / manager fromuser = username1 touser = username2 ignore = y file = expfile.dmp
ORACLE database There are two types of backup methods. The first category is physical backup, the method to achieve the full recovery of the database, but the database must be running the business database archiving mode (non-return block mode), and requires a great deal of external storage devices, such as tape libraries; II backup mode the a logical backup service database used in this way, this method does not require a database to run in return block mode, not only backup is simple, but can not require an external storage device.
Database logical backup method
ORACLE database logical backup is divided into three modes: table backup, backup and full backup.
The table mode backup a user mode specified object (table). Operational databases often use this backup.
If the backup to a local file, use the following command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = exp_icdmain_csd_yyyymmdd.dmp
log = exp_icdmain_csd_yyyymmdd.log
tables = icdmain.commoninformation, icdmain.serviceinfo, icdmain.dealinfo
Direct backup to a tape device, use the following command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = / dev/rmt0
log = exp_icdmain_csd_yyyymmdd.log
tables = icdmain.commoninformation, icdmain.serviceinfo, icdmain.dealinfo
Note: In the case of disk space permits, should be backed up to a local server, and then copied to tape. For speed considerations, try not to be backed up directly to a tape device.
User mode to back up all of the objects in a user mode. Operational databases often use this backup.
If the backup to a local file, use the following command:
exp icdmain / icd owner = icdmain rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = exp_icdmain_yyyymmdd.dmp
log = exp_icdmain_yyyymmdd.log
Direct backup to a tape device, use the following command:
exp icdmain / icd owner = icdmain rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0
file = / dev/rmt0
log = exp_icdmain_yyyymmdd.log
Note: If the disk space, it is recommended to backup to disk, and then copied to tape. If the the database smaller amount of data, this approach can be backed up.
Full mode complete database backup. The business database does not use this backup. The backup command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0 full = y
the file = exp_fulldb_yyyymmdd.dmp (the tape device compared / dev/rmt0 of)
log = exp_fulldb_yyyymmdd.log
Recommended for database backup, incremental backup, which backs up only on the data that has changed since the last backup. Incremental backup command:
exp icdmain / icd rows = y indexes = n compress = n buffer = 65536
feedback = 100000 volsize = 0 full = y inctype = incremental
the file = exp_fulldb_yyyymmdd.dmp (the tape device compared / dev/rmt0 of)
log = exp_fulldb_yyyymmdd.log
Note: For incremental backups must meet the following conditions:
Only full database backup is valid, and the first time need full = y parameter, after inctype = incremental parameters.
2 user must have system role EXP_FULL_DATABASE.
Traffic is small before the database backup.
(4) If the disk space, it is recommended that backup to disk, and then backed up to tape.
The business database backup methods and backup cycle EXP before the SYS user run CATEXP.SQL file (If you have previously run the file, you do not execute this script).
There is no special instructions, do not allow the client to perform backup command.
Backup backup command in the command reference table mode.
Backed up to tape first backup to a local disk file from a disk file, you need to dump to a tape device.
To view the tape device configured on the host, use the following command:
lsdev-Cc tape
The results shown in the following example shows:
rmt0 Available 30-58-00-2,0 SCSI 4mm Tape Drive
rmt1 Defined 30-58-00-0,0 SCSI 4mm Tape Drive
Available equipment is marked with tape devices available.
To view the contents of tape storage, use the following command:
tar-tvf / dev/rmt0
The results shown in the following example shows:
-Rw-r - r - 300 400 8089 600 Jan 11 14:33:57 2001 exp_icdmain_20010111.dmp
If the display similar to the following, it means that the tape storage backup data directly from the database backup to tape, rather than dump to tape backup files from the local disk, so the operating system does not recognize.
tar: 0511-193 An error occurred while reading from the media.
There is an input or output error.
Or
tar: 0511-169 A directory checksum error on media; -267331077 not equal to 25626.
3 new tape or no tape to retain the existing data, use the following command:
tar-cvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: A. The command will unconditionally overwrite the existing data on the tape.
B. The file name may not contain path information, such as :/ backup / exp_icdmain_yyyymmdd.dmp.
Need to retain the existing data tape, use the following command:
tar-rvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: This command file exp_icdmain_yyyymmdd.dmp appended to the end of the tape does not overwrite existing data.
Particular emphasis on: If the backup directly from the database backup to tape, not further additional copy any other files on the tape, otherwise the backup data failure.
For dump to tape backup files to the local hard disk, use the following command:
A. will copy all the files on the tape to the current directory of the local hard disk
tar-xvf / dev/rmt0
B. the specified file on the tape is copied to the current directory of the local hard disk
tar-xvf / dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup Schedule backup system I / O, so it is recommended that the backup job after 23:00.
Business Database Oracle version recovery, recovery programs to be determined according to the backup program. The business database table and users back up programs combining business database recovery needs table recovery and recovery of combining program according to the actual situation.
The logic of the recovery program database recovery is divided into table recovery, user recovery, fully restored three modes.
This will be based on the table mode to restore the backed-up data in accordance with Table mode.
A. restore backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_cs
d_yyyymmdd.dmp
log = imp_icdmain_csd_yyyymmdd.log
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = / dev/rmt0
log = imp_icdmain_csd_yyyymmdd.log
B. Restoration of the specified table in the backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_cs
d_yyyymmdd.dmp
log = imp_icdmain_csd_yyyymmdd.log
tables = commoninformation, serviceinfo
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = / dev/rmt0
log = imp_icdmain_csd_yyyymmdd.log
tables = commoninformation, serviceinfo
This will be based on the user mode to restore the backed-up data in accordance with the user-mode.
A. restore backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_yy
yymmdd.dmp
log = imp_icdmain_yyyymmdd.log
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = / dev/rmt0
log = imp_icdmain_yyyymmdd.log
B. Restoration of the specified table in the backup data from a local file, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
file = exp_icdmain_yy
yymmdd.dmp
log = imp_icdmain_yyyymmdd.log
tables = commoninformation, serviceinfo
Restore from the tape device, use the following command:
imp icdmain / icd fromuser = icdmain touser = icdmain rows = y indexes = n
commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file = / dev/rmt0
log = imp_icdmain_yyyymmdd.log
tables = commoninformation, serviceinfo
Full mode if the backup mode to full mode, the following recovery methods:
From a local file, use the following command:
imp system / manager rows = y indexes = n commit = y buffer = 65536
feedback = 100000 ignore = y volsize = 0 full = y
file = exp_icdmain_yyyymmdd.dmp
log = imp_icdmain_yyyymmdd.log
Restore from the tape device, use the following command:
imp system / manager rows = y indexes = n commit = y buffer = 65536
feedback = 100000 ignore = y volsize = 0 full = y
file = / dev/rmt0
log = imp_icdmain_yyyymmdd.log
Parameter Description
1 ignore parameters
Oracle in the process of restoring data when restoring a table, the table already exists, is necessary according to the the ignore parameter settings to determine how to do this.
If IGNORE = y, Oracle does not perform CREATE TABLE statement directly inserted into the data table, if the inserted record against constraints, such as primary key constraint error records will not be inserted, but the legal records will be added to the table .
If IGNORE = n, Oracle does not perform a CREATE TABLE statement will not insert data into the table, but ignore the error of the table, continue to recover under a table.
Indexes parameters in the process of restoring data, indexes = n, then the indexes on the table will not be restored, but the primary key corresponding to the unique index unconditional restoration, to ensure data integrity.
Character set conversion for single-byte character set (for example, US7ASCII) is restored, the database automatically converted to the session character set (NLS_LANG parameter); For multi-byte character set (for example, ZHS16CGB231280), the recovery should try to make the character set (to avoid conversion), if you want to convert, the target database character set should be a superset of the output database character set.
The recovery method business database table recovery program. Before recovery IMP, first in the SYS user run CATEXP.SQL file (If you have previously run the file, you do not run this script), and then run the following command:
IMP ICDMAIN / ICD FILE = file names LOG = LOG file name ROWS = Y
COMMIT = Y BUFFER = Y IGNORE = Y TABLES = table name Note: To restore the name of the table with reference to the backup table name. The recovery is in the original table based on the accumulated data. No special instructions, do not allow the client to perform the restore command
To learn Oracle's six summary
Oracle beginners often asked this question, be regarded as a unified response.
2, in order to learn ORACLE, we must first learn ORACLE architecture, now you do not need in-depth understanding of them, but to distinguish between several key concepts:
instance & database,
memory structure, process & those files, such as data file, control file, init parameter file etc
Database, tablespace, Data File and tablespace, segmnet, extent & block the relationship between them.
Of course, to distinguish between the undo tablespace & redo log file, and so on,
For many beginners, these concepts more clear understanding is not an easy thing to do, and these are not Oracle's all.
3,2 some basic concepts about Oracle below to learn some simple practical, is how to manage the Oracle database, of course, is not to manage all Oracle. In this process, you will have a deeper understanding of SQL and Oracle architecture.
4, to this point you basically be initial grasp Oracle, the following will be under your direction to specific learning other ORACLE knowledge. If you're a developer, you need more to learn PL / SQL and Developer, which will be a new course. If you are a DBA, please continue.
5, you can according to their actual situation, there is a choice of learning, which means that the contents of the following requirements in no particular order. Can choose the in-depth study of ORACLE management, backup and recovery, performance tuning, network, etc.. Of course, in the process of learning this knowledge, if the actual work better, so that you can grow up in practice, learn Trouble SHOOTING.
, Of course, in the process of learning, you can with others in the website or forum to communicate, you can see some of the experiences of others articles can also write your own experiences. Technological progress is the accumulation of experience and refining process, I hope you grow together, we exchange.
data warehouse architecture can be divided into three levels:
Data acquisition layer: Database Enterprise ETL Option + Oracle Database Data Quality Option in Oracle Database 10g with a software from all of the features of the design of the data model, data quality management, ETL process design and meta-data management. All the ETL process can be provided by the Oracle data warehouse ETL Tools: Oracle Warehouse Builder generates scripts in Oracle 10g database periodically, in accordance with the requirements of the data warehouse system, data extraction and loaded into the data warehouse system. Since the implementation of the ETL in the Oracle 10g database, you can take full advantage of Oracle 10g database provides a powerful parallel processing capabilities, to ensure efficient and reliable execution of data acquisition.
Data layer: Oracle 10g database to achieve the centralized storage and management of various types of data in a data warehouse system, including a variety of structured data and unstructured data. Oracle 10g database built-in OLAP and data mining capabilities, there is no need for complex data migration, you can complete complex statistical analysis functions directly in a relational database. Oracle 10g database by using the partitioning technology can support the storage of huge amounts of data, the maximum amount of data a database of 8,000 PB (1 petabyte (PB) = 1024TB). Oracle 10g provides a powerful parallel processing capability to meet the requirements of the data warehouse system for performance and scalability. And the system console through a grid (Grid Control) unified management of the data warehouse.
Data Presentation Layer: Oracle offers a new business intelligence to solve the program Oracle BI EE, OLAP analysis and development tools (JDeveloper + BI Beans) and data mining tools (Oracle Data Miner), the results of statistical analyzes show a variety of ways. The Oracle data show program using and HTML in two ways, standards-based J2EE platform. With a single element database, there is no need for the metadata , to minimize the maintenance of the system. At the same time, the the Oracle data show program with powerful analysis capabilities and very easy-to-use analytical dashboard and support through the portal (Portal) technology integration, to provide consistent access interface for different types of users.
Related Reading
The data warehouse is a subject-oriented, integrated, non-renewable, changing with the time of data collection, which is used to support decision analysis and processing of the business or organization.
Data warehouse, the English name for the Data Warehouse, can be abbreviated as DW.
The father of the data warehouse Bill Inmon, "Building the Data Warehouse", a book published in 1991, the proposed definition is widely accepted - the data warehouse (Data Warehouse) is a subject-oriented (Subject Oriented), integrated (Integrated) relatively stable (non-volatile), reflects the historical changes (Time Variant) collection of data used to support management decision-making (Decision Making Support).
No comments:
Post a Comment