rman 创建物理备库

1、ora19c.oracle.com主机中的 PROD 作为主库， SID 为 PROD， DB_UNIQUE_NAME

设置为 PROD

2、emcc.oracle.com主机中的 SBDB作为备库，SID 为 SBDB， DB_UNIQUE_NAME 设置为 SBDB

3、备库文件路径 /u01/app/oracle/oradata/SBDB

	主库	备库
db_name	PROD	PROD
db_unique_name	PROD	SBDB
net service name	PROD	SBDB
SID/instance_name	PROD	SBDB
Service_name	PROD.oracle.com	SBDB.oracle.com
hostname	ora19c.oracle.com	emcc.oracle.com
ip	192.168.100.191	192.168.100.192

一、主库开归档

startup mount;
alter database archivelog;
alter database open;

二、FORCE LOGGING

SYS@PROD>ALTER DATABASE FORCE LOGGING;
SYS@PROD>select log_mode,force_logging from v$database;

 

三、主库 添加 standby 日志文件

SYS@PROD>select group#,bytes/1024/1024 MB from v$log;

 添加 standby 日志文件

ALTER DATABASE ADD STANDBY LOGFILE'/u01/app/oracle/oradata/PROD/redostd01.log' size 200m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/PROD/redostd02.log' size 200m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/PROD/redostd03.log' size 200m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/PROD/redostd04.log' size 200m;

 

四、创建主库 pfile 文件

SYS@PROD>shutdown immediate;
SYS@PROD>create pfile from spfile;

五、修改 pfile 文件

cd $ORACLE_HOME/dbs
vi initPROD.ora

DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,SBDB)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=PROD'

LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB5 ASYNC
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=SBDB'

FAL_SERVER=SBDB
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SBDB','/u01/app/oracle/oradata/PROD'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SBDB','/u01/app/oracle/oradata/PROD'
STANDBY_FILE_MANAGEMENT=AUTO

删除新参数中 db_name 、 control_files 、 remote_login_passwordfile

删除原参数中 log_archive_dest_1

新参数中 log_archive_dest_1 可以使用指定归档目录

修改原参数中 local_listener=''

六、创建 spfile,重新启库

SYS@PROD>create spfile from pfile;
SYS@PROD>startup force;

七、主库开告警日志  开库  查参数

[oracle@ora19c dbs]$ tail -f /u01/app/oracle/diag/rdbma/prod/PROD/trace/alter_PROD.log

SYS@PROD>startup
SYS@PROD>show parameter name

八、备库修改备库环境变量、拷贝参数文件、密码文件

 

[oracle@ora19c dbs]$scp initPROD.ora 192.168.100.192:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initSBDB.ora
[oracle@ora19c dbs]$scp orapwPROD 192.168.100.192:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwSBDB

修改备库参数文件 vi initSBDB.ora

:%s/SBDB/AAAA/g

:%s/PROD/SBDB/g

:%s/AAAA/PROD/g

物理备库，主备库 db_name 相同

备库创建目录、备库

备库开告警日志

创建 spfile 重启实例到 nomount 查看参数确认

九、配置监听

主库 listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
 (SID_LIST=
  (SID_DESC=
    (GLOBAL_DBNAME=PROD.oracle.com)
    (SID_NAME=PROD)
    (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
  )
 )

备库 listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = emcc.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST=
  (SID_DESC=
    (GLOBAL_DBNAME=SBDB.oracle.com)
    (SID_NAME=SBDB)
    (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
  )
 )

主库tnsnames.ora

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.oracle.com)
    )
  )

SBDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.192)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SBDB.oracle.com)
    )
  )

备库tnsnames.ora

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.191)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.oracle.com)
    )
  )
SBDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.192)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SBDB.oracle.com)
    )
  )

开监听：lsnrtart

十、测试网络

[oracle@ora19c ~] sqlplus sys/oracle@prod5 as sysdba
[oracle@ora19c ~] sqlplus sys/oracle@sbdb5 as sysdba
[oracle@emcc ~]$ sqlplus sys/oracle@prod5 as sysdba
[oracle@emcc ~]$ sqlplus sys/oracle@sbdb5 as sysdba

十一、rman duplicate 创建物理备库

rman登录主备库

[oracle@ora19c ~]$ rman target sys/oracle@prod auxiliary sys/oracle@sbdb

执行 rman duplicate

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

十二、查看主备库状态

SYS@PROD>select db_unique_name,database_role,protection_mode,open_mode from v$database;
SYS@SBDB>select db_unique_name,database_role,protection_mode,open_mode from v$database;
SYS@SBDB>alter database open;
SYS@SBDB>select db_unique_name,database_role,protection_mode,open_mode from v$database;

 

十三、主库切归档测试 

SYS@PROD>alter system switch logfile;
SYS@PROD>select group#,sequence#,status from v$log;

SYS@SBDB>select group#,sequence#,status from v$log;
SYS@SBDB>select group#,sequence#,status from v$standby_log;

 主库多次切归档 备库查看