本文详细介绍了如何在Oracle 11g环境中手动和自动设置数据库镜像（ADG）。内容涵盖从创建监听、配置tnsnames.ora和listener.ora，到验证主备库连接，再到修改数据库参数，创建备用重做日志，直至最终的主备库实时同步。文中包含关键命令和配置示例，适合DBA参考。
1m. 登录主
2m/s. 在主备库同时创建静态监听ltnsnames
主库的tnsnames.ora 添加备库TNS , 不需要重启 lsnrctl
备库设定listener.ora（参考主）需重启LSNRCTL，tnsnames.ora 添加主和备库TNS
主庫：
[oracle@cos6M5]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = testdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@cos6M5]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
TESTDB_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb_std)
)
)
TESTDB_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
[oracle@cos6M5 ~]$ cat /home/oracle/product/11.2.2/dbhome_1/network/admin/listener.ora
#有 ODBC 連接的
# listener.ora Network Configuration File: /home/oracle/product/11.2.2/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sfcdb)
(ORACLE_HOME = /home/oracle/product/11.2.2/dbhome_1)
(SID_NAME = sfcdb)
)
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = /home/oracle/product/11.2.2/dbhome_1)
(PROGRAM = dg4msql)
)
(SID_DESC =
(SID_NAME = MSSQL)
(ORACLE_HOME = /home/oracle/product/11.2.2/dbhome_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521)) ## 有雙網卡
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
)
)
#啟動監控 LSNRCTL
[oracle@cos6M5]$ lsnrctl start/stop
備庫類似。
4m/s. 验证主可tnsping 备，反之同。 如：
[oracle@cos6M5 ~]$ tnsping tsmaster_st
5m. 创建主库归档/备份目录
[oracle@cos6M5 ~]$ mkdir -p /data/oradata/TSMASTER/archivelog
6s. 创建备库归档目录, $ORACLE_BASE/admin下的目錄一般要手工建
[oracle@cos6s5 ~]$ mkdir -p /data/oradata/TSMASTER/controlfile
[oracle@cos6s5 ~]$ mkdir -p /data/oradata/TSMASTER/archivelog
[oracle@cos6s5 ~]$ mkdir -p /home/oracle/admin/tsmaster/adump
[oracle@cos6s5 ~]$ chown -R oracle:dba /home/oracle/admin/
7m. 修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。 用于所有的事务都可以通过归档进行回滚
[oracle@cos6M5 ~]$ sqlplus / as sysdba
set linesize 1000;
set pagesize 1000;
SQL> alter database force logging;
SQL> alter database archivelog;
Database altered. ## 一般主机都有开启规档模式，先查看，如果没有开启，再作业
SQL> SELECT name,log_mode,force_logging FROM v$database; --验证设定成功的状态
NAME LOG_MODE FOR
--------- ------------ ---
TSMASTER ARCHIVELOG YES
M_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ----------------
SFCDB READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
S_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
SFCDB READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
7.1m.查看Log 及logfile
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 13 52428800 512 2 NO CURRENT 1127786 22-SEP-21 2.8147E+14
3 1 12 52428800 512 2 YES INACTIVE 1107440 22-SEP-21 1127786 22-SEP-21
2 1 11 52428800 512 2 YES INACTIVE 1087159 22-SEP-21 1107440 22-SEP-21
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- -------
3 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_3_jm118n37_.log
3 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_3_jm118n7t_.log
2 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_2_jm118lds_.log
2 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_2_jm118lgt_.log
1 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_1_jm118jyx_.log
1 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_1_jm118k26_.log
6 rows selected.
8m. 创建standby redo log，一般与 online SID log对应并多一个文件
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data/oradata/TSMASTER/onlinelog/tsm04.log') size 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data/oradata/TSMASTER/onlinelog/tsm05.log') size 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/data/oradata/TSMASTER/onlinelog/tsm06.log') size 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/data/oradata/TSMASTER/onlinelog/tsm07.log') size 50M;
Database altered.
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ------------------------------------------------------------------------------------------------------------------------
3 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_3_jm118n37_.log
3 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_3_jm118n7t_.log
2 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_2_jm118lds_.log
2 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_2_jm118lgt_.log
1 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_1_jm118jyx_.log
1 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_1_jm118k26_.log
4 STANDBY /data/oradata/TSMASTER/onlinelog/tsm04.log
5 STANDBY /data/oradata/TSMASTER/onlinelog/tsm05.log
6 STANDBY /data/oradata/TSMASTER/onlinelog/tsm06.log
7 STANDBY /data/oradata/TSMASTER/onlinelog/tsm07.log
10 rows selected.
例如，以下SQL语句可用于在要从具有两个重做线程的重做源数据库接收重做的数据库上创建备用重做日志：
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
9m. Oracle Pfile參數文件设定：
9m.1a 重启主库的参数变更方案a： 非使用状态，可以重启
[oracle@cos6M5]$ sqlplus / as sysdba
SQL> create pfile from spfile;
SQL> shutdown immediate;
[oracle@cos6M5]$ vim $ORACLE_HOME/dbs/inittestdb.ora
testdb.__db_cache_size=687865856
testdb.__java_pool_size=16777216
testdb.__large_pool_size=33554432
testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=671088640
testdb.__sga_target=989855744
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=234881024
testdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/TESTDB/controlfile/o1_mf_gq3chycs_.ctl','/u01/app/oracle/fast_recover
y_area/TESTDB/controlfile/o1_mf_gq3chyhs_.ctl'*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle'
*.db_domain=''
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1660944384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
以下為新增：
DB_UNIQUE_NAME=testdb_pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_pri'
LOG_ARCHIVE_DEST_2='SERVICE=testdb_std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_std'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=testdb_std
STANDBY_FILE_MANAGEMENT=AUTO
变更后，使用文件值导入 DB 并生效 spfile
[oracle@cos6M5]$ sqlplus / as sysdba
SQL>startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb.ora';
SQL>create spfile from pfile;
9m.1b 主库生产状态的变更方案b： 使用状态，DB不重启
-- 查看 DB_UNIQUE_NAME，如无设定此参数与db_name 同 此参数如有变理需要重启服务器生效
-- DB_UNIQUE_NAME 参数指本机TNS，要与后面的统一，避免混乱，如统一使用 testdb_pri
-- standby 激活或停ENABLE / DEFER
SQL> show parameter UNIQUE
SQL> show parameter LOG_ARCHIVE_FORMAT
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope = spfile; 静态参数變更，變更后需重啟DB生效
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_pri'
SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=testdb_std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_std';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> alter system set FAL_SERVER='testdb_std';
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO ; ---manual
SQL> alter system switch logfile;
9m.2 将主库的参数文件inittestdb.ora, 秘钥文件orapwtestdb Copy到备库的 $ORACL_HOME/dbs/ 目录下
[oracle@cos6M5]$scp {inittestdb.ora,orapwtestdb} oracle@196.168.1.1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
9s.3 備庫參數：
[oracle@cos6s5]$ vi $ORACLE_HOME/dbs/inittestdb.ora
主备信息指向变更其他不需变更：
[]# grep Huge /proc/meminfo #查詢大内存页”的详细内容
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
从上面输出可以看到，每个页的大小为 2MB（Hugepagesize），并且系统中目前有 0 个“大内存页”（HugePages_Total）。这里“大内存页”的大小可以从 2MB 增加到 1GB
# 一般分配缓存做DB的Buff，越大越好，通常 cache+ Share pool <sga_target
tjb2bdb.__db_cache_size=8589934592
tjb2bdb.__shared_pool_size=6006243328
DB_UNIQUE_NAME=testdb_std
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/TESTDB/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb_std'
LOG_ARCHIVE_DEST_2='SERVICE=testdb_pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_pri'
FAL_SERVER=testdb_pri
#如果主备目录不一致需添加， *.db_file_name_convert= 主数据库数据文件目录，备用数据库数据文件目录，如
DB_FILE_NAME_CONVERT='/u01/app/oracle/TESTDB/','/usr/oracle/app/oradata/TESTDB/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/TESTDB/','/usr/oracle/app/oradata/TESTDB/'
#2G 依据连线最大量来判断 2000 内的连线，一般一个连接占用2KB左右。如果分配给太多，未使用则释放，但没必要
pga_aggregate_target=2147483648
#分配DB使用内存的方案，一般占HOst内存的60%~70% 最少50%；如果有多个实例，需按资料量合理分配占比
sga_max_size=16G
sga_target=15G
[oracle@cos6s5]$ cat $ORACLE_HOME/dbs/inittestdb.ora
testdb.__db_cache_size=687865856
testdb.__java_pool_size=16777216
testdb.__large_pool_size=33554432
testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=671088640
testdb.__sga_target=989855744
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=234881024
testdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/TESTDB/controlfile/o1_mf_gq3chycs_.ctl','/u01/app/oracle/fast_recovery_area/TESTDB/controlfile/o1_mf_gq3chyhs_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle'
*.db_domain=''
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1660944384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=testdb_std
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_pri,testdb_std)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/TESTDB/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=testdb_std'
LOG_ARCHIVE_DEST_2='SERVICE=testdb_pri LGWR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_pri'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=testdb_pri
STANDBY_FILE_MANAGEMENT=AUTO
用pfile啟動，并生效Pfile
[oracle@cos6s5]$ sqlplus / as sysdba
S_SQL> startup nomount pfile='$ORACLE_HOME/dbs/inittestdb.ora';
S_SQL> create spfile from pfile;
10m. 使用RMAN 的RUN生成主库的 database/controlfile/spfile 文件，手动复制主库文件到备库，再手动将文件中的资料恢復到备库中(自动则主不需做此操作)
10m.1 主库：
[oracle@cos6M5 ~]$ rman target /
RMAN>run{
allocate channel c1 device type disk; --手动分配一个通道 参考：https://blog.youkuaiyun.com/cys_firefly/article/details/9229009
allocate channel c2 device type disk;
backup as compressed backupset database format '/data/oradata/TSMASTER/controlfile/full_backup_%d_%T_%U.bak'; --备份整个数据库并压缩备份集
backup spfile format ='/data/oradata/TSMASTER/controlfile/spfile_backup_%s_%p_%s.ora';
sql 'alter system switch logfile';
backup current controlfile for standby format '/data/oradata/TSMASTER/controlfile/sty_ctlfile_backup_%s_%p_%s.ctl';
backup archivelog from time='sysdate-0.5' until time 'sysdate' format = '/data/oradata/TSMASTER/controlfile/archivelog_%n_arch_%T_%U.bak';
release channel c1; --释放通道
release channel c2;
}
10m.2 把備份文件傳送到備庫相同目錄下
[oracle@cos6M5 ~]$ scp -rp /data/oradata/TSMASTER/controlfile/* oracle@备库IP:/data/oradata/TSMASTER/controlfile/
11s. 在备服务器上恢复控制参数及数据
11s.1a 手动使用备份文件恢复数据库，备库是 nomount 状态,pfile一般都指定对应data / log 对应目录
[oracle@cos6s5 ~]$ rman target /
RMAN>restore standby controlfile from '/home/oracle/TESTDB/backup/sty_ctlfile_backup_9_1_9.ctl';
RMAN> catalog start with '/data/dbbak/dbbak/'; ### 当主库备份到文件命令中（backup as compressed backupset database format '目录'） 使用的目录与备库上的bak目录不一致，则需这个命令对照
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database; #导入 achive log 文件，此处是导入最后一个archive log 出错，是因主库最后使用中的log文件未导出，不用管， 使用 Achive LOG同步和实时员步完成无缝衔接
11s.1b 自动克隆并备份数据库
[oracle@centos6s ~]$ rman target sys/Foxconn123@testdb_pri auxiliary sys/Foxconn123@testdb_std
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 17 00:52:29 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MMIS (DBID=1366936292) ## 主庫一定是正常 DBID= ID
connected to auxiliary database: MMIS (not mounted) ## 備庫一定是 startup nomount
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 17-DEC-21
......
datafile 32 switched to datafile copy
input datafile copy RECID=181 STAMP=1091499539 file name=/data/oradata/mmis/sysaux03.dbf
Finished Duplicate Db at 17-DEC-21
12s. 在备服务器上 将DB设为只读，启动实时同步
[oracle@cos6s5]$ sqlplus / as sysdba
#直接读取 archive logfile 中的数据，即同步主库 archive log文件中的数据
S_SQL> alter database recover managed standby database disconnect from session;
#取消同步 Job
S_SQL> alter database recover managed standby database cancel;
#将备库设定为只读
S_SQL>alter database open read only;
#直接从 主库的 内存里同步，即实时同步主库数据
S_SQL> alter database recover managed standby database using current logfile disconnect from session;
M_SQL> alter system switch logfile;
System altered.
S_SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
## SEQUENCE# APP 如果有NO 则提示有未应用的Log，此时无法切换
M/S_SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/TSMASTER/archivelog
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17 ##如果Standby Server上的值是 0 ，说明参数有问题，再Check先同步AchiveLog再实时同步
验证：
SQL> create table testc(id number(10),name varchar2(20));
Table created.
SQL> insert into testc values(1,'testc');
1 row created.
SQL> insert into testc values(2,'testc');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
SQL> archive log list;
#########################################################################
## 如下实例
主： M3： 196.168.1.1
备：S3: 196.168.1.2
#########################################################################
#########################################################################
主： M3： 196.168.1.1
[oracle@cos6m5 ~]$ cat /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tsmaster)
(ORACLE_HOME = /home/oracle/product/11.2.4/dbhome_1)
(SID_NAME = tsmaster)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/oracle/
[oracle@cos6m5 ~]$ vi /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
[oracle@cos6m5 ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2021 14:11:50
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /home/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Log messages written to /home/oracle/diag/tnslsnr/cos6m5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=196.168.1.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 11-OCT-2021 14:11:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/diag/tnslsnr/cos6m5/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=196.168.1.1)(PORT=1521)))
Services Summary...
Service "tsmaster" has 1 instance(s).
Instance "tsmaster", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@cos6m5 ~]$ cat /home/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
tsmaster_st =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tsmaster)
)
)
tsmaster =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tsmaster)
)
)
tsmaster_pd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tsmaster)
)
)
[oracle@cos6m5 ~]$ sqlplus / as sysdba
SQL> select * from csong;
ID NAME
---------- --------------------
1 Csong
2 Lyuanyuan
4 Lyuanyuan4
3 Lyuanyuan3
SQL> set linesize 1000;
SQL> set pagesize 1000;
SQL> ---------- --------------------
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
SQL> SELECT name,log_mode,force_logging FROM v$database;
NAME LOG_MODE FOR
--------- ------------ ---
TSMASTER ARCHIVELOG YES
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 28 52428800 512 2 NO CURRENT 1219357 11-OCT-21 2.8147E+14
2 1 26 52428800 512 2 YES INACTIVE 1174668 25-SEP-21 1198514 11-OCT-21
3 1 27 52428800 512 2 YES INACTIVE 1198514 11-OCT-21 1219357 11-OCT-21
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_3_jm118n37_.log
3 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_3_jm118n7t_.log
2 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_2_jm118lds_.log
2 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_2_jm118lgt_.log
1 ONLINE /data/oradata/TSMASTER/onlinelog/o1_mf_1_jm118jyx_.log
1 ONLINE /data/fast_recovery_area/TSMASTER/onlinelog/o1_mf_1_jm118k26_.log
4 STANDBY /data/oradata/TSMASTER/onlinelog/tsm04.log
5 STANDBY /data/oradata/TSMASTER/onlinelog/tsm05.log
6 STANDBY /data/oradata/TSMASTER/onlinelog/tsm06.log
7 STANDBY /data/oradata/TSMASTER/onlinelog/tsm07.log
10 rows selected.
SQL> show parameter archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string DG_CONFIG=(tsmaster_pd,tsmaste
r_st)
log_archive_dest string
log_archive_dest_1 string LOCATION=/data/oradata/TSMASTE
R/archivelog VALID_FOR=(ALL_L
OGFILES,ALL_ROLES) DB_UNIQUE_
NAME=tsmaster_pd
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
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=tsmaster_st ASYNC VALI
D_FOR=(ONLINE_LOGFILES,PRIMARY
_ROLE) DB_UNIQUE_NAME=tsmaster
_st
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string ENABLE
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
standby_archive_dest string ?/dbs/arch
SQL> create pfile from spfile;
File created.
SQL> alter database create standby controlfile as '/data/control01.ctl';
Database altered.
SQL> quit
[oracle@cos6m5 ~]$ cat /home/oracle/product/11.2.4/dbhome_1/dbs/inittsmaster.ora
tsmaster.__db_cache_size=411041792
tsmaster.__java_pool_size=4194304
tsmaster.__large_pool_size=8388608
tsmaster.__oracle_base='/home/oracle'#ORACLE_BASE set from environment
tsmaster.__pga_aggregate_target=209715200
tsmaster.__sga_target=633339904
tsmaster.__shared_io_pool_size=0
tsmaster.__shared_pool_size=197132288
tsmaster.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/tsmaster/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/TSMASTER/controlfile/o1_mf_jm118fy2_.ctl','/data/fast_recovery_area/TSMASTER/controlfile/o1_mf_jm118g5t_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain=''
*.db_name='tsmaster'
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='tsmaster_pd'
*.diagnostic_dest='/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tsmasterXDB)'
*.FAL_SERVER='tsmaster_st'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(tsmaster_pd,tsmaster_st)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/oradata/TSMASTER/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tsmaster_pd'
*.log_archive_dest_2='SERVICE=tsmaster_st ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tsmaster_st'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=839909376
*.open_cursors=300
*.processes=800
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sessions=885
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@cos6m5 ~]$ scp /home/oracle/product/11.2.4/dbhome_1/dbs/inittsmaster.ora oracle@196.168.1.2:/home/oracle/product/11.2.4/dbhome_1/dbs/
The authenticity of host '196.168.1.2 (196.168.1.2)' can't be established.
RSA key fingerprint is 2f:ce:54:cf:76:56:d5:7b:a6:5d:1e:1b:29:2e:f6:2b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '196.168.1.2' (RSA) to the list of known hosts.
oracle@196.168.1.2's password:
inittsmaster.ora 100% 1513 1.5KB/s 00:00
[oracle@cos6m5 ~]$ scp /home/oracle/product/11.2.4/dbhome_1/dbs/orapwtsmaster oracle@196.168.1.2:/home/oracle/product/11.2.4/dbhome_1/dbs/
oracle@196.168.1.2's password:
orapwtsmaster 100% 1536 1.5KB/s 00:00
[oracle@cos6m5 ~]$ scp /data/control01.ctl oracle@196.168.1.2:/data/oradata/TSMASTER/controlfile/
oracle@196.168.1.2's password:
control01.ctl 100% 9552KB 9.3MB/s 00:00
[oracle@cos6m5 ~]$ mkdir /data/oradata/TSMASTER/archivelog
### 使用RMAN 的RUN生成主库在的 database/controlfile/spfile 文件，命令如下：################
```
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset database format '/data/oradata/TSMASTER/controlfile/full_backup_%d_%T_%U.bak';
backup spfile format ='/data/oradata/TSMASTER/controlfile/spfile_backup_%s_%p_%s.ora';
sql 'alter system switch logfile';
backup current controlfile for standby format '/data/oradata/TSMASTER/controlfile/sty_ctlfile_backup_%s_%p_%s.ctl';
backup archivelog from time='sysdate-0.5' until time 'sysdate' format = '/data/oradata/TSMASTER/controlfile/archivelog_%n_arch_%T_%U.bak';
release channel c1;
release channel c2;
}
```
### 使用RMAN 的RUN生成主库在的 database/controlfile/spfile 文件，命令如上：################
[oracle@cos6m5 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 11 14:53:25 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TSMASTER (DBID=172793101)
RMAN>run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset database format '/data/oradata/TSMASTER/controlfile/full_backup_%d_%T_%U.bak';
backup spfile format ='/data/oradata/TSMASTER/controlfile/spfile_backup_%s_%p_%s.ora';
sql 'alter system switch logfile';
backup current controlfile for standby format '/data/oradata/TSMASTER/controlfile/sty_ctlfile_backup_%s_%p_%s.ctl';
backup archivelog from time='sysdate-0.5' until time 'sysdate' format = '/data/oradata/TSMASTER/controlfile/archivelog_%n_arch_%T_%U.bak';
release channel c1;
release channel c2;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=20 device type=DISK
allocated channel: c2
channel c2: SID=627 device type=DISK
Starting backup at 11-OCT-21
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/data/oradata/TSMASTER/datafile/o1_mf_system_jm116d22_.dbf
input datafile file number=00004 name=/data/oradata/TSMASTER/datafile/o1_mf_users_jm116d3r_.dbf
channel c1: starting piece 1 at 11-OCT-21
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/data/oradata/TSMASTER/datafile/o1_mf_sysaux_jm116d2s_.dbf
input datafile file number=00003 name=/data/oradata/TSMASTER/datafile/o1_mf_undotbs1_jm116d3d_.dbf
channel c2: starting piece 1 at 11-OCT-21
channel c2: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_070bc0aj_1_1.bak tag=TAG20211011T145514 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:25
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
including current control file in backup set
channel c2: starting piece 1 at 11-OCT-21
channel c2: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_080bc0bc_1_1.bak tag=TAG20211011T145514 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c2: starting piece 1 at 11-OCT-21
channel c1: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_060bc0ai_1_1.bak tag=TAG20211011T145514 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:31
channel c2: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_090bc0bg_1_1.bak tag=TAG20211011T145514 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-21
Starting backup at 11-OCT-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 11-OCT-21
channel c1: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/spfile_backup_10_1_10.ora tag=TAG20211011T145547 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-21
sql statement: alter system switch logfile
Starting backup at 11-OCT-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 11-OCT-21
channel c1: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/sty_ctlfile_backup_11_1_11.ctl tag=TAG20211011T145549 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-21
Starting backup at 11-OCT-21
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=26 RECID=30 STAMP=1085653653
input archived log thread=1 sequence=27 RECID=31 STAMP=1085654007
input archived log thread=1 sequence=28 RECID=32 STAMP=1085667992
channel c1: starting piece 1 at 11-OCT-21
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=29 RECID=33 STAMP=1085667995
input archived log thread=1 sequence=30 RECID=34 STAMP=1085669749
channel c2: starting piece 1 at 11-OCT-21
channel c1: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/archivelog_TSMASTER_arch_20211011_0c0bc0bn_1_1.bak tag=TAG20211011T145551 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 11-OCT-21
piece handle=/data/oradata/TSMASTER/controlfile/archivelog_TSMASTER_arch_20211011_0d0bc0bn_1_1.bak tag=TAG20211011T145551 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 11-OCT-21
released channel: c1
released channel: c2
RMAN> exit
[oracle@cos6m5 ~]$ ls -l /data/oradata/TSMASTER/controlfile/
总用量 334076
-rw-r----- 1 oracle oinstall 40637952 10月 11 14:55 archivelog_TSMASTER_arch_20211011_0c0bc0bn_1_1.bak
-rw-r----- 1 oracle oinstall 980480 10月 11 14:55 archivelog_TSMASTER_arch_20211011_0d0bc0bn_1_1.bak
-rw-r----- 1 oracle oinstall 202694656 10月 11 14:55 full_backup_TSMASTER_20211011_060bc0ai_1_1.bak
-rw-r----- 1 oracle oinstall 76865536 10月 11 14:55 full_backup_TSMASTER_20211011_070bc0aj_1_1.bak
-rw-r----- 1 oracle oinstall 1097728 10月 11 14:55 full_backup_TSMASTER_20211011_080bc0bc_1_1.bak
-rw-r----- 1 oracle oinstall 98304 10月 11 14:55 full_backup_TSMASTER_20211011_090bc0bg_1_1.bak
-rw-r-----. 1 oracle oinstall 9781248 10月 11 14:57 o1_mf_jm118fy2_.ctl
-rw-r----- 1 oracle oinstall 98304 10月 11 14:55 spfile_backup_10_1_10.ora
-rw-r----- 1 oracle oinstall 9830400 10月 11 14:55 sty_ctlfile_backup_11_1_11.ctl
[oracle@cos6m5 ~]$ scp -rp /data/oradata/TSMASTER/controlfile/* oracle@196.168.1.2:/data/oradata/TSMASTER/controlfile/
oracle@196.168.1.2's password:
archivelog_TSMASTER_arch_20211011_0c0bc0bn_1_1.bak 100% 39MB 38.8MB/s 00:00
archivelog_TSMASTER_arch_20211011_0d0bc0bn_1_1.bak 100% 958KB 957.5KB/s 00:00
full_backup_TSMASTER_20211011_060bc0ai_1_1.bak 100% 193MB 96.7MB/s 00:02
full_backup_TSMASTER_20211011_070bc0aj_1_1.bak 100% 73MB 36.7MB/s 00:02
full_backup_TSMASTER_20211011_080bc0bc_1_1.bak 100% 1072KB 1.1MB/s 00:00
full_backup_TSMASTER_20211011_090bc0bg_1_1.bak 100% 96KB 96.0KB/s 00:00
o1_mf_jm118fy2_.ctl 100% 9552KB 9.3MB/s 00:00
spfile_backup_10_1_10.ora 100% 96KB 96.0KB/s 00:00
sty_ctlfile_backup_11_1_11.ctl 100% 9600KB 9.4MB/s 00:00
#############################################################################
备：S3: 196.168.1.2
[oracle@cos6s5 ~]$ mkdir -p /data/oradata/TSMASTER/controlfile
[oracle@cos6s5 ~]$ mkdir -p /data/oradata/TSMASTER/archivelog
[oracle@cos6s5 ~]$ mkdir -p /home/oracle/admin/tsmaster/adump
[oracle@cos6s5 ~]$ chown -R oracle:dba /home/oracle/admin/
[oracle@cos6s5 ~]$ rm -rf /data/oradata/TSMASTER/datafile/*
[oracle@cos6s5 ~]$ rm -rf /data/oradata/TSMASTER/onlinelog/*
[oracle@cos6s5 ~]$ cat /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tsmaster)
(ORACLE_HOME = /home/oracle/product/11.2.4/dbhome_1)
(SID_NAME = tsmaster)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.2)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/oracle/
[oracle@cos6s5 ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2021 14:15:22
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /home/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Log messages written to /home/oracle/diag/tnslsnr/cos6s5/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=196.168.1.2)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 11-OCT-2021 14:15:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/diag/tnslsnr/cos6s5/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=196.168.1.2)(PORT=1521)))
Services Summary...
Service "tsmaster" has 1 instance(s).
Instance "tsmaster", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@cos6s5 ~]$ cat /home/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TSMASTER_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tsmaster)
)
)
TSMASTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tsmaster)
)
)
TSMASTER_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 196.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tsmaster)
)
)
[oracle@cos6s5 ~]$ cat /home/oracle/product/11.2.4/dbhome_1/dbs/inittsmaster.ora
tsmaster.__db_cache_size=411041792
tsmaster.__java_pool_size=4194304
tsmaster.__large_pool_size=8388608
tsmaster.__oracle_base='/home/oracle'#ORACLE_BASE set from environment
tsmaster.__pga_aggregate_target=209715200
tsmaster.__sga_target=633339904
tsmaster.__shared_io_pool_size=0
tsmaster.__shared_pool_size=197132288
tsmaster.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/tsmaster/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/TSMASTER/controlfile/o1_mf_jm118fy2_.ctl','/data/fast_recovery_area/TSMASTER/controlfile/o1_mf_jm118g5t_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain=''
*.db_name='tsmaster'
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='tsmaster_st'
*.diagnostic_dest='/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tsmasterXDB)'
*.FAL_SERVER='tsmaster_pd'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(tsmaster_pd,tsmaster_st)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/oradata/TSMASTER/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tsmaster_st'
*.log_archive_dest_2='SERVICE=tsmaster_pd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tsmaster_pd'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=839909376
*.open_cursors=300
*.processes=800
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sessions=885
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@cos6s5 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL> startup nomount pfile='/home/oracle/product/11.2.4/dbhome_1/dbs/inittsmaster.ora';
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 717229096 bytes
Database Buffers 113246208 bytes
Redo Buffers 6549504 bytes
SQL> create spfile from pfile;
File created.
SQL> quit
[oracle@cos6s5 ~]$ rman target /
RMAN> restore standby controlfile from '/data/oradata/TSMASTER/controlfile/sty_ctlfile_backup_11_1_11.ctl';
Starting restore at 11-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=621 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/TSMASTER/controlfile/o1_mf_jm118fy2_.ctl
output file name=/data/fast_recovery_area/TSMASTER/controlfile/o1_mf_jm118g5t_.ctl
Finished restore at 11-OCT-21
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 11-OCT-21
Starting implicit crosscheck backup at 11-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=625 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 11-OCT-21
Starting implicit crosscheck copy at 11-OCT-21
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 11-OCT-21
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /data/oradata/TSMASTER/datafile/o1_mf_sysaux_jm116d2s_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/oradata/TSMASTER/datafile/o1_mf_undotbs1_jm116d3d_.dbf
channel ORA_DISK_1: reading from backup piece /data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_070bc0aj_1_1.bak
channel ORA_DISK_1: piece handle=/data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_070bc0aj_1_1.bak tag=TAG20211011T145514
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/oradata/TSMASTER/datafile/o1_mf_system_jm116d22_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/oradata/TSMASTER/datafile/o1_mf_users_jm116d3r_.dbf
channel ORA_DISK_1: reading from backup piece /data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_060bc0ai_1_1.bak
channel ORA_DISK_1: piece handle=/data/oradata/TSMASTER/controlfile/full_backup_TSMASTER_20211011_060bc0ai_1_1.bak tag=TAG20211011T145514
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 11-OCT-21
RMAN>exit
[oracle@cos6s5 ~]$ sqlplus / as sysdba
SQL> alter database open read only;
Database altered.
SQL> select * from csong;
ID NAME
---------- --------------------
1 Csong
2 Lyuanyuan
4 Lyuanyuan4
3 Lyuanyuan3
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
## 以上完成 ADG 设定 #####################
## 以下在主库上添加记录 52 52Lyuanyuan， 再来看备库是理同步如下#######
SQL> select * from csong;
ID NAME
---------- --------------------
1 Csong
2 Lyuanyuan
4 Lyuanyuan4
3 Lyuanyuan3
52 52Lyuanyuan
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/TSMASTER/archivelog
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17 -- 比对主备最后的 sequence 是否一致，一致代表同步成功
扫一扫
1588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
