oracle10g R2 "Single Instance fs→ Single Instance FS"
Environment used in this demo case:
|
Primary |
Standby |
OS Hostname |
Pry |
Std |
OS Version |
RHEL5.8 |
RHEL5.8 |
DB Version |
10.2.0.5 |
10.2.0.5 |
Db_name |
Stephen |
Stephen |
Db_unique_name |
Stephen |
Standby |
Service_names |
Stephen |
Standby |
Instance_name |
Stephen |
Standby |
Primary database Configure1. Enable Primary Force logging
Sql> select force_logging from V$database;
sql> ALTER DATABASE force logging;
2. Configuring Redo Transport Authentication
(1) Tnsnames.ora
STEPHEN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = pry) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = Stephen)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = std) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = standby)
)
)
(2) tnsping command to parse the network service name, the command must return OK.
At this point, the standby side is not configured to listen, can not be resolved, standby configuration and then test.
$ tnsping Standby
(3) password file
If there is no password file, create a password file. Format:orapw<oracle_sid>
CD $ORACLE _home/dbs
Orapwd File=orapwstephen password=oracle Force=y
3. Add Standby LogFiles
Requirements for adding standby logfile:
(1) Ensure that the log file size is the same on the primary and standby databases
(2) Determine the appropriate number of repository redo log file groups
Standby logfile Number = (number of logfile per thread +1) * Number of threads
(3) Check the maxlogfiles and Maxlogmembers parameters specified during CREATE database, which can be viewed by trace controlfile.
(4) RAC Environment Create standby logfile, specify thread# Create
Standby logfile per thread = number of logfile per thread + 1
Check the logfile of the current environment:
Set Lines pages 300
Col Member for A60
Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from V$log a,v$logfile b where a.group#=b.group#
UNION ALL
Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from V$standby_log a,v$logfile b where a.group#=b.group #;
Based on the query's logfile information, add standby logfile requirements above, add a reasonable standby logfile, and then use the SQL above to view the logfile of the current environment again:
sql> ALTER DATABASE Add standby logfile
Group 4 '/u01/oradata/stephen/redo04.log ' size 50M,
Group 5 '/u01/oradata/stephen/redo05.log ' size 50M,
Group 6 '/u01/oradata/stephen/redo06.log ' size 50M,
Group 7 '/u01/oradata/stephen/redo07.log ' size 50M;
4. Modify the primary parameter file
Backup SPFile:
Create pfile= '/tmp/pfile.bak ' from SPFile;
Modify the following parameters, where Stephen,standby is the db_unqiue_name/tns-alias of Primary,standby, see the Detailed Parameters section:
Alter system set log_archive_config= ' dg_config= (stephen,standby) ';
Alter system set log_archive_dest_1= ' Location=/u01/arch valid_for= (all_logfiles,all_roles) Db_unique_name=stephen ';
Alter system set log_archive_dest_2= ' Service=standby lgwr async valid_for= (online_logfiles,primary_role) db_unique_ Name=standby ';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30; #根据需求调整个数
Confirm the following parameter default values, if not the following parameter values, modify:
Alter system set remote_login_passwordfile=exclusive Scope=spfile; #exclusive/shared
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
The Log_archive_format parameter controls the format of the archive file name, with the default value ending with. DBF, which is proposed to be modified to the end of. Arc in order to differentiate it from datafile:
Alter system set log_archive_format= '%t_%s_%r.arc ' scope=spfile;
When primary switches to the standby role, the following parameters need to be modified, which is recommended to configure:
alter system set Fal_server=standby;
alter system set Fal_client=stephen;
alter system set Standby_file_management=auto;
# Primary and standby the datafile and logfile storage paths are different, set the following parameters:
Alter system set db_file_name_convert= '/oradata/standby ', '/u01/oradata/stephen ' scope=spfile;
Alter system set log_file_name_convert= '/oradata/standby ', '/u01/oradata/stephen ' scope=spfile;
Note: The above parameters, specified SPFile modified, the instance restarts after the effective.
5. Open Archive mode
First verify that the current database is an archive mode, and if not, turn on archive mode.
Archive log list;
Shutdown immediate;
startup Mount;
ALTER DATABASE Archivelog;
Archive log list;
ALTER DATABASE open;
6. Backup Primary Database
You can use the previous backup, but you need to ensure that the archive has not been lost since the backup.
Rman Target/<<eof
Run{allocate channel C1 type disk;
Allocate channel C2 type disk;
Backup full database format '/backup/full_%u.bak ' plus archivelog;
SQL ' alter system switch logfile ';
Backup current controlfile for standby format '/backup/c_%u.bak ';
Release channel C1;
Release channel C2;
}
Eof
Standby database configure1. Configuring environment variables
Export Oracle_base=/u01/app/oracle
Export Oracle_home= $ORACLE _base/product/10.2/db_1
Export Oracle_sid=standby
Export path= $ORACLE _home/bin: $PATH
Export ld_library_path= $ORACLE _home/lib #不同平台变量名不一样
2. Configuring Redo Transport Authentication
(1) Listener.ora
To configure static monitoring:
LISTENER =
(address_list=
(Address= (PROTOCOL=TCP) (HOST=STD) (port=1521))
)
Sid_list_listener=
(sid_list=
(sid_desc=
(Global_dbname=standby)
(Sid_name=standby)
(oracle_home=/u01/app/oracle/product/10.2/db_1)
)
)
Enable monitoring:
Lsnrctl start
Lsnrctl status
(2) Tnsnames.ora
STEPHEN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = pry) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = Stephen)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = std) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = standby)
)
)
(3) tnsping Command parses the network service name, the command must return OK.
$ tnsping Stephen
...
OK (0 msec)
(4) password file
Using the Master Vault's password file:
SCP pry: $ORACLE _home/dbs/orapwstephen $ORACLE _home/dbs/orapwstandby
3. Create a related directory
Mkdir-p $ORACLE _base/admin/standby/{adump,bdump,cdump,dpdump,pfile,udump,hdump}
Mkdir-p/oradata/{standby,arch}
4. parameter file
Modified with primary pfile to ensure that some parameters are consistent with primary, note that the size of the main repository memory is appropriately adjusted for memory-related parameters.
SCP Pry:/tmp/pfile.bak $ORACLE _home/dbs/initstandby.ora
Modify the pfile to the desired standby pfile and create the SPFile. Pay particular attention to the following example parameter settings:
*.audit_file_dest= '/u01/app/oracle/admin/standby/adump '
*.background_dump_dest= '/u01/app/oracle/admin/standby/bdump '
*.core_dump_dest= '/u01/app/oracle/admin/standby/cdump '
*.user_dump_dest= '/u01/app/oracle/admin/standby/udump '
*.control_files= '/oradata/standby/control01.ctl '
*.log_archive_format= '%t_%s_%r.arc '
*.log_archive_config= ' dg_config= (standby,stephen) '
*.log_a rchive_dest_1= ' Location=/oradata/arch valid_for= (all_logfiles,all_roles) Db_unique_name=standby '
*.log_a rchive_dest_2= ' service=stephen lgwr async valid_for= (online_logfiles,primary_role) Db_unique_name=stephen '
*.log_archive_dest_state_1= ' Enable '
*.log_archive_dest_state_2= ' Enable '
*.log_archive_max_processes=30 # Adjust the number according to demand
*.db_name= ' Stephen '
*.db_unique_name= ' standby '
*.instance_name= ' standby '
*.service_names= ' standby '
*.fal_server= ' Stephen '
*.fal_client= ' standby '
*.standby_file_management=auto
*.remote_login_passwordfile= ' EXCLUSIVE ' #exclusive/shared
# The main, standby log files and data file storage paths are inconsistent, set the following parameters:
*.db_file_name_convert= '/u01/oradata/stephen ', '/oradata/standby '
*.log_file_name_convert= '/u01/oradata/stephen ', '/oradata/standby '
Create SPFile:
Create SPFile from Pfile;
5. Recovering standby Controlfile
sql> startup Nomount;
rman> restore standby controlfile from '/backup/c_05qlbira_1_1.bak ';
6. Recovering Data files
(1) Standby and primary data file storage path consistent:
rman> SQL ' ALTER DATABASE mount standby database ';
Run
{Allocate channel C1 type disk;
Allocate channel C2 type disk;
Resotore database;
Recover database;
Release channel C1;
Release channel C2;
}
(2) Standby and primary data file storage path inconsistent:
Query the data file information for primary database:
Set Lines pages 300
Col name for A60
Col Member for A60
Select File#,name from V$datafile
UNION ALL
Select File#,name from V$tempfile;
Start the standby to mount stage and recover the data files:
rman> SQL ' ALTER DATABASE mount standby database ';
Run
{Set newname for datafile 1 to '/oradata/standby/system01.dbf ';
Set newname for datafile 2 to '/oradata/standby/sysaux01.dbf ';
Set newname for DataFile 3 to '/oradata/standby/undotbs1.dbf ';
Set newname for datafile 4 to '/oradata/standby/user01.dbf ';
Set newname for Tempfile 1 to '/oradata/standby/temp01.dbf ';
Restore database;
Switch datafile all;
Switch Tempfile all;
Recover database;
}
View Standby database log file:
Set Lines pages 300
Col Member for A60
Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from V$log a,v$logfile b where a.group#=b.group#
UNION ALL
Select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from V$standby_log a,v$logfile b where a.group#=b.group #;
7. Application Log
Apply Archive log:
Recover managed standby Database disconnect;
Apply Redo logfile:
Recover managed Standby database using current logfile disconnect;
To cancel the application log:
Recover managed standby database cancel;
8. Observing log Transfers
Follow the alert log of Primary,standby database to see if there is an error, and you can view information about the application log.
Verify that standby applies the log:
--primary end more than a few times log, observe alert log information.
alter system switch logfile;
alter system switch logfile;
-- Watch the Master repository log sequence number
Archive log list;
--primary End Query V$archived_log view to confirm that the log is applied:
Set Lines pages 300
Col name for A20
Select Name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from V$archived_log
where standby_dest= ' YES '
Order BY thread#,sequence#;
--primary the maximum log sequence number for the Primary,standby-side query is consistent:
Select ' Primary: ' DB Role ', Thread#,max (sequence#)
From V$archived_log
where standby_dest= ' NO '
GROUP BY thread#
Union
Select ' Standby: ' DB Role ', Thread#,max (sequence#)
From V$archived_log
where standby_dest= ' yes ' and applied= ' yes '
GROUP BY thread#
Order BY thread#;
oracle10g R2 "Single Instance fs→ Single instance FS"