[RAC] voting disk 维护命令

1. 查看
# crsctl query css votedisk


2. 备份 (裸设备)
# dd if=/dev/raw/raw1 of=/tmp/votedisk.dd.bak
# dd if=/dev/raw/raw1 of=/tmp/votedisk.dd.bak bs=1024k count=10


3. 恢复 (可在线恢复)
# dd if=/tmp/votedisk.bak of=/dev/raw/raw1
# cat $CRS_HOME/log/rac1/alertrac1.log
voting file is online: /dev/raw/raw1. Details in ...


4. 添加表决磁盘

4-1. 在线添加, 有可能导致节点重启
# crsctl add css votedisk /dev/raw/raw6 -force

4-2. 离线添加 (停止 crs)
# crsctl add css votedisk /dev/raw/raw7


5. 表决磁盘损坏

5-1. 不能启动 crs 时的信息
# cat $CRS_HOME/log/rac1/alertrac1.log
voting file is online: /dev/raw/raw1. Details in ...
voting file is offline: /dev/raw/raw6. Details in ...
Insufficient voting files available [1 of 2]. Details in ...

5-2. 在线破坏第0块表决磁盘, 会导致所有节点重启

[RAC] ocr 维护命令

1. 查看状态
# ocrcheck
# cat /etc/oracle/ocr.loc

2. 查看内容
默认输出文件在 $CRS_HOME/bin/OCRDUMPFILE
# ocrdump

3. 查看备份 (自动4小时物理备份一次)
# ocrconfig -showbackup
rac2 2012/07/22 07:06:37 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
rac2 2012/07/22 03:06:39 /u01/app/oracle/product/10.2.0/crs_1/cdata/crs
...

4. 恢复自动物理备份
# ocrconfig -restore /u01/app/oracle/product/10.2.0/crs_1/cdata/crs/backup00.ocr

5. 导入导出
# ocrconfig -export a.dump
# ocrconfig -import a.dump

6. 在线添加删除镜像
# ocrconfig -replace ocrmirror /dev/raw/raw5
# ocrconfig -replace ocrmirror -- 空白会清除镜像
# cat /etc/oracle/ocr.loc

[Windows] Loopback Adapter 设置

http://blogs.yahoo.co.jp/akio_myau/39554285.html

[工具使用] VirtualBox 相关

1. GUID 已经存在
--
Failed to open the hard disk <FilePath>.
Cannot register the hard disk <FilePath><GUID> becuase a hard disk <DifferentFilePath> with UUID <GUID> already exists.
--
================================================================================
解决方法:
# vboxmanage internalcommands sethduuid <PathOfNewVHD>

2. 系统不能启动
--
VirtualBox can’t operate in VMX root mode. Please disable the KVM kernel extension, recompile your kernel and reboot (VERR_VMX_IN_VMX_ROOT_MODE).
--

解决方法:
# lsmod | grep kvm
kvm_intel              85256  0
kvm                   225824  2 ksm,kvm_intel

# modprobe -r kvm_intel
# lsmod | grep kvm
kvm                   225824  1 ksm
================================================================================
3. Windows 与 Linux 下的虚拟机转换
Windows 或 Linux 上创建的虚拟机, 在相对的操作系统上, 直接可以使用
VirtualBox->添加已存在的虚拟机后, 直接不能打开, 点一下设定会自动转换
然后就可以直接使用了

需要拷贝的文件是？？？
Windows
Linux

[有待整理] 数据库更名

http://www.dba-oracle.com/t_rename_database_oracle_sid.htm

change controlfile, dumpfile path in init file etc.
change datafile logfile path in controlfile

select member from v$logfile;
select name from v$tempfile;
select name from v$datafile;

[安装配置] 动态与静态监听

1. 自动注册的监听

1-1. 数据库实例自动注册监听
$ cat $ORACLE_HOME/network/log/listener.log
17-JUL-2012 15:36:24 * service_register * insname * 0

# 其中 insname 是数据库初始化参数的 instance_name 的值
# 数据库启动后, 由 PMON 进程自动注册, 需要 1 分钟左右的时间


1-2. 自动注册后监听器状态
$ lsnrctl status
Service "dbname.mydomain" has 1 instance(s).
  Instance "insname", status READY, has 1 handler(s) for this service...

# dbname.mydomain 是监听对外提供的服务名, 要设置成全局数据库名或参数 service_names 的值
# dbname 是数据库初始化参数 db_name 的值
# mydomain 是数据库初始化参数的 db_domain 的值
# insname 是数据库初始化参数的 instance_name 的值
# READY 状态表示是自动注册的监听

$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )


1-3. 客户端 tnsname.ora
$ cat $ORACLE_HOME/network/admin/tnsnames.ora
MY_TNS_NAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srvname)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbname.mydomain)
    )
  )

# HOST 是数据库服务器主机名或 IP 地址, 在服务器上可通过 netstat -tulnp|grep 1521 查看
# MY_TNS_NAME 是连接数据库时的连接字符串别名
# SERVICE_NAME 与 1-2 中监听对外提供的服务名相同

1-4. 连接

1-4-1. 本地连接, 不经过监听器
sqlplus hr/hr

1-4-2. 经过监听器并使用客户端 tnsname.ora 文件解析
sqlplus hr/hr@my_tns_name

1-4-3. 简易连接方式, 经过监听但不使用客户端 tnsname.ora 文件
sqlplus hr/hr@srvname:1521/dbname.mydomain

2. 静态注册的监听
# 为了解决动态注册不稳定的情况

2-1. 配置监听器并重启监听
$ cat $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (global_dbname = dbname.mydomain)
      (oracle_home = D:\oracle\product\10.2.0\db_1)
      (sid_name = go)
    )
  )

# global_dbname 设置为全局数据库名, 或参数 service_names 的值
# sid_name 设置为参数 instance_name 的值

2-2. 监听器状态
$ lsnrctl status
Service "dbname.mydomain" has 1 instance(s).
  Instance "insname", status UNKNOWN, has 1 handler(s) for this service...

2-3. 客户端设置
# 与动态注册监听时设置相同

[有待整理] 逻辑备库的限制

1. 是否具有不支持的数据类型
PRIM> select * from dba_logstdby_unsupported;

2. 存储类型

3. PL/SQL 包

4. SQL

5. DML

6. 主库中个表行可唯一被标识
# 主库中的表是否具有主键或非空唯一索引/约束
PRIM> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) and bad_column = 'Y';

# 如果可以确定某表数据是唯一的, 可以为表创建 Primary-Key Rely 约束
PRIM> alter table t add primary key (id) rely disable;

[DataGuard] 管理用视图

1. 逻辑备库

1-1. 查看不被应用的对象
# sys 用户创建的表不会被应用
LDG> select statement_opt,owner,name from dba_logstdby_skip;

1-2. 逻辑备库操作日志, 只保留近 100 条
LDG> select event_time, status, event from dba_logstdby_events order by event_timestamp; 

1-3. 当前重做日志的情况
LDG> select sequence#, first_change#, next_change#, timestamp, applied from dba_logstdby_log;

1-4. LogMiner 状态信息
LDG> select * from v$logstdby_stats;

1-5. 当前日志服务相关信息
LDG> select sid, serial#, spid, type, status, high_scn from v$logstdby_process;

1-6. 当前 LOG 应用服务进展情况
LDG> select applied_scn, latest_scn, mining_scn, restart_scn from v$logstdby_progress;

1-7. SQL 应用大概状态
LDG> select session_id, state from v$logstdby_state;

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

2. 物理备库

2-1. 物理备库相关进程, redo 应用, redo 传输服务的活动状态
PDG> select process, client_process, sequence#, status, block#, blocks from v$managed_standby;

2-2. REDO 应用进度
PDG> select dest_name, archived_thread#, archived_seq#, applied_thread#, applied_seq#, db_unique_name from v$archive_dest_status where status = 'VALID';

2-3. 归档信息
PDG> select name, creator, sequence#, applied, completion_time from v$archived_log;

2-4. 归档历史
PDG> select first_time, first_change#, next_change#, sequence# from v$log_history;

2-5. 物理备库未接收的日志
# N -- 主库本地归档路径
# M -- 备库远程归档路径
PRIM> select local.thread#, local.sequence# from (select thread#, sequence# from v$archived_log where dest_id=N) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=M and thread# = local.thread#);

2-6. 当前数据库基本信息
PDG> select database_role, db_unique_name, open_mode, protection_mode, protection_level, switchover_status from v$database;

2-7. failover 后快速启动信息
PDG> select fs_failover_status, fs_failover_current_target, fs_failover_threshold, fs_failover_observer_present from v$database;

2-8. 查看应用模式
# IDLE, MANAGED, MANAGED REAL TIME APPLY
PDG> select recovery_mode from v$archive_dest_status;

2-9. DataGuard 事件 (alert.log)
PDG> select message from v$dataguard_status;

[DataGuard] 通过物理备库创建逻辑备库

将 DG 中一个物理备库转换为逻辑备库

1. 前提假设数据库具备创建逻辑备库的条件

2. 创建物理备库 (LDG), 配置监听, 网络服务 --- 参照物理备库的创建

3. 设置主库初始化参数
# switchover 相关设置略
PRIM> alter system set log_archive_config = 'dg_config=(prim,ldg)';
PRIM> alter system set log_archive_dest_1 = 'location=/u01/arch/prim';
PRIM> alter system set log_archive_dest_2 = 'service=ldg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=ldg';
PRIM> alter system set log_archive_dest_state_1 = enable;
PRIM> alter system set log_archive_dest_state_2 = defer;

4. 调整备库 (LDG) 初始化参数
LDG> alter system set log_archive_config = 'dg_config=(prim,ldg)';
[LDG]$ mkdir /u01/arch/ldgstd

# 区分本地日志归档与主库发来的日志归档路径
4-1. 本地归档
LDG> alter system set log_archive_dest_1 = 'location=/u01/arch/ldg valid_for=(online_logfiles,all_roles) db_unique_name=ldg';

4-2. 主库发来的 standby logs 归档
LDG> alter system set log_archive_dest_2 = 'location=/u01/arch/ldgstd valid_for=(standby_logfiles,standby_role) db_unique_name=ldg';

5. 创建 standby redologs
LDG> alter database add standby logfile group 4 '/u01/data/ldg/redo04.log' size 50m;
LDG> alter database add standby logfile group 5 '/u01/data/ldg/redo05.log' size 50m;
LDG> alter database add standby logfile group 6 '/u01/data/ldg/redo06.log' size 50m;
LDG> alter database add standby logfile group 7 '/u01/data/ldg/redo07.log' size 50m;

6. 挂载备库 (LDG), 主库开启发送日志, 备库开始接收日志, 但不启动日志应用
LDG> startup mount;
PRIM> alter system set log_archive_dest_state_2 = enable;
LDG> select sequence#,applied from v$archived_log;
LDG> alter database recover managed standby database cancel;

7. 主库生成数据字典
PRIM> execute dbms_logstdby.build;

8. 转换物理备库为逻辑备库
LDG> alter database recover to logical standby ldg; -- new db_name
LDG> shutdown immediate
LDG> startup mount
LDG> show parameter db_name
LDG> select database_role, switchover_status, open_mode from v$database;

9. 打开逻辑备库
LDG> alter database open resetlogs;

10. 开启 redo 应用
LDG> alter database start logical standby apply;
LDG> alter database stop logical standby apply;
LDG> alter database start logical standby apply immediate;
LDG> alter database stop logical standby apply immediate;

11. 验证

[DataGuard] Redo 数据传输方式

DataGuard Redo 数据传输的几种方式, 与数据库保护模式密切相关

[DataGuard] 数据库保护模式

1. 查看当前保护模式
PRIM> select protection_mode, protection_level from v$database;

2. 设置数据库保护模式
# 设置之前注意 log_archive_dest_n 参数的调整
PRIM> alter database set standby database to maximize availability [protection] [performance];

3. 重启主库使设置生效

[DataGuard] REDO 应用相关操作

1. redo 实时应用

1.1 物理备库
PDG> alter database recover managed standby database [disconnect from session] [using current logfile];
PDG> alter database recover managed standby database cancel;

1.2 逻辑备库
LDG> alter database start logical standby apply [immediate];
LDG> alter database stop logical standby apply;
LDG> alter database abort logical standby apply;


2. redo 应用延迟

2.1 设定, 单位分, 默认 30 分
PRIM> alter system set log_archive_dest_3='service=test arch valid_for ... delay=15';

2.2 忽略 redo 应用延迟

2.2.1 如果启动应用时指定实时应用, 延迟将被忽略

2.2.2 启动应用时指定 nodelay
PDG> alter database recover managed standby database nodelay;
LDG> alter database start logical standby apply nodelay;

[DataGuard] 创建物理备库与角色切换

创建一主库PRIM, 二物理备库PDG, LDG

1. 设置主库归档及强制日志模式
PRIM> select log_mode, force_logging from v$database;
LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES

PRIM> alter database [no]archivelog;
PRIM> alter database [no] force logging;

2. 创建密码文件并复制到备库
[PRIM]$ orapwd file=$ORACLE_HOME/dbs/orapwprim password=oracle entries=10
[PRIM]$ cp orapwprim orapwpdg
[PRIM]$ cp orapwprim orapwldg

3. 采用最大保护或最高可用模式时, 从库要创建 standby redologs
# 为了将来主库也能顺利的做角色切换, 最好也创建
PRIM> alter database add standby logfile group 4 ('/u01/data/prim/redo04.log') size 50m;
PRIM> alter database add standby logfile group 5 ('/u01/data/prim/redo05.log') size 50m;
PRIM> alter database add standby logfile group 6 ('/u01/data/prim/redo06.log') size 50m;
PRIM> alter database add standby logfile group 7 ('/u01/data/prim/redo07.log') size 50m;

4. 设置主库初始化参数
PRIM> create pfile from spfile;
[PRIM]$ vi $ORACLE_HOME/dbs/initprim.ora
--
# primary settings
*.db_name='dg'
*.db_unique_name='prim'
*.log_archive_config='dg_config=(prim,pdg,ldg)'
*.log_archive_dest_1='location=/u01/arch/prim'
*.log_archive_dest_2='service=pdg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=pdg'
*.log_archive_dest_3='service=ldg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=ldg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.remote_login_passwordfile='exclusive'

# standby settings
*.fal_server=pdg_tns_name,ldg_tns_name
*.fal_client=prim_tns_name
*.db_file_name_convert='/u01/data/pdg','/u01/data/prim','/u01/data/ldg','/u01/data/prim'
*.log_file_name_convert='/u01/data/pdg','/u01/data/prim','/u01/data/ldg','/u01/data/prim'
*.standby_file_management=auto
--

PRIM> shutdown immediate
PRIM> create spfile from pfile;
PRIM> startup

5. 创建备库库控制文件
PRIM> alter database create standby controlfile as '/tmp/control01.ctl' reuse;

6. 创建主库冷(热)备份文件
[PRIM]$ 复制备份数据文件, 初始化参数文件, 控制文件到备库

7.主, 备库创建监听与网络服务名
[PRIM/PDG/LDG]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )

[PRIM/PDG/LDG]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
prim_tns_name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prim_host_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prim.localhost)
    )
  )

pdg_tns_name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pdg_host_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdg.localhost)
    )
  )

ldg_tns_name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ldg_host_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ldg.localhost)
    )
  )

[PRIM/PDG/LDG]$ tnsping prim_tns_name
[PRIM/PDG/LDG]$ tnsping pdg_tns_name
[PRIM/PDG/LDG]$ tnsping ldg_tns_name

8. 创建备库所需要的文件夹
[PDG]$ mkdir -p $ORACLE_BASE/admin/pdg/{adump,bdump,cdump,udump}
[LDG]$ mkdir -p $ORACLE_BASE/admin/ldg/{adump,bdump,cdump,udump}
[PDG]$ mkdir -p /u01/arch/pdg
[LDG]$ mkdir -p /u01/arch/ldg

9. 设置备库初始化参数
[PDG]$ cat $ORACLE_HOME/dbs/initpdg.ora
--
# primary settings
*.db_name='dg' => 与主库相同
*.db_unique_name='pdg'
*.log_archive_config='dg_config=(prim,pdg,ldg)'
*.log_archive_dest_1='location=/u01/arch/pdg'
*.log_archive_dest_2='service=prim_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.log_archive_dest_3='service=ldg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=ldg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.remote_login_passwordfile='exclusive'
# standby settings
*.fal_server=prim_tns_name,ldg_tns_name
*.fal_client=pdg_tns_name
*.db_file_name_convert='/u01/data/prim','/u01/data/pdg','/u01/data/ldg','/u01/data/pdg'
*.log_file_name_convert='/u01/data/prim','/u01/data/pdg','/u01/data/ldg','/u01/data/pdg'
*.standby_file_management=auto
--

[LDG]$ cat $ORACLE_HOME/dbs/initldg.ora
--
# primary settings
*.db_name='dg' => 与主库相同
*.db_unique_name='ldg'
*.log_archive_config='dg_config=(prim,pdg,ldg)'
*.log_archive_dest_1='location=/u01/arch/pdg'
*.log_archive_dest_2='service=prim_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.log_archive_dest_3='service=pdg_tns_name arch valid_for=(online_logfiles,primary_role) db_unique_name=pdg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.remote_login_passwordfile='exclusive'
# standby settings
*.fal_server=prim_tns_name,pdg_tns_name
*.fal_client=ldg_tns_name
*.db_file_name_convert='/u01/data/prim','/u01/data/ldg','/u01/data/pdg','/u01/data/ldg'
*.log_file_name_convert='/u01/data/prim','/u01/data/ldg','/u01/data/pdg','/u01/data/ldg'
*.standby_file_management=auto
--

[PDG/LDG]$ export ORACLE_SID=pdg => ldg
[PDG/LDG]$ sqlplus / as sysdba
PDG/LDG> create spfile from pfile;

10. 挂载从库, 启动从库日志应用, 启动主库发送日志
PDG/LDG> startup mount
PDG/LDG> alter database recover managed standby database disconnect from session;
PRIM> alter system set log_archive_dest_state_2=enable;
PRIM> alter system set log_archive_dest_state_3=enable;

11. 验证创建结果

12. SwitchOver
12-1. 主库->备库
PRIM> alter database commit to switchover to physical standby [with session shutdown];
PRIM> shutdown immediate
PRIM> startup mount
PRIM> select database_role,switchover_status,open_mode from v$database;
PRIM> alter database recover managed standby database disconnect from session;

12-2. 备库->主库
PDG> alter database commit to switchover to primary [with session shutdown];
ERROR at line 1:
ORA-16139: media recovery required
PDG> alter database recover managed standby database disconnect from session;
Database altered.
PDG> shutdown immediate
PDG> startup
PDG> select database_role,switchover_status,open_mode from v$database;

13. FailOver
# 基本都是在待转换的物理备库上执行, 因为此时主库可能无法访问

13-1. 查看保护模式, 切换到最大性能模式
PDG> select protection_level from v$database;
PDG> alter database set standby database to maximize performance;

13-2. 检查归档文件是否连续
PDG> select * from v$archive_gap;

# 可能的话从主库把缺少的归档文件复制到备库并注册
PDG> alter database register physical logfile 'logfilepath';

13-3. 检查归档是否完整
# 可能的话将多出的序号归档拷贝到备库
PRIM> select distinct resetlogs_id, thread#, max(sequence#) over(partition by resetlogs_id, thread#) a from v$archived_log;
PDG> select distinct resetlogs_id, thread#, max(sequence#) over(partition by resetlogs_id, thread#) a from v$archived_log;

13-4. 切换备库为主库
PDG> alter database recover managed standby database finish force;
PDG> alter database commit to switchover to primary [with session shutdown];

13-5. 启动新主库
# 当前为mount的话直接打开即可, 否则需要先关闭再启动
PDG> shutdown immediate
PDG> startup

13-6. 利用备份恢复或重建旧主库


14. 调整物理备库redo应用频率
14-1. 设置recover并行度
PDG> recover standby database parallel N; -- cpus x 2
PDG> show parameter parallel_max_servers -- 重启后会恢复为此初始值
NAME                  TYPE     VALUE
--------------------- -------- -------
parallel_max_servers  integer  40

14-2. 物理备库设置
PDG> show parameter db_block_checking
NAME                TYPE    VALUE
------------------- ------- -------
db_block_checking   string  FALSE

14-3. 提高次值, 如4096能提高20%性能, 注意会占用更多内存
PDG> show parameter parallel_execution_message_size
NAME                             TYPE    VALUE
-------------------------------- ------- -------
parallel_execution_message_size  integer 2148

14-4. 优化磁盘I/O -- 控制到数据文件磁盘IO是否异步
PDG> show parameter disk_asynch_io
NAME            TYPE     VALUE
--------------- -------- -------
disk_asynch_io  boolean  TRUE

[ORA_ERRORS] Linux Error: 111: Connection refused

1. 问题

监听突然不能启动, 监听命令都不能使用, 执行都报以下错误, 重建监听问题依旧

$ lsnrctl start
$ lsnrctl stat

TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused


2. 解决方法
修改/etc/hosts文件, 添加

127.0.0.1 localhost.localdomain localhost

[工具使用] sys用户使用TNS登录远程数据库

1. orapwd 命令创建 sys 用户密码文件

2. 设置数据库初始化参数
> show parameter remote_login_passwordfile
NAME                       TYPE    VALUE
-------------------------- ------- ----------
remote_login_passwordfile  string  EXCLUSIVE

3. 客户端配置 tns 并连接
$ sqlplus sys@tns_name/password as sysdba
$ sqlplus sys/password@tns_name as sysdba