Build Oracle DataGuard (using Rman Backup)
Operating system: Windows 2008
Database: 11.1.0.6
I. Information collection
1. Configuration information Collection
Description Host name IP address db_name SID db_unique_name Service_Name
Main Library DG1 192.168.0.11 ORCL ORCL orcldg1 ORCLDG1
Standby library DG2 192.168.0.22 ORCL ORCL orcldg2 orcldg2
2. Main Library Information collection
2.1 Viewing database version information
Sql> SELECT * from V$version;
2.2 Viewing Database status
Sql> SELECT name,open_mode,database_role,force_logging,log_mode,flashback_on from V$database;
sql> SHOW PARAMETER NAME;
Ii. preparatory work
1. The Standby library installs the Oracle database software, does not create the database, guarantees two databases the installation path to be consistent;
Standby Settings Environment variables:
Oracle_home to D:\app\Administrator\product\11.1.0.6\db_1
Oracle_sid to ORCL
2. The repository uses the net Configuration Assistant in the Oracle Configuration and Migration Tool to create the listener;
3. Master Repository Creation Directory
Cmd> MD D:\app\Administrator\product\11.1.0.6\archive\arch
Cmd> MD D:\APP\ADMINISTRATOR\DATABASE\DG
Third, build Dataguard
1. Configuring the Listener.ora and Tnsnames.ora files for the master and standby libraries
1.1 Modify Listener.ora File
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = Plsextproc)
(Oracle_home = D:\app\Administrator\product\11.1.0.6\db_1)
(program = Extproc)
)
# # #主备库都新增内容
(Sid_desc =
(Global_dbname = ORCL)
(Oracle_home = D:\app\Administrator\product\11.1.0.6\db_1)
(Sid_name = ORCL)
)
#####
)
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521))
)
)
Adr_base_listener = D:\app\Administrator
1.2 Modify Tnsnames.ora File
The main repository adds the following:
ORCLDG1 =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521))
)
(Connect_data =
(SERVER = dedicated)
(service_name = ORCLDG1)
)
)
ORCLDG2 =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.22) (PORT = 1521))
)
(Connect_data =
(SERVER = dedicated)
(service_name = ORCLDG2)
)
)
1.3 Rebooting the Master and Standby repository for monitoring
Cmd>lsnrctl stop
Cmd>lsnrctl start
1.4 Checking the Sql*net configuration
Use the following command to check the sql*net configuration in the primary and standby databases
Cmd>tnsping ORCLDG1
Cmd>tnsping ORCLDG2
1.5 Testing Remote Logins
Cmd>sqlplus Sys/[email protected] as Sysdba
Cmd>sqlplus Sys/[email protected] as Sysdba
2. Modify the main library parameters
sql> ALTER SYSTEM SET instance_name= ' ORCL ' scope=spfile; --Can not, use default settings
sql> ALTER SYSTEM SET db_unique_name= ' ORCLDG1 ' scope=spfile;
sql> ALTER SYSTEM SET local_listener= ' ORCLDG1 ' scope=spfile; --with TNS Name, or the default settings are not used
sql> ALTER SYSTEM SET log_archive_config= ' dg_config= (ORCLDG1,ORCLDG2) ' Scope=spfile; --This is db_unique_name!. If the db_unique_name is consistent, this parameter does not require a
sql> ALTER SYSTEM SET log_archive_dest_1= ' Location=d:\app\administrator\product\11.1.0.6\archive\arch LGWR VALID_ For= (all_logfiles,all_roles) db_unique_name=orcldg1 ' scope=spfile;
sql> ALTER SYSTEM SET log_archive_dest_2= ' service=orcldg2 ASYNC valid_for= (online_logfiles,primary_role) DB_UNIQUE _name=orcldg2 ' Scope=spfile;
sql> ALTER SYSTEM SET log_archive_dest_state_1= ' ENABLE ' scope=spfile;
sql> ALTER SYSTEM SET log_archive_dest_state_2= ' DEFER ' scope=spfile;--delay synchronization
sql> ALTER SYSTEM SET standby_file_management= ' AUTO ' scope=spfile;
sql> ALTER SYSTEM SET fal_client= ' ORCLDG1 ' scope=spfile; --With the main library TNS Name
sql> ALTER SYSTEM SET fal_server=orcldg2; scope=spfile;--with library TNS Name
sql> ALTER SYSTEM SET db_file_name_convert= ' D:\app\Administrator\database\dg ', ' D:\APP\ADMINISTRATOR\DATABASE\DG \ ' Scope=spfile;
sql> ALTER SYSTEM SET log_file_name_convert= ' D:\app\Administrator\database\dg ', ' D:\app\Administrator\database\ Dg\ ' Scope=spfile;
sql> ALTER SYSTEM SET standby_archive_dest= ' D:\app\Administrator\product\11.1.0.6\archive\arch ' scope=spfile; --Match the path to log_archive_dest_1 or not, use default settings
sql> ALTER SYSTEM SET log_archive_format= ' Arc_%t_%s_%r.arc ' scope=spfile; --Can not, use default settings
sql> ALTER SYSTEM SET log_archive_max_processes=5 scope=spfile; --Can not, use default settings
Note:
1) in Dataguar, there is no special case. Be sure to set the Standby_file_management parameter to auto and set the values for Db_file_name_convert and Log_file_name_convert.
If standby_file_management=manual, even if the main repository data file path is consistent, the main library to add data files, the repository is not automatically added, must be done manually.
If Standby_file_management=auto, the main library adds data files, the repository automatically creates the data files to the correct path based on the Db_file_name_convert parameter.
2) Db_file_name_convert and Log_file_name_convert are the locations used to convert data and standby log files online. Mainly used in the physical standby database and Rman database replication and TSPITR operations.
Their role is: The Master library path is not the same, the repository will be based on these two parameters to the data file and the redo file to the correct location.
3) Db_file_name_convert and Log_file_name_convert must be directories when specified, cannot be a file, or there will be a ORA-01678 error when starting the database.
3. Main Library enable force logging mode
sql> ALTER DATABASE Force LOGGING;
4. Turn on archive mode
Sql> SHUTDOWN IMMEDIATE;
Sql> STARTUP MOUNT;
sql> ALTER DATABASE ARCHIVELOG;
sql> ALTER DATABASE OPEN;
5. Make a full backup of the main library and transfer the backup set to the same location in the repository
Cmd> RMAN TARGET/
RUN
{
ALLOCATE CHANNEL C0 DEVICE TYPE DISK;
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
CONFIGURE Controlfile autobackup on;
CONFIGURE controlfile autobackup FORMAT for DEVICE TYPE DISK to ' D:\backup\%F ';
BACKUP DATABASE FORMAT ' D:\backup\ora11g_full_db_%d_%T_%u.bak ';
BACKUP ARCHIVELOG all FORMAT ' D:\backup\ora11g_arc_%s_%p_%t.bak ';
}
If the main library backup disk space is not enough can use compressed backup, but the time is slightly longer;
Compress Backup:
Modify the Backup database format in the above script to the backup as compressed database format
The backup takes a long time and the following configuration can continue during the backup process.
6. Copy the password file from the main library to the repository location
Password file default location: D:\app\Administrator\product\11.1.0.6\db_1\database\PWDorcl.ora
If it does not exist, you can generate the file as follows
cmd> orapwd file= ' $ORACLE _home/dbs/pwd%oracle_sid% ' password=xxxx entries=10 force=y
7. Generate a standby pfile file
7.1 Create a standby pfile file in the main library:
sql> CREATE pfile= ' D:\app\Administrator\product\11.1.0.6\db_1\dbs\init2.ora ' from SPFILE;
7.2 Copy the generated Init2.ora to the repository at the appropriate location
7.3 Modify Init2.ora File
Note : The following parameters are modified (as are the parameters that are modified when the main library is configured, the values are specific)
#DG CONFIG
*.log_archive_config= ' dg_config= (ORCLDG1,ORCLDG2) '
*.log_archive_dest_1= ' Location=d:\app\administrator\product\11.1.0.6\archive\arch LGWR VALID_FOR= (ALL_LOGFILES, All_roles) Db_unique_name=orcldg2 '
*.log_archive_dest_2= ' SERVICE=ORCLDG1 ASYNC valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=ORCLDG1 '
*.standby_file_management= ' AUTO '
*.log_archive_dest_state_1= ' ENABLE '
*.log_archive_dest_state_2= ' ENABLE '
*.fal_server=orcldg1
*.db_unique_name=orcldg2
8. Start Standby monitoring
Cmd> Lsnrctl Start
9. Recovery of the Standby repository
Attention:
1) Although this is a recovery repository, the Rman command needs to be executed on the main library;
2) When recovering, copy the archived log files generated on the library to the repository, so that the differential data will be restored to the standby repository when the repository is restored.
Cmd> RMAN TARGET/
rman> CONNECT Auxiliary sys/[email protected]
Rman> DUPLICATE TARGET DATABASE for STANDBY Nofilenamecheck;
10. When the repository is restored, set the log_archive_dest_state_2 of the main library to enable
sql> ALTER SYSTEM SET log_archive_dest_state_2= ' ENABLE ';
11. Add standby Redo log to the main standby library
11.1 Viewing redo log information
View Log Group members
Sql> SELECT MEMBER from V$logfile;
View Log size
Sql> SELECT group,bytes/1024*1024*1024,members from V$log;
View Standby Redo Log
Sql> SELECT group#, BYTES from V$standby_log;
11.2 New Standby Redo Log
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ' D:\app\Administrator\database\dg\stdredo04.log ' SIZE 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ' D:\app\Administrator\database\dg\stdredo05.log ' SIZE 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ' D:\app\Administrator\database\dg\stdredo06.log ' SIZE 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ' D:\app\Administrator\database\dg\stdredo07.log ' SIZE 50M;
Note:
Standby Redo Log Group number reference formula: (online redo Log group number + 1) * Number of database threads, the number of single-machine threads is 1,rac generally 2.
Standby Redo Log has the same number of members and sizes as possible with the online redo log.
12. The Standby library is enabled
Start the standby to the (standby state) Mount mode
sql> ALTER database MOUNT STANDBY database;
Set up a repository for app log status
sql> ALTER database RECOVER MANAGED STANDBY database DISCONNECT from SESSION;
Automatic recovery of the standby library
sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;
Start Standby to ReadOnly mode
sql> ALTER DATABASE OPEN;
Set up a standby to apply log status
sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;
Reference:
Blog.chinaunix.net/uid-23284114-id-4142519.html
blog.itpub.net/25105315/viewspace-710330/
blog.csdn.net/hj402555749/article/details/17654769
blog.itpub.net/12679300/viewspace-1127775/
blog.csdn.net/yuzhenhuan01/article/details/6450565
Blog.chinaunix.net/uid-15108676-id-4134935.html
This article is from the "4699096" blog, please be sure to keep this source http://4709096.blog.51cto.com/4699096/1761537
Build Oracle DataGuard (using Rman Backup)