Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to build oracle DataGuard". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Reserve database environment variable

Copy the main database with the same environment variables. Pay attention to sid,$ORACLE_BASE,$ORACLE_HOME.

two。 Software and patches

Check the patch version in the main library

Select COMMENTS from dba_registry_history

Or opatch lsinv

Mkdir / u01/oracle/software

Cd / u01/oracle/software

Note: all 7u4 systems need to download this patch pack

Get software/oracle/db/PSU171017/p19692824_112040_Linux-x86-64.zip

Sftp

Sftp > cd software/oracle/db

Get p13390677_112040_Linux-x86-64_1of7.zip

Get p13390677_112040_Linux-x86-64_2of7.zip

Get p6880880_112000_Linux-x86-64.zip

Get

Bye

Unzip p13390677_112040_Linux-x86-64_1of7.zip

Unzip p13390677_112040_Linux-x86-64_2of7.zip

Unzip p6880880_112000_Linux-x86-64.zip

Unzip p19692824_112040_Linux-x86-64.zip

Unzip

Cd database

Xclock

. / runInstaller

Run script

Finish

Cd..

Mv $ORACLE_HOME/OPatch/ $ORACLE_HOME/OPatch.bak

Mv / u01/oracle/software/OPatch/ $ORACLE_HOME/

Patches must have environment variables.

Cd 19692824

Opatch apply

Cd..

Cd

Opatch apply (if patches cannot be made, check to see if listening is off)

Opatch lsinv

3. Main library parameter check

Check the master and standby libraries cpu, memory and file system

The standby database is consistent with the main database as far as possible.

Free-g

Lscpu

Df-h

Check the parameters of the main library (ensure that archiving and force logging are on)

Set line 200

Col DATABASE_ROLE for a30

Col DB_UNIQUE_NAME for a20

Col OPEN_MODE for a30

Col LOG_MODE for a30

Select database_role,db_unique_name,open_mode,log_mode,force_logging from v$database

Check the parameters of the main library (whether the datafile is in online status or system status)

Select distinct status from v$datafile

Check the parameters of the main library (whether the tablespaces are all online states)

Select distinct STATUS from dba_tablespaces

Check the parameters of the main library (all FULL)

Show parameter DB_BLOCK_CHECKING

Show parameter DB_LOST_WRITE_PROTECT

Show parameter DB_BLOCK_CHECKSUM

If not, check whether it is modified or need to be restarted, and modify it if not.

Alter system set DB_BLOCK_CHECKING='FULL'

Alter system set DB_LOST_WRITE_PROTECT = 'FULL'

Alter system set DB_BLOCK_CHECKSUM = 'FULL'

Main library channel check

Set linesize 200 pages 999

Col dest_id for 9999

Col dest_name for a30

Col status for a10

Col error for a50

Select dest_id,dest_name,status,error from v$archive_dest

Show parameter dest

Exit

If the main library already has a dg library, note that the name of the _ sec variable should be changed to a non-conflicting name

The log_archive_dest_2 parameter should be changed to a non-conflicting channel

two。 Operation steps

1. Configure tnsnames.ora for the main library (same as the standby database)

Vi $ORACLE_HOME/network/admin/tnsnames.ora

=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST =) (PORT =))

)

(CONNECT_DATA =

(SERVICE_NAME =)

)

)

_ sec =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST =) (PORT =))

)

(CONNECT_DATA =

(SERVICE_NAME =)

)

)

two。 Listener.ora and sqlnet.ora for preparing the library

Vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_ =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME =)

(ORACLE_HOME = / u01/oracle/product/db11gr2)

(SID_NAME =)

)

)

=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST =) (PORT =))

)

ADR_BASE_ = / u01/oracle

INBOUND_CONNECT_TIMEOUT_=60

SECURE_REGISTER_ = (TCP)

ADMIN_RESTRICTIONS_ = ON

DIAG_ADR_ENABLED_ = ON

Vi $ORACLE_HOME/network/admin/sqlnet.ora

SQLNET.INBOUND_CONNECT_TIMEOUT = 60

Sqlnet.expire_time = 10

Sqlnet.allow_logon_version = 8

DIAG_ADR_ENABLED_ = ON

Lsnrctl start

Main and standby library

Tnsping

Tnsping _ sec

3. The main library transfers necessary files (password files, parameter files, control files).

Export ORACLE_SID=

Sqlplus / as sysdba

Create pfile='/tmp/init.ora' from spfile

Exit

Scp $ORACLE_HOME/dbs/orapw: / home/oracle

Scp / tmp/init.ora: / tmp

Scp / usr/openv/scripts/_controlfile.ctl: / tmp

Prepare for database execution

Mv / home/oracle/orapw $ORACLE_HOME/dbs

4. Modify the parameters to boot to mount

Modify the parameter file:

If it is a dg library for migration, do not modify the parameter file

If it is a simple dg library, it will be modified according to the actual memory of the library.

In general, modify the archive path, fal_server and fal_client, create the archive file and audit file path

Xmata.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment # modify the directory

* .audit_file_dest='/db_audit_file_dest/xmata' # modify the directory

* .control_files='/xmata/xmatasys_u01/xmata/control01.ctl','/xmata/xmatardo_u01/xmata/control02.ctl','/xmata/xmataudo_u01/xmata/control03.ctl' # modify the directory

* .db_cache_size=4294967296 # modify the size

* .diagnostic_dest='/u01/oracle' # modify the directory

* .fal_client='xmata_new' # change the name by yourself on the client

* .fal_server='xmata' # change the name

* .java_pool_size=134217728 # modify the size

* .large_pool_size=268435456 # modify the size

* .log_archive_dest_1='LOCATION=/oraarchivelog/xmata' # modify the directory

* .log_archive_dest_2='service=zdhdb_sec LGWR ASYNC NOAFFIRM NET_TIMEOUT=30' # Delete this line

* .log_archive_format='%t_%s_%r.arc' # add this line (make the archive file end in .arc)

* .pga_aggregate_target=2147483648 # modify the size

* .sga_max_size=12884901888 # modify the size

* .shared_pool_size=1073741824 # modify the size

* .utl_file_dir='/oraarchivelog/xmata' # modify the directory

Boot to nomount and modify parameters

Export ORACLE_SID=

Sqlplus / as sysdba

Create spfile from pfile='/tmp/init.ora'

Startup nomount

When the alter system set standby_file_management=auto; # main library adds data files, the dg library automatically adds

Alter system set filesystemio_options=setall scope=spfile; # set Asynchronous IO

Alter system set parallel_execution_message_size=32768 scope=spfile; # performs message size in parallel. Do not set this parameter for migration.

Exit

Boot to mount

Rman target /

Restore controlfile from'/ tmp/_controlfile.ctl'

Exit

Sqlplus / as sysdba

Alter database mount

Alter database create standby controlfile as'/ tmp/control01.ctl'

Shutdown immediate

Startup nomount

Exit

Rman target /

Restore controlfile from'/ tmp/control01.ctl'

Exit

Sqlplus / as sysdba

Alter database mount

Exit

5. The main library configures the log transfer channel, and the standby library deploys the self-deletion script.

The main library configures the log transfer channel

COMPRESSION=enable compression parameters

Alter system set log_archive_dest_2='service=_sec LGWR ASYNC NOAFFIRM NET_TIMEOUT=30'

Alter system set log_archive_dest_state_2='enable'

Col dest_name format a30

Select DEST_ID,DEST_NAME,STATUS,error from v$archive_dest_status

Standby deployment self-deletion script

Mkdir-p / home/oracle/scripts_/

Cd / home/oracle/scripts_/

Vi dg_rm_applied_arclog.sh (modify script sid,$ORACLE_HOME,$ORACLE_BASE)

# deployment built by DG automatically deletes the scheduled task of archive logs #

Export ORACLE_BASE=/u01/oracle

Export ORACLE_HOME=/u01/oracle/product/db11gr2

Export ORACLE_SID=

Export ORACLE_TERM=xterm

Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Export ORA_NLS10=$ORACLE_HOME/nls/data

Export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/RDBMS/lib:/lib:/usr/lib

Export JAVA_HOME=$ORACLE_HOME/jdk

Export ORACLE_DOC=$ORACLE_HOME/doc

Export SQLPATH=$ORACLE_HOME/dbs

Export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH:/usr/vacpp/bin:/usr/ccs/bin

Export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib

Export TMP=/tmp

Export TMPDIR=$TMP

Export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'

Export NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh34:mi:ss.ff'

Export NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd hh34:mi:ss.ff'

Export EDITOR=vi

Stty erase ^ H

# delete archivelog tread 1

SEQ1= `sqlplus-s "/ as sysdba"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.