ORA-01196/ORA-01110特殊恢复案例一则

前不久,我们一套生产数据库的dataguard的备库出现实时应用过慢,数据库服务器IOWAIT高的情况,为了避免主库归档日志出现大量挤压,所以值班同事临时应急将主备库关系配置断掉,后续再进行DataGuard重构。除此之外,我们准备将备库打开进行一系列测试,试图探寻数据库服务器IOWAIT高的原因并解决DataGuard延迟的问题。

第二天,同事尝试打开备库,但是报了ORA-01196与错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

此错误因为备库还未将redo log与archive恢复至一致状态,数据库此时处于不一致状态是无法打开的。但是,备库所有的归档日志已经被删除,此时数据库无法进行一个完全恢复

SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;

NAME      OPEN_MODE            CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba  MOUNTED                          7669047             966955     7669046 PHYSICAL STANDBY

SQL>  ALTER DATABASE ACTIVATE STANDBY DATABASE;
 ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

此时,我准备重建控制文件,进行不完全恢复操作

QL> alter database backup controlfile to trace as '/home/ora11g/albert.ctl';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> 

albert.ctl文件内容:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA1459T" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo11.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo12.log'
  ) SIZE 300M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo21.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo22.log'
  ) SIZE 300M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo31.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo32.log'
  ) SIZE 300M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo04.log'  SIZE 300M BLOCKSIZE 512,
--   GROUP 5 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo05.log'  SIZE 300M BLOCKSIZE 512
DATAFILE
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/sysaux01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/undotbs01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users02.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users03.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users04.dbf'
CHARACTER SET AL32UTF8
;

[ora11g@ALBERT ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 12:36:00 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.  --控制文件重建完毕

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;  --仍然无法打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

修改数据库alter system set “_allow_resetlogs_corruption” =true scope=spfile;参数,再次进行不完全恢复(此操作会有严重丢失数据风险,生产系统请慎用)

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes
SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;

System altered.
SQL> shutdown abort;
ORACLE instance shut down.

SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [7669055], [0],
[7996264], [4194545], [], [], [], [], [], []
Process ID: 12086
Session ID: 1705 Serial number: 3

此时出现ORA-00600 2262错误,此时距离成功已经不远,只需要推进SCN即可,
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7779049 generated at 03/28/2020 12:54:12 needed for thread 1
ORA-00289: suggestion : /oracle/oradata_Travelskydba1/Travelskydba_DG/1_2_1036241636.dbf
ORA-00280: change 7779049 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started


SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL>  oradebug poke 0x06001AE70 4 8279047 
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 007E5407

SQL> alter database open resetlogs;  --数据库成功打开

Database altered.


SQL> set line 1000 pagesize 200    
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;

NAME      OPEN_MODE            CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba  READ WRITE                       8280898            8279049     8280903 PRIMARY

主库 drop tablespace后备库存储空间不释放案例一则

上周,应用人员误操作在平台上tablespace name 打错名字,从而想删除表空间进行重新建立。重建后此数据库备库触发了文件系统满的报警,备库文件系统使用率已经为100%,但是数据库值班人员观察到备库之前建立错误的数据文件已经不在,为何文件系统存储中文件却没有删掉?之前怎么没有出现如此的情况?

我随后做了一个实验给他,原因是在drop tablespace时,没有加上including contents and datafiles

--主库:
SQL> select username,count(*) from gv$session group by username order by 2 desc;

USERNAME                         COUNT(*)
------------------------------ ----------
                                      174
ZABBIX                                  6
SYS                                     5
DBSNMP                                  4
PUBLIC                                  1

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create tablespace albert datafile '+DG_DATA' size 1g;

Tablespace created.


SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT --刚刚创建的表空间

     FILE# NAME
---------- -----------------------------------------------------------------
         1 +DG_DATA/Travelskydba/datafile/system.259.1002299725
         2 +DG_DATA/Travelskydba/datafile/sysaux.260.1002299741
         3 +DG_DATA/Travelskydba/datafile/undotbs1.261.1002299753
         4 +DG_DATA/Travelskydba/datafile/undotbs2.263.1002299769
         5 +DG_DATA/Travelskydba/datafile/users.264.1002299781
         6 +DG_DATA/Travelskydba/datafile/tbs_test_1025_1.269.1022603323
         7 +DG_DATA/Travelskydba/datafile/tbs_test_1025_2.270.1022603429
         8 +DG_DATA/Travelskydba/datafile/albert.271.1035217783


--备库:
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT


SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert.271.1035217783

8 rows selected.

一、加入including contents and datafiles

--主库删除:
SQL> drop tablespace albert including contents and datafiles;

Tablespace dropped.



--备库确认,正常删除掉
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

7 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected.


total 68780320
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:35 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl01.ctl

此时文件已经正常删除掉。


二、主库不加including contents and datafiles;

--主库创建文件:
SQL> create tablespace albert_leung datafile '+DG_DATA' size 2g;

Tablespace created.

--备库确认:

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert_leung.271.1035218283

8 rows selected.

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT_LEUNG


drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:38 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl01.ctl


--主库删除文件:
SQL> drop tablespace albert_leung;

Tablespace dropped.

--备库数据字典:

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected

SQL> select FILE#,NAME from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

--可以看到,备库中的数据字典中已经删除掉,也就是说controlfile中该记录已经被删除

--但是观察到,备库文件系统,物理的数据文件还在,并没有删除。
[ora11g@vm-vmw45661-ora Travelskydba]$ ls -l
total 68780320
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283   --主库CHECKPOINT后不更新
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl01.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:41 system.259.1002299725
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
[ora11g@vm-vmw45661-ora Travelskydba]$ 

--此时,可以使用操作系统命令rm删除掉文件系统中命名错误的数据文件,因为controlfile中已经没有此记录,所以不会涉及数据库的实例恢复。

这个实验,没有加上including contents and datafiles 我只是回答了他第一个问题,但是第二个问题:“为什么之前我删除tablespace的时候,也没有加,怎么就没事呢?

根因在于Oracle Managed Files,这次出问题的此套备库没有使用OMF特性,也就是drop tablespace后,Oracle不会帮你自动的去清理物理文件,只会删除controlfile中的记录,但是之前同事所提到的没事的状况,是因为备库为RAC+ASM架构,默认采用了OMF管理数据文件,所以及时没有添加 including contents and datafiles 也会帮你自动清理文件。

官方原文如下:

1、 Unlike files that are not managed by the database, when an Oracle managed data file or temp file is dropped, the filename is removed from the control file and the file is automatically deleted from the file 

2、 When recovery of a standby database encounters redo for the deletion of a tablespace, it deletes any Oracle managed data files in the local file system. Note that this is independent of the INCLUDING DATAFILES option issued at the primary database.

User_Profile设置成Default= Resuorce UNLIMITED?

前几日,有位新员工准备调整数据库中User_Profile的SESSIONS_PER_USER限制前抛出了一个结论:“如果把用户的Profile设置成Default后就等同于资源无限制了,百度搜索显示很多文章表明Default便代表无限制

很显然这个结论是不严谨或者是错误的,Oracle官方文档中已经有明确的说明:

“Specify DEFAULT if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. The DEFAULT profile initially defines unlimited resources. You can change those limits with the ALTER PROFILE statement.

Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, then the user is subject to the limits on those resources defined by the DEFAULT profile.”

也就是说,如果用户省略设置单独PROFILE, 分配此PROFILE的用户受Default Profile文件中指定的此资源的限制,如果之前没有修改DEFAULT PROFILE前,DEFAULT PROFILE中的资源限定是: unlimited ,也就是说,即使用户使用继承了Default Profile内容,众多数据库中配置不一,因为Default Profile是可以被修改的, 在没有确认Default Profile资源限制前不能下此结论即”Default Profile = unlimited resources”

为了帮助他今后思考更加严谨,做了一个实验给他:

SQL> select *from dba_profiles where profile='DEFAULT';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   3
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

16 rows selected.

SQL> create user liangce identified by liangce123;

User created.

SQL> grant connect,resource to liangce;

Grant succeeded.

SQL> select username,profile from dba_users where username='LIANGCE';

USERNAME                       PROFILE
------------------------------ ------------------------------
LIANGCE                        DEFAULT

开启3个会话,均执行:
SQL> conn liangce/liangce123;
Connected.

第4个会话:
SQL> conn liangce/liangce123;
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Warning: You are no longer connected to ORACLE.
创建新的PROFILE,并没有显示指定SESSIONS_PER_USER limit
SQL> CREATE PROFILE "LIANGCE"
  2      LIMIT
  3           COMPOSITE_LIMIT DEFAULT
         CPU_PER_SESSION DEFAULT
         CPU_PER_CALL DEFAULT
  4    5    6           LOGICAL_READS_PER_SESSION DEFAULT
  7           LOGICAL_READS_PER_CALL DEFAULT
  8           IDLE_TIME DEFAULT
  9           CONNECT_TIME DEFAULT
 10           PRIVATE_SGA DEFAULT
 11           FAILED_LOGIN_ATTEMPTS DEFAULT
 12           PASSWORD_LIFE_TIME DEFAULT
 13           PASSWORD_REUSE_TIME DEFAULT
 14           PASSWORD_REUSE_MAX DEFAULT
 15           PASSWORD_VERIFY_FUNCTION DEFAULT
 16           PASSWORD_LOCK_TIME DEFAULT
 17           PASSWORD_GRACE_TIME DEFAULT;

Profile created.

SQL> alter profile LIANGCE limit SESSIONS_PER_USER 2;

Profile altered.

SQL> select *from dba_profiles where PROFILE='LIANGCE';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
LIANGCE                        COMPOSITE_LIMIT                  KERNEL   DEFAULT
LIANGCE                        SESSIONS_PER_USER                KERNEL   2
LIANGCE                        CPU_PER_SESSION                  KERNEL   DEFAULT
LIANGCE                        CPU_PER_CALL                     KERNEL   DEFAULT
LIANGCE                        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
LIANGCE                        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
LIANGCE                        IDLE_TIME                        KERNEL   DEFAULT
LIANGCE                        CONNECT_TIME                     KERNEL   DEFAULT
LIANGCE                        PRIVATE_SGA                      KERNEL   DEFAULT
LIANGCE                        FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
LIANGCE                        PASSWORD_LIFE_TIME               PASSWORD DEFAULT
LIANGCE                        PASSWORD_REUSE_TIME              PASSWORD DEFAULT
LIANGCE                        PASSWORD_REUSE_MAX               PASSWORD DEFAULT
LIANGCE                        PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
LIANGCE                        PASSWORD_LOCK_TIME               PASSWORD DEFAULT
LIANGCE                        PASSWORD_GRACE_TIME              PASSWORD DEFAULT

SQL> alter user liangce PROFILE LIANGCE;

User altered.

SQL> select username,profile from dba_users where username='LIANGCE';

USERNAME                       PROFILE
------------------------------ ------------------------------
LIANGCE                        LIANGCE

此时,才不会继续继承DEFAULT的配置,SESSIONS_PER_USER 为2,只能再一个实例中登录2个会话,RAC多个实例中单独计算,不进行累计。

DataGuard 切换流程梳理(三)- 命令行 Switchover

虽然我们生产目前DataGuard的切换主要依赖于 DataGuard Broker工具,但是日常工作中经常会发现Broker出现配置问题导致无法正常切换,准备好DG切换的标准SQL*PLUS的命令行方式才是万全之策。

1.切换前确认主备库是否具备切换条件,关注是否Error与delay_time
select thread#,
       DB_UNIQUE_NAME,
       DEST_NAME,
       TARGET,
       DATABASE_MODE,
       error,
       RECOVERY_MODE,
       GAP_STATUS,
       APPLIED_SEQ#,
       current_scn,
       applied_scn,
       to_char((scn_to_timestamp(current_scn)),'yyyymmdd hh24:mi:ss') current_scn_time,
       to_char((scn_to_timestamp(applied_scn)),'yyyymmdd hh24:mi:ss') applied_scn_time,
       scn_to_timestamp(current_scn)- scn_to_timestamp(current_scn) as delay_time
  from (SELECT al.thread#,
               ads.dest_id,
               ads.DEST_NAME,
               ads.TYPE || ' ' || ad.TARGET TARGET,
               ADS.DATABASE_MODE,
               ads.STATUS,
               ads.error,
               ads.RECOVERY_MODE,
               ads.DB_UNIQUE_NAME,
               ads.DESTINATION,
               ads.GAP_STATUS,
               (SELECT sequence#
                  FROM v$log na
                 WHERE na.thread# = al.thread#
                   and status = 'CURRENT') Current_Seq#,
               Last_Archived,
               al.APPLIED_SEQ#,
               ad.applied_scn,
               current_scn
          FROM (SELECT dest_id,
                       thread#,
                       d.current_scn,
                       MAX(sequence#) Last_Archived,
                       max(CASE
                             WHEN APPLIED = 'YES' THEN
                              sequence#
                           end) APPLIED_SEQ#
                  FROM v$archived_log V, v$database d
                 WHERE V.resetlogs_change# = d.RESETLOGS_CHANGE#
                 group by dest_id, thread#, d.current_scn) al,
               v$archive_dest_status ads,
               v$archive_dest AD
         WHERE al.dest_id(+) = ads.dest_id 
           AND ads.STATUS != 'INACTIVE'
           AND AD.DEST_ID = ADS.DEST_ID and ad.APPLIED_SCN <> 0
         ORDER BY al.thread#, ads.dest_id);

SQL> select name,database_role,switchover_status from v$database;
       --确认为 to stanby或SESSIONS ACTIVE状态
2.SERVICE处理:
备库添加:
SQL> show parameter service;

NAME                  TYPE        VALUE
------------------- ----------- ------------------------------
service_names       string      ORA1436B,dbaas

SQL> alter system set service_names='ORA1436B,dbaas,Albert';

System altered.

SQL> show parameter service;

NAME                  TYPE        VALUE
----------------    ----------- ------------------------------
service_names         string      ORA1436B,dbaas,Albert


主库修改:
SQL> show parameter service; 

NAME                  TYPE        VALUE
--------------     ----------- ------------------------------
service_names         string      ORA1436B,dbaas,Albert

SQL> alter system set service_names='ORA1436B,dbaas';

System altered.

SQL> show parameter service;

NAME                   TYPE        VALUE
-------------        ----------- ------------------------------
service_names        string      ORA1436B,dbaas
3.RAC主库切换前保留1个节点
登陆到主库其他节点
SQL>SHUTDOWN IMMEDIATE
4.主库SWITCHOVER
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 
或 
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown; 

注:如果有主库有活动的 session 可以使用此选项,否则转换会遇到 ORA-01093 错误或直接KILL活动会话
5.主库打开至Mount状态:
sqlplus / as sysdba 
SQL> startup mount 
6.备库SWITCH OVER操作
检查备库状态,是否可以切换
SQL> select name,database_role,switchover_status from v$database; 
--确认是否为to primary

原备库取消恢复进程:
SQL> alter database recover managed standby database cancel;

原备库(新主库)switchover 操作
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 

可能会抛出报ORA-16139: media recovery required,是是由于未应用日志引起,西药先执行执行 :
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT 
FROM SESSION;
7.原备库(新主库)重启
SQL> shutdown immediate 
SQL> startup
如不重启,新备库MRP进程则会一直处于WAIT_FOR_LOG的状态
8.原主库,新备库启用日志应用: 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 
9.确认主备库数据库数据字典状态与数据库日志有无异常,报错
select name,db_unique_name,database_role,open_mode from v$database;

主库进行日志切换,查看备库的日志,看是否开始接收并应用
SQL>select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and THREAD# <> 0