SQL – Adamhuan's Data Center – 【逻辑驱动数据】

Oracle 12c DG，error：ORA-19815: WARNING: db_recovery_file_dest_size of 3145728000 bytes is 96.92% used, and has 96872960 remaining bytes available.

在我的一个12c的DG的备库上，检查Alert日志的时候发现了如题所示的告警。
具体如下：

2018-12-18T17:40:21.746945+08:00
Media Recovery Waiting for thread 1 sequence 45
Fetching gap sequence in thread 1, gap sequence 45-68
2018-12-18T17:40:22.458591+08:00
Completed: alter database recover managed standby database disconnect from session
2018-12-18T17:42:18.403378+08:00
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 45-68
 DBID 1520552370 branch 992771642
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------------------

（过多的输出，...）

2018-12-18T17:51:27.279736+08:00
Errors in file /u01/app/oracle/diag/rdbms/orclstdby/orcl/trace/orcl_m000_15625.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 3145728000 bytes is 96.92% used, and has 96872960 remaining bytes available.
2018-12-18T17:51:27.280216+08:00
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

2018-12-18T17:40:21.746945+08:00

Media Recovery Waiting for thread 1 sequence 45

Fetching gap sequence in thread 1, gap sequence 45-68

2018-12-18T17:40:22.458591+08:00

Completed: alter database recover managed standby database disconnect from session

2018-12-18T17:42:18.403378+08:00

FAL[client]: Failed to request gap sequence

GAP - thread 1 sequence 45-68

DBID 1520552370 branch 992771642

FAL[client]: All defined FAL servers have been attempted.

-------------------------------------------------------------------------

Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

parameter is defined to a value that's sufficiently large

enough to maintain adequate log switch information to resolve

archivelog gaps.

-------------------------------------------------------------------------

（过多的输出，...）

2018-12-18T17:51:27.279736+08:00

Errors in file /u01/app/oracle/diag/rdbms/orclstdby/orcl/trace/orcl_m000_15625.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 3145728000 bytes is 96.92% used, and has 96872960 remaining bytes available.

2018-12-18T17:51:27.280216+08:00

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

************************************************************************

备库上查看下DB_RECOVERY的相关设置：

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 3000M
SQL>

1

2

3

4

5

6

7

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /oradata/fast_recovery_area

db_recovery_file_dest_size big integer 3000M

SQL>

文件系[……]

Database – RDBMS and Not Only SQL

Xmind Zen:

————————————
Done。

发表于： 2018年11月23日 2018年11月23日
作者： Adamhuan
分类： Database - 数据库
标签： database, newsql, NoSQL, not only sql, RDBMS, SQL, XMIND, ZEN, 脑图
发表评论: Database – RDBMS and Not Only SQL

SHELL：批量对MySQL某个库执行【*.sql】

脚本：

import_db="$1"
current_dir=`pwd`
sql_file_list=`ls | grep sql`

for item in $sql_file_list
do
    echo "=================="
    echo "Current is: $item"
    echo "Import into: $import_db"
    echo "-----------"
    time mysql -u root -p'****' "$import_db" < $item
    echo "-----------"
    echo "done."
done

1

2

3

4

5

6

7

8

9

10

11

12

13

14

import_db="$1"

current_dir=`pwd`

sql_file_list=`ls | grep sql`

for item in $sql_file_list

do

echo "=================="

echo "Current is: $item"

echo "Import into: $import_db"

echo "-----------"

time mysql -u root -p'****' "$import_db" < $item

echo "-----------"

echo "done."

done

————————————————————————
Done。

发表于： 2017年11月5日 2017年11月5日
作者： Adamhuan
分类： MySQL, SHELL
标签： Linux, MySQL, Shell, SQL, 执行, 批量, 脚本
发表评论: SHELL：批量对MySQL某个库执行【*.sql】

SQL，查询正在执行的事务

SQL：

set linesize 600
col event for a18
col sql_text for a29
col sql_fulltext for a29
col username for a9
col machine for a9
col program for a18
col direct_writes for a18
col terminal for a9
SELECT s.sid,  
      s.serial#,  
      s.event,  
      a.sql_text,  
      a.sql_fulltext,  
      s.username,  
      s.status,  
      s.machine,  
      s.terminal,  
      s.program,  
      a.executions,  
      s.sql_id,  
      p.spid,  
      a.direct_writes  
 FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s  
 LEFT JOIN v$sqlarea a  
   ON s.sql_id = a.sql_id  
INNER JOIN v$process p  
   ON s.paddr = p.addr  
where sql_text is not null and rownum<2;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

set linesize 600

col event for a18

col sql_text for a29

col sql_fulltext for a29

col username for a9

col machine for a9

col program for a18

col direct_writes for a18

col terminal for a9

SELECT s.sid,

s.serial#,

s.event,

a.sql_text,

a.sql_fulltext,

s.username,

s.status,

s.machine,

s.terminal,

s.program,

a.executions,

s.sql_id,

p.spid,

a.direct_writes

FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s

LEFT JOIN v$sqlarea a

ON s.sql_id = a.sql_id

INNER JOIN v$process p

ON s.paddr = p.addr

where sql_text is not null and rownum<2;

Like This：

SQL> set linesize 600
SQL> col event for a18
SQL> col sql_text for a29
SQL> col sql_fulltext for a29
col username for a9
SQL> SQL> col machine for a9
SQL> col program for a18
SQL> col direct_writes for a18
col terminal for a9
SQL> SQL> SELECT s.sid,  
      s.serial#,  
  2    3        s.event,  
  4        a.sql_text,  
  5        a.sql_fulltext,  
  6        s.username,  
  7        s.status,  
  8        s.machine,  
  9        s.terminal,  
 10        s.program,  
 11        a.executions,  
 12        s.sql_id,  
 13        p.spid,  
 14        a.direct_writes  
 15   FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s  
 16   LEFT JOIN v$sqlarea a  
 17     ON s.sql_id = a.sql_id  
 18  INNER JOIN v$process p  
 19     ON s.paddr = p.addr  
 20  where sql_text is not null and rownum<2;

       SID    SERIAL# EVENT              SQL_TEXT                      SQL_FULLTEXT                  USERNAME  STATUS   MACHINE   TERMINAL  PROGRAM            EXECUTIONS SQL_ID        SPID                     DIRECT_WRITES
---------- ---------- ------------------ ----------------------------- ----------------------------- --------- -------- --------- --------- ------------------ ---------- ------------- ------------------------ -------------
         7         29 db file sequential DELETE FROM "SXMSPMAST" WHERE DELETE FROM "SXMSPMAST" WHERE SAPSR3    ACTIVE   uxgdc115            dw.sapNXP_DVEBMGS0         16 5zs82knjqrj2r 8716586                     ##########
                       read               "MANDT"=:A0 AND "MSGGUID"=:A  "MANDT"=:A0 AND "MSGGUID"=:A                                        0@uxgdc115 (TNS V1
                                         1 AND "PID"=:A2               1 AND "PID"=:A2                                                      -V3)


SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

SQL> set linesize 600

SQL> col event for a18

SQL> col sql_text for a29

SQL> col sql_fulltext for a29

col username for a9

SQL> SQL> col machine for a9

SQL> col program for a18

SQL> col direct_writes for a18

col terminal for a9

SQL> SQL> SELECT s.sid,

s.serial#,

2 3 s.event,

4 a.sql_text,

5 a.sql_fulltext,

6 s.username,

7 s.status,

8 s.machine,

9 s.terminal,

10 s.program,

11 a.executions,

12 s.sql_id,

13 p.spid,

14 a.direct_writes

15 FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s

16 LEFT JOIN v$sqlarea a

17 ON s.sql_id = a.sql_id

18 INNER JOIN v$process p

19 ON s.paddr = p.addr

20 where sql_text is not null and rownum<2;

SID SERIAL# EVENT SQL_TEXT SQL_FULLTEXT USERNAME STATUS MACHINE TERMINAL PROGRAM EXECUTIONS SQL_ID SPID DIRECT_WRITES

---------- ---------- ------------------ ----------------------------- ----------------------------- --------- -------- --------- --------- ------------------ ---------- ------------- ------------------------ -------------

7 29 db file sequential DELETE FROM "SXMSPMAST" WHERE DELETE FROM "SXMSPMAST" WHERE SAPSR3 ACTIVE uxgdc115 dw.sapNXP_DVEBMGS0 16 5zs82knjqrj2r 8716586 ##########

read "MANDT"=:A0 AND "MSGGUID"=:A "MANDT"=:A0 AND "MSGGUID"=:A 0@uxgdc115 (TNS V1

1 AND "PID"=:A2 1 AND "PID"=:A2 -V3)

SQL>

——————
Done。

SQL：当前会话

SQL：

SET LINESIZE 400
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
col machine for a14
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.machine
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SET LINESIZE 400

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

col machine for a14

SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program,

s.machine

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

Like this：

SQL> SET LINESIZE 400
SQL> COLUMN spid FORMAT A10
SQL> COLUMN username FORMAT A10
SQL> COLUMN program FORMAT A45
SQL> col machine for a14
SQL> SELECT s.inst_id,
  2         s.sid,
  3         s.serial#,
  4         p.spid,
  5         s.username,
  6         s.program,
  7         s.machine
  8  FROM   gv$session s
  9         JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'; 10  

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM                                       MACHINE
---------- ---------- ---------- ---------- ---------- --------------------------------------------- --------------
         1       1156         15 9629       SYS        sqlplus@DC15VM-032 (TNS V1-V3)                DC******2
         1        582         49 10247      VPXADMIN   sqlplus@DC15VM-032 (TNS V1-V3)                DC******2

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

SQL> SET LINESIZE 400

SQL> COLUMN spid FORMAT A10

SQL> COLUMN username FORMAT A10

SQL> COLUMN program FORMAT A45

SQL> col machine for a14

SQL> SELECT s.inst_id,

2 s.sid,

3 s.serial#,

4 p.spid,

5 s.username,

6 s.program,

7 s.machine

8 FROM gv$session s

9 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND'; 10

INST_ID SID SERIAL# SPID USERNAME PROGRAM MACHINE

---------- ---------- ---------- ---------- ---------- --------------------------------------------- --------------

1 1156 15 9629 SYS sqlplus@DC15VM-032 (TNS V1-V3) DC******2

1 582 49 10247 VPXADMIN sqlplus@DC15VM-032 (TNS V1-V3) DC******2

SQL>

————————————————
Done。

发表于： 2017年8月20日 2017年8月20日
作者： Adamhuan
分类： SQL
标签： kill session, Oracle, Program, SQL, 会话, 用户
发表评论: SQL：当前会话