S7

### Skill Set 7:

**Database Name User/Password**
PROD1 sh/sh、hr/hr、oltp_user/oltp_user1、sys/oracle、system/oracle
PROD2/PROD3 sys/oracle、system/oracle
PROD4 sys/oracle、system/oracle、rc_admin/RC_ADMIN
EMREP sys/Oracle123, system/Oracle123, sysman/Oracle123

---

### TASKS:

**1: Creating a Physical Standby Database**

**1: 创建物理备用数据库**

Use the following specifications to create the physical standby database:
使用以下规格创建物理备用数据库：

1.1. The ORACLE_SID should be SBDB1 for the standby database to be created on the Management Server.
为在管理服务器上创建的备用数据库，ORACLE_SID 应为 SBDB1。
1.2. The entire production database PROD1 should be included in the physical standby database.
整个生产数据库 PROD1 应包含在物理备用数据库中。
1.3. Specify the new directory location as /u01/app/oracle/oradata/SBDB1 for the data files and online redo log for the standby database.
将新的目录位置指定为 /u01/app/oracle/oradata/SBDB1，用于备用数据库的数据文件和在线重做日志。
1.4. Allow remote connections to the SBDB1 database. Example: CONNECT sys/oracle@SBDB1 as sysdba should connect to the SBDB1 database using a dedicated server connection.
允许远程连接到 SBDB1 数据库。例如：使用 CONNECT sys/oracle@SBDB1 as sysdba 应该通过专用服务器连接到 SBDB1 数据库。

```sql
开归档

--打开强制日志
select force_logging from v$database;
alter database force logging;
alter system set diagnostic_dest='/u01/app/oracle';
alter database flashback off;
drop table sh.prod_master;

--监听配置
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1521))
))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD1.us.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD1)))

lsnrctl stop
lsnrctl start

添加tnsname
SBDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521)) (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SBDB1.us.oracle.com)
))

--增加 standby logfile 文件（大小相同、比主库多一组）
select group#, member from v$logfile;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo04.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo05.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo06.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo07.log' size 50m;

--配置pfile
create pfile from spfile;
shutdown  immediate

修改pfile
 vim initPROD1.ora
DB_UNIQUE_NAME=PROD1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SBDB1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=SBDB1
DB_FILE_NAME_CONVERT='SBDB1','PROD1'
LOG_FILE_NAME_CONVERT='SBDB1','PROD1'
STANDBY_FILE_MANAGEMENT=AUTO

用新的pfile创建spfile，启动数据库
create spfile from pfile;
startup

备库
--配置静态监听
/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521))
))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=SBDB1.us.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=SBDB1)))

重启监听

主库拷贝来pfile、tnsname.ora、密码文件,密码文件改名
修改pfile
可以sed
:%s#PROD1#AAAA#g
:%s#SBDB1#PROD1#g
:%s#AAAA#SBDB1#g

DB_UNIQUE_NAME=SBDB1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB1,PROD1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SBDB1'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PROD1
DB_FILE_NAME_CONVERT='PROD1','SBDB1'
LOG_FILE_NAME_CONVERT='PROD1','SBDB1'
STANDBY_FILE_MANAGEMENT=AUTO

--备库创建目录
mkdir -p /u01/app/oracle/oradata/SBDB1/
mkdir -p /u01/app/oracle/admin/SBDB1/adump
mkdir /home/oracle/flash

主库恢复到备库
export ORACLE_SID=SBDB1
rman target / auxiliary sys/oracle@sbdb1
duplicate target database for standby from active database;

select DATABASE_ROLE from v$database;

备库
recover managed standby database cancel；
recover managed standby database using current logfile disconnect from session;
```

---

**2: Test the Standby Database Perform the following tasks:**

**2: 测试备用数据库，执行以下任务：**

2.1. Configure the standby database SBDB1 to be used for testing, including updates, and make no changes to the database.
配置备用数据库 SBDB1 用于测试，包括更新，但不对数据库进行更改。
```
show parameter recover;
--开启闪回
select FLASHBACK_ON from v$database;
alter database convert to snapshot standby; 
select open_mode from v$database;
startup
 
```
2.2. Run the sbdb1.sql script to perform updates on the SBDB1 database.
运行 sbdb1.sql 脚本以对 SBDB1 数据库进行更新。
```

```
---

**3: Restore the Standby Database**

**3: 恢复备用数据库**

Restore the standby database to its normal function for disaster recovery.

将备用数据库恢复到正常功能以进行灾难恢复。

3.1. Restore the standby database to be used as a reporting server and ensure that it is open for querying.
恢复备用数据库，使其作为报告服务器使用，并确保可以进行查询。
```sql
备库
shutodun
startup mount;
alter database convert to physical standby;
startup

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

```

3.2. Configure managed recovery so that changes to the primary database are reflected in the standby as soon as possible.
配置管理恢复，以便主数据库的更改尽快反映在备用数据库中。
```sql
recover managed standby database cancel；
recover managed standby database using current logfile disconnect from session;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
--实时同步
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

```
3.3. Reduce the overheads of fast incremental backups to a minimum on the primary database.
将主数据库上快速增量备份的开销降到最低。
```sql
alter database enable block change tracking using file '/u01/app/oracle/oradata/SBDB1/block_change_tracke.log';

select * from v$block_change_tracking;
```
3.4. Archive logs must not be deleted on the primary database until they have been transported to the standby database.
在主数据库上，归档日志在传输到备用数据库之前不得删除。
```sql
export ORACLE_SID=PORD1
rman target sys/oracle@PROD1
show all;

CONFIGURE ARCHIVELOG DELETION POLICY TO applied on all standby;
```
---

**4: Configure Physical Standby Database**

**4: 配置物理备用数据库**

4.1 Create the Service, known as SBDB1SRV, this service can be started automatically only if EMREP is used as a physical standby database.
创建服务，称为 SBDB1SRV，此服务仅在 EMREP 被用作物理备用数据库时可以自动启动。
```sql
crs_stat -t -v
./srvctl add database -d EMREP -o  /u01/app/oracle/product/11.2.0/db_1/
./srvctl add service -s SBDB1SRV -d EMREP -l physical_standby
crs_stat -t -v
srvctl start database -d EMREP -o mount
crs_stat -t -v
 
 
```
4.2 Configure connect time failover for database guard configuration. Configure the PRODSRV local net service for connection to the standby database in case of switchover.
配置数据库保护配置的连接时间故障转移。为切换情况下连接备用数据库配置 PRODSRV 本地网络服务。
```sql
tnsname.ora
PRODSRV =
(DESCRIPTION=
(ADDRESS_LIST=
(FAILOVER=on)
(LOAD_BALANCE=off)
(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p1.example.com)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p2.example.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=PRODSRV.us.oracle.com)))

--主库
show parameter service;
alter system set service_names='PROD1.us.oracle.com','PRODSRV';
show parameter service_names;

```
---

**5: Configure the Observer and Connect Time Failover (Configuration Fast-start Failover)**

**5: 配置观察者和连接时间故障转移（配置快速启动故障转移）**

5.1. Configure Protection Mode.
配置保护模式。
```sql
主备都开启dg_broker
show parameter dg_broker;
alter system set dg_broker_start=true;

dgmgrl
connect sys
create configuration 'drsolution' as primary database is 'PROD1' connect identifier is PROD1;
show configuration;
add database 'SBDB1' as connect identifier is SBDB1;

enable configuration;
enable database 'SBDB1';
show configuration;
show database 'PROD1';
show database 'SBDB1';
```
5.2. Configure the Observer and connect time failover.
配置观察者和连接时间故障转移。
```
主
ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SHOW PARAMETER UNDO;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH; ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET db_recovery_file_dest_size=4g;
alter system set db_recovery_file_dest='/home/oracle/flash';
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

dgmgrl
connect sys/oracle
start observer;

新窗口
开启闪回
dgmgrl
connect sys/oracle
enable fast_start failover;
show fast_start failover;

```

DBLOG

导航1234

最近发表

友情链接