Oracle Dataguard Build Manual ____oracle

Oracle Dataguard Build Manual


First, preparatory work
1, the installation of database software:
Primary and Standby Install database software:
Step 7: Select the recovery area for the database: Tick Specify the Quick Resume option and enable archiving.


2, the parameter opens the file function
Sqlplus/nolog
Conn/as SYSDBA


View Quick reply Area parameters
Show Parameter Db_recovery
Set Quick reply area parameters
Alter system set DB_RECOVERY_FILE_DEST_SIZE=4182M Scope=spfile;
Alter system set db_recovery_file_dest= ' D:\DATAREOV ' scope=spfile;
Check log mode
Archive loglist;
Open Archive Mode
Shutdown immediate;
startup Mount;
ALTER DATABASE Archivelog;
ALTER DATABASE open;
View Quick recovery Area usage space
Select name,space_limit,space_used from V$recovery_file_dest;
Modify the archive log path, and the quick recovery area should not be in the same directory as the archive
Alter system set log_archive_dest_1= ' LOCATION=D:\LOG\ORCL ';
--Check that the archive directory generates files after it is turned on.




Second, the database configuration




1, different database name, different SID.
Main Library:
ip:192.168.199.177
Database name: ORCL
Database SID:ORCL
Db_unique_name:orcl
Database installation path: D:\oracle\A
Data file path: D:\DATABASE
Local Archive path: D:\DATAREOV


Listener.ora
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = ORCL)
(Oracle_home = D:\oracle\A\product\11.2.0\dbhome_1)
(Sid_name = ORCL)
)

)
Tnsname.ora
Orclbak =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.179) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = Orclbak)
)
)


ORCL =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.177) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)


Spfile.ora:
*.db_unique_name=orcl
*.log_archive_config= ' dg_config= (Orcl,orclbak) '
*.log_archive_dest_1= ' Location=d:\datareov\orcl valid_for= (all_logfiles,all_roles) Db_unique_name=ORCL '
*.log_archive_dest_2= ' service=orclbak async valid_for= (online_logfiles,primary_role) Db_unique_name=orclbak '
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orclbak
*.fal_client=orcl
*.standby_file_management=auto


Prepare library:
ip:192.168.199.179
Database name: Orclbak
Database Sid:orclbak
Db_unique_name:orclbak
Database installation path: D:\oracle
Data file path: D:\DATABASE
Local Archive path: D:\DATAREOV


Listener.ora
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = Orclbak)
(Oracle_home = D:\oracle\product\11.2.0\dbhome_1)
(Sid_name = Orclbak)
)

)
Tnsname.ora
Orclbak =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.179) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = Orclbak)
)
)


ORCL =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.177) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)


Spfile.ora:
*.db_unique_name=orclbak
*.log_archive_config= ' dg_config= (Orcl,orclbak) '
*.log_archive_dest_1= ' Location=d:\datareov\orclbak valid_for= (all_logfiles,all_roles) Db_unique_name=ORCLBAK '
*.log_archive_dest_2= ' service=orcl async valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=ORCL '
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.fal_client=orclbak
*.standby_file_management=auto


2, with the database name, with the SID.
Main Library:
ip:192.168.199.177
Database name: ORCL
Database SID:ORCL
Db_unique_name:orcl
Database installation path: D:\oracle\A
Data file path: D:\DATABASE
Local Archive path: D:\DATAREOV


Listener.ora
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = ORCL)
(Oracle_home = D:\oracle\A\product\11.2.0\dbhome_1)
(Sid_name = ORCL)
)

)


Tnsname.ora
Orclbak =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.179) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)


ORCL =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.177) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)


Spfile.ora:
*.db_unique_name=orcl
*.log_archive_config= ' dg_config= (Orcl,orclbak) '
*.log_archive_dest_1= ' Location=d:\datareov\orcl valid_for= (all_logfiles,all_roles) DB_UNIQUE_NAME=ORCL '
*.log_archive_dest_2= ' service=orclbak async valid_for= (online_logfiles,primary_role) Db_unique_name=orclbak '
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orclbak
*.fal_client=orcl
*.standby_file_management=auto








Prepare library:
ip:192.168.199.179
Database name: ORCL
Database SID:ORCL
Db_unique_name:orclbak
Database installation path: D:\oracle
Data file path: D:\DATABASE
Local Archive path: D:\DATAREOV


Listener.ora
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = ORCL)
(Oracle_home = D:\oracle\product\11.2.0\dbhome_1)
(Sid_name = ORCL)
)
)


Tnsname.ora
ORCL =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.177) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)


Orclbak =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.199.179) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)




Spfile.ora:
*.db_unique_name=orclbak
*.log_archive_config= ' dg_config= (Orcl,orclbak) '
*.log_archive_dest_1= ' Location=d:\log\orcl valid_for= (all_logfiles,all_roles) Db_unique_name=orclbak '
*.log_archive_dest_2= ' service=orcl async valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=ORCL '
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.fal_client=orclbak
*.standby_file_management=auto




Second, the main library processing:
1. Adjusting the Database model
--Check the database schema
Select force_logging from V$database;
--Modify Mode
ALTER DATABASE force logging;


2. Set the Standby log file
--Add standby redo Log
Select Group#,member from V$logfile;
--Check log file size
Select Group#,bytes/(1024*1024) from V$log;
--Adding log groups
ALTER DATABASE ADD standby logfile Group 4 ' D:\DATABASE\ORCL\orclbak_redo04.log ' size 50m;
ALTER DATABASE ADD standby logfile Group 5 ' D:\DATABASE\ORCL\orclbak_redo05.log ' size 50m;
ALTER DATABASE ADD standby logfile Group 6 ' D:\DATABASE\ORCL\orclbak_redo06.log ' size 50m;
ALTER DATABASE ADD standby logfile Group 7 ' D:\DATABASE\ORCL\orclbak_redo07.log ' size 50m;
--Check creation results
Select Group#,member from V$logfile where type= ' STANDBY ';


3. Modify the main library Pfile file
--Create Pfile file
Create pfile= ' D:\1.ora ' from SPFile;
--added in 1.ora, and saved. Follow the configuration selection Spfile.ora parameter add




4. Copy the password file to the same path as the standby and modify it to the repository filename.
D:\oracle\A\product\11.2.0\dbhome_1\database\INITorcl.ORA


5. Create a control file for the repository, which requires the database to be open.
--Check Control files
SELECT * from V$controlfile;
--Create the control files used by the repository and copy the control01.ctl copy Control02.ctl and Control03.ctl to the repository.
ALTER DATABASE create standby Controlfile as ' d:\CONTROL01. CTL ';


6. Close the database, copy the files to the standby.
Shutdown immdediate;




Second, prepare the storehouse processing:
1. Set up the database of the database according to the main library.


2. Copy the main library *.dbf, Redo*.log, Std*.log and password files to the repository corresponding file location, and overwrite the original file (the original file can do a backup).


3. Modify the Standby Pfile file
--Create Pfile file
Create pfile= ' D:\1.ora ' from SPFile;
--added in 1.ora, and saved. Follow the configuration selection Spfile.ora parameter add


4. Start a standby
--Boot to Nomount state
Startup pfile= ' D:\1.ora ' nomount;
--Boot to Mount state
ALTER DATABASE Mount Orclbak database;
--Open standby monitoring
ALTER DATABASE recover managed standby database disconnect from session;






Third, start the main reserve library
1. Boot sequence: First start the standby library, then start the main library. Close order: First close the main library, then close the library.
1. The startup repository is unreadable and can be synchronized.
--The main library is started, you can update the parameters in SPFile, and then use the new parameter by default.
Startup pfile= ' D:\1.ora ';
--The standby library is started, this startup method library cannot be accessed as read-only.
Startup pfile= ' D:\1.ora ' nomount;
ALTER DATABASE mount standby database;
--The standby checks whether the RFS program starts
Select Process,pid,status,client_process from V$managed_standby;
--Standby library startup Redo APPLY
ALTER DATABASE recover managed standby database disconnect from session;
--Prepare the library check the program to start the situation, process:mrp0,status:wait_for_log,client_p:n/a.
Select Process,pid,status,client_process from V$managed_standby;


2. The startup standby can be read and synchronized.
--The main library is started, you can update the parameters in SPFile, and then use the new parameter by default.
Startup pfile= ' D:\1.ora ';
--Standby library startup, read-only access after startup
Startup pfile= ' D:\1.ora ' nomount;
ALTER DATABASE mount standby database;
ALTER DATABASE open read only;
Select Process,pid,status,client_process from V$managed_standby;
ALTER DATABASE recover managed standby database disconnect from session;
Select Process,pid,status,client_process from V$managed_standby;
--Check the status of the database, read only with apply read-only mode open can apply redo log, synchronization will take a certain amount of time. Only in read only with apply and mounted state can sync.
Select Open_mode,database_role from V$database;




Four, switch the main reserve library
1, check the main library switching status. PRIMARY to standby no user connection can be switched, SESSIONS ACTIVE
Select Database_role,switchover_status from V$database;
2, in the current main library to initialize the switch to the standby library, the standby will automatically receive the main library
ALTER DATABASE commit to switchover to physical standby with session shutdown wait;
3, will switch to become the main library execution statement does not perform external operations.
Shutdown immediate;
Startup Nomunt;
4. Check the switching status of the primary and standby libraries
Select Database_role,switchover_status from V$database;
5. Start log application in new Standby library
ALTER DATABASE recover managed standby database disconnect from session;
6, check the program to start the situation, process:mrp0,status:wait_for_log,client_p:n/a.
Select Process,pid,status,client_process from V$managed_standby;
7. Check the switching status of the standby library
Select Database_role,switchover_status from V$database;
8. Switch the standby library to the main library
ALTER DATABASE commit to switchover to primary and session shutdown;
9, open the main library
ALTER DATABASE open;
10, check the main library switching status
Select Database_role,switchover_status from V$database;
11, to carry out the detection log, etc. switch success.


V. Related orders
--View pfile boot path
Show Parameter SPFile
Show Parameter Pfile
--Use the Pfile file to update the parameters in SPFile, please back up the original pfile file.
Create SPFile from pfile= ' D:\1.ora ';


--Manually switch logs, triggering commit log to standby
alter system switch logfile;


--Check the main repository query the current log sequence number
Select sequence# from V$log;
--View archived logs
Select name,sequence#,thread# from V$archived_log;
--Check table space
Select name from V$datafile;
--Check log status
Archive log list;
--Check the main library to switch status
--Main Library: PRIMARY to STANDBY
--Prepare library: physical STANDBY not allowed
Select Database_role,switchover_status from V$database;
--Check the status of the database, read only with apply read-only mode open can apply redo log, synchronization will take a certain amount of time. Only in read only with apply and mounted state can sync.
Select Open_mode,database_role from V$database;
--Start the standby application log shipping mode
ALTER DATABASE recover managed standby database disconnect from session;
--Start the standby Application log shipping mode and make the standby apply the archive log immediately
ALTER DATABASE recover managed standby database using current logfile disconnect from session;
Related statements:
--Canceling the standby listener
ALTER DATABASE recover managed standby database cancel;
--View Database protection mode
Select Protection_mode from V$database;
--Change Protection mode
ALTER DATABASE set standby database to maximize protection;
ALTER DATABASE set standby database to maximize availability;
ALTER DATABASE set standby database to maximize Performancen;