Oracle knowledge collection: monitoring and checking Oracle databases

Oracle knowledge collection: monitoring and checking Oracle databases

I sorted out some basic Oracle knowledge and shared it with you.

1. How to monitor and check Oracle databases

Sqlplus '/as sysdba'

Manually generate an oracle database Snapshot

Execute dbbms_workload_repository.create_snapshot ();

Execute the SQL script of the automatic workload Library

@? /Rdbms/admin/awrrpt. SQL; @ indicates that the SQL script is executed in the database ,? The $ ORACLE_HOME directory.

Enter the time number you want to analyze.

Html format is exported by default.

Generate automatic diagnosis report

Sqlplus/as sysdba

@? /Rdbms/admin/addmrpt. SQL

Enter a number.

2. Oracle dbf File problems completely, ORA-01219

Alter database datafile '/data/app/oradata/ora237/users01.dbf' offline drop;

Force Delete this file

3. Check the Oracle database and routine maintenance database

Check database logs

The most important log of the database is the warning log. The alert_sid.log is generally located in the $ ORACLE_BASE/admin/ORACLE_SID/bdump directory.

You can also execute

Show parameter background_dump_dest;

Use cat to view the returned results. Focus on the ORA-or Errors keywords.

Check whether the database data files are automatically extended.

Select file_id, file_name, tablespace_name, autoextensible from dba_data_files;

4. logminer log analysis in Oracle

Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Select sysdate from dual;

Add or delete operations

Alter system switch logfile; switch the current redo log to generate an archive log (equivalent to re-storing subsequent operations in another log)

Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; modify the format for ease of viewing

Select sequence #, first_time from v $ log_history order by first-time desc;

Archive logs are stored in/u01/app/oracle/flash_recovery_area/ORCL/archivelog by default.

View analysis logs

Select t. FIRST_TIME, t. NAME from v $ archived_log t order by t. FIRST_TIME desc;

Use logminer to analyze data

Conn/as sysdba

Execute dbma_logmnr.add_logfile (logfilename => '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011-05_04/reply _. arc', options => dbms_logmnr.new );

Start logminer

Execute dbms_logmnr.start_logmnr (options => dbms_logmnr.dict_from_online_catalog );

Put the analysis data into the table and grant the table permissions to common users.

Create table logminer_20110504 tablespace BANK_TBS as select * from v $ logmnr_contents;

Grant select on logminer_20110504 to bankuser;

End logminer Analysis

Execute dbms_logmnr.end_logmnr;

View analysis log results

Select t. timestamp, t. SQL _redo, t. SQL _undo from sys. logminer_20110504 t where t. seg_owner = 'bankuser' and t. SQL _undo is not null;

5. Activation and cancellation of Oracle Audit

Audit the operations performed by the audit user, and oracle stores the audit trail results in the OS file or database.

Activate Audit

Conn/as sysdba

Show parameter audit_sys_operations; view audit parameters

Alter system set audit_sys_operations = TRUE scope = spfile; set audit parameter to true

Alter system set audit_trail = db scope = spfile;

Startup force; reboot takes effect

Show parameter audit_sys_operations; view audit parameters

Define tables to be audited

Execute dbms_fga.add_policy (object_schema => 'bankuser', object_name => 'emp', policy_name => 'chk _ emp', statement_types => 'insert, update, delete ');

Authorize normal users to view audit tables

Grant select on dba_fga_audit_trail to bankuser;

Perform the Add/delete operation to view the table audit.

Select t. timestamp, t. SQL _text from sys. dba_fga_audit_trail t;

Cancel Audit

Execute dbms_fga.drop_policy (object_schema => 'bankuser', object_name => 'emp', policy_name => 'chk _ emp ');

6. Restore the Oracle database to a certain time point or an scn

Alter session set nls_date_format = 'yyyymmdd hh24: mi: ss ';

Select sysdate from dual;

Conn dbauser/123456; delete any user and use one of the tables for testing.

Drop table test;

Rman target/

Startup mount;

Restore database; restore data files

SQL 'alter session set nls_date_format = "yyyymmdd hh24: mi: ss" '; SQL ''is required because it is executed in rman '';

Recover database until time '2014 11:33:56 '; recover data at this time, and re-generate the redo log file after execution

SQL 'alter database open resetlogs ';

Scn refers to the system change number, which is used by the oracle database to record the status and track of the database in the past time.

Conn/as sysdba

Select dbms_flashback.get_system_chage_number from dual;

Select to_char (scn_to_timestamp (963959), 'yyyy-MM-DD HH24: MI: ss') from dual; converts scn to time

Select * from (select time_dp, scn from smon_scn_time order by time_dp desc) where rownum <10; find the corresponding scn number based on the data deletion time.

Rman target/

Startup mount;

Restore database; restore data files

Recover database until scn 963915;

SQL 'alter database open resetlogs'; regenerate the redo log file

7. Oracle redo log file hard disk failure Solution

Rman target/

List backup;

List backup summary;

Delete log files in the database data folder, for example, all files suffixed with log under/u01/app/oracle/oradata/ORCL.

Sqlplus/as sysdba

Alter system switch logfile; because the log file is not redone, it will remain waiting for loading. If you need to switch the log, the database will report an error.

Shutdown immediate;

Startup also reports an error.

Recover database until cancel; reset the redo log file of the oracle database (this recovery is incomplete recovery database)

Alter database open resetlogs; regenerate the redo File

Archive log list; view logs

8. When Oracle uses flashback, undosql is not displayed

This is because oracle11g does not enable this function.

Run the following statement with the Administrator sys (sysdba ):

Alter databases add supplemental log data;

If we want to restore some data, execute (view the incorrect SQL statement, for example, we accidentally deleted a piece of data from the test table, copy and execute the insert statement from the following results)

Select undo_ SQL from flashback_transaction_query where table_name = 'test'; # note that the table name after where in oracle must be in uppercase; otherwise, the system will prompt that the table cannot be found.

The result is the latest operation.

Restore scott's test table to data 10 minutes ago

Flashback select * from scott. test to timestamp sysdate-10/1440;

Accidentally delete a database table and use flashback for restoration

Create table test (a int) tablespace USERS; the following tablespace cannot be missing; otherwise, it cannot enter the recycle bin after deletion.

Drop table test;

Flashback table test to before drop; the data in the original database table is also restored.

9. Oracle Database often prompts 27102 out of memory Solution

There are many solutions on the Internet, but I can't understand either of them. I don't know what the writing is, why can I solve the problem in one sentence? I have to give a long story about what the principle is, it seems awesome.

My Solutions

Vi/etc/sysctl. conf

Modify kernel. the value of shmall (total shared memory), which was originally 2097152. I gave it to 4097152 (I don't know what value to change for the moment, but it's right to increase the value, I just want to change the first 2 to 4 and solve the problem automatically.) then, sqlplus/as sysdba

Execute select status from v $ instance. The error "out of memory" is not displayed, but "open" is displayed normally.

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151089.htm