主页 / user-92505

r0tt's questions

Martin Hope
r0tt
Asked: 2021-08-27 23:54:50 +0800 CST
  • 3

如何使用 pg_isready 监控特定数据库？

实例：DBSPG10T1 版本：10.14 端口：62​​41

postgres@postgres # SELECT datname FROM pg_database;
  datname
-----------
 postgres
 template1
 template0
 dbspg10t1

[postgres@HOSTNAME ~]$ if pg_isready -d postgres://localhost:6241/dbspg10t1; then echo "200 OK"; else echo "500 NOT OK"; fi
localhost:6241 - accepting connections
200 OK

数据库测试不存在，但 pg_isready 仍然返回 ok。

[postgres@HOSTNAME ~]$ if pg_isready -d postgres://localhost:6241/test; then echo "200 OK"; else echo "500 NOT OK"; fi
localhost:6241 - accepting connections
200 OK

这是语法问题吗？

postgresql monitoring
Martin Hope
r0tt
Asked: 2021-08-27 22:25:02 +0800 CST
  • 1

数据泵元数据仅在通过转换导入时更改数据长度：（以 WE8ISO8859P15 完成导出，以 AL32UTF8 字符集和 AL16UTF16 NCHAR 字符集完成导入）

示例表：Source-SYSTEM (WE8ISO8859P15)：

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_LENGTH from dba_tab_columns where TABLE_NAME='STRING';

OWNER TABLE_NAME COLUMN_NAME DATA_LENGTH
--------------- -------------------- -------------------- -----------
SCHEMA_NAME STRING DE 2000

目标系统（AL16UTF16）：

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_LENGTH from dba_tab_columns where TABLE_NAME='STRING';

OWNER TABLE_NAME COLUMN_NAME DATA_LENGTH
--------------- -------------------- -------------------- -----------
SCHEMA_NAME STRING DE 4000

varchar2(char 2000) 的数据长度已自动更改为 varchar2(char 4000)。这种行为的规则是什么？这是记录在案的行为吗？

编辑：这个问题似乎与数据泵无关，而是与从单字符到多字符集的转换有关：

源系统（WE8ISO8859P15）：

create table test(name varchar2(50), name2 varchar2(5 char), name3 clob);

INSERT INTO test VALUES('Susanne','Test','Hi This is Row one');

select owner,TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_LENGTH, data_type,char_used FROM ALL_TAB_COLUMNS where TABLE_NAME='TEST';
SCHEMA_NAME TEST    NAME    50  50  VARCHAR2    B
SCHEMA_NAME TEST    NAME2   5   5   VARCHAR2    C
SCHEMA_NAME TEST    NAME3   4000    0   CLOB

目标系统（AL16UTF16）：

create table test(name varchar2(50), name2 varchar2(5 char), name3 clob);

INSERT INTO test VALUES('Susanne','Test','Hi This is Row one');

select owner,TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_LENGTH, data_type,char_used FROM ALL_TAB_COLUMNS where TABLE_NAME='TEST';
SCHEMA_NAME TEST    NAME    50  50  VARCHAR2    B
SCHEMA_NAME TEST    NAME2   20  5   VARCHAR2    C
SCHEMA_NAME TEST    NAME3   4000    0   CLOB

注意：在旧数据库（字符集 = WE8ISO8859P15）中，对于所有具有 VARCHAR2 DATA_TYPE（独立于 CHAR_USED）的列，DATA_LENGTH=1*CHAR_LENGTH。

在新数据库（使用 UNICODE 字符集 AL32UTF8）中，对于所有具有 VARCHAR2 DATA_TYPE 和 CHAR_USED=C 的列，DATA_LENGTH=4*CHAR_LENGTH（或最大值 = 4000）。

oracle unicode
Martin Hope
r0tt
Asked: 2021-02-18 08:13:05 +0800 CST
  • 0

我需要添加一个额外的端口：1522

Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/xxxx/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))
Services Summary...
Service "xxxx" has 1 instance(s).

/u01/app/19.0.0.0/grid/network/admin/listener.ora->

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))))            # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                #

每次编辑 listener.ora 监听器启动失败：

我试过了：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1522))
    )
  )
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

srvctl start listener PRCR-1079：无法启动资源 ora.LISTENER.lsnr CRS-5016：由代理“ORAAGENT”为操作“start”生成的进程“/u01/app/19.0.0.0/grid/bin/lsnrctl”失败： “/u01/app/grid/diag/crs/xxxx/crs/trace/crsd_oraagent_grid.trc”中“(:CLSN00010:)”的详细信息

侦听器无法启动。请参阅上面的错误消息...

2021-02-17 15:32:19.462 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [开始] (:CLSN00010:)Utils:execCmd scls_process_join() uret 1

2021-02-17 15:32:19.462 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [开始] clsnUtils::error 异常类型 = 2 字符串 = CRS-5016: 进程 "/u01 /app/19.0.0.0/grid/bin/lsnrctl”由代理“ORAAGENT”生成，用于操作“start”失败：“/u01/app/grid/diag/crs/etabonxrdbs0010/”中“(:CLSN00010:)”的详细信息crs/trace/crsd_oraagent_grid.trc"

2021-02-17 15:32:19.463 : AGFW:2610939648: [ INFO] {1:35727:65428} 代理发送回复：RESOURCE_START[ora.LISTENER.lsnr etabonxrdbs0010 1] ID 4098:1355034 2021-02-17 15 :32:19.463 :CLSDYNAM:2613040896: [ora.LISTENER.lsnr]{1:35727:65428} [start] LsnrAgent::Lsnrctl::start lsnrctl start complete lsnrname:LISTENER output: LSNRCTL for Linux: Version 19.0.0.0. 0 - 2021 年 2 月 17 日 15:32:19 生产

版权所有 (c) 1991, 2020，甲骨文。版权所有。

...

2021-02-17 18:19:34.200 :CLSDYNAM:2606737152: [ora.LISTENER.lsnr]{1:35727:1260} [检查] clsnUtils::error 异常类型 = 2 字符串 = CRS-5020：并非所有端点都是为听众 LISTENER 注册

I tried: srvctl modify listener -p "TCP:1521/TCP:1522"; 

srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521, 1522
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
oracle oracle-asm
Martin Hope
r0tt
Asked: 2020-12-17 06:30:36 +0800 CST
  • 0

更改上述用户的密码时，会话挂起。这似乎是一个僵局。更改密码时，似乎产生了两个会话：

Blocking session
C##DVAM_ROOT
1 26 58821  Concurrency row cache lock
Waiting session:
C##DVAM_ROOT
0 405 45629 fuvf4b27452y5 Idle PX Deq: Execute Reply


fuvf4b27452y5 -> /* SQL Analyze(405,0) */
SELECT /*+PARALLEL(1) NO_STATEMENT_QUEUING */ *
FROM X$KXFTASK /*kpdbReplayDDL,PDB_Replay_DDL*/

当具有 dv_patch_admin 角色的用户更改密码时，它会起作用。禁用 Database Vault 时，它可以工作。我在 oracle 支持中没有找到这个。这是一个 Oracle Database 19.9 多租户。

oracle security
Martin Hope
r0tt
Asked: 2019-04-02 23:32:28 +0800 CST
  • 2

Oracle 12c 数据库有一个包含链式行的表：

select 
   owner              c1, 
   table_name         c2, 
   pct_free           c3, 
   pct_used           c4, 
   avg_row_len        c5, 
   num_rows           c6, 
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
   where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;
RESULT:
C1  C2  C3  C4  C5  C6  C7  C8
SCHEMA_XY   PERSON  10      662 98.930  85.872  1

我知道这个表有 85.872 % 链接的行。我试过了：

alter table SCHEMA_XY.PERSON move;

alter table SCHEMA_XY.PERSON move pctfre 30;

SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online ;'FROM DBA_INDEXES where TABLE_NAME='PERSON';

Chained Rows 仍然很高。该表有 250 列，主要是 varchar2 和数字。我该如何解决？

oracle oracle-12c
Martin Hope
r0tt
Asked: 2018-03-15 04:46:10 +0800 CST
  • 1

在 Oracle 12c 数据库上，我想重新组织 LOB 段。我曾经按照 sql 来生成 alter move 命令：

    select 'ALTER TABLE '||s.owner||'.'||l.table_name||' move to('||l.column_name||') store as (TABLESPACE NAME1);' 
      from dba_segments s, dba_lobs l
     where s.segment_name = l.segment_name
       and s.tablespace_name = 'NAME1'
       and segment_type='LOBSEGMENT'
       and partition_name is null;

我要重建的 lob 段约为 200Gb。我可以使用 lob 所在的同一个表空间吗？该表空间中的总可用空间需要为 200Gb？在移动期间吊球被锁定？

oracle blob
Martin Hope
r0tt
Asked: 2018-03-07 02:08:59 +0800 CST
  • 0

我们正在使用 TSM/TDPO SBT 来备份 Oracle 数据库。将 Oracle Data Guard 与 TDPO 结合使用时，确保备份在故障转移/切换后继续进行的最佳策略是什么？TSM/TDPO 使用一种称为数据移动的功能，该功能可确保将来自主数据库和备用数据库的备份移动到一个 TSM 节点。（TSM 节点 A（主）和 B（备用）将备份移动到 C。还原将使用 C）。TDPO 调用的 RMAN 脚本需要一个逻辑来防止从两者（主数据库和备用数据库）获取备份。这可以通过查询数据库的角色来完成。

oracle backup
Martin Hope
r0tt
Asked: 2017-12-08 06:08:46 +0800 CST
  • 10

我有一个大小为 90GB 的 Oracle 12c 表空间。近 40GB 是免费的。

TB1 54 %    90880   41383   49497   46  98304   50 %    ONLINE  PERMANENT

表空间有 3 个数据文件。

E:\ORACLE\ORADATA\xx\TB1.DBF    33554416    True    100 
F:\ORACLE\ORADATA\xx\TB1_2.DBF  33554416    True    100 
G:\ORACLE\ORADATA\xx\TB1_3.DBF  25344000    True    100

如何缩小表空间以回收可用的 40GB（磁盘上的数据文件）？我不能有一个停机时间。

oracle oracle-12c
Martin Hope
r0tt
Asked: 2017-11-21 00:37:17 +0800 CST
  • 1

在 Oracle 12c 数据库上查询 dba_autotask_client 视图时，我可以看到自动空间顾问已启用。Oracle 文档说，这些发现现在是自动实施的。如何查看此自动任务的结果？ 

CLIENT_NAME                      STATUS   CONSUMER_GROUP       MEAN_JOB_DURATION
-------------------------------- -------- -------------------- --------------------------------------------------
sql tuning advisor               ENABLED  ORA$AUTOTASK         +000000000 00:00:00.700000000
auto optimizer stats collection  ENABLED  ORA$AUTOTASK         +000000000 00:00:06.841993315
auto space advisor               ENABLED  ORA$AUTOTASK         +000000000 00:00:01.273776967
oracle oracle-12c
Martin Hope
r0tt
Asked: 2017-11-17 04:50:45 +0800 CST
  • 0

我设置了 Oracle 12c Dataguard 配置。为了构建备用数据库，我使用了 RMAN DUPLICATE 命令，该命令需要静态侦听器配置。

HOSTNAMEA 和 HOSTNAMEB 上的 tnsname.ora 相同：

BR =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

BR_STBY =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

listener.ora 主机名：

    SID_LIST_LISTENER =
        (SID_DESC =
          (GLOBAL_DBNAME = BR_DGMGRL)
          (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
          (SID_NAME = BR)
        )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
  )

listener.ora 主机名B：

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = BR_STDBY_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
      (SID_NAME = BR)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

此配置使用静态侦听器配置。例如，应用程序如何知道数据库已从 HOSTNAMEA 切换到 HOSTNAMEB？我应该使用动态侦听器注册吗？配置应该如何？

oracle oracle-12c
Martin Hope
r0tt
Asked: 2017-09-13 05:47:17 +0800 CST
  • 0

我必须在 2017-09-12:13:30:00 做一个恢复点。我遇到了 TDPO/TSM 错误 ANS1302E (RC2)。列表备份显示备份和归档日志可用于还原。以前有人遇到过这个问题吗？

rman target /
Startup mount
RUN {
   allocate channel t0 type 'sbt_tape' parms
   'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
   set until time "to_date('2017-09-12:13:30:00', 'yyyy-mm-dd:hh24:mi:ss')";

   restore database;
   recover database;

   release channel t0;

    }
connected to target database (not started)

Oracle instance started
database mounted

Total System Global Area    2399141888 bytes

Fixed Size                     8623496 bytes
Variable Size                671091320 bytes
Database Buffers            1711276032 bytes
Redo Buffers                   8151040 bytes

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: t0
channel t0: SID=135 device type=SBT_TAPE
channel t0: Data Protection for Oracle: version 8.1.0.0

executing command: SET until clause

Starting restore at 12-SEP-17

channel t0: starting datafile backup set restore
channel t0: specifying datafile(s) to restore from backup set
channel t0: restoring datafile 00004 to /opt/oracle/oradata/datafile/DBOLR2T/use
channel t0: reading from backup piece DB_954446866_1042_1
channel t0: ORA-19870: Fehler beim Zuruckschreiben von Backup Piece DB_954446866
ORA-19507: Fehler beim Abrufen der sequentiellen Datei, Handle="DB_954446866_104
ORA-27029: skgfrtrv: sbtrestore hat Fehler zuruckgegeben
ORA-19511: Kein RMAN-, sondern Media Manager- oder herstellerspezifischer Fehler
ANS1302E (RC2)    No objects on server match query
oracle restore
Martin Hope
r0tt
Asked: 2017-02-14 00:10:15 +0800 CST
  • 0

我需要为我们的数据中心创建一个数据库即服务 (DBaaS) 概念。目前我们拥有 VMware 的一切——但我认为这并不是真正的 DaaS 概念。公司政策不允许公共云解决方案。是否可以在您自己的数据中心中实施类似 Azure SQL 数据库的东西？

sql-server daas