rman搭建dg环境
主库目录结构：
[oracle@localhost ~]$ cd /oradata/dbtest/
[oracle@localhost dbtest]$ ls
control data redo
[oracle@localhost dbtest]$ cd /opt/app/oracle/admin/
[oracle@localhost admin]$ ls
dbtest
[oracle@localhost admin]$ cd dbtest/
[oracle@localhost dbtest]$ ls
adump bdump cdump dpdump pfile udump
[oracle@localhost dbtest]$ cd /opt/app/
[oracle@localhost app]$ ls
backup oracle
在搭建环境不熟悉情况下备库目录结构和主库保持一致
主库：
[oracle@localhost ~]$ orapwd file=/opt/app/oracle/product/10.2.1/db_1/dbs/orapwdbtest password=dbtest entries=5
[oracle@localhost response]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 22 13:27:45 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/app/oracle/product/10.2.1/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 62916756 bytes
Database Buffers 117440512 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/product/10.2.1/db_1/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>
SQL> create pfile='/opt/app/backup/pfile.ora' from spfile='/opt/app/oracle/product/10.2.1/db_1/dbs/spfiledbtest.ora';
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 22 16:20:23 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBTEST (DBID=1115208984)
RMAN> backup database format '/opt/app/backup/test%U.bak';
SQL> alter database create standby controlfile as '/opt/app/backup/control01.ctl';
Database altered.
SQL> alter system set log_archive_dest_1='location=/opt/app/backup';
System altered.
SQL> alter system set log_archive_dest_2='service=standby';
System altered.
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=/opt/app/backup
log_archive_dest_10 string
log_archive_dest_2 string service=standby
[oracle@localhost ~]$ ls /opt/app/backup/
pfile.ora test01lgsegm_1_1.bak control01.ctl test02lgsei4_1_1.bak
[oracle@localhost backup]$ scp /opt/app/backup/* root@192.168.1.226:/opt/app/backup/
在备库：
[oracle@localhost ~]$ cd /opt/app/backup/
[oracle@localhost backup]$ ls
control01.ctl pfile.ora test01lgsegm_1_1.bak test02lgsei4_1_1.bak
[oracle@localhost backup]$ cp control01.ctl /oradata/dbtest/control/
cp: cannot open `control01.ctl' for reading: Permission denied
[oracle@localhost backup]$ su - roo t
su: user roo does not exist
[oracle@localhost backup]$ su - root
Password:
[root@localhost ~]#
[root@localhost ~]# chown -R oracle.oinstall /opt/app/
[root@localhost ~]# su - oracle
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ cd /opt/app/oracle/
[oracle@localhost oracle]$ ls
admin oraInventory product
[oracle@localhost oracle]$ cd ..
[oracle@localhost app]$ cd backup/
[oracle@localhost backup]$ ls
control01.ctl pfile.ora test01lgsegm_1_1.bak test02lgsei4_1_1.bak
[oracle@localhost backup]$ cp control01.ctl /oradata/dbtest/control/
[oracle@localhost backup]$ cp control01.ctl /oradata/dbtest/control/control02.ctl
[oracle@localhost backup]$ cp control01.ctl /oradata/dbtest/control/control03.ctl
[oracle@localhost backup]$ ls /opt/app/oracle/admin/dbtest/
adump/ bdump/ cdump/ dpdump/ pfile/ udump/
[oracle@localhost backup]$ ls
control01.ctl pfile.ora test01lgsegm_1_1.bak test02lgsei4_1_1.bak
[oracle@localhost ~]$ vim /opt/app/backup/pfile.ora
dbtest.__db_cache_size=117440512
dbtest.__java_pool_size=4194304
dbtest.__large_pool_size=4194304
dbtest.__shared_pool_size=54525952
dbtest.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/dbtest/adump'
*.background_dump_dest='/opt/app/oracle/admin/dbtest/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oradata/dbtest/control/control01.ctl','/oradata/dbtest/control/control02.ctl','/oradata/dbtest/control/control03.ctl'
*.core_dump_dest='/opt/app/oracle/admin/dbtest/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dbtest'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbtestXDB)'
*.job_queue_processes=10
*.nls_language='AMERICAN'
*.open_cursors=300
*.pga_aggregate_target=59768832
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=181403648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/dbtest/udump'
###add on standby
*.log_archive_dest_1='location=/opt/app/backup/'
*.log_archive_dest_2='service=PRIMARY'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.standby_file_management='auto'
[oracle@localhost ~]$ orapwd file=/opt/app/oracle/product/10.2.1/db_1/dbs/orapwdbtest password=dbtest entries=5
[oracle@localhost backup]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 22 07:05:32 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/opt/app/backup/pfile.ora;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 62916756 bytes
Database Buffers 117440512 bytes
Redo Buffers 2973696 bytes
SQL> alter database mount standby database;
Database altered.
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 22 07:07:46 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBTEST (DBID=1115208984, not open)
RMAN> restore database
2> ;
Starting restore at 22-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/dbtest/data/system01.dbf
restoring datafile 00002 to /oradata/dbtest/data/undotbs01.dbf
restoring datafile 00003 to /oradata/dbtest/data/sysaux01.dbf
restoring datafile 00004 to /oradata/dbtest/data/users01.dbf
restoring datafile 00005 to /oradata/dbtest/data/test01.dbf
channel ORA_DISK_1: reading from backup piece /opt/app/backup/test01lgsegm_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/app/backup/test01lgsegm_1_1.bak tag=TAG20100622T133102
channel ORA_DISK_1: restore complete, elapsed time: 00:01:59
Finished restore at 22-JUN-10
RMAN> exit
开启主库/备库监听：
主库
[oracle@localhost ~]$ vim /opt/app/oracle/product/10.2.1/db_1/network/admin/listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.29)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dbtest)
(SID_NAME=dbtest)
(ORACLE_HOME=/opt/app/oracle/product/10.2.1/db_1)
(PRESPAWN_MAX=20)
(PRESPAWN_LIST=
(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
)
)
)
[oracle@localhost ~]$ vim /opt/app/oracle/product/10.2.1/db_1/network/admin/tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.1.29)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtest)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.1.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtest)
)
)
[oracle@localhost ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 16:52:59
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /opt/app/oracle/product/10.2.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/app/oracle/product/10.2.1/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/product/10.2.1/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.29)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.29)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-JUN-2010 16:52:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/10.2.1/db_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/product/10.2.1/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.29)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Services Summary...
Service "dbtest" has 1 instance(s).
Instance "dbtest", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully
在备库
[oracle@localhost ~]$ vim /opt/app/oracle/product/10.2.1/db_1/network/admin/listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.226)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dbtest)
(SID_NAME=dbtest)
(ORACLE_HOME=/opt/app/oracle/product/10.2.1/db_1)
(PRESPAWN_MAX=20)
(PRESPAWN_LIST=
(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
)
)
)
[oracle@localhost ~]$ vim /opt/app/oracle/product/10.2.1/db_1/network/admin/tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.1.29)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtest)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.1.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbtest)
)
)
[oracle@localhost ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 08:54:28
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /opt/app/oracle/product/10.2.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/app/oracle/product/10.2.1/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/product/10.2.1/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.226)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.226)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-JUN-2010 08:54:29
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/10.2.1/db_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/product/10.2.1/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.226)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Services Summary...
Service "dbtest" has 1 instance(s).
Instance "dbtest", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully
在主库测试：
[oracle@localhost ~]$ tnsping standby
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 16:58:15
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.1.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbtest)))
OK (30 msec)
在备库测试：
[oracle@localhost ~]$ tnsping primary
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 08:55:44
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.1.29)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbtest)))
OK (50 msec)
继续在备库操作：
SQL> alter database recover managed standby database disconnect from session;
Database altered.
在主库：
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 NO
3 NO
4 NO
5 NO
6 NO
7 NO
2 NO
3 NO
4 NO
5 NO
6 NO
SEQUENCE# APP
---------- ---
7 YES
12 rows selected.
SQL> select count(*) from smith.test;
COUNT(*)
----------
1528
SQL> desc smith.test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(10)
CONTENT VARCHAR2(100)
SQL> delete from smith.test where id>500;
1022 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from smith.test;
COUNT(*)
----------
506
SQL> alter system switch logfile;
System altered.
在备库：
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS IDLE
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from smith.test;
COUNT(*)
----------
506
主备切换：
主库：
SQL> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 67111060 bytes
Database Buffers 113246208 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database recover managed standby database disconnect from session;
Database altered.
在备库：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 62916756 bytes
Database Buffers 117440512 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> delete from smith.test where id>100;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from smith.test;
COUNT(*)
----------
106
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 YES
3 YES
4 YES
5 YES
6 YES
7 YES
8 YES
9 YES
10 YES
11 YES
10 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 YES
3 YES
4 YES
5 YES
6 YES
7 YES
8 YES
9 YES
10 YES
11 YES
12 NO
SEQUENCE# APP
---------- ---
12 NO
12 rows selected.
SQL>
在主库：
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 YES
3 YES
4 YES
5 YES
6 YES
7 YES
2 NO
3 NO
4 NO
5 NO
6 NO
SEQUENCE# APP
---------- ---
7 YES
8 YES
8 YES
9 YES
9 YES
10 YES
10 YES
11 YES
11 NO
12 YES
21 rows selected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select count(*) from smith.test;
select count(*) from smith.test
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open read only;
Database altered.
SQL> select count(*) from smith.test;
COUNT(*)
----------
106
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
-------------FAILOVER步骤：
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22804047/viewspace-683100/ ，如需转载，请注明出处，否则将追究法律责任。
