SQL> select dw.waiting_session, dw.holding_session, b.serial#, w.event, w.program wprogram, b.program bprogram, w.module wmod, b.module bmod, LOCK_ID1 from sys.dba_waiters dw, v$session w, v$session b where dw.waiting_session = w.sid and dw.holding_session = b.sid and (w.module like 'Data Pump%' or w.program like '%EXPDP%' or w.program like '%IMPDP%') order by dw.holding_session;
no rows selected
SQL> select sw.SID, sw.SEQ#, sw.EVENT, sw.WAIT_TIME, sw.SECONDS_IN_WAIT, sw.STATE, sw.P1TEXT, sw.P1, sw.P2TEXT, sw.P2, sw.P3TEXT, sw.P3 from V$SESSION_WAIT sw, v$session s where sw.wait_class <> 'Idle' and sw.sid = s.sid and (s.module like 'Data Pump%' or s.program like '%EXPDP%' or s.program like '%IMPDP%');
ORA-00210: cannot open the specified control file ORA-00202: control file: '+FRA/STBRAC/CONTROLFILE/current.294.1005908473' ORA-17503: ksfdopn:2 Failed to open file +FRA/STBRAC/CONTROLFILE/current.294.1005908473 ORA-15001: diskgroup "FRA" does not exist or is not mounted ORA-15040: diskgroup is incomplete ORA-205 signalled during: alter database mount...
WARNING: failed to open a disk[/dev/asmdiskd] ORA-15025: could not open disk "/dev/asmdiskd" ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied
检查asm的磁盘等信息均正常
1 2 3 4 5 6 7 8 9 10 11
[grid@stbracnode1 ~]$ asmcmd ASMCMD> lsdg State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 512 4096 4194304 51200 16784 0 16784 0 Y CRS/ MOUNTED EXTERN N 512 512 4096 4194304 61440 55336 0 55336 0 N DATA/ MOUNTED EXTERN N 512 512 4096 4194304 20480 17404 0 17404 0 N FRA/
[oracle@xb admin]$ sqlplus sys/oracle@pdb12c as sysdba
sys@PDB12C> @?/rdbms/admin/awrsqrpt.sql
Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type:
Type Specified: html
Specify the location of AWR Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ AWR_ROOT - Use AWR data from root (default) AWR_PDB - Use AWR data from PDB Enter value for awr_location: AWR_PDB
Location of AWR Data Specified: AWR_PDB >>>>====这里选择的AWR_PDB
declare * ERROR at line 1: ORA-20200: Database/Instance 3393322654/1 does not exist in AWR_PDB_DATABASE_INSTANCE ORA-06512: at line 27
DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 380590835 ORA12C 1 ora12c
...
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ ---------- --------- ---------- ------ * 380590835 1 ORA12C ora12c xb.oracle.co
Using 380590835 for database Id Using 1 for instance number
Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'.
'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report
Enter value for report_type:
Type Specified: html
Specify the location of AWR Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ AWR_ROOT - Use AWR data from root (default) AWR_PDB - Use AWR data from PDB Enter value for awr_location: AWR_PDB
Location of AWR Data Specified: AWR_PDB >>>>====选择AWR_PDB并未报错
...
Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing >return> without specifying a number lists all completed snapshots.
SQL> startup nomount ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpcreates
------ Semaphore Limits -------- max number of arrays = 128 // SEMMNI max semaphores per array = 250 // SEMMSL max semaphores system wide = 32000 // SEMMNS max ops per semop call = 100 // SEMOP semaphore max value = 32767
select P2TEXT,p2, p3 from v$session_wait where event = 'enq: HW - contention';
P2TEXT P2 P3 -------------------- ---------- ---------- table space 6 21051234
可以看出是表空间的自动扩展导致，然后用于分析是什么对象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(21051234) FILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(21051234) BLOCK# from dual;
FILE# BLOCK# ---------- ---------- 5 79714
select owner, segment_type, segment_name from dba_extents where file_id = 5 and 79714 between block_id and block_id + blocks - 1 and tablespace_name = (select name from ts$ where ts# = 6);
Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'
16:54:07 sys. >alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'
sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;
set numwidth 30; set pagesize 50000; alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
sys.>select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------------------------------------- --- 3 ONLINE +REDO/xxx/redo03.log NO 2 ONLINE +REDO/xxx/redo02.log NO 1 ONLINE +REDO/xxx/redo01.log NO 4 STANDBY +REDO/xxx/stdredo01.log NO 5 STANDBY +REDO/xxx/stdredo02.log NO 6 STANDBY +REDO/xxx/stdredo03.log NO 7 STANDBY +REDO/xxx/stdredo04.log NO 10 ONLINE +REDO/xxx/redo10.log NO 11 ONLINE +REDO/xxx/redo11.log NO
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo03.log' to '+REDO/xxx/redo03.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo02.log' to '+REDO/xxx/redo02.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo01.log' to '+REDO/xxx/redo01.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo01.log' to '+REDO/xxx/stdredo01.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo02.log' to '+REDO/xxx/stdredo02.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo03.log' to '+REDO/xxx/stdredo03.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo04.log' to '+REDO/xxx/stdredo04.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo10.log' to '+REDO/xxx/redo10.log'; ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo11.log' to '+REDO/xxx/redo11.log';
22:52:18 sys. xxx>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ; ORA-00279: change 75238614094 generated at 02/23/2019 00:15:46 needed for thread 1 ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429 ORA-00280: change 75238614094 for thread 1 is in sequence #21201
22:52:35 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL} +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429 ORA-00279: change 75238624057 generated at 02/23/2019 00:19:25 needed for thread 1 ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179 ORA-00280: change 75238624057 for thread 1 is in sequence #21202 ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429' no longer needed for this recovery
22:52:49 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL} +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179 ORA-00279: change 75238632711 generated at 02/23/2019 00:22:37 needed for thread 1 ORA-00289: suggestion : +nvmedg ORA-00280: change 75238632711 for thread 1 is in sequence #21203 ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179' no longer needed for this recovery
22:53:03 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL} >>>>====缺少进一步的日志用于恢复 auto ORA-00308: cannot open archived log '+nvmedg' ORA-17503: ksfdopn:2 Failed to open file +nvmedg ORA-15045: ASM file name '+nvmedg' is not in reference form
ORA-00308: cannot open archived log '+nvmedg' ORA-17503: ksfdopn:2 Failed to open file +nvmedg ORA-15045: ASM file name '+nvmedg' is not in reference form
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+NVMEDG/xxx/datafile/system01.dbf'
当我恢复完21201和21202两个文件以后，发现依然无法打开数据库。
1 2 3 4 5 6
sys. >alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'
sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;
set numwidth 30; set pagesize 50000; alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 97239 Session ID: 1921 Serial number: 1
sys@> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------ LOGMNR LogMiner 13696 LOGSTDBY Logical Standby 1408 SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 3328 PL/SCOPE PL/SQL Identifier Collection 1600 STREAMS Oracle Streams 1024 AUDIT_TABLES DB audit tables 320 XDB XDB 129984 AO Analytical Workspace Object Table 39104 XSOQHIST OLAP API History Tables 39104 XSAMD OLAP Catalog 5248 SM/AWR Server Manageability - Automatic Workload Repository 30022848 SM/ADVISOR Server Manageability - Advisor Framework 189312 SM/OPTSTAT Server Manageability - Optimizer Statistics History 1424768 SM/OTHER Server Manageability - Other Components 12416 STATSPACK Statspack Repository 0 SDO Oracle Spatial 76032 WM Workspace Manager 3584 ORDIM Oracle Multimedia ORDSYS Components 448 ORDIM/ORDDATA Oracle Multimedia ORDDATA Components 13888 ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS Components 0 ORDIM/SI_INFORMTN_SCHEMA Oracle Multimedia SI_INFORMTN_SCHEMA Components 0 EM Enterprise Manager Repository 47168 TEXT Oracle Text 3712 ULTRASEARCH Oracle Ultra Search 0 ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User 0 EXPRESSION_FILTER Expression Filter System 3712 EM_MONITORING_USER Enterprise Manager Monitoring User 1408 TSM Oracle Transparent Session Migration User 0 SQL_MANAGEMENT_BASE SQL Management Base Schema 1728 AUTO_TASK Automated Maintenance Tasks 384 JOB_SCHEDULER Unified Job Scheduler 14528
很明显的看到绝大部分是AWR占用了
通过语句可以查看是哪些表占用的表空间最大
1 2 3 4
col segment_name for a50 col partition_name for a30 col segment_type for a30 select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum>=10;
sys@TFDW1> SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT) /
BEGIN dbms_workload_repository.drop_snapshot_range(low_snap_id => 16873, high_snap_id=>17083); END; /
这语句执行了很长时间都没反应，最终放弃。采用另外一个办法
1 2 3
SQL> connect / as sysdba SQL> @?/rdbms/admin/catnoawr.sql SQL> @?/rdbms/admin/catawrtb.sql
其实就是把awr相关的对象重建了一遍，如果觉得风险大可以通过truncate的方式
1 2 3 4 5 6
select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024 from dba_segments s where s.segment_name like 'WRH$%' and segment_type in ('TABLE PARTITION', 'TABLE') and s.bytes/1024/1024>100 order by s.bytes/1024/1024/1024 desc;
早上来检查告警邮件，一套10.2.0.5的dg主库一直在报ora-16086错误，根据错误提示大概是备库没有可用的standby log files。在11g以后，这个错误名称换成了ORA-16086 Redo data cannot be written to the standby redo log
1 2 3 4 5
16086, 0000, "standby database does not contain available standby log files" // *Cause: The primary database is in "no data loss" mode, but the standby // database does not contain any "standby log files". // *Action: Add one or more standby log files to the standby database. // This can be done while the standby database is mounted.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- MAXIMUM AVAILABILITY RESYNCHRONIZATION
而主备库的standby log file均存在，且大小一致，状态正常
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------------------------------------- --- 1 ONLINE /u01/app/oracle/oradata/test/redo01.log NO 2 ONLINE /u01/app/oracle/oradata/test/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/test/redo03.log NO 4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO 5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO 6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO 7 STANDBY /u01/app/oracle/oradata/test/redo07.log NO 8 STANDBY /u01/app/oracle/oradata/test/redo08.log NO 9 STANDBY /u01/app/oracle/oradata/test/redo09.log NO 10 STANDBY /u01/app/oracle/oradata/test/redo10.log NO 11 STANDBY /u01/app/oracle/oradata/test/redo11.log NO 12 STANDBY /u01/app/oracle/oradata/test/redo12.log NO 13 STANDBY /u01/app/oracle/oradata/test/redo13.log NO
MOS上有说可能是因为fra目录满导致的bug，但检查数据库并没有启用fra
1 2 3 4 5 6 7
SQL> show parameter recovery
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 recovery_parallelism integer 0
尝试重建standby log file，主备库均删除重建
1 2 3 4 5 6 7 8 9 10 11 12 13
alter database drop standby logfile group 7; alter database drop standby logfile group 8; alter database drop standby logfile group 9; alter database drop standby logfile group 10; alter database drop standby logfile group 11; alter database drop standby logfile group 12; alter database drop standby logfile group 13;
select 'alter database add standby logfile group '||group#||' '''||member||''' size 1g reuse;' from v$logfile where type='STANDBY';
recover managed standby database cancel;
recover managed standby database using current logfile disconnect from session;
SYS@> show sga ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp")
根据错误代码可以判断是共享池使用出了问题
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
[oracle@xxx trace]$ oerr ora 4031 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")" // *Cause: More shared memory is needed than was allocated in the shared // pool or Streams pool. // *Action: If the shared pool is out of memory, either use the // DBMS_SHARED_POOL package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // initialization parameters SHARED_POOL_RESERVED_SIZE and // SHARED_POOL_SIZE. // If the large pool is out of memory, increase the initialization // parameter LARGE_POOL_SIZE. // If the error is issued from an Oracle Streams or XStream process, // increase the initialization parameter STREAMS_POOL_SIZE or increase // the capture or apply parameter MAX_SGA_SIZE.
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------------------ sga_target big integer 12G
在sga中有6个子池，每个子池中大部分内存都分配给了”KGH: NO ACCESS”,都在1G左右。
NUM N_HEX NAME VALUE DESCRIPTION ---------- ----- ------------------------------------------------------ ------------------------------ --------------------------------------------- 111 6F _enable_shared_pool_durations TRUE temporary to disable/enable kgh policy
最后解决办法
1 2
SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile; - restart the database
