[Oracle] 关于oracle-rac环境下sequence参数cache、nocache、order、noorder的实验。


1、默认值

1
2
cache 20
noorder

2、默认情况下

1
create sequence t0_seq start with 1 maxvalue 100;

rac1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
16:46:40 SCOTT@ testcadb1> select t0_seq.nextval from dual;
   NEXTVAL
----------
	 1
Elapsed: 00:00:00.02
16:46:41 SCOTT@ testcadb1> select t0_seq.nextval from dual;
   NEXTVAL
----------
	 2
Elapsed: 00:00:00.00
16:46:47 SCOTT@ testcadb1> select t0_seq.nextval from dual;
   NEXTVAL
----------
	 3

rac2：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
16:47:01 SCOTT@ testcadb2> select t0_seq.nextval from dual;
   NEXTVAL
----------
	21
Elapsed: 00:00:00.14
16:47:02 SCOTT@ testcadb2> select t0_seq.nextval from dual;
   NEXTVAL
----------
	22
Elapsed: 00:00:00.01
16:47:04 SCOTT@ testcadb2> select t0_seq.nextval from dual;
   NEXTVAL
----------
	23

3、指定cache大小

1
create sequence t1_seq start with 1 maxvalue 100 cache 10;

rac1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
16:39:03 SCOTT@ testcadb1> select t1_seq.nextval from dual;
   NEXTVAL
----------
	 1
Elapsed: 00:00:00.09
16:40:10 SCOTT@ testcadb1> select t1_seq.nextval from dual;
   NEXTVAL
----------
	 2
Elapsed: 00:00:00.00
16:40:16 SCOTT@ testcadb1> select t1_seq.nextval from dual;
   NEXTVAL
----------
	 3

rac2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
16:39:56 SCOTT@ testcadb2> select t1_seq.nextval from dual;
   NEXTVAL
----------
	11
Elapsed: 00:00:00.16
16:39:57 SCOTT@ testcadb2> select t1_seq.nextval from dual;
   NEXTVAL
----------
	12
Elapsed: 00:00:00.01
16:40:18 SCOTT@ testcadb2> select t1_seq.nextval from dual;
   NEXTVAL
----------
	13

3、指定cache和order

1
create sequence t2_seq start with 1 maxvalue 100 cache 15 order;

rac1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
17:07:16 SCOTT@ testcadb1> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 1
Elapsed: 00:00:00.03
17:07:31 SCOTT@ testcadb1> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 2
Elapsed: 00:00:00.01
17:07:33 SCOTT@ testcadb1> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 3

rac2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
17:07:37 SCOTT@ testcadb2> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 4
Elapsed: 00:00:00.03
17:07:38 SCOTT@ testcadb2> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 5
Elapsed: 00:00:00.00
17:07:40 SCOTT@ testcadb2> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 6

rac1:

1
2
3
4
5
6
7
8
9
17:07:33 SCOTT@ testcadb1> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 7
Elapsed: 00:00:00.00
17:08:19 SCOTT@ testcadb1> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 8

rac2:

1
2
3
4
5
6
7
8
9
17:07:40 SCOTT@ testcadb2> select t2_seq.nextval from dual;
   NEXTVAL
----------
	 9
Elapsed: 00:00:00.01
17:08:39 SCOTT@ testcadb2> select t2_seq.nextval from dual;
   NEXTVAL
----------
	10

4、指定nocache和noorder

1
create sequence t3_seq start with 1 maxvalue 100 nocache  noorder;

rac1:

1
2
3
4
5
6
7
8
9
10
17:10:30 SCOTT@ testcadb1> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 1
Elapsed: 00:00:00.07
17:10:45 SCOTT@ testcadb1> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 2
Elapsed: 00:00:00.02

rac2:

1
2
3
4
5
6
7
8
9
10
17:10:55 SCOTT@ testcadb2> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 3
Elapsed: 00:00:00.08
17:10:57 SCOTT@ testcadb2> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 4
Elapsed: 00:00:00.01

rac1:

1
2
3
4
17:10:47 SCOTT@ testcadb1> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 5

rac2:

1
2
3
4
5
6
7
8
9
17:10:58 SCOTT@ testcadb2> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 6
Elapsed: 00:00:00.02
17:11:02 SCOTT@ testcadb2> select t3_seq.nextval from dual;
   NEXTVAL
----------
	 7

5、指定nocache和order

1
create sequence t4_seq start with 1 maxvalue 100 nocache  order;

rac1：

1
2
3
4
5
6
7
8
9
17:13:04 SCOTT@ testcadb1> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 1
Elapsed: 00:00:00.01
17:13:04 SCOTT@ testcadb1> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 2

rac2：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
17:13:12 SCOTT@ testcadb2> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 3
Elapsed: 00:00:00.05
17:13:13 SCOTT@ testcadb2> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 4
Elapsed: 00:00:00.03
17:13:15 SCOTT@ testcadb2> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 5

rac1：

1
2
3
4
5
6
7
8
9
17:13:05 SCOTT@ testcadb1> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 6
Elapsed: 00:00:00.02
17:13:18 SCOTT@ testcadb1> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 7

rac2：

1
2
3
4
5
6
7
8
9
17:13:15 SCOTT@ testcadb2> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 8
Elapsed: 00:00:00.02
17:13:21 SCOTT@ testcadb2> select t4_seq.nextval from dual;
   NEXTVAL
----------
	 9

6、关于cache和nocache在并发比较大的情况下的取值效率，参考链接

1
2
3
4
cache和nocache参数取值效率对比
RAC两个会话分别处于不同node同时并发循环间断去取4万个值  ：            
nocache：           　　  2100s
cache ＝1000：　　       55s

7、结论

根据上面实验可以看到，在rac环境下，使用cache noorder，各个节点缓存的序列是不同的，因此获取到的序列值也不同。使用cache order方式创建的序列，多个实例缓存的相同的序列，很容易存在序列资源争用的问题，因此性能会比noorder 差很多。在非严格要求下建议不要使用order。更要避免使用nocache noorder。
在RAC情况下，使用cache可以尽量调大一点值（默认为20），使用频繁的序列Cache值增加到10000或者更高到50000。

oracle

点击阅读

[Oracle] oracle基于rowid删除重复行


1、以empno为唯一值

1
2
select min(rowid) from empbak group by empno;
delete from empbak where rowid not in (select min(rowid) from empbak group by empno);

2、以empno、ename等多列为唯一值

1
2
select min(rowid) from empbak group by empno,ename;
delete from empbak where rowid not in (select min(rowid) from empbak group by empno,ename);

3、使用over分析函数，以empno、ename分组来作为唯一值，按照empno的大小排序，获取排序完之后的row_number()行值

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
select empno,ename,rowid,row_number() over(partition by empno,ename order by empno) as number1 from empbak;
     EMPNO ENAME      ROWID		    NUMBER1
---------- ---------- ------------------ ----------
      7369 SMITH      AAAVuKAAEAAAADdAAA	  1
      7369 SMITH      AAAVuKAAEAAAADbAAA	  2
      7369 test       AAAVuKAAEAAAADdAAL	  1
      7369 test       AAAVuKAAEAAAADdAAO	  2
      7499 ALLEN      AAAVuKAAEAAAADdAAP	  1
      7499 ALLEN      AAAVuKAAEAAAADdAAM	  2
      7521 WARD       AAAVuKAAEAAAADbAAC	  1
      7521 WARD       AAAVuKAAEAAAADdAAB	  2
      7566 JONES      AAAVuKAAEAAAADdAAN	  1
      7566 JONES      AAAVuKAAEAAAADdAAR	  2
      7654 MARTIN     AAAVuKAAEAAAADdAAC	  1
      7654 MARTIN     AAAVuKAAEAAAADbAAE	  2
      7698 BLAKE      AAAVuKAAEAAAADbAAF	  1
      7698 BLAKE      AAAVuKAAEAAAADdAAD	  2
      7782 CLARK      AAAVuKAAEAAAADdAAE	  1
      7782 CLARK      AAAVuKAAEAAAADbAAG	  2
      7788 SCOTT      AAAVuKAAEAAAADbAAH	  1
      7788 SCOTT      AAAVuKAAEAAAADdAAF	  2
      7839 KING       AAAVuKAAEAAAADdAAG	  1
      7839 KING       AAAVuKAAEAAAADbAAI	  2
      7844 TURNER     AAAVuKAAEAAAADdAAH	  1
      7844 TURNER     AAAVuKAAEAAAADbAAJ	  2
      7876 ADAMS      AAAVuKAAEAAAADdAAI	  1
      7876 ADAMS      AAAVuKAAEAAAADbAAK	  2
      7900 JAMES      AAAVuKAAEAAAADdAAJ	  1
      7900 JAMES      AAAVuKAAEAAAADbAAL	  2
      7902 FORD       AAAVuKAAEAAAADbAAM	  1
      7902 FORD       AAAVuKAAEAAAADdAAK	  2
      7934 MILLER     AAAVuKAAEAAAADdAAb	  1
      7934 MILLER     AAAVuKAAEAAAADdAAc	  2

然后根据获取到的row_number()的值，将大于1的其他重复值删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
04:27:50 SCOTT@ boston> select empno,ename,rowid from (select empno,ename,rowid,row_number() over(partition by empno,ename order by empno) as number1 from empbak) where number1 > 1;
     EMPNO ENAME      ROWID
---------- ---------- ------------------
      7369 SMITH      AAAVuKAAEAAAADbAAA
      7369 test       AAAVuKAAEAAAADdAAO
      7499 ALLEN      AAAVuKAAEAAAADdAAM
      7521 WARD       AAAVuKAAEAAAADdAAB
      7566 JONES      AAAVuKAAEAAAADdAAR
      7654 MARTIN     AAAVuKAAEAAAADbAAE
      7698 BLAKE      AAAVuKAAEAAAADdAAD
      7782 CLARK      AAAVuKAAEAAAADbAAG
      7788 SCOTT      AAAVuKAAEAAAADdAAF
      7839 KING       AAAVuKAAEAAAADbAAI
      7844 TURNER     AAAVuKAAEAAAADbAAJ
      7876 ADAMS      AAAVuKAAEAAAADbAAK
      7900 JAMES      AAAVuKAAEAAAADbAAL
      7902 FORD       AAAVuKAAEAAAADdAAK
      7934 MILLER     AAAVuKAAEAAAADdAAc
15 rows selected.

根据获取到的rowid删除重复值

1
delete from empbak where rowid in (select rowid from (select empno,ename,rowid,row_number() over(partition by empno,ename order by empno) as number1 from empbak) where number1 > 1);
oracle

点击阅读

[Oracle] oracle修改SID、DB_NAME，由默认的orcl改为chicago


1、查看sid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@dax-mysql-slave dbs]$ sql
20:06:01 SYS@ orcl> show parameter name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name		     string
db_file_name_convert		     string
db_name 			             string	 orcl
db_unique_name			         string	 orcl
global_names			         boolean	 FALSE
instance_name			         string	 orcl
lock_name_space 		         string
log_file_name_convert		     string
processor_group_name		     string
service_names			         string	 orcl

2、关闭数据库

1
2
3
4
5
20:06:12 SYS@ orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
20:06:24 SYS@ orcl> exit

3、修改相关配置配置文件

编辑/etc/oratab将orcl替换为chicago

1
2
3
[oracle@dax-mysql-slave dbs]$ vim /etc/oratab 
[oracle@dax-mysql-slave dbs]$ more /etc/oratab 
chicago:/data/u01/app/oracle/product/11.2.0.4/dbhome_1:N

修改dbs目录将orcl相关的配置文件文件名替换为chicago

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[oracle@dax-mysql-slave dbs]$ cd $ORACLE_HOME/dbs
[oracle@dax-mysql-slave dbs]$ ll *orcl*
-rw-rw----. 1 oracle oinstall 1544 Mar 16 20:06 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall 1177 Mar 16 19:48 initorcl.ora
-rw-r-----. 1 oracle oinstall 1536 Mar 11 02:15 orapworcl
-rw-r-----. 1 oracle oinstall 2560 Mar 16 19:50 spfileorcl.ora
[oracle@dax-mysql-slave dbs]$ mv hc_orcl.dat hc_chicago.dat
[oracle@dax-mysql-slave dbs]$ mv initorcl.ora initchicago.ora
[oracle@dax-mysql-slave dbs]$ mv orapworcl orapwchicago
[oracle@dax-mysql-slave dbs]$ mv spfileorcl.ora spfilechicago.ora
[oracle@dax-mysql-slave dbs]$ ll *orcl*
ls: cannot access *orcl*: No such file or directory
[oracle@dax-mysql-slave dbs]$ ll *chicago*
-rw-rw----. 1 oracle oinstall 1544 Mar 16 20:06 hc_chicago.dat
-rw-r--r--. 1 oracle oinstall 1177 Mar 16 19:48 initchicago.ora
-rw-r-----. 1 oracle oinstall 1536 Mar 11 02:15 orapwchicago
-rw-r-----. 1 oracle oinstall 2560 Mar 16 19:50 spfilechicago.ora

4、重新启动数据库

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
[oracle@dax-mysql-slave dbs]$ export ORACLE_SID=chicago
[oracle@dax-mysql-slave dbs]$ sql

Connected to an idle instance.
20:09:10 SYS@ chicago> startup
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size		    2255712 bytes
Variable Size		  620758176 bytes
Database Buffers	 1811939328 bytes
Redo Buffers		   20275200 bytes
Database mounted.
Database opened.
20:09:28 SYS@ chicago> show parameter name;
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name		     string
db_file_name_convert		     string
db_name 			             string	 orcl
db_unique_name			         string	 orcl
global_names			         boolean	 FALSE
instance_name			         string	 chicago
lock_name_space 		         string
log_file_name_convert		     string
processor_group_name		     string
service_names			         string	 orcl
20:09:39 SYS@ chicago> exit

至此sid已从orcl更改为chicago，下面开始修改db_name

5、备份控制文件，并关闭数据库

1
2
3
4
5
6
7
8
9
[oracle@dax-mysql-slave dbs]$ sql
20:17:36 SYS@ chicago> alter database backup controlfile to trace resetlogs;  
Database altered.
Elapsed: 00:00:00.05
20:17:45 SYS@ chicago> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
20:18:03 SYS@ chicago> exit

6、根据alert_.log日志，查看备份控制文件的路径，并复制到其他目录

1
2
3
4
[oracle@dax-mysql-slave dbs]$ cd /data/u01/app/oracle/diag/rdbms/orcl/chicago/trace/
[oracle@dax-mysql-slave trace]$ cat alert_chicago.log |grep -E "Backup controlfile written"
Backup controlfile written to trace file /data/u01/app/oracle/diag/rdbms/orcl/chicago/trace/chicago_ora_14486.trc
[oracle@dax-mysql-slave trace]$ cp /data/u01/app/oracle/diag/rdbms/orcl/chicago/trace/chicago_ora_14486.trc /tmp/chicago.sql

7、修改复制出来的控制文件

未修改之前的文件内容

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
Trace file /data/u01/app/oracle/diag/rdbms/orcl/chicago/trace/chicago_ora_14486.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /data/u01/app/oracle/product/11.2.0.4/dbhome_1
System name:	Linux
Node name:	dax-mysql-slave
Release:	3.10.0-327.el7.x86_64
Version:	#1 SMP Thu Nov 19 22:10:57 UTC 2015
Machine:	x86_64
Instance name: chicago
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 14486, image: oracle@dax-mysql-slave (TNS V1-V3)


*** 2019-03-16 20:17:45.310
*** SESSION ID:(131.5) 2019-03-16 20:17:45.310
*** CLIENT ID:() 2019-03-16 20:17:45.310
*** SERVICE NAME:(SYS$USERS) 2019-03-16 20:17:45.310
*** MODULE NAME:(sqlplus@dax-mysql-slave (TNS V1-V3)) 2019-03-16 20:17:45.310
*** ACTION NAME:() 2019-03-16 20:17:45.310
 
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/data/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/data/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/data/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/u01/app/oracle/oradata/orcl/system01.dbf',
  '/data/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/data/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/data/u01/app/oracle/oradata/orcl/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/data/u01/app/oracle/product/11.2.0.4/dbhome_1
/dbs/arch1_1_1002593622.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/data/u01/app/oracle/product/11.2.0.4/dbhome_1
/dbs/arch1_1_1003088967.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/u01/app/oracle/oradata/orcl/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

*** 2019-03-16 20:17:54.765
Stopping background process SMCO

*** 2019-03-16 20:17:55.918
Stopping background process QMNC

*** 2019-03-16 20:17:56.918
Stopping background process MMNL

*** 2019-03-16 20:17:57.918
Stopping background process MMON

*** 2019-03-16 20:17:58.939
ksukia: Starting kill, flags = 1
ksukia: killed 0 out of 0 processes.

*** 2019-03-16 20:18:00.031
*** 2019-03-16 20:18:00.031 4329 krsh.c
ARCH: Archival disabled due to shutdown: 1089

*** 2019-03-16 20:18:01.032
*** 2019-03-16 20:18:01.032 4329 krsh.c
ARCH: Archival disabled due to shutdown: 1089

修改操作过程：

1
2
3
4
5
6
7
STARTUP NOMOUNT该行上面的内容全部删除
以--开头的内容全部删除；（vim 方法 :%s/^--.*//g)
***下的记录进程动态的内容全部删除
CREATE CONTROLFILE REUSE DATABASE替换为CREATE CONTROLFILE SET DATABASE
RECOVER DATABASE USING BACKUP CONTROLFILE加上注释--RECOVER DATABASE USING BACKUP CONTROLFILE（也可删除）
ORCL替换为CHICAGO (vim 方法 :%s/ORCL/CHICAGO/g)
orcl替换为chicago (vim 方法 :%s/orcl/chicago/g)

修改之后

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
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CHICAGO" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/data/u01/app/oracle/oradata/chicago/redo01.log'  SIZE 50M BLOCKSIZE 51
2,
  GROUP 2 '/data/u01/app/oracle/oradata/chicago/redo02.log'  SIZE 50M BLOCKSIZE 51
2,
  GROUP 3 '/data/u01/app/oracle/oradata/chicago/redo03.log'  SIZE 50M BLOCKSIZE 51
2
DATAFILE
  '/data/u01/app/oracle/oradata/chicago/system01.dbf',
  '/data/u01/app/oracle/oradata/chicago/sysaux01.dbf',
  '/data/u01/app/oracle/oradata/chicago/undotbs01.dbf',
  '/data/u01/app/oracle/oradata/chicago/users01.dbf'
CHARACTER SET ZHS16GBK
;
--RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/data/u01/app/oracle/oradata/chicago/temp01.db
f' SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

8、创建pfile文件，根据pfile修改对应的数据目录，并修改pfile文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[oracle@dax-mysql-slave trace]$ sql
Connected to an idle instance.
22:23:19 SYS@ chicago> create pfile='?/dbs/initchicago.ora' from spfile;
File created.
Elapsed: 00:00:00.01
22:23:43 SYS@ chicago> exit
Disconnected

[oracle@dax-mysql-slave dbs]$ cat initchicago.ora |grep orcl
orcl.__db_cache_size=1879048192
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/data/u01/app/oracle','/data/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=822083584
orcl.__sga_target=2466250752
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=0
*.audit_file_dest='/data/u01/app/oracle/admin/orcl/adump'
*.control_files='/data/u01/app/oracle/oradata/orcl/control01.ctl','/data/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

修改audit_file_dest路径

1
2
[oracle@dax-mysql-slave dbs]$ cd /data/u01/app/oracle/admin/
[oracle@dax-mysql-slave admin]$ mv orcl/ chicago/

修改数据文件路径

1
2
[oracle@dax-mysql-slave admin]$ cd /data/u01/app/oracle/oradata/
[oracle@dax-mysql-slave oradata]$ mv orcl/ chicago/

修改日志文件路径

1
2
3
4
[oracle@dax-mysql-slave oradata]$ cd /data/u01/app/oracle/diag/rdbms/
[oracle@dax-mysql-slave rdbms]$ mv orcl/ chicago/
```       
根据pfile文件提示的control_files文件路径，将对应路径下的control_files删除

[oracle@dax-mysql-slave rdbms]$ cd /data/u01/app/oracle/oradata/chicago/
[oracle@dax-mysql-slave chicago]$ mv control01.ctl /tmp
[oracle@dax-mysql-slave chicago]$ mv control02.ctl /tmp

1
修改pfile文件，将文本内容orcl全部替换为chicago

[oracle@dax-mysql-slave chicago]$ more $ORACLE_HOME/dbs/initchicago.ora
chicago.db_cache_size=1879048192
chicago.java_pool_size=16777216
chicago.large_pool_size=33554432
chicago.oracle_base=’/data/u01/app/oracle’,’/data/u01/app/oracle’#ORACLE_BASE se
t from environment
chicago.pga_aggregate_target=822083584
chicago.sga_target=2466250752
chicago.shared_io_pool_size=0
chicago.shared_pool_size=503316480
chicago.__streams_pool_size=0
*.audit_file_dest=’/data/u01/app/oracle/admin/chicago/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0’
*.control_files=’/data/u01/app/oracle/oradata/chicago/control01.ctl’,’/data/u01/ap
p/oracle/oradata/chicago/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=’chicago’
*.diagnostic_dest=’/data/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=chicagoXDB)’
*.open_cursors=300
*.pga_aggregate_target=819986432
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2462056448
*.undo_tablespace=’UNDOTBS1’

1
2
#### 9、进入实例，恢复控制文件
创建spfile文件

[oracle@dax-mysql-slave chicago]$ sql
Connected to an idle instance.
22:33:42 SYS@ chicago> create spfile from pfile=’?/dbs/initchicago.ora’ ;
File created.

1
执行编辑完成之后的控制文件脚本

22:36:05 SYS@ chicago> @/tmp/chicagolast.sql
Control file created.
Elapsed: 00:00:03.50
Database altered.
Elapsed: 00:00:07.48
Tablespace altered.
Elapsed: 00:00:00.94

1
数据库启动，查看相关参数

22:36:26 SYS@ chicago> show parameter name;
NAME TYPE VALUE

cell_offloadgroup_name string
db_file_name_convert string
db_name string chicago
db_unique_name string chicago
global_names boolean FALSE
instance_name string chicago
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string chicago

22:36:32 SYS@ chicago> select instance_name,status from v$instance;
INSTANCE_NAME STATUS

chicago OPEN

22:36:52 SYS@ chicago> select name,open_mode from v$database;
NAME OPEN_MODE

CHICAGO READ WRITE

22:37:56 SCOTT@ chicago> exit

```
至此数据库的dbname也修改完成。

oracle

点击阅读

[Oracle] oracle expdp导出报错ORA-39213


1、使用expdp导出

1
2
3
4
5
6
7
8
[oracle@rac1 ~]$ expdp \"/ as sysdba\" directory=exp_data_dir full=y dumpfile=oibs_full.dmp cluster=n parallel=4 logfile=expdp.log exclude=STATISTICS
Export: Release 11.2.0.4.0 - Production on Wed Mar 13 16:14:01 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

2、查看报错信息

1
2
3
4
5
6
[oracle@rac1 ~]$ oerr ora 39213
39213, 00000, "Metadata processing is not available"
// *Cause:  The Data Pump could not use the Metadata API.  Typically,
//          this is caused by the XSL stylesheets not being set up properly.
// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
//          to reload the stylesheets.

3、根据提示执行dbms_metadata_util.load_stylesheets命令

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 13 16:15:27 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

16:15:27 SYS@ oibs11> execute dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.93
oracle

点击阅读

[Linux] Access gitlab return 422 error


1、浏览器访问gitlab，web界面直接返回The change you wanted was rejected或者查看/var/log/gitlab/gitlab-rails下面的production.log提示Can’t verify CSRF token authenticity Completed 422 Unprocessable：



出现该问题，一般都是前端nginx的代理报头配置出现了问题，下面是分别在https和http不同情况下的解决方法

2、如果在/etc/gitlab/gitlab.rb下的参数external_url “https://***”配置有https，访问遇到422错误,修改/etc/gitlab/gitlab.rb下nginx配置，添加以下配置信息：

1
2
3
4
nginx['proxy_set_headers'] = {
  "X-Forwarded-Proto" => "https",
  "X-Forwarded-Ssl" => "on"
 }

之后重新编译gitlab,命令如下：

1
gitlab-ctl reconfigure

3、如果在/etc/gitlab/gitlab.rb下的参数external_url “http://***”配置有http，访问遇到422错误，修改/etc/gitlab/gitlab.rb下nginx配置，添加以下配置信息：

1
2
3
4
nginx['proxy_set_headers'] = {
  "X-Forwarded-Proto" => "http",
  "CUSTOM_HEADER" => "VALUE"
 }

之后重新编译gitlab,命令如下：

1
gitlab-ctl reconfigure

4、参考链接

gitlab关于nginx配置的官网链接

linux

点击阅读

[Oracle] oracle查询乱码、导入sql脚本乱码问题


1、查看当前数据库字符集

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
02:30:58 SYS@ chicago> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.4.0

20 rows selected.

2、查看当前连接终端字符编码是否为gbk，如果不是gbk需要修改，以securecrt为例：

3、如果都是gbk，查询出来的仍为乱码，再次查看环境变量NLS_LANG

1
2
[oracle@dax-mysql-slave ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK

如果为空，或者与数据库字符集不匹配，修改为与数据库相同的字符集

4、 在linux系统LANG环境变量为en_US.UTF-8，其他以上参数都为gbk的情况下，查看带有中文的文本文件会是乱码：


这种情况下在sqlplus执行sql脚本，会报错，

5、要执行脚本，需要修改LANG变量，将该传输修改为gbk

6、重新编辑文本文件

7、在sqlplus下再次执行脚本



脚本执行成功。

oracle

点击阅读

[Linux] 记一次服务器被植入watchbog（并不是linux自带的watchdog进程）挖坑病毒的处理过程


1、服务器收到cpu报警，cpu被占用达到100%，登录服务器查看，发现cpu被一个watchbog的进程占满了，如下图所示


该进程为被植入的挖矿程序，该程序会在cron下面写入脚本，定期去pastebin.com下载木马开始挖矿，如果删除不彻底仍然会不定期启动这个挖矿程序。

2、下面开始删除这个病毒木马，防火墙配置，禁用这个域名对应ip的出入限制：

1
2
3
iptables -A INPUT -s 104.20.209.21 -j DROP
iptables -A OUTPUT -s 104.20.209.21 -j DROP
iptables -A OUTPUT -j DROP -d 104.20.209.21

保存修改内容

1
/sbin/service iptables save

为安全起见，同时修改本地hosts文件添加，将pastebin.com重定向到本地：
cat /etc/hosts|grep pastebin

1
127.0.0.1 pastebin.com

3、移除wget、curl脚本里面会使用curl和wget等命令下载病毒(如果确认病毒可以被彻底删除，这不无需操作)

1
2
mv /usr/bin/curl /usr/bin/lruc
mv /usr/bin/wget /usr/bin/tegw

4、杀掉watchbog进程

1
ps -ef|grep watchbog|awk '{print $2}'|xargs kill -9

5、删掉cron里面的相关任务

1
2
3
4
5
6
7
8
crontab -l
/etc/cron.d     
/etc/cron.deny    
/etc/cron.monthly  
/etc/cron.daily  
/etc/cron.hourly  
/etc/crontab       
/etc/cron.weekly

上面所有的文档一定要都仔细检查一边，有相关curl pastebin.com等信息要彻底删除

6、在cron的文件下还发现的其他脚本命令，/bin/httpntp、/bin/ftpsdns等，同时删除watchbog

1
2
3
rm -rf /usr/bin/watchbog
rm -rf /bin/httpntp
rm -rf /bin/ftpsdns

7、如果/tmp目录下存在文件/tmp/timesyncc.service，也要删除

8、参考链接

linux

点击阅读

[Oracle] oracle ORA-00020: maximum number of processes (150) exceeded


1、报错提示oracle的会话连接数达到上限，解决方法如下

2、发生报错之后，使用sqlplus / as sysdba也不能登录，oracle不像mysql一样专门为超级管理员用户保留一个连接会话，oracle只能通过杀掉session连接才能再次登录。

2.1、批量杀掉会话连接：
1
ps -ef|grep oracle|grep LOCAL|grep -v grep|awk '{print $2}'|xargs kill -9

如果当前系统下存在多个实例(可以手动指定${ORACLE_SID}，不指定会使用当前环境变量默认下的值)

1
ps -ef|grep oracle${ORACLE_SID}|grep LOCAL|grep -v grep|awk '{print $2}'|xargs kill -9
2.2、单独杀掉会话连接
1
2
ps -ef|grep oracle${ORACLE_SID}|grep LOCAL|awk '{print $2}'
kill -9 ***

3、杀掉会话连接后，等待一段时间再次sqlplus / as sysdba登录显示正常。

登录之后修改process进程数

1
alter system set processes=500 scope=spfile;

修改完该参数之后要重新启动db，才能生效,该参数不支持动态修改。

oracle

点击阅读

[Linux] nginx日志分析工具goaccess


0、yum安装依赖包

1
2
3
yum install GeoIP-devel gcc ncurses* glib2 glib2-devel zlib zlib-devel bzip2-devel -y
rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
yum -y install GeoIP-update

1、下载安装goaccess（需科学上网）

1
2
3
4
5
6
wget https://tar.goaccess.io/goaccess-1.3.tar.gz
tar -xzvf goaccess-1.3.tar.gz
cd goaccess-1.3/
./configure --enable-utf8 --enable-geoip=legacy
make
make install

2、修改配置文件（可根据自己需求配置），也可以在启动命令是单独指定：

more /etc/goaccess.conf

1
2
3
4
5
6
7
8
9
10
time-format %H:%M:%S
date-format %d/%b/%Y
log-format %h %^[%d:%t %^] "%r" %s %b "%R" "%u"

real-time-html true
ssl-cert <cert.crt>
ssl-key <priv.key>
ws-url wss://<your-domain>
port <port>
output /var/www/<xxx>/stat/index.html

3、安装tokyocabinet用于持久化数据（如果不需要持久化数据到磁盘可不必安装）

1
2
3
4
5
6
7
cd /data/soft
wget http://fallabs.com/tokyocabinet/tokyocabinet-1.4.48.tar.gz
tar -zxvf tokyocabinet-1.4.48.tar.gz
cd tokyocabinet-1.4.48
./configure --prefix=/usr/local --enable-off64 --enable-fastest
make
make install

将生成的模块加入/lib64下

1
ln -s /data/soft/tokyocabinet-1.4.48/libtokyocabinet.so.9 /lib64/

如果没有这个模块会，重新编译goaccess会报错

1
/usr/local/bin/goaccess: error while loading shared libraries: libtokyocabinet.so.9: cannot open shared object file: No such file or directory

4、安装完tokyocabinet，配置goaccess加入btree

1
2
3
./configure --enable-utf8 --enable-geoip=legacy --enable-tcb=btree
make
make install

5、增加打印日志功能（可选）

1
2
./configure --enable-utf8 --enable-geoip=legacy --enable-tcb=btree --enable-debug
make && make install

指定输出日志文件：

1
--debug-file=/tmp/goaccess.log

6、添加定时任务生成月度的日志分析表（月度报表）

添加到定时任务

1
2
cat /etc/crontab |grep sh
00 01 * * * root source /etc/profile && sh /root/test.sh

cat /root/test.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!bin/bash
#前一天的日期
prestamp=`date -d '1 day ago' +'%Y%m%d'`
#指定需要分析的前一天的日志的文件名称
prelogname=/usr/local/openresty/nginx/logs/go_access${prestamp}.log
#prelogname=/usr/local/openresty/nginx/logs/go_access.log
#每个月1号不生成
premonstamp=`echo ${prestamp:0:6}`
#月度报表名称
monreportname=/data/web/oldreport/report${premonstamp}.html
#持久化磁盘路径
disk_db_path=/tmp/goaccess${premonstamp}
#将前一天生成日志文件追加到月度持久化磁盘里，并生成新的报表
/usr/local/bin/goaccess ${prelogname} -o ${monreportname} --load-from-disk --db-path=${disk_db_path}  --keep-db-files --date-format='%d/%b/%Y' --log-format='%h %^[%d:%t %^] "%r" %s %b' --time-format='%H:%M:%S'

生成的报表样式如下：

7、每天凌晨定时生成新一天的报表，但是不建议持久化到磁盘，因为一旦中途进程挂掉，需要清空日志文件，如果不清空日志文件再次启动的话，会重复加载已经存在的数据。

cat /etc/crontab |grep sh

1
00 00 * * * root source /etc/profile && sh /root/test.sh

每天定时关闭goaccess并重新启动，将数据持久化到磁盘
cat /root/test.sh

1
2
3
4
5
6
#!/bin/bash
stamp=`date +'%Y%m%d'`
logname=/usr/local/openresty/nginx/logs/go_access.log
reportname=/data/web/report/report${stamp}.html
ps -ef|grep '/usr/local/bin/goaccess'|grep -v grep |awk  '{print $2}' |xargs kill
/usr/local/bin/goaccess ${logname} -o ${reportname} --log-format=COMBINED  --real-time-html --daemonize

8、生成前一天的历史报表(在日志文件每天定时切割的情况下，如果不按天切割将无法生成前一天的报表)

cat /etc/crontab |grep sh

1
00 01 * * * root source /etc/profile && sh /root/test.sh

cat /root/test.sh

1
2
3
4
5
6
#!/bin/bash
prestamp=`date -d '1 day ago' +'%Y%m%d'` 
prelogname=/usr/local/openresty/nginx/logs/go_access${prestamp}.log
#prelogname=/usr/local/openresty/nginx/logs/go_access.log
prereportname=/data/web/report/report${prestamp}.html
/usr/local/bin/goaccess ${prelogname} -o ${prereportname}

9、下面列举一些单独的命令生成自己需要的报表

实时报表

1
/usr/local/bin/goaccess /usr/local/openresty/nginx/logs/go_access.log -o /data/web/report/report20181204.html --log-format=COMBINED  --real-time-html  --daemonize

增量追加持久化数据，将日志加载到磁盘，不使用配置文件（命令行指定）

1
/usr/local/bin/goaccess /tmp/api_access.log --load-from-disk --db-path=/tmp/goaccess  --keep-db-files --debug-file=/tmp/goaccess.log --date-format='%d/%b/%Y' --log-format='%h %^[%d:%t %^] "%r" %s %b' --time-format='%H:%M:%S'

增量追加持久化数据，将日志加载到磁盘，同时输出到html表（添加-o 参数，指定输出html）

1
/usr/local/bin/goaccess /tmp/api_access.log -o /data/web/oldreport/oldreport20181205.html --load-from-disk --db-path=/tmp/goaccess  --keep-db-files --debug-file=/tmp/goaccess.log --date-format='%d/%b/%Y' --log-format='%h %^[%d:%t %^] "%r" %s %b' --time-format='%H:%M:%S'

添加到后台运行（因为没有–real-time-html使用–daemonize是不生效的）

1
/usr/local/bin/goaccess /tmp/api_access.log -o /data/web/oldreport/oldreport20181205.html --load-from-disk --db-path=/tmp/goaccess  --keep-db-files --debug-file=/tmp/goaccess.log --date-format='%d/%b/%Y' --log-format='%h %^[%d:%t %^] "%r" %s %b' --time-format='%H:%M:%S' &

对已经持久化的数据，使用-o参数生成报表

1
/usr/local/bin/goaccess -o /data/web/oldreport/oldreport20181205.html --log-format=COMBINED  --keep-db-files --db-path=/tmp/goaccess --load-from-disk

10、使用过程遇到的问题

1
2
3
4
1、在goaccess后台运行并且开启持久化的情况下，不能使用kill -9 pid方法，使用这种方法杀掉进程会毁坏数据，可以使用kill pid的方法
2、使用持久化功能，进程宕掉之后，重启进程需要清空日志文件中的数据，否则会把日志文件中的旧数据重复加载一遍。
3、--daemonize添加守护进程，只用--real-time-html可用时生效
4、使用real_time_html适时生效需要开启goaccess的端口默认为7890

11、goaccess html参数详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
General Statistics：此面板提供了几个指标的摘要，其中一些包括：有效和无效请求的数量，分析数据集所花费的时间，唯一访问者，请求的文件，静态文件（CSS，ICO，JPG等）HTTP引用，404s，已解析日志文件的大小和带宽消耗。
Unique visitors（每日独立访客 - 包括网络机器人）：此面板显示点击次数，唯一身份访问者和每个日期的累积带宽等指标。包含相同IP，相同日期和相同用户代理的HTTP请求被视为唯一访问者。默认情况下，它包括网络爬虫/蜘蛛。 可以使用--date-spec = hr将日期特异性设置为小时级别，这将显示日期，例如05 / Jun / 2016：16。如果您想跟踪小时级别的每日流量，这非常棒。
Requested files（请求的文件）：此面板显示Web服务器上请求最多的文件。它显示了匹配，唯一身份访问者和百分比，以及累积带宽，协议和使用的请求方法。
Requested static files（请求的文件）：列出了最常用静态文件，如：JPG，CSS，SWF，JS，GIF，和PNG文件类型，使用相同的指标作为最后的面板一起。可以将其他静态文件添加到配置文件中。
404 or Not Found（未找到的URLS）：显示与先前请求面板相同的指标，但是，其数据包含在服务器上找不到的所有页面，或通常称为404状态代码。
Hosts（未找到的URLS）：此面板包含有关主机本身的详细信息。这非常适合发现×××性爬虫，并确定谁在吃你的带宽。 扩展面板可以显示更多信息，例如主机的反向DNS查找结果，原产国和城市。如果-a启用了参数，则可以通过选择所需的IP地址，然后按Enter来显示用户代理列表。
Operating Systems（操作系统）：此面板将报告主机在到达服务器时使用的操作系统。它试图提供每个操作系统的最具体版本。
Browsers（操作系统）：此面板将报告主机在访问服务器时使用的浏览器。它试图提供每个浏览器的最具体版本。
Visit Times（时间分配）：此面板将显示每小时报告。此选项显示24个数据点，每天一小时一个。 可选地，可以使用--hour-spec = min将小时特异性设置为十分之一水平，这将显示小时为16：4如果您想要发现服务器上的流量峰值，这很好。
Virtual Hosts：此面板将显示从访问日志中解析的所有不同虚拟主机。如果在日志格式字符串中使用％v，则会显示此面板。
Referrers URLs：如果相关主机通过其他资源访问了网站，或者是从其他主机链接转移给您，则会在此面板中提供引用它们的网址。请参阅`--ignore-panel`配置文件以启用它。 （默认禁用）
Referring Sites（推荐网站）：此面板仅显示主机部分，但不显示整个URL。请求来自的URL。
Keyphrases（谷歌搜索关键字）：它报告了用于Google搜索，Google缓存和Google翻译的关键字，这些关键字已导致您的网络服务器。目前，它仅通过HTTP支持Google搜索查询。请参阅`--ignore-panel`配置文件以启用它。 （默认禁用）
Geo Location（地理位置）：确定IP地址在地理位置的位置。统计数据按大陆和国家分列。它需要使用GeoLocation支持进行编译。
HTTP Status Codes（HTTP状态码）：HTTP请求的数字状态代码的值。
Remote User (HTTP authentication)：这是HTTP身份验证确定的请求文档的人员的用户ID。如果文档没有密码保护，则此部分将为“ - ”，就像前一个部分一样。除非%e在log-format变量中给出，否则不会启用此面板。
--real-os
展示真实的操作系统。在Operation System模块中，是否展示更详细的操作系统信息。

12、goaccess与nginx日志格式日常转换表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
nginx               goaccess
$time_local         %d:%t %^
$host               %v
$http_host          %v
$remote_addr        %h
$request_time       %T
$request_method     %m
$request_uri        %U
$server_protocol    %H
$request            %r
$status             %s
$body_bytes_sent    %b
$bytes_sent         %b
$http_referer       %R
$http_user_agent    %u

如果找不到对应的格式可以使用“%^”–Ignore this field，来忽略那一部分

linux

点击阅读

[Oracle] oracle-DB_NAME、DB_UNIQUE_NAME、GLOBAL_NAME、INSTANCE_NAME、SERVICE_NAME详解


1、DB_NAME

DB_NAME:指定最多8个字符的数据库连接标识符。rac环境每个实例都要配置该值，且每个实例的值必须相同。如果存在从库，主库和从库的db_name要相同。

2、DB_UNIQUE_NAME

db_unique_name：数据库全局唯一名称。如果没有修改，默认情况下，数据库实例对应的名称为db_name。
oracle asm实例对应的名称为：+ASM。
rac各实例的对应的该值必须相同。
对于adg环境，主从具有相同的db_name，db_domain，但是必须保证db_unique_name不同，主从库有自己唯一的db_unique_name。最多设置30个字符。

3、GLOBAL_NAME

global_names：指定dblink是否需要和所要连接的远程库服务名称（service_names）一样，如果为false则不检查。
如果计划使用分布式处理，建议将该参数设为true，在一个网络环境下来确定数据库和连接的一致性命名规范。
该值为fasle|true影响的只是本机，远程库的global_names值为true|false对本机使用dblink的查询没有任何影响。
如果该值设置为true，dblink名称与service_name名称不一致则会报错ora-02085，实验如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SYS@ boston> alter system set global_names=true;
SYS@ boston> create database link oibs1 connect to test identified by oracle using 'oibs1';
Database link created.
SYS@ boston> select * from t1@oibs1;
	ID
----------
	 1
	 2
SYS@ boston> create database link oibs connect to test identified by oracle using 'oibs1';
Database link created.
Elapsed: 00:00:00.03
SYS@ boston> select * from t1@oibs;
select * from t1@oibs
             *
ERROR at line 1:
ORA-02085: database link OIBS connects to OIBS1

4、INSTANCE_NAME

instance_name：实例名，rac环境下，多个实例可以关联一个单独的数据服务。客户端可以通过指定一个特殊的实例来做负载均衡。
一个单实例的数据系统，实例名与数据库名相同。
tnsname.ora下的连接如果使用SID时要与该值相同。

5、SERVICE_NAMES

service_names：默认值为db_unique_name.db_domain。
在rac环境下不要通过sqlplus设置service_names参数，如果需要设置该参数，通过oracle EM和srvctl配置。
tnsname.ora下的连接如果使用SERVICE_NAME时要与该值相同。

5.1、默认db_domain值为空的情况下，测试修改service_names

因此service_names=db_unique_name，单实例手动设置service_names，检查service_names

1
2
3
4
18:50:14 SYS@ boston> show parameter service_names
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 boston

修改service_names

1
2
3
19:56:42 SYS@ boston> alter system set service_names='chicago.us.oracle';
System altered.
Elapsed: 00:00:00.23

查看service_names已修改

1
2
3
4
19:56:56 SYS@ boston> show parameter service_names
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 chicago.us.oracle

此时查看监听，新增了chicago.us.oracle的服务，原来boston的服务名并没有被覆盖掉：

1
2
3
4
5
6
7
8
9
10
11
[oracle@dax-mysql-slave admin]$ lsnrctl status
......
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dax-mysql-slave)(PORT=1521)))
Services Summary...
Service "boston" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "bostonXDB" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "chicago.us.oracle" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
The command completed successfully

再次修改service_names

1
2
3
19:56:59 SYS@ boston> alter system set service_names='chicago.us.oracle.com';
System altered.
Elapsed: 00:00:00.12

查看监听

1
2
3
4
5
6
7
8
9
10
11
[oracle@dax-mysql-slave admin]$ lsnrctl status
......
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dax-mysql-slave)(PORT=1521)))
Services Summary...
Service "boston" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "bostonXDB" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "chicago.us.oracle.com" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
The command completed successfully

chicago.us.oracle的服务名被替代为chicago.us.oracle.com

5.2、在db_domain存在的情况下(如果没有，可以自己修改，修改该值需要重启库)

先修改db_domain值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
22:18:05 SYS@ boston> alter system set db_domain='us.oracle.cn' scope=spfile;
System altered.
22:18:22 SYS@ boston> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:18:38 SYS@ boston> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size		    2253664 bytes
Variable Size		  503319712 bytes
Database Buffers	 1090519040 bytes
Redo Buffers		    7319552 bytes
Database mounted.
Database opened.
22:19:36 SYS@ boston> show parameter db_domain
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_domain			     string	 us.oracle.cn

修改service_names，在不指定后缀的情况下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
22:45:57 SYS@ boston> alter system set service_names='chicago';
System altered.
Elapsed: 00:00:00.11
22:46:45 SYS@ boston> show parameter name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 chicago

[oracle@dax-mysql-slave admin]$ lsnrctl status
......
Services Summary...
Service "boston.us.oracle.cn" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "bostonXDB.us.oracle.cn" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "chicago.us.oracle.cn" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
The command completed successfully

监听的服务名自动添加了db_domain,服务名变为了chicago.us.oracle.cn
当修改的service_names自己添加了其他的domian，那么系统自带的db_domain就不会覆盖了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
22:46:49 SYS@ boston> alter system set service_names='chicago.cn.oracle.com';
System altered.
Elapsed: 00:00:00.15
22:48:40 SYS@ boston> show parameter name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 chicago.cn.oracle.com
[oracle@dax-mysql-slave admin]$ lsnrctl status
.....
Services Summary...
Service "boston.us.oracle.cn" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "bostonXDB.us.oracle.cn" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
Service "chicago.cn.oracle.com" has 1 instance(s).
  Instance "boston", status READY, has 1 handler(s) for this service...
The command completed successfully

监听的service_names为chicago.cn.oracle.com，而不是chicago.us.oracle.com

oracle

点击阅读

归档:

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog