DJI erpdb Database: oracle11gr2
Master database (the following well number does not need to be executed)
Alter database force logging;
Alter system set db_unique_name = 'erp 'scope = spfile; -- let's set the master database db_name = db_unique_name
Alter system set remote_login_passwordfile = exclusive scope = spfile;
Alter system set log_archive_format = '% T _ % S _ % R. arc' scope = spfile;
Alter system set log_archive_config = 'dg _ Config = (ERP, erpdg) 'scope = both;
# Alter system set log_archive_dest_1 = 'location = use_db_recovery_file_dest valid_for = (all_logfiles, all_roles) db_unique_name = ERP 'scope = both;
Alter system set log_archive_dest_2 = 'service = erpdg async valid_for = (online_logfiles, primary_role) db_unique_name = erpdg 'scope = both;
# Alter system set log_archive_dest_2 = 'service = 10.10.1.20.: 1601/erpdg async valid_for = (online_logfiles, primary_role) db_unique_name = erpdg 'scope = both;
Alter system set log_archive_dest_state_1 = Enable scope = both;
Alter system set log_archive_dest_state_2 = defer scope = both;
Alter system set log_archive_max_processes = 30 scope = both;
Restart Database
Shutdown immediate;
Startup Mount
Enable Archiving:
Alter database archivelog;
Alter database open;
Configure the listeners and TNS of both databases to enable tnsping for each other.
Listener reference
Sid_list_dg1 = (sid_list = (sid_desc = (sid_name = DG) (ORACLE_HOME =/DG1/product/11.2.0 ))) DG1 = (description_list = (description = (address = (Protocol = TCP) (host = 10.10.1.20.) (Port = 1800 ))))
Connection string reference:
Dg2 = (description = (address = (Protocol = TCP) (host = 10.10.1.20.) (Port = 1801) (CONNECT_DATA = (SID = DG )))
Create a password file for the master database and send it to the DG database. [DG1] CP $ ORACLE_HOME/dbs/orapw $ {oracle_sid}/tmp [DG1] chmod 777/tmp/orapw $ {oracle_sid} [dg2] mV/tmp/orapw $ {oracle_sid} $ ORACLE_HOME/DBS or the DG database directly creates a password file, the sys password is consistent with that of the master database. CD $ ORACLE_HOME/DBS & orapwd file = orapw $ oracle_sid Password = PASSWORD force = y
The following figure shows the [dg2] of the parameter file for creating the standby database.
CD $ ORACLE_HOME/DBS & VI fwy. ora
#
Db_name = ERP
Db_unique_name = ERP
Db_create_file_dest = '/erpd_bk/DG'
Diagnostic_dest = '/u01/ERP/DB/tech_st/11.2.0/admin/erpdg'
Log_archive_config = 'dg _ Config = (ERP, erpdg )'
Fal_server = ERP
Fal_client = erpdg db_recovery_file_dest_size = 200gdb_recovery_file_dest = '/erpd_bk/DG/fra'
#
Utl_file_dir = '/tmp'
Log_buffer = 15728640 #15 m
Pga_aggregate_target = 1073741824 #1g
Java_pool_size = 157286400 #150 m
Large_pool_size = 157286400 #150 m
Sga_target = 0
Shared_pool_size = 1073741824 #1g
Db_cache_size = 1073741824 #1g
Parallel_max_servers = 8
_ B _tree_bitmap_plans = false
_ Fast_full_scan_enabled = false
O7_dictionary_accessibility = false
_ Like_with_bind_as_equality = true
_ Optimizer_autostats_job = false
_ Sort_elimination_cost_ratio = 5
_ System_trig_enabled = true
_ Trace_files_public = true
Plsql_code_type = 'native'
Plsql_optimize_level = 2
Compatible = 11.2.0
Cursor_sharing = exact
Db_block_checking = false
Db_block_checksum = true
Db_files = 5120
Dml_locks = 30000
Log_checkpoint_interval = 100000
Log_checkpoint_timeout = 1200
Log_checkpoints_to_alert = true
Nls_territory = America
Olap_page_pool_size = 4194304
Optimizer_secure_view_merging = false
Parallel_min_servers = 0
Sec_case_sensitive_logon = false
Undo_management = auto
Undo_retention = 21600 #6 hours
Undo_tablespace = pai_undots1
Remote_login_passwordfile = exclusive
Query_rewrite_enabled = true
DB _ block_size = 8192
Db_file_multiblock_read_count = 64
Db_writer_processes = 10
Resource_manager_plan =''
_ Resource_manager_always_on = false
Disk_asynch_io = false
Open_cursors = 7000
Recyclebin = off
_ System_trig_enabled = true
O7_dictionary_accessibility = false
Nls_language = American
Nls_territory = America
Nls_date_format = 'dd-MON-RR'
Nls_numeric_characters = '.,'
Nls_sort = binary
Nls_comp = binary
Nls_length_semantics = byte
Max_dump_file_size = 51200
Timed_statistics = true
Processes = 3000
Sessions = 6000
Aq_tm_processes = 2
Job_queue_processes = 30
_ Sqlexec_progression_cost = 2147483647
Workarea_size_policy = auto
Olap_page_pool_size = 4194304
Optimizer_mode = first_rows
Log_archive_format = '% T _ % S _ % R. arc'
Standby_file_management = auto
Slave database starts to nomount status [dg2]
Sqlplus '/As sysdba'
Startup nomount pfile =? /Dbs/fwy. ora
The source uses the 11g duplicate from active Technology
Under the source [DG1], a green connection is made to the source database. Pink indicates that the connection is made to the target database.
RMAN target sys/Oracle auxiliary sys/[email protected]
Duplicate target database for standby from active database;
Or if you want to initiate RMAN from the target end, you can also [dg2] RMAN target sys/[email protected] auxiliary sys/Oracle
After duplicate is complete, the slave Database: [dg2]
Create spfile = '? /Dbs/fwy2.ora 'from memory;
CD $ ORACLE_HOME/DBS & CP fwy2.ora spfile $ {oracle_sid}. ora
Add a log group for the slave database and four groups for the execution four times. [Dg2]
Alter database add standby Logfile size 50 m;
Enable log transfer in the master database [DG1]
Alter system set log_archive_dest_state_2 = Enable scope = both;
Do not open the database first, even if open fails, because the data files may be inconsistent at this time. Therefore, we should first recover it for a while and make it recover to a consistent state.
[Dg2] alter database recover managed standby database using current logfile disconnect from session parallel 8;
After a while, stop the MRP process:
Alter database recover managed standby database cancel;
Then you can enable the open read only mode.
Alter database open read only;
Alter database recover managed standby database using current logfile disconnect from session parallel 8;
Check whether the MRP process works normally.
Select process, status, client_process, group #, thread #, sequence #, block # from V $ managed_standby where process = 'mrp0 ';
-- Compare the Maximum Application Log time of the master database and slave Database
Alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
Col max (first_time) for A30
Select max (first_time) from V $ log_history;
-- The master database is the largest log that has been written, and the slave database is the largest log that has been applied.
Alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
Select max (sequence #) from V $ log_history where resetlogs_time >=( select max (resetlogs_time) from V $ log_history );
How to delete archive logs in the master database: Configure archivelog deletion policy to shipped to all standby; -- configure archivelog deletion policy to applied on all standby; -- backup database
Disaster recovery database construction by using dataguard