一、主库操作
[oracle@o12u19p ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 18 14:41:34 2024
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
service_names string orcl
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Current log sequence 17
SQL>
1.1开归档改归档路径开强记日志
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6174015488 bytes
Fixed Size 2938016 bytes
Variable Size 1241516896 bytes
Database Buffers 4915724288 bytes
Redo Buffers 13836288 bytes
Database mounted.
SQL> alter database archivelog;
alter database force logging;
alter database open;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17
SQL>
1.2主库创建日志目录
[oracle@o12u19p adump]$ cd /u01/app/oracle/oradata/orcl
[oracle@o12u19p orcl]$ mkdir -p arch
[oracle@o12u19p orcl]$ ls -ld /u01/app/oracle/oradata/orcl/arch/
drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:17 /u01/app/oracle/oradata/orcl/arch/
1.3主库修改参数
alter system set log_archive_config='dg_config=(orcl,orcldg)' scope=spfile;
alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;
alter system set log_archive_dest_2='service=orcldg valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=spfile;
alter system set standby_file_management='auto' scope=spfile;
alter system set fal_server='orcldg' scope=spfile;
alter system set fal_client='orcl' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcl/' scope=spfile ;
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/arch/','/u01/app/oracle/oradata/orcldg/arch/' scope=spfile;
1.4主库增加standbylog
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6174015488 bytes
Fixed Size 2938016 bytes
Variable Size 1241516896 bytes
Database Buffers 4915724288 bytes
Redo Buffers 13836288 bytes
Database mounted.
Database opened.
SQL>
SQL> select group#,type,member from v$logfile;
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/orcl/redo04.log
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
SQL> select bytes/1024/1024,group#,thread# from v$log;
BYTES/1024/1024 GROUP# THREAD#
--------------- ---------- ----------
50 1 1
50 2 1
50 3 1
50 4 1
alter database add standby logfile
group 5 ('/u01/app/oracle/oradata/orcl/arch/standby_redo05.log') size 50m reuse,
group 6 ('/u01/app/oracle/oradata/orcl/arch/standby_redo06.log') size 50m reuse,
group 7 ('/u01/app/oracle/oradata/orcl/arch/standby_redo07.log') size 50m reuse,
group 8 ('/u01/app/oracle/oradata/orcl/arch/standby_redo08.log') size 50m reuse,
group 9 ('/u01/app/oracle/oradata/orcl/arch/standby_redo09.log') size 50m reuse;
select group#,type,member from v$logfile;
1.5生成pfile 将pfile和密码文件拷到备库
SQL> create pfile from spfile;
File created.
[oracle@o12u19p dbs]$ scp initorcl.ora orapworcl oracle@o12u19s:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs
oracle@o12u19s's password:
initorcl.ora 100% 1535 1.5KB/s 00:00
orapworcl 100% 7680 7.5KB/s 00:00
[oracle@o12u19p dbs]$
3.2备库操作
3.2.1备库修改spfile
[oracle@o12u19s ~]$ cd $ORACLE_HOME/dbs
[oracle@o12u19s dbs]$ ls
init.ora initorcl.ora orapworcl
[oracle@o12u19s dbs]$ vi initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=4781506560
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=2063597568
orcl.__sga_target=6174015488
orcl.__shared_io_pool_size=318767104
orcl.__shared_pool_size=1006632960
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/'
*.db_name='orcl'
*.db_unique_name='orcldg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10240m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.fal_client='orcldg'
*.fal_server='orcl'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcldg/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_file_name_convert='/u01/app/oracle/oradata/orcldg/arch/','/u01/app/oracle/oradata/orcl/arch/'
*.open_cursors=300
*.pga_aggregate_target=1960m
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5880m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
二、备库操作
2.1备库修改spfile
[oracle@o12u19s ~]$ cd $ORACLE_HOME/dbs
[oracle@o12u19s dbs]$ ls
init.ora initorcl.ora orapworcl
[oracle@o12u19s dbs]$ vi initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=4781506560
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=2063597568
orcl.__sga_target=6174015488
orcl.__shared_io_pool_size=318767104
orcl.__shared_pool_size=1006632960
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/'
*.db_name='orcl'
*.db_unique_name='orcldg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10240m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.fal_client='orcldg'
*.fal_server='orcl'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcldg/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_file_name_convert='/u01/app/oracle/oradata/orcldg/arch/','/u01/app/oracle/oradata/orcl/arch/'
*.open_cursors=300
*.pga_aggregate_target=1960m
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5880m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
2.2创建相关目录
cd /u01/app/oracle
mkdir -p oradata/orcldg/arch
mkdir -p fast_recovery_area
mkdir -p admin/orcl/adump
[oracle@o12u19s oracle]$ ls -ld /u01/app/oracle/fast_recovery_area
drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:43 /u01/app/oracle/fast_recovery_area
[oracle@o12u19s oracle]$ ls -ld /u01/app/oracle/oradata/orcldg/arch
drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:42 /u01/app/oracle/oradata/orcldg/arch
[oracle@o12u19s oracle]$ ls -ld /u01/app/oracle/admin/orcl/adump/
drwxr-xr-x 2 oracle oinstall 6 Jun 18 15:43 /u01/app/oracle/admin/orcl/adump/
[oracle@o12u19s oracle]$
2.3备库配置静态监听
cd $ORACLE_HOME/network/admin
cd samples/
cp * $ORACLE_HOME/network/admin
cd ..
listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@o12u19s admin]$ vi listener.ora
LISTENER_ORCLDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.121)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/dbhome_1)
(SID_NAME = orcldg)
)
)
[oracle@o12u19s admin]$ lsnrctl start
2.4主备库均配置 tnsnames
[oracle@o12u19s admin]$ vi tnsnames.ora
[oracle@o12u19s admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.120)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(SERVER = DEDICATED)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.121)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldg)
(SERVER = DEDICATED)
)
)
2.5修改参数文件名和密码文件名
[oracle@o12u19s admin]$ cd $ORACLE_HOME/dbs
[oracle@o12u19s dbs]$ ls
init.ora initorcl.ora orapworcl
[oracle@o12u19s dbs]$ mv initorcl.ora initorcldg.ora
[oracle@o12u19s dbs]$ mv orapworcl orapworcldg
[oracle@o12u19s dbs]$ ls
init.ora initorcldg.ora orapworcldg
[oracle@o12u19s dbs]$
2.6测试登录
[oracle@o12u19s dbs]$ sqlplus sys/oracle_4U@orcldg as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 16:00:18 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@o12u19s dbs]$ sqlplus sys/oracle_4U@orcl as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 16:00:26 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
2.7备库启动到nomount状态下
[oracle@o12u19s dbs]$ export ORACLE_SID=orcldg
[oracle@o12u19s dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 18 16:01:20 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 6174013840 bytes
Fixed Size 9148816 bytes
Variable Size 1056964608 bytes
Database Buffers 5100273664 bytes
Redo Buffers 7626752 bytes
[oracle@o12u19s ~]$ cd /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/
[oracle@o12u19s trace]$ tail -f alert_orcldg.log
三、主库备份
主库做备份
mkdir -p /backup
chown -R oracle:oinstall /backup/
[root@o12u19p ~]# su - oracle
Last login: Tue Jun 18 15:23:09 CST 2024 on pts/3
[oracle@o12u19p ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 18 16:06:34 2024
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1699750341)
RMAN> rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/backup/data_%d_%T_%s_%p' tag 'data';
sql 'alter system archive log current';
backup archivelog all format='/backup/arch_%d_%T_%s_%p' tag 'arc';
backup current controlfile for standby format='/backup/ctl_%d_%T_%s_%p' tag 'cur';
release channel c1;
release channel c2;
}
四、备库恢复
[oracle@o12u19p ~]$ cd /backup/
[oracle@o12u19p backup]$ ls
arch_ORCL_20240618_32_1 arch_ORCL_20240618_34_1 data_ORCL_20240618_25_1 data_ORCL_20240618_27_1 data_ORCL_20240618_29_1
arch_ORCL_20240618_33_1 ctl_ORCL_20240618_35_1 data_ORCL_20240618_26_1 data_ORCL_20240618_28_1 data_ORCL_20240618_30_1
[oracle@o12u19p backup]$ scp * oracle@o12u19s:/backup
[oracle@o12u19s ~]$ cd /backup/
[oracle@o12u19s backup]$ ls
arch_ORCL_20240618_32_1 arch_ORCL_20240618_34_1 data_ORCL_20240618_25_1 data_ORCL_20240618_27_1 data_ORCL_20240618_29_1
arch_ORCL_20240618_33_1 ctl_ORCL_20240618_35_1 data_ORCL_20240618_26_1 data_ORCL_20240618_28_1 data_ORCL_20240618_30_1
[oracle@o12u19s backup]$ export ORACLE_SID=orcldg
[oracle@o12u19s backup]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 18 16:13:11 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN>
restore standby controlfile from '/backup/ctl_ORCL_20240618_35_1';
alter database mount;
catalog start with "/backup";
Run{
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel c1;
release channel c2;
}
SQL> select group#,type,member from v$logfile;
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/orcl/redo04.log
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
5 STANDBY
/u01/app/oracle/oradata/orcl/arch/standby_redo05.log
6 STANDBY
/u01/app/oracle/oradata/orcl/arch/standby_redo06.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
7 STANDBY
/u01/app/oracle/oradata/orcl/arch/standby_redo07.log
8 STANDBY
/u01/app/oracle/oradata/orcl/arch/standby_redo08.log
9 STANDBY
/u01/app/oracle/oradata/orcl/arch/standby_redo09.log
9 rows selected.
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database clear unarchived logfile group 4;
Database altered.
五、开启日志应用
alter database recover managed standby database disconnect from session;