oracle19c rac asm-m6米乐安卓版下载

主库不停机的情况下完成adg的搭建，备库单实例仅作为实时数据库备份，还可供实时查询使用。备库不考虑切换成主库。

提示:检查主库已启用归档模式，所以可以不停机实现上述需求。

基本信息:（ip/库名/密码等敏感信息均已替换）

备库操作系统配置和数据库安装建议:

客户提供的基本配置：

cpu 16核

内存 64g

硬盘 1t

操作系统和数据库补丁版本同主库一致。

操作系统：oracle linux7.9

数据库补丁：oracle19.9

备库ip和主库建议同网段，并保证带宽足够。

磁盘空间规划：大小>=主库

swap虚拟内存：32g

数据库目录：/u01分配1t

数据库软件安装基本参数优化(此处已省略)。

sql> archive log list;

database log mode              archive mode

automatic archival             enabled

archive destination            use_db_recovery_file_dest

oldest online log sequence     21

next log sequence to archive   22

current log sequence           22

sql> alter database force logging;

database altered.

sql> select force_logging from v$database;

force_logging

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

yes

sql> show parameter db_recovery_file_dest;

[oracle@rac1 ~]$ echo $oracle_sid

jycdb1

如果不是jycdb1

则[oracle@rac1 ~]$ export oracle_sid=jycdb1

[oracle@rac1 ~]$ sqlplus / as sysdba

sql*plus: release 19.0.0.0.0 - production on thu jul 30 14:30:52 2020

version 19.9.0.0.0

米乐app官网下载 copyright (c) 1982, 2020, oracle.  all rights reserved.

connected to:

oracle database 19c enterprise edition release 19.0.0.0.0 - production

version 19.9.0.0.0

sql> archive log list;

sql> set line 160   

sql> set wrap off

sql> select * from v$log;

    group#    thread#  sequence#      bytes  blocksize    members arc status           first_change# first_time          next_change# next_time

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

         1          1         21  209715200        512          1 yes inactive               3782017 2020-07-29 12:22:29      3964886 2020-07-30 13:51:03

         2          1         22  209715200        512          1 no  current                3964886 2020-07-30 13:51:03   9.2954e 18

         3          2          9  209715200        512          1 yes inactive               3964887 2020-07-30 13:51:04      3985738 2020-07-30 13:59:05

         4          2         10  209715200        512          1 no  current                3987920 2020-07-30 14:28:03   9.2954e 18 2020-07-30 14:28:03

sql> select * from v$standby_log;

no rows selected

增加standbby log(可选，此项目不考虑切换所以不做此操作)

alter database add standby logfile thread 1 group 11 size 2048m;

alter database add standby logfile thread 1 group 12 size 2048m;

alter database add standby logfile thread 1 group 13 size 2048m;

alter database add standby logfile thread 1 group 14 size 2048m;

alter database add standby logfile thread 1 group 15 size 2048m;

alter database add standby logfile thread 1 group 16 size 2048m;

alter database add standby logfile thread 2 group 17 size 2048m;

alter database add standby logfile thread 2 group 18 size 2048m;

alter database add standby logfile thread 2 group 19 size 2048m;

alter database add standby logfile thread 2 group 20 size 2048m;

alter database add standby logfile thread 2 group 21 size 2048m;

alter database add standby logfile thread 2 group 22 size 2048m;

sql> select * from v$standby_log;

sql> exit

sql> alter system set db_unique_name='jycdb' scope=spfile sid='*';

备库检查：

sid_list_listener =

  (sid_list =

    (sid_desc =

      (global_dbname = dgjycdb)

      (oracle_home = /u01/app/oracle/product/19c/dbhome_1)

      (sid_name = dgjycdb)

    )

  ) 

sqlnet.ora添加低版本客户端兼容连接:

cd /oracle/app/oracle/product/19c/dbhome_1/network/admin

$ more sqlnet.ora

sqlnet.allowed_logon_version_server=8

sqlnet.allowed_logon_version_client=8

启动备库监听：

lsnrctl start

lsnrctl status

节点1和2都配置：

[oracle@rac1 admin]$ cat tnsnames.ora

# tnsnames.ora network configuration file: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora

# generated by oracle configuration tools. ###(instance_name=jycdb1/2)

jycdb1 =

  (description =

    (address = (protocol = tcp)(host = 192.168.8.192)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = jycdb)

    )

  )

jycdb2 =

  (description =

    (address = (protocol = tcp)(host = 192.168.8.193)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = jycdb)

    )

  )

jyc=

  (description =

    (address = (protocol = tcp)(host = 192.168.8.194)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = jyc)

    )

  )

dgjyc =

  (description =

    (address = (protocol = tcp)(host = 192.168.8.197)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = jyc)

    )

  )

tns_jycdb =

  (description =

    (address = (protocol = tcp)(host = 192.168.8.194)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = jycdb)

    )

  )

tns_dgjycdb =

  (description =

    (address = (protocol = tcp)(host = 192.168.8.197)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = dgjycdb)

    )

  )

登录主库任意节点拷贝密码文件并传输到备库：

su - grid

asmcmd

pwcopy pwdjycdb.256.1024323395 /oracle/app/grid/orapwjycdb1

scp /oracle/app/grid/orapwjycdb1 oracle@192.168.8.197: /u01/app/oracle/product/19c/dbhome_1/dbs/orapwdgjycdb

注意密码文件的名称orapw

连主库：

sqlplus sys/abcd_2021@tns_jycdb as sysdba
sqlplus sys/abcd_2021@jycdb1 as sysdba
sqlplus sys/abcd_2021@jycdb2 as sysdba

连备库：
sqlplus sys/abcd_2021@tns_dgjycdb as sysdba

注意：如果密码文件错误但登录密码正确，将提示连接到空实例。如果密码错误，则提示密码错误。所以第6步很关键。

传统方法参考：

主库创建参数文件：

create pfile='/home/oracle/pfile.txt' from spfile;注意修改控制文件路径及相应目录。

*.audit_file_dest='/u01/app/oracle/admin/jycdb/adump'

*.audit_trail='none'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/ctl01.dbf','/u01/app/oracle/oradata/ctl02.dbf'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_files=2000

*.db_name='jycdb'

*.db_unique_name='dgjycdb'

*.db_recovery_file_dest='/u01/app/oracle/reco'

*.db_recovery_file_dest_size=1t

*.diagnostic_dest='/u01/app/oracle'

*.enable_pluggable_database=true

*.log_archive_format='%t_%s_%r.dbf'

*.nls_language='american'

*.nls_territory='america'

*.open_cursors=300

*.pga_aggregate_target=5g

*.processes=3000

*.recyclebin='off'

*.remote_login_passwordfile='exclusive'

*.sga_target=15g

*.undo_retention=10800

*.undo_tablespace='undotbs1'

备库启动startup nomount;

在备库操作：

rman target sys/abcd_2021@tns_jycdb auxiliary sys/abcd_2021@tns_dgjycdb

run

{

allocate channel cl1 type disk;

allocate auxiliary channel c1 type disk;

duplicate target database for standby from active database nofilenamecheck dorecover;

release channel c1;

release channel cl1;

}

$ cat dg.sh

rman target sys/abcd_2021@jycdb auxiliary sys/abcd_2021@dgjycdb log /home/oracle/rman-`date %y%m%d-%h%m`.log <

run

{

allocate channel cl1 type disk;

allocate auxiliary channel c1 type disk;

duplicate target database for standby from active database nofilenamecheck dorecover;

release channel c1;

release channel cl1;

}

eof

$ chmod x dg.sh

$ nohup ./dg.sh &

查看日志：tail -f /home/oracle/rman-xxx.log

创建完成后注意检查路径下文件，确认是否预期结果：

ll /u01/app/oracle/reco

ll /u01/app/oracle/oradata

本来想用19c新特性试试，没想报错了，可能是由于主备内存差异太大，此次由于时间关系，没再多试，改用传统方法处理。

dbca -silent -createduplicatedb \

-gdbname jycdb \

-sid dgjycdb \

-syspassword abcd_2021 \

-primarydbconnectionstring 192.168.8.194:1521/jycdb \

-nodelist dgjycdb \

-databaseconfigtype single \

-storagetype fs \

-createasstandby -dbuniquename dgjycdb \

-datafiledestination ' /u01/app/oracle/oradata' \

-initparams sga_target=30g,processes=2000,pga_aggregate_target=10g,db_recovery_file_dest_size=1000gb,db_recovery_file_dest=/u01/app/oracle/reco,audit_trail=none,db_create_file_dest=/u01/app/oracle/oradata, db_create_online_log_dest_1=/u01/app/oracle/oradata

检查主库文件路径：cdb和pdb下都要检查

select name from v$datafile;

select * from v$logfile;

根据路径确定db_file_name_convert和log_file_name_convert对应方式。

主库修改参数：

alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=jycdb' scope=both sid='*';

alter system set log_archive_dest_2='service=tns_dgjycdb valid_for=(online_logfiles,primary_role) db_unique_name=dgjycdb' scope=both sid='*';

alter system set log_archive_config='dg_config=(jycdb,dgjycdb)' scope=both sid='*';

alter system set db_file_name_convert='/u01/app/oracle/oradata/dgjycdb',' datac1/jycdb' scope=spfile sid='*';

alter system set log_file_name_convert='/u01/app/oracle/oradata/dgjycdb',' datac1/jycdb' scope=spfile sid='*';

alter system set standby_file_management=auto scope=both sid='*';

alter system set fal_client='tns_jycdb' scope=both sid='*';

alter system set fal_server='tns_dgjycdb' scope=both sid='*';

备库修改参数：

alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=dgjycdb' scope=both sid='*';

alter system set log_archive_dest_2='service=tns_jycdb valid_for=(online_logfiles,primary_role) db_unique_name=jycdb' scope=both sid='*';

alter system set log_archive_config='dg_config=(jycdb,dgjycdb)' scope=both sid='*';

alter system set db_file_name_convert=' datac1/jycdb','/u01/app/oracle/oradata/dgjycdb' scope=spfile sid='*';

alter system set log_file_name_convert=' datac1/jycdb','/u01/app/oracle/oradata/dgjycdb' scope=spfile sid='*';

alter system set standby_file_management=auto scope=both sid='*';

alter system set fal_client='tns_dgjycdb' scope=both sid='*';

alter system set fal_server='tns_jycdb' scope=both sid='*';

sql> select * from v$standby_log;

alter database add standby logfile thread 1 group 11 size 2048m;

alter database add standby logfile thread 1 group 12 size 2048m;

alter database add standby logfile thread 1 group 13 size 2048m;

alter database add standby logfile thread 1 group 14 size 2048m;

alter database add standby logfile thread 1 group 15 size 2048m;

alter database add standby logfile thread 1 group 16 size 2048m;

alter database add standby logfile thread 2 group 17 size 2048m;

alter database add standby logfile thread 2 group 18 size 2048m;

alter database add standby logfile thread 2 group 19 size 2048m;

alter database add standby logfile thread 2 group 20 size 2048m;

alter database add standby logfile thread 2 group 21 size 2048m;

alter database add standby logfile thread 2 group 22 size 2048m;

备库：

sql> startup mount;

sql> show pdbs;

    con_id con_name                       open mode  restricted

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

         2 pdb$seed                       read only  no

         3 pdb                            mounted

         4 jyc                            mounted

sql> alter pluggable database all open instances=all;

pluggable database altered.

sql>  show pdbs;

    con_id con_name                       open mode  restricted

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

         2 pdb$seed                       read only  no

         3 pdb                            read only  no

         4 jyc                            read only  no

sql> alter database recover managed standby database using current logfile disconnect;

database altered.

sql> select database_role,open_mode from v$database;

database_role     open_mode

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

physical standby read only with apply

取消日志应用操作：alter database recover managed standby database cancel;

备库检查：select process,status,sequence#,thread# from v$managed_standby;

检查alert日志。

检查主备库状态:

set line 160

set wrap off

col inst_id for 99

select inst_id, dbid,name,db_unique_name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

备库检查日志同步情况参考脚本1：

select 'last applied : ' logs,

to_char(next_time,'dd-mon-yy:hh24:mi:ss') time,thread#,sequence#

from v$archived_log

where sequence# =

(select max(sequence#) from v$archived_log where applied='yes'

)

union

select 'last received : ' logs,

to_char(next_time,'dd-mon-yy:hh24:mi:ss') time,thread#,sequence#

from v$archived_log

where sequence# =

(select max(sequence#) from v$archived_log );

主库：

sql> select database_role,open_mode from v$database;

database_role     open_mode

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

primary  read write

主库pdb连接

conn sys/abcd_2021@jyc

创建测试表空间

create tablespace test datafile ' datac1' size 100m autoextend on next 10m;

建个用户

create user test identified by test default tablespace test;

grant dba to test;

建个表

create table test(id int);

插入记录

insert into test values(1);

连接主库和备库检查记录：

conn test/test@jyc

select * from test;

conn test/test@dgjyc

select * from test;

主备库检查脚本参考2:

select

 (select name from v$database

 ) name,

 (select max (sequence#) from v$archived_log where dest_id = 1

 ) current_primary_seq,

 (select max (sequence#)

 from v$archived_log

 where trunc(next_time) > sysdate - 1

 and dest_id = 2

 ) max_stby,

 (select nvl (

 (select max (sequence#) - min (sequence#)

 from v$archived_log

 where trunc(next_time) > sysdate - 1

 and dest_id = 2

 and applied = 'no'

 ), 0)

from dual

) "to be applied",

(

 (select max (sequence#) from v$archived_log where dest_id = 1

 ) -

 (select max (sequence#) from v$archived_log where dest_id = 2

 )) "to be shipped"

from dual;

防止未应用归档却被删除:

configure archivelog deletion policy to applied on all standby;

定期删除归档策略部署参考:

[oracle@racd2 ~]$ crontab -l

0 12 * * * /home/oracle/clear_archivelog.sh

[oracle@racd2 ~]$ cat clear_archivelog.sh

#!/bin/sh

source ~/.bash_profile

export oracle_sid=xxx1

/u01/app/oracle/product/19.9.0/db_1/bin/rman target / >> /home/oracle/clear_archive20190412.log << eof

delete noprompt archivelog all completed before 'sysdate - 15';

exit

eof