[Oracle] Data Guard series (4)-create a physical standby database without stopping the master database

[Oracle] Data Guard series (4)-creating a physical standby database without stopping the master database needs to create a physical standby database of DG for a 24x7 production database, the master database must not be shut down. At first, it was impossible. Later, I checked the document and used the rman duplicate command to create a physical standby database online. The information of the master and slave databases is as follows: the network connection string of the master database jkka lx32 jkkapri slave database jkka jkka2 lx201 jkkasta 1. preparations for the master database 1) Make sure that archive and force loggingDG are enabled for the master database. You can use the following statement to query: [SQL] SYS @ JKKA> select log_mode, force_logging from v $ database; LOG_MODE FOR ------------ --- archivelog no from above we found that the archive has been enabled FOR the master database, but force logging has not been enabled. You can use the following statement to enable it: [SQL] SYS @ JKKA> alter database force logging; Database altered. 2) Create the master database and password file. Password files need to communicate with each other. We first create a password file on the master database, and then synchronize it to the slave database: [plain] $ orapwd file = $ ORACLE_HOME/dbs/orapwjkka confirm that the remote_login_passwordfile parameter is EXCLUSIVE (default ): [SQL] SYS @ JKKA> show parameter remote_login_passwordfile NAME TYPE VALUE ----------- using remote_login_passwordfile string EXCLUSIVE re-confirm the sys user in the password file through v $ pwfile_users: [SQL] SYS @ JKKA> select * from v $ pwfile_u Sers; username sysdb sysop sysas ------------------------------ ----- sys true false 3) configure the master database parameters first, view the db_name and db_unique_name of the master database: [SQL] SYS @ JKKA> show parameter db_name NAME TYPE VALUE types ------------- ---------------------------- db_name string jkka SYS @ JKKA> show parameter db_unique_name NAME TYPE VALUE types ------------------------------------------------ --------------------------- Db_unique_name string jkka. Then, modify the log_acchive_config parameter and write db_unique_name of the master database and slave database in the dg_config attribute: [SQL] SYS @ JKKA> alter system set log_archive_config = 'dg _ CONFIG = (jkka, jkka2) '; System altered. configure log_archive_dest_1 to write data to the local directory: [SQL] SYS @ JKKA> alter system set log_archive_dest_1 = 'location =/data/oradata/jkka/archivelog valid_for =) db_unique_name = jkka '; System altered. SYS @ JKKA> alter system set log_archive_dest_state_1 = enable; System altered. configure log_archive_dest_2 to write data to the remote directory, because no slave database is available at this time. Here we temporarily set log_archive_dest_state_2 to defer: [SQL] SYS @ JKKA> alter system set lifecycle = defer; System altered. SYS @ JKKA> alter system set log_archive_dest_2 = 'service = jkkasta ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name = jkka2 '; System altered. the following parameters are optional only when the master database It is valid when switched to the standby database, but it is strongly recommended that you configure it. Otherwise, the switchover of the primary and standby databases will fail in the future. [SQL] SYS @ JKKA> alter system set fal_server = jkka2; System altered. SYS @ JKKA> alter system set fal_client = jkka; System altered. SYS @ JKKA> alter system set standby_file_management = auto; System altered. finally, create pfile [SQL] SYS @ JKKA> create pfile from spfile; File created. 2. start slave database instance 1) install the database software and install the Oracle software. Note that the version of the slave database must be the same as that of the master database. (Note: The version here must be the same as the minor version. For example, if the master database is 11.2.0.1 and the slave database is 11.2.0.3, it won't work.) 2) create a parameter file for the slave database. You can modify the parameter file for the slave database according to the parameter file of the master database. We have generated the pfile for the master database, all we need to copy it to the slave database and then modify the following parameters: [plain] db_unique_name = 'jkka2 'fal_client = 'jkka2' fal_server = 'jkka 'servers = 'location =/data/oradata/JKKA/archivelog valid_for = (all_logfiles, all_roles) db_unique_name = jkka2 'log_archive_dest_2 = 'service = jkkapri ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) db_uniq Ue_name = jkka '3) copy the password file of the master database to the slave database 4) the directories that need to be created in the same directory structure as the master database on the slave database include log files, control files, data files, archived logs, and audit directories. 5) Start the slave database to the nomount status [SQL] $ export ORACLE_SID = jkka $ sqlplus/as sysdba SQL> startup nomount 4. network settings of the master and slave databases 1) LISTENER settings of the master database [plain] LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 )) (ADDRESS = (PROTOCOL = TCP) (HOST = lx32) (PORT = 1521) 2) set the LISTENER of the standby database to [plain] LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521) (ADDRESS = (PROT OCOL = TCP) (HOST = lx201) (PORT = 1521) 3) master-slave database tnsnames. ora setting [plain] jkkasta = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = lx201) (PORT = 1521 ))) (CONNECT_DATA = (sid = jkka) jkkapri = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = lx32) (PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (sid = jkka) 4) test whether the master and slave databases can communicate with each other. Because the master and slave databases already have password files, all tests can be performed using sys user remote Logon: [plain] master database: $ sq Lplus sys @ jkkasta as sysdba slave Database: $ sqlplus sys @ jkkapri as sysdba 4. copy the master database to the slave database. We can use the rman duplicate command to create the slave database without stopping the master database. The premise is that the directory structure of the slave database is identical to that of the master database. 1) create a master database to back up and back up the full database and archive logs: [SQL] $ rman target/RMAN> backup full format = '/home/oracle/backup/ora_bak/data/db_jkka _ % U' database include current controlfile for standby plus archivelog format = '/home/oracle/backup/ora_bak/data/arc_jkka _ % U '; after the backup is successful, manually copy the backup file to the standby Database Host (scp, ftp, etc ). 2) Before restoring the slave database, make sure that the backup file is in the same directory as the master database. 2, the corresponding directories such as the backup inventory data files already exist. 3. the standby database is in the nomount status [SQL] $ rman target sys/*** @ jkkapri AUXILIARY/RMAN> duplicate target database for standby dorecover nofilenamecheck; after the preceding statement is executed, data files, log files, and audit are generated in the corresponding directory, and the database is in the mount state. The following error may occur: [SQL] ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed ORA-00312: online log 1 thread 1: '/data/oradata/jkka/data/redoa1.log' RMAN-05535: WARNING: All redo log files were not defined properly. this is because oracle has added an enhanced function from 10.2 to accelerate the speed of switchover between the standby database and the primary database, that is, when MRP is started, it will clean up the online redo log on the standby database. If you do not consider switchover (do not create online reod log on the slave database), you can ignore this error because this error is only a prompt message and does not affect the MRP work of the slave database. If you consider switchover, you need to create an online reod log on the slave database. If you need to open the database, see step 5th. [SQL] <pre> </pre> 3) create standby redo logOracle standby redo log [SQL] SYS @ JKKA> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/data/oradata/jkka/data/standby_redo10.log ') size 100 M; Database altered. SYS @ JKKA> alter database add standby logfile group 11 ('/data/oradata/jkka/data/standby_redo11.log') size 100 M; Database altered. SYS @ JKKA> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/data/oradata/jkka/data/standby_redo12.log') size 100 M; Database altered. SYS @ JKKA> alter database add standby logfile group 13 ('/data/oradata/jkka/data/standby_redo13.log') size 100 M; Database altered. 4) Create the spfile of the slave database, restart the slave database to the mount or open state from 11 GB, the physical slave database can be started to moutn, you can also start open [SQL] SQL> create spfile from pfile; File created. SQL> shutdown immediate SQL> startup 5) rebuild the online redo log of the standby database [SQL] <span sty Le = "font-weight: normal;"> SYS @ jkka> alter system set standby_file_management = manual; System altered. SYS @ jkka> alter system set LOG_FILE_NAME_CONVERT = '/data/oradata/jkka/data/', '/data/oradata/jkka/data/' scope = spfile; System altered. shutdown immediate; startup; SYS @ jkka> alter database clear logfile group 1; Database altered. SYS @ jkka> alter database clear logfile group 2; Database altered. SYS @ j Kka> alter database clear logfile group 3; Database altered. SYS @ jkka> alter system reset LOG_FILE_NAME_CONVERT; System altered. shutdown immediate; startup; SYS @ jkka> alter system set standby_file_management = auto; System altered. </span> 5. the standby database starts redo apply because we previously set log_archive_dest_state_2 to defer. Now we need to set it to enable in the primary and standby databases: [SQL] SYS @ JKKA> alter system set log_archive_dest_state_2 = enable; System altered. now everything All are ready. You can start the log application in the slave DATABASE: [SQL] SQL> ALTER DATABASE RECOVER MANAGED STANDBY Database DISCONNECT FROM SESSION; DATABASE altered. next, verify whether the master and slave databases can be synchronized. First, check the archive logs of the slave Database: [SQL] SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #; SEQUENCE # FIRST_TIME NEXT_TIME ---------- ----------------- --------------------- 504 2013-08-22 14:41:54 2013-08-22 20:32:39 From the above we can see that the maximum archive log of the slave database is 504. Then, check that the maximum archive log of the master database is 504: [SQL] SYS @ JKKA> select max (sequence #) from v $ archived_log; MAX (SEQUENCE #) -------------- 504 we perform a log switch in the master database. At this time, the maximum archive log of the master database is 505. [SQL] SYS @ JKKA> ALTER SYSTEM SWITCH LOGFILE; System altered. now let's look at the archive logs of the slave database. If 505 has been successfully copied from the master database to the slave database, the synchronization is normal. [SQL] SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #; SEQUENCE # FIRST_TIME NEXT_TIME ---------- timeout limit 504 14:41:54 20:32:39 505 20:32:39