精华内容
下载资源
问答
  • Oracle ADG 数据库完整部署、切换、备份、运维手册(绝对干货)。 阿里云上部署Oracle ADG,并且带有节点故障自动切换功能。同时,也包括数据库异常故障的处理,比如死锁、数据误删除进行恢复 Oracle ADG DG Broker ...
  • 阿里云上部署Oracle ADG,并且带有节点故障自动切换功能。同时,也包括数据库异常故障的处理,比如死锁、数据误删除进行恢复
  • 格式: *.db_file_name_convert=‘主数据库数据文件目录’,‘备用数据库数据文件目录’ log_file_name_convert 指明主数据库和备用数据库的log文件转换目录对映。 格式: *. log_file_name_convert=‘主数据库redo...

    本文参考自
    https://www.cnblogs.com/zheng-hong-bo/p/12374078.html

    set linesize 200;
    set long 100000;

    环境信息: 教程中IP
    yxn-c76-oracle11g-172.16.102.20 main 10.211.55.100
    yxn-c76-oracle11g-172.16.102.21 standby 10.211.55.101

    查看DG

    SELECT * FROM v$option where parameter=‘Oracle Data Guard’;

    主端

    select name,log_mode,force_logging from v$database;
    alter database force logging;

    开启强制日志模式

    ALTER DATABASE NO FORCE LOGGING;

    关闭强制日志模式

    主端开启归档模式

    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;
    archive log list;

    在主库创建密码文件,将密码文件拷贝至备机。

    cd $ORACLE_HOME/dbs
    orapwd file=orapworcl password=123456 force=y

    scp orapworcl oracle@172.16.102.21:$ORACLE_HOME/dbs/orapworcl/orapworcl

    主库上创建standby redolog日志组。

    SQL> select thread#,group#,bytes/1024/1024 from v$log;

    THREAD# GROUP# BYTES/1024/1024


         1          1              50
         1          2              50
         1          3              50
    

    当前实例日志组个数为3,所以需要至少需要创建(3+1)*1=4组standby redolog,大小均为50M。

    col member for a50;
    select group#,member from v$logfile;

    GROUP# MEMBER
    

         3 /home/oracle/app/oracle/oradata/orcl/redo03.log
         2 /home/oracle/app/oracle/oradata/orcl/redo02.log
         1 /home/oracle/app/oracle/oradata/orcl/redo01.log
    

    新建4个standby redolog日志组,编号从4开始:

    SQL>
    alter database add standby logfile group 4 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo04.log’ size 50M;
    alter database add standby logfile group 5 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo05.log’ size 50M;
    alter database add standby logfile group 6 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo06.log’ size 50M;
    alter database add standby logfile group 7 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo07.log’ size 50M;

    set pagesize 100;
    col member for a60;
    select group#,member from v$logfile order by group#;

    SQL> set pagesize 100;
    col member for a60;
    select group#,member from v$logfile order by group#;SQL> SQL>

    GROUP# MEMBER
    

         1 /home/oracle/app/oracle/oradata/orcl/redo01.log
         2 /home/oracle/app/oracle/oradata/orcl/redo02.log
         3 /home/oracle/app/oracle/oradata/orcl/redo03.log
         4 /home/oracle/app/oracle/oradata/orcl/standby_redo04.log
         5 /home/oracle/app/oracle/oradata/orcl/standby_redo05.log
         6 /home/oracle/app/oracle/oradata/orcl/standby_redo06.log
         7 /home/oracle/app/oracle/oradata/orcl/standby_redo07.log
    

    7 rows selected.

    修改主库和备库监听文件,设置静态监听

    cd $ORACLE_HOME/network/admin/
    vim listener.ora

    主库的listener.ora文件改成:
    [oracle@apollo admin]$ cat listener.ora

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (GLOBAL_DBNAME= orcl)
    (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
    (SID_NAME=orcl)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.20)(PORT = 1521))
    )
    )
    ADR_BASE_LISTENER = /home/oracle/app/oracle

    备库的listener.ora文件:

    [oracle@neptune admin]$ cat listener.ora

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (GLOBAL_DBNAME= orcl)
    (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
    (SID_NAME=orcl)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.21)(PORT = 1521))
    )
    )
    ADR_BASE_LISTENER = /home/oracle/app/oracle

    主库和备库的监听文件listener.ora按照上面改好后,重启监听服务

    lsnrctl stop

    lsnrctl status

    lsnrctl start

    lsnrctl status

    编辑主库和备库的网络服务名tnsname.ora文件

    主库和备库的tnsnames.ora文件是一模一样的。

    PRIMARY =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.20)(PORT = 1521))
    (CONNECT_DATA =
    (SID=orcl)
    )
    )

    STANDBY =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.21)(PORT = 1521))
    (CONNECT_DATA =
    (SID=orcl)
    )
    )

    主库和备库的tnsnames.ora文件是一模一样的。

    配好后,tnsping一下对方,看是否通的:

    主库tnsping备库:

    [oracle@apollo admin]$ tnsping STANDBY

    配置主端pfile

    show parameter spfile;

    create pfile from spfile;

    orcl.__db_cache_size=3087007744
    orcl.__java_pool_size=67108864
    orcl.__large_pool_size=67108864
    orcl.__oracle_base=’/home/oracle/app/oracle’#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=2684354560
    orcl.__sga_target=4026531840
    orcl.__shared_io_pool_size=0
    orcl.__shared_pool_size=738197504
    orcl.__streams_pool_size=0
    *.audit_file_dest=’/home/oracle/app/oracle/admin/orcl/adump’
    *.audit_trail=‘db’
    *.compatible=‘11.2.0.0.0’
    *.control_files=’/home/oracle/app/oracle/oradata/orcl/control01.ctl’,’/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’
    *.db_block_size=8192
    *.db_domain=’’
    *.db_name=‘orcl’
    *.db_recovery_file_dest=’/home/oracle/app/oracle/flash_recovery_area’
    *.db_recovery_file_dest_size=4070572032
    *.diagnostic_dest=’/home/oracle/app/oracle’
    *.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
    *.memory_target=6662651904
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile=‘EXCLUSIVE’
    *.undo_tablespace=‘UNDOTBS1’

    *.fal_client=‘PRIMARY’
    *.fal_server=‘STANDBY’
    *.log_archive_config=‘DG_CONFIG=(PRIMARY,STANDBY)’
    *.log_archive_dest_1=‘location=/home/oracle/app/oracle/oradata/orcl/arc valid_for=(all_logfiles,all_roles) db_unique_name=PRIMARY’
    *.log_archive_dest_2=‘service=STANDBY lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=STANDBY’
    *.standby_file_management=‘AUTO’
    *.db_unique_name=‘PRIMARY’

    log_archive_dest_1 为主端归档路径/home/oracle/app/oracle/oradata/orcl/arc

    在主库手动创建arc目录

    cd /home/oracle/app/oracle/oradata/orcl
    mkdir -p arc

    shutdown immediate;
    create spfile from pfile;
    startup open;

    show parameter spfile;

    路径存在则说明是用create的spfile加载数据库

    create pfile from spfile;

    create pfile=’/home/oracle/pfile.ora’ from spfile;

    查看生成的pfile,看是否生效

    直接将pfile从主库拷贝到备库,然后在备库修改pfile文件

    scp initorcl.ora oracle@172.16.102.21:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs

    切到备库,编辑pfile文件

    vim /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora

    orcl.__db_cache_size=3087007744
    orcl.__java_pool_size=67108864
    orcl.__large_pool_size=67108864
    orcl.__oracle_base=’/home/oracle/app/oracle’#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=2684354560
    orcl.__sga_target=4026531840
    orcl.__shared_io_pool_size=0
    orcl.__shared_pool_size=738197504
    orcl.__streams_pool_size=0
    *.audit_file_dest=’/home/oracle/app/oracle/admin/orcl/adump’
    *.audit_trail=‘db’
    *.compatible=‘11.2.0.0.0’
    *.control_files=’/home/oracle/app/oracle/oradata/orcl/control01.ctl’,’/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’
    *.db_block_size=8192
    *.db_domain=’’
    *.db_name=‘orcl’
    *.db_recovery_file_dest=’/home/oracle/app/oracle/flash_recovery_area’
    *.db_recovery_file_dest_size=4070572032
    *.diagnostic_dest=’/home/oracle/app/oracle’
    *.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
    *.memory_target=6662651904
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile=‘EXCLUSIVE’
    *.undo_tablespace=‘UNDOTBS1’

    *.fal_client=‘STANDBY’
    *.fal_server=‘PRIMARY’
    *.log_archive_config=‘DG_CONFIG=(PRIMARY,STANDBY)’
    *.log_archive_dest_1=‘location=/home/oracle/app/oracle/oradata/orcl/arc valid_for=(all_logfiles,all_roles) db_unique_name=STANDBY’
    *.log_archive_dest_2=‘service=STANDBY lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=PRIMARY’
    *.standby_file_management=‘AUTO’
    *.db_unique_name=‘STANDBY’
    *.db_file_name_convert=’/home/oracle/app/oracle/oradata/orcl/’,’/home/oracle/app/oracle/oradata/orcl/’,’/home/oracle/Oracle_datafile/’,’/home/oracle/Oracle_datafile/’
    *.log_file_name_convert=’/home/oracle/app/oracle/oradata/orcl/’,’/home/oracle/app/oracle/oradata/orcl/’

    db_file_name_convert

    db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映(如果两数据库的目录结构不一样),如果有多个对映,逐一指明对映关系。

    格式: *.db_file_name_convert=‘主数据库数据文件目录’,‘备用数据库数据文件目录’

    log_file_name_convert

    指明主数据库和备用数据库的log文件转换目录对映。

    格式: *. log_file_name_convert=‘主数据库redolog目录’,‘备用数据库redolog目录’

    查看数据文件路径

    select name from v$datafile;

    查看redolog文件路径

    SELECT MEMBER FROM v$logfile;

    备库上手工创建相应目录

    mkdir -p /home/oracle/app/oracle/oradata/orcl/arc

    mkdir -p /home/oracle/app/oracle/admin/orcl/adump

    备库上从pfile创建spfile,重启数据库至nomount状态

    shutdown immediate;
    create spfile from pfile;
    startup nomount;
    show parameter spfile;

    create pfile=’/home/oracle/pfile.ora’ from spfile;

    利用RMAN在备库上恢复主库。

    rman target sys/123456@primary auxiliary sys/123456@standby

    duplicate target database for standby from active database nofilenamecheck;

    alter database recover managed standby database using current logfile disconnect from session;

    以下是回显信息

    RMAN>
    RMAN>
    RMAN> duplicate target database for standby from active database nofilenamecheck;

    Starting Duplicate Db at 24-AUG-20
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=189 device type=DISK

    contents of Memory Script:
    {
    backup as copy reuse
    targetfile ‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl’ auxiliary format
    ‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl’ ;
    }
    executing Memory Script

    Starting backup at 24-AUG-20
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=98 device type=DISK
    Finished backup at 24-AUG-20

    contents of Memory Script:
    {
    backup as copy current controlfile for standby auxiliary format ‘/home/oracle/app/oracle/oradata/orcl/control01.ctl’;
    restore clone controlfile to ‘/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’ from
    ‘/home/oracle/app/oracle/oradata/orcl/control01.ctl’;
    }
    executing Memory Script

    Starting backup at 24-AUG-20
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    copying standby control file
    output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20200824T175549 RECID=4 STAMP=1049306150
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
    Finished backup at 24-AUG-20

    Starting restore at 24-AUG-20
    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 24-AUG-20

    contents of Memory Script:
    {
    sql clone ‘alter database mount standby database’;
    }
    executing Memory Script

    sql statement: alter database mount standby database
    Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/system01.dbf for datafile 1 with checkpoint SCN of 1069782
    Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf for datafile 2 with checkpoint SCN of 1070042
    Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf for datafile 3 with checkpoint SCN of 1070101
    Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/users01.dbf for datafile 4 with checkpoint SCN of 1070110
    Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/example01.dbf for datafile 5 with checkpoint SCN of 1070104

    contents of Memory Script:
    {
    set newname for tempfile 1 to
    “/home/oracle/app/oracle/oradata/orcl/temp01.dbf”;
    set newname for tempfile 2 to
    “/home/oracle/Oracle_datafile/user_temp.dbf”;
    switch clone tempfile all;
    set newname for datafile 6 to
    “/home/oracle/Oracle_datafile/user_data.dbf”;
    backup as copy reuse
    datafile 6 auxiliary format
    “/home/oracle/Oracle_datafile/user_data.dbf” ;
    sql ‘alter system archive log current’;
    }
    executing Memory Script

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    renamed tempfile 1 to /home/oracle/app/oracle/oradata/orcl/temp01.dbf in control file
    renamed tempfile 2 to /home/oracle/Oracle_datafile/user_temp.dbf in control file

    executing command: SET NEWNAME

    Starting backup at 24-AUG-20
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00006 name=/home/oracle/Oracle_datafile/user_data.dbf
    output file name=/home/oracle/Oracle_datafile/user_data.dbf tag=TAG20200824T175557
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 24-AUG-20

    sql statement: alter system archive log current

    contents of Memory Script:
    {
    backup as copy reuse
    archivelog like “/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf” archivelog like
    “/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf” archivelog like
    “/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf” archivelog like
    “/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf” archivelog like
    “/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf” archivelog like
    “/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf” archivelog like
    “/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf” auxiliary format
    “/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf” ;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf”;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf”;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf”;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf”;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf”;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf”;
    catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf”;
    catalog clone datafilecopy “/home/oracle/app/oracle/oradata/orcl/system01.dbf”,
    “/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf”,
    “/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf”,
    “/home/oracle/app/oracle/oradata/orcl/users01.dbf”,
    “/home/oracle/app/oracle/oradata/orcl/example01.dbf”;
    switch clone datafile 1 to datafilecopy
    “/home/oracle/app/oracle/oradata/orcl/system01.dbf”;
    switch clone datafile 2 to datafilecopy
    “/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf”;
    switch clone datafile 3 to datafilecopy
    “/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf”;
    switch clone datafile 4 to datafilecopy
    “/home/oracle/app/oracle/oradata/orcl/users01.dbf”;
    switch clone datafile 5 to datafilecopy
    “/home/oracle/app/oracle/oradata/orcl/example01.dbf”;
    switch clone datafile all;
    }
    executing Memory Script

    Starting backup at 24-AUG-20
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=15 RECID=4 STAMP=1049302240
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=16 RECID=5 STAMP=1049303781
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=17 RECID=6 STAMP=1049303785
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=18 RECID=7 STAMP=1049304157
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=19 RECID=8 STAMP=1049305237
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=20 RECID=9 STAMP=1049305238
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting archived log copy
    input archived log thread=1 sequence=21 RECID=10 STAMP=1049306158
    output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf RECID=0 STAMP=0
    channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
    Finished backup at 24-AUG-20

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf RECID=1 STAMP=1049306166

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf RECID=2 STAMP=1049306166

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf RECID=3 STAMP=1049306166

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf RECID=4 STAMP=1049306166

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf RECID=5 STAMP=1049306166

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf RECID=6 STAMP=1049306166

    cataloged archived log
    archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf RECID=7 STAMP=1049306166

    cataloged datafile copy
    datafile copy file name=/home/oracle/app/oracle/oradata/orcl/system01.dbf RECID=4 STAMP=1049306166
    cataloged datafile copy
    datafile copy file name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf RECID=5 STAMP=1049306166
    cataloged datafile copy
    datafile copy file name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf RECID=6 STAMP=1049306166
    cataloged datafile copy
    datafile copy file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=7 STAMP=1049306166
    cataloged datafile copy
    datafile copy file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf RECID=8 STAMP=1049306166

    datafile 1 switched to datafile copy
    input datafile copy RECID=4 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/system01.dbf

    datafile 2 switched to datafile copy
    input datafile copy RECID=5 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

    datafile 3 switched to datafile copy
    input datafile copy RECID=6 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

    datafile 4 switched to datafile copy
    input datafile copy RECID=7 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf

    datafile 5 switched to datafile copy
    input datafile copy RECID=8 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf

    datafile 6 switched to datafile copy
    input datafile copy RECID=9 STAMP=1049306167 file name=/home/oracle/Oracle_datafile/user_data.dbf
    Finished Duplicate Db at 24-AUG-20

    RMAN> quit;

    至此,ADG搭建完成,补充说明下duplicate target database for standby from active database nofilenamecheck;这个命令,这个命令可以直接恢复数据文件、standby日志文件和控制文件。

    检查主库和备库的归档序列号是否一致

    alter system switch logfile;

    主库(先手动切换一下日志文件),这个命令每执行一次,Current log sequence就会加一

    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /home/oracle/app/oracle/oradata/orcl/arc
    Oldest online log sequence 26
    Next log sequence to archive 28
    Current log sequence 28
    SQL>

    Current log sequence为28

    备库

    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /home/oracle/app/oracle/oradata/orcl/arc
    Oldest online log sequence 26
    Next log sequence to archive 0
    Current log sequence 28
    SQL>

    Current log sequence也为28,可见主库和备库的归档序列号是一致的

    备库恢复完成后,数据库的状态是mounted,并且此时DG的保护模式是最高性能,查询备库:

    SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

    DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE


    PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED

    shutdown immediate;
    startup open;

    重启后查看数据库的状态是只读

    SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

    DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE


    PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY

    SQL> select process,client_process,sequence#,status from v$managed_standby;

    PROCESS CLIENT_P SEQUENCE# STATUS


    ARCH ARCH 0 CONNECTED
    ARCH ARCH 0 CONNECTED
    ARCH ARCH 28 CLOSING
    ARCH ARCH 0 CONNECTED
    RFS N/A 0 IDLE
    RFS LGWR 29 IDLE

    6 rows selected.

    SQL>
    SQL> recover managed standby database using current logfile disconnect from session;
    Media recovery complete.
    SQL>

    SQL> select process,client_process,sequence#,status from v$managed_standby;

    PROCESS CLIENT_P SEQUENCE# STATUS


    ARCH ARCH 0 CONNECTED
    ARCH ARCH 0 CONNECTED
    ARCH ARCH 28 CLOSING
    ARCH ARCH 0 CONNECTED
    RFS N/A 0 IDLE
    RFS LGWR 29 IDLE
    MRP0 N/A 29 APPLYING_LOG

    7 rows selected.

    SQL>

    展开全文
  • 本文主要向大家介绍了Oracle数据库ADG terminated by LGWR, terminating the instance,通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。11.2.0.4 RAC TO RAC FOR ADG环境。由于历史原因,备库...

    本文主要向大家介绍了Oracle数据库之ADG terminated by LGWR, terminating the instance,通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。

    5f167cd2fc50a22ac4819f9a34ed1dca.png

    11.2.0.4 RAC TO RAC FOR ADG环境。由于历史原因,备库节点二一直没有启动,一直是启动节点一对外提供服务。节点一alert报错,lgwr进行kill实例操作并自行重启。Mon Dec 24 16:11:24 2018Archived Log entry 262740 added for thread 2 sequence 185858 ID 0x92570693 dest 1:Mon Dec 24 16:12:28 2018Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7FFCD7489FF8] [PC:0x9899B16, qcsAnalyzeBooleanExpr()+144] [flags: 0x0, count: 1]Errors in file /u01/app/oracle/diag/rdbms/qdb/qdb1/trace/qdb1_ora_18912.trc  (incident=580470):ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr()+144] [SIGSEGV] [ADDR:0x7FFCD7489FF8] [PC:0x9899B16] [Address not mapped to object] []Incident details in: /u01/app/oracle/diag/rdbms/qdb/qdb1/incident/incdir_580470/qdb1_ora_18912_i580470.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.

    根据抛出的trc文件,我们可以追踪到时间段内的某条SQL

    **2018-12-24 16:12:28.596dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)----- Current SQL Statement for this session (sql_id=34vxxxxxx) -----select from MSS.T_MSS_MOBILE_LOGIN_ADDR where UUID in ('7xxxxx4xxxxxx', '48xxxxxxx, 。。。。。。整个trc文件190M,文本内容UUID几乎占了整个文件。

    new   2:  where owner = 'MSS' and segment_name = 'T_MSS_MOBILE_LOGIN_ADDR'

    OWNER        SEGMENT_NAME                   SEGMENT_TYPE         Total_Bytes(MB)

    MSS          T_MSS_MOBILE_LOGIN_ADDR        TABLE PARTITION               230054

    [oracle@qdb1 trace]$ du -sh /u01/app/oracle/diag/rdbms/qdb/qdb1/incident/incdir_580470/qdb1_ora_18912_i580470.trc190M    /u01/app/oracle/diag/rdbms/qdb/qdb1/incident/incdir_580470/qdb1_ora_18912_i580470.trc

    紧接着系统报了4021错误代码Mon Dec 24 16:30:17 2018System State dumped to trace file /u01/app/oracle/diag/rdbms/qdb/qdb1/trace/qdb1_ora_67693.trcMon Dec 24 16:32:32 2018Errors in file /u01/app/oracle/diag/rdbms/qdb/qdb1/trace/qdb1_lgwr_39157.trc:ORA-04021: timeout occurred while waiting to lock objectLGWR (ospid: 39157): terminating the instance due to error 4021一直到最后的kill 实例,自动重启Mon Dec 24 16:32:39 2018License high water mark = 1203Instance terminated by LGWR, pid = 39157USER (ospid: 6702): terminating the instanceInstance terminated by USER, pid = 6702Mon Dec 24 16:32:48 2018Starting ORACLE instance (normal)

    查询MOS相关文档,有一篇文档与我们的环境相符ORA-04021: timeout occurred while waiting to lock object : DR Instance terminated by LGWR (文档 ID 2183882.1)

    命中了BUG了。根据bug描述,需要修改参数SQL> show parameter cursor_sharing

    NAME                                 TYPE        VALUE

    cursor_sharing                       string      EXACT在cursor设置为exact时,两条sql语句如果存在一点不同,就不会共享cursor,而进行两次硬解析。设置为force时Oracle对输入的SQL值,会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时,会进行cursor sharing共享游标根据这个临时方案,我做了一个小实验。我在主库创建了一个t表作为该参数的测试

    14:35:02 SYS@bapdb1(bapdb1)> create table t as select * from dba_objects;

    Table created.

    然后到备库进行具体实验。

    Mark一下,择日进行参数修改。

    本文由职坐标整理并发布,希望对同学们学习Oracle有所帮助,更多内容请关注职坐标数据库Oracle数据库频道!

    展开全文
  • 1、从主库辅助数据库文件到standby数据库 $ rman target / auxiliary sys/oracle@rheldg1/rtdg1 2、复制数据库 RMAN> duplicate tar...
    1、从主库辅助数据库文件到standby数据库

    $ rman target / auxiliary sys/oracle@rheldg1/rtdg1

    2、复制数据库

    RMAN> duplicate target database for standby from active database nofilenamecheck;

    3、或者主库已有数据库备份,也可以执行下列命令从备份中复制数据库

    RMAN> duplicate target database for standby dorecover nofilenamecheck;

    --特别备注: 如果duplicate 命令指定dorecover选项,则在还原之后会进行恢复操作,恢复活动的归档日志和增量备份,从备份中复制数据库,一般推荐这样。

    4、复制完成后,standby数据库会从nomount状态被加载到mount状态下。


    正常开启和关闭DATA GUARD的相关流程:

    1、 alter database recover managed standby database cancel;【停止standby数据库redo日志应用】
    2、关闭主库
    3、关闭standby库

    启动顺序则恰好相反

    1、启动standby数据库;
    2、启动主数据库;
    3、开启standby数据库的redo日志应用


    监控standby日志的传输服务

    1、查询standby数据库 standby redo log 的使用情况
    select thread#,sequence#,archived,status from v$standby_log;
    在主库执行查询日志序列号

    select thread#,sequence#,status from v$log;

    从上面结果对比,主库的current 联机redo日志与standby 数据库的active状态的standby redo日志序列号,就可以检验出redo传输服务是否启用了实时传输。

    手动解决日志缺失

    物理standby数据库日志缺失查询sql:

    select * from v$archive_gap;

    THREAD#  LOW_SEQUENCE#  HIGH_SEQUENCE#
    ------------  ------------------------   -----------------------
         1                     7                             10

    上面结果表明 standby数据库线程1的序列7到10是当前缺失的日志

    通过在standby数据库查询以下sql,同样可以验证日志的实时应用情况

    select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process != 'ARCH';

    在standby数据库上,管理日志应用服务

    1、在前台启动redo apply 应用
    alter database recover managed standbydatabase;

    2、在后台启动redo apply 日志应用

    alter database recover managed standby database disconnect from session;

    disconnect from session 表示redo apply会在后台会话中运行

    以上2中redo apply 都只会会在主库发生log switch时才能在standby数据库应用日志。

    3、 执行以下sql在standby数据库,表示启动redo实时应用

    alter database recover managed standby database using current logfile disconnect from session;【在后台启动redo log 实时应用模式】


    4、停止redo apply 

    SQL> alter database recover managed standby database cancel;

    --The End--

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28211342/viewspace-2129702/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/28211342/viewspace-2129702/

    展开全文
  • 备库DUPLICATE后启用同步,开启ADG打开数据库时报错:ORA-10458 SQL>selectstatusfromv$instance; STATUS ------------ ...

        备库DUPLICATE后启用同步,开启ADG打开数据库时报错:ORA-10458

    
    SQL> select status from v$instance;
    STATUS
    ------------
    MOUNTED
    SQL> recover managed standby database using current logfile disconnect;
    Media recovery complete.
    SQL> 
    SQL> recover managed standby database cancel;
    Media recovery complete.
    SQL> 
    SQL> alter database open ;
    alter database open
    *
    ERROR at line 1:
    ORA-10458: standby database requires recovery
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '+SDE_DATA/sdedg/datafile/system.283.984230107'
    SQL>
    

         查看日志,发现所有主库的归档日志都没有被应用,手动恢复日志

    
    SQL> recover managed standby database
    

         查看告警日志,发现错误

    
    [oracle@sde1 trace]$ tail -f alert_sde1.log 
    FAL[client]: Failed to request gap sequence
     GAP - SCN range: 0x0e57.4d6ec257 - 0x0e57.4d6ec257
     DBID 2155281896 branch 984123832
    FAL[client]: All defined FAL servers have been attempted.
    ------------------------------------------------------------
    Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
    parameter is defined to a value that's sufficiently large
    enough to maintain adequate log switch information to resolve
    archivelog gaps.
    ------------------------------------------------------------
    Wed Aug 15 14:47:10 2018
    Recovery interrupted!
    Media Recovery failed with error 448
    Errors in file /u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc:
    ORA-00283: recovery session canceled due to errors
    ORA-00448: normal completion of background process
    Slave exiting with ORA-283 exception
    

        查看错误文件;

    
    [oracle@sde1 ~]$ tail -fn200 /u01/app/oracle/diag/rdbms/sdedg/sde1/trace/sde1_pr00_29797.trc
    

        里边有错误指示:

    
    *** 2018-08-15 14:34:10.634
    Media Recovery add redo thread 2
    *** 2018-08-15 14:34:10.721 4320 krsh.c
    Media Recovery Waiting for thread 1 sequence 129
    Redo shipping client performing standby login
    

        是日志文件没有被应用,查看备库的日志组文件,发现日志组过多,先删除过多的备库日志组,然后重建备库日志组

    
    SQL> select group#,member,type from v$logfile;
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
     6 +SDE_FRA/redo06.log                                          ONLINE
     5 +SDE_FRA/redo05.log                                          ONLINE
     2 +SDE_FRA/redo02.log                                          ONLINE
     1 +SDE_FRA/redo01.log                                          ONLINE
     3 +SDE_DATA/sdedg/onlinelog/group_3.284.984232869              ONLINE
     3 +data                                                        ONLINE
     4 +SDE_DATA/sdedg/onlinelog/group_4.302.984232871              ONLINE
     4 +data                                                        ONLINE
     7 +SDE_DATA/sdedg/onlinelog/group_7.301.984232871              ONLINE
     8 +SDE_DATA/sdedg/onlinelog/group_8.281.984232871              ONLINE
     9 +SDE_DATA/sdedg/onlinelog/group_9.280.984232871              ONLINE
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873             ONLINE
    11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873             ONLINE
    12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873             ONLINE
    13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873             ONLINE
    14 +SDE_DATA/sdedg/onlinelog/group_14.284.984230143             STANDBY
    15 +SDE_DATA/sdedg/onlinelog/group_15.284.984230145             STANDBY
    16 +SDE_DATA/sdedg/onlinelog/group_16.284.984230147             STANDBY
    17 +SDE_DATA/sdedg/onlinelog/group_17.284.984230149             STANDBY
    18 +SDE_DATA/sdedg/onlinelog/group_18.284.984230151             STANDBY
    19 +SDE_DATA/sdedg/onlinelog/group_19.284.984230153             STANDBY
    20 +SDE_DATA/sdedg/onlinelog/group_20.284.984230155             STANDBY
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    21 +SDE_DATA/sdedg/onlinelog/group_21.284.984230157             STANDBY
    22 +SDE_DATA/sdedg/onlinelog/group_22.284.984230159             STANDBY
    23 +SDE_DATA/sdedg/onlinelog/group_23.284.984230161             STANDBY
    24 +SDE_DATA/sdedg/onlinelog/group_24.284.984230163             STANDBY
    25 +SDE_DATA/sdedg/onlinelog/group_25.284.984230165             STANDBY
    26 +SDE_DATA/sdedg/onlinelog/group_26.284.984230167             STANDBY
    27 +SDE_DATA/sdedg/onlinelog/group_27.284.984230169             STANDBY
    28 +SDE_DATA/sdedg/onlinelog/group_28.284.984230171             STANDBY
     7 +data                                                        ONLINE
     8 +data                                                        ONLINE
     9 +data                                                        ONLINE
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    10 +data                                                        ONLINE
    11 +data                                                        ONLINE
    12 +data                                                        ONLINE
    13 +data                                                        ONLINE
    14 +data                                                        STANDBY
    15 +data                                                        STANDBY
    16 +data                                                        STANDBY
    17 +data                                                        STANDBY
    18 +data                                                        STANDBY
    19 +data                                                        STANDBY
    20 +data                                                        STANDBY
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    21 +data                                                        STANDBY
    22 +data                                                        STANDBY
    23 +data                                                        STANDBY
    24 +data                                                        STANDBY
    25 +data                                                        STANDBY
    26 +data                                                        STANDBY
    27 +data                                                        STANDBY
    28 +data                                                        STANDBY
    52 rows selected.
    SQL> alter database drop logfile group 14;
    Database altered.
    SQL> alter database drop logfile group 15;
    Database altered.
    SQL> alter database drop logfile group 16;
    Database altered.
    SQL> alter database drop logfile group 17;
    Database altered.
    SQL> alter database drop logfile group 18;
    Database altered.
    SQL> alter database drop logfile group 19;
    Database altered.
    SQL> 
    SQL> alter database drop logfile group 20;
    Database altered.
    SQL> alter database drop logfile group 21;
    Database altered.
    SQL> alter database drop logfile group 22;
    Database altered.
    SQL> alter database drop logfile group 23;
    Database altered.
    SQL> alter database drop logfile group 24;
    Database altered.
    SQL> alter database drop logfile group 25;
    Database altered.
    SQL> alter database drop logfile group 26;
    Database altered.
    SQL> alter database drop logfile group 27;
    Database altered.
    SQL> alter database drop logfile group 28;
    Database altered.
    SQL> select group#,member,type from v$logfile;
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
     6 +SDE_FRA/redo06.log                                          ONLINE
     5 +SDE_FRA/redo05.log                                          ONLINE
     2 +SDE_FRA/redo02.log                                          ONLINE
     1 +SDE_FRA/redo01.log                                          ONLINE
     3 +SDE_DATA/sdedg/onlinelog/group_3.284.984232869              ONLINE
     3 +data                                                        ONLINE
     4 +SDE_DATA/sdedg/onlinelog/group_4.302.984232871              ONLINE
     4 +data                                                        ONLINE
     7 +SDE_DATA/sdedg/onlinelog/group_7.301.984232871              ONLINE
     8 +SDE_DATA/sdedg/onlinelog/group_8.281.984232871              ONLINE
     9 +SDE_DATA/sdedg/onlinelog/group_9.280.984232871              ONLINE
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873             ONLINE
    11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873             ONLINE
    12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873             ONLINE
    13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873             ONLINE
     7 +data                                                        ONLINE
     8 +data                                                        ONLINE
     9 +data                                                        ONLINE
    10 +data                                                        ONLINE
    11 +data                                                        ONLINE
    12 +data                                                        ONLINE
    13 +data                                                        ONLINE
    22 rows selected.
    

         然后通过添加日志组的脚本重新添加日志组

    
    SQL> select group#,member,type from v$logfile;
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
     6 +SDE_FRA/redo06.log                                          ONLINE
     5 +SDE_FRA/redo05.log                                          ONLINE
     2 +SDE_FRA/redo02.log                                          ONLINE
     1 +SDE_FRA/redo01.log                                          ONLINE
     3 +SDE_DATA/sdedg/onlinelog/group_3.284.984232869              ONLINE
     3 +data                                                        ONLINE
     4 +SDE_DATA/sdedg/onlinelog/group_4.302.984232871              ONLINE
     4 +data                                                        ONLINE
     7 +SDE_DATA/sdedg/onlinelog/group_7.301.984232871              ONLINE
     8 +SDE_DATA/sdedg/onlinelog/group_8.281.984232871              ONLINE
     9 +SDE_DATA/sdedg/onlinelog/group_9.280.984232871              ONLINE
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    10 +SDE_DATA/sdedg/onlinelog/group_10.300.984232873             ONLINE
    11 +SDE_DATA/sdedg/onlinelog/group_11.299.984232873             ONLINE
    12 +SDE_DATA/sdedg/onlinelog/group_12.271.984232873             ONLINE
    13 +SDE_DATA/sdedg/onlinelog/group_13.274.984232873             ONLINE
    14 +SDE_FRA/sdedg/onlinelog/group_14.276.984234319              STANDBY
    15 +SDE_FRA/sdedg/onlinelog/group_15.277.984234319              STANDBY
    16 +SDE_FRA/sdedg/onlinelog/group_16.270.984234321              STANDBY
    17 +SDE_FRA/sdedg/onlinelog/group_17.271.984234321              STANDBY
    18 +SDE_FRA/sdedg/onlinelog/group_18.278.984234321              STANDBY
    19 +SDE_FRA/sdedg/onlinelog/group_19.269.984234321              STANDBY
    20 +SDE_FRA/sdedg/onlinelog/group_20.267.984234323              STANDBY
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    21 +SDE_FRA/sdedg/onlinelog/group_21.264.984234323              STANDBY
    22 +SDE_FRA/sdedg/onlinelog/group_22.279.984234323              STANDBY
    23 +SDE_FRA/sdedg/onlinelog/group_23.280.984234323              STANDBY
    24 +SDE_FRA/sdedg/onlinelog/group_24.281.984234325              STANDBY
    25 +SDE_FRA/sdedg/onlinelog/group_25.282.984234325              STANDBY
    26 +SDE_FRA/sdedg/onlinelog/group_26.283.984234325              STANDBY
    27 +SDE_FRA/sdedg/onlinelog/group_27.284.984234325              STANDBY
    28 +SDE_FRA/sdedg/onlinelog/group_28.404.984234327              STANDBY
     7 +data                                                        ONLINE
     8 +data                                                        ONLINE
     9 +data                                                        ONLINE
    GROUP# MEMBER                                                       TYPE
    ---------- ------------------------------------------------------------ -------
    10 +data                                                        ONLINE
    11 +data                                                        ONLINE
    12 +data                                                        ONLINE
    13 +data                                                        ONLINE
    37 rows selected.
    SQL>
    

        重新执行同步

    
    SQL> recover managed standby database using current logfile disconnect;
    Media recovery complete.
    SQL> 
    SQL> 
    SQL> recover managed standby database cancel;
    Media recovery complete.
    SQL> 
    SQL> 
    SQL> alter database open ;
    alter database open
    *
    ERROR at line 1:
    ORA-10458: standby database requires recovery
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '+SDE_DATA/sdedg/datafile/system.283.984230107'
    

        日志组的问题已经解决,现在还需要恢复数据库,考虑主备库同步的问题,查看主备库参数是否正常

    
    SQL> show parameter log_archive_config
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config                   string      
    SQL> show parameter log_archive_dest_2
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_17                  string
    log_archive_dest_18                  string
    log_archive_dest_19                  string
    log_archive_dest_2                   string
    

            发现主库的转换参数都没有设置,重新设置这两个参数

    
    SQL> alter system set log_archive_config='dg_config=(sde,sdedg)';
    SQL> alter system set log_archive_dest_2='service=sde_new lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sdedg';	
    
    
    SQL> show parameter log_archive_
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config                   string      dg_config=(sde,sdedg)
    log_archive_dest                     string
    log_archive_dest_1                   string      location=use_db_recovery_file_
     dest
    log_archive_dest_10                  string
    log_archive_dest_11                  string
    log_archive_dest_12                  string
    log_archive_dest_13                  string
    log_archive_dest_14                  string
    log_archive_dest_15                  string
    log_archive_dest_16                  string
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_17                  string
    log_archive_dest_18                  string
    log_archive_dest_19                  string
    log_archive_dest_2                   string      service=sde_new lgwr async val
     id_for=(online_logfiles,primar
     y_role) db_unique_name=sdedg
    

        参数设置正常了,备库重新开同步看能否正常

    
    SQL> recover managed standby database using current logfile disconnect;
    Media recovery complete.
    

         备库查看日志应用进度,在主库查看已经归档的sequence号

    
    SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" 
             FROM (select thread# thrd, MAX(sequence#) almax 
                   FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, 
                  (SELECT thread# thrd, MAX(sequence#) lhmax 
                    FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh 
             WHERE al.thrd = lh.thrd;
    Thread Last Seq Received Last Seq Applied
    ---------- ----------------- ----------------
     1               143              143
     2               138              138
    SQL> archive log list 
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     138
    Next log sequence to archive   144
    Current log sequence           144
    

        等同步完成,开启ADG

    
    SQL> recover managed standby database cancel;
    Media recovery complete.
    SQL> 
    SQL> alter database open ;
    Database altered.
    SQL> 
    SQL> recover managed standby database using current logfile disconnect;
    Media recovery complete.
    SQL> 
    SQL> set lines 1000
    SQL> select PROCESS,PID,STATUS, GROUP# , RESETLOG_ID,THREAD#,SEQUENCE# from v$managed_standby;
    PROCESS          PID STATUS       GROUP#                                   RESETLOG_ID    THREAD#  SEQUENCE#
    --------- ---------- ------------ ---------------------------------------- ----------- ---------- ----------
    ARCH           22841 CONNECTED    N/A                                                0          0          0
    ARCH           22843 CONNECTED    N/A                                                0          0          0
    ARCH           22845 CLOSING      15                                         984123832          1        143
    ARCH           22847 CLOSING      22                                         984123832          2        138
    MRP0            3116 APPLYING_LOG N/A                                        984123832          2        139
    RFS             2722 IDLE         N/A                                                0          0          0
    RFS             2892 IDLE         N/A                                                0          0          0
    RFS             2894 IDLE         6                                          984123832          1        144
    RFS             2896 IDLE         N/A                                                0          0          0
    RFS             2928 IDLE         N/A                                                0          0          0
    RFS             2930 IDLE         N/A                                                0          0          0
    PROCESS          PID STATUS       GROUP#                                   RESETLOG_ID    THREAD#  SEQUENCE#
    --------- ---------- ------------ ---------------------------------------- ----------- ---------- ----------
    RFS             2932 IDLE         N/A                                                0          0          0
    RFS             2948 IDLE         3                                          984123832          2        139
    13 rows selected.
    

         现在DG同步正常了。


    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31546994/viewspace-2200160/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/31546994/viewspace-2200160/

    展开全文
  • 构建最高可用Oracle数据库系统RAC和ADG环境搭建文档。
  • Oracle11.2.0.4数据库ADG主备环境搭建 实现目标 两台X86数据库服务器实现oracle11g的主备adg环境,避免单点故障,满足数据库基本容灾备份需求。其中操作系统版本要求centos7.9,数据库版本要求oracle11.2.0.4+最新...
  • Oracle数据库管理每周一例(12.2,18c,19c) 2020-10-16第十七期 ADG1.概念2.环境说明3.搭建流程-主库处理1.配置静态监听2.配置本地命名3.数据库参数配置4.生成pfile和密码文件4.搭建流程-备库处理1.创建目录并上传...
  • Oracle的ADG备库可以作为OGG的源头数据库
  • 1. 主库数据库文件、控制文件、日志文件的规划 SYS@chicago > select name from v$datafile union select name from v$controlfile union select member from v$...
  • Oracle数据库管理每周一例(12.2,18c,19c) 2020-11-01第十九期 ADG(03)1.Far Sync Standby2.EMCC中的DG下期预告: 第十九期 ADG(03) 本周又忘记写了,第二周周一补上,本周主要说一些ADG的一些其他东西 1.Far Sync ...
  • Oracle数据库管理每周一例(12.2,18c,19c) 2020-10-25第十八期 ADG1.主备切换2.一些常见故障处理3.FSFO4.DG需要注意的一些问题5.自动切换连接字符串下期预告: 第十八期 ADG 本周因为各种安全检查比较闲,结果闲的...
  • 构建最高可用Oracle数据库系统 环境搭建文档,包括RAC、dataguard、broker
  • 标签:Oracle DG、Data Guard、Oracle ADG、Active Data Guard、搭建DG 注意:文中删去了不需要的多余参数,让初学者一目了然一学就会 版本区别:DG和ADG的搭建方式是一样的,同样可以参考本文 系统安装:对于Oracle...
  • 标签:Oracle DG、Data Guard、Oracle ADG、Active Data Guard、搭建DG 注意:文中删去了不需要的多余参数,让初学者一目了然一学就会 版本区别:DG和ADG的搭建方式是一样的,同样可以参考本文 系统安装:对于Oracle...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,226
精华内容 890
关键字:

adg数据库