Shulou(Shulou.com)06/01 Report--

Tnsname.ora

Fal_client

Fal_server

Deployment of data guard

* * *

Create a physical standby:

1. Preparatory work:

1. Make sure the database is in archive mode

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Current log sequence 5

SQL > show parameter recovery

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_

Area

Db_recovery_file_dest_size big integer 4122M

Recovery_parallelism integer 0

SQL > alter system set db_recovery_file_dest_size=1G

System altered.

SQL > show parameter recovery

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_

Area

Db_recovery_file_dest_size big integer 1G

Recovery_parallelism integer 0

SQL >

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 722366464 bytes

Fixed Size 2231872 bytes

Variable Size 436208064 bytes

Database Buffers 281018368 bytes

Redo Buffers 2908160 bytes

Database mounted.

SQL > alter database archivelog

Database altered.

SQL > alter database open

Database altered.

2. Turn on Forced Logging mode

SQL > select force_logging from v$database

FOR

-

NO

SQL > alter database force logging

Database altered.

2. Create a password file (keep two nodes existing, otherwise the cluster will not find the password file if you switch to another node)

Orapwd file=$ORACLE_HOME/dbs/orapworc1 password=welcome1 force=y ignorecase=y

3. Change the name (db_unique_name) of the database

SQL > show parameter name

NAME TYPE VALUE

-

Db_file_name_convert string

Db_name string orcl

Db_unique_name string orcl

Global_names boolean FALSE

Instance_name string orcl

Lock_name_space string

Log_file_name_convert string

Processor_group_name string

Service_names string orcl

SQL > alter system set db_unique_name=orcl scope=spfile

System altered.

4. Change standby_file_management

SQL > show parameter standby

NAME TYPE VALUE

-

Standby_archive_dest string? / dbs/arch

Standby_file_management string MANUAL

SQL > alter system set standby_file_management=AUTO

System altered.

SQL > show parameter standby

NAME TYPE VALUE

-

Standby_archive_dest string? / dbs/arch

Standby_file_management string AUTO

5. Modify fal server and client configuration

SQL > alter system set fal_client=orcl

System altered.

SQL > alter system set fal_server=orcldg

System altered.

SQL > show parameter fal

NAME TYPE VALUE

-

Fal_client string ORCL

Fal_server string ORCLDG

6. Modify log_archive_config parameters

SQL > show parameter log_archive_config

NAME TYPE VALUE

-

Log_archive_config string

SQL > alter system set log_archive_config='dg_config= (orcl,orcldg)'

System altered.

SQL > show parameter log_archive_config

NAME TYPE VALUE

-

Log_archive_config string dg_config= (orcl,orcldg)

7. Modify log_archive_dest_2 parameters

SQL > alter system set log_archive_dest_state_2='defer'

System altered.

SQL > show parameter log_archive_dest_2

NAME TYPE VALUE

-

Log_archive_dest_2 string

SQL > alter system set log_archive_dest_2='service=orcldg lgwr sync affirm valid_for= (online_logfiles,primary_role) db_unique_name=orcldg'

System altered.

SQL > show parameter log_archive_dest_2

NAME TYPE VALUE

-

Log_archive_dest_2 string service=orcldg lgwr sync affir

M valid_for= (online_logfiles,p

Rimary_role) db_unique_name=or

Cldg

SQL >

9. Enter rman for backup

View the size of the database

Select sum (bytes) / 1024 Universe 1024 from v$datafile

Backup control file

Run {

Backup database format'/ home/oracle/rman_backup/FULL_%U.bak'

Backup archivelog all format'/ home/oracle/rman_backup/ARC_%U.bak'

Backup format'/ home/oracle/rman_backup/standby_%U.ctl' current controlfile for standby

}

Backup device type disk format'/ rman_backup/standby_%U.ctl' current controlfile for standby

The method of restoring control files

RMAN > restore standby controlfile from'/ home/oracle/rman_backup/standby_0tpk53jq_1_1.ctl';-the path to the backup slice of the control file

10, create the pfile of orcldg

SQL > create pfile='/home/oracle/orcldgpfile.ora' from spfile

File created.

11. Pfile that transmits orcldg

[oracle@oracle1 ~] $scp orcldgpfile.ora oracle@192.168.74.21:/home/oracle

Oracle@192.168.74.21's password:

Orcldgpfile.ora

12. Modify the parameter file (reserve library)

[oracle@oracle ~] $vim orcldgpfile.ora

[oracle@oracle ~] $cat orcldgpfile.ora

* .audit_file_dest='/u01/app/oracle/admin/orcldg/adump'

* .audit_trail='db'

* .compatible='11.2.0.0.0'

* .control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/fast_recovery_area/orcldg/control02.ctl'

* .db_block_size=8192

* .db_domain=''

* .db_name='orcl'

* .db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

* .db_recovery_file_dest_size=1073741824

* .db_unique_name='ORCLDG'

* .diagnostic_dest='/u01/app/oracle'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)'

* .fal_client='ORCLDG'

* .fal_server='ORCL'

* .log_archive_config='dg_config= (orcldg,orcl)'

* .log_archive_dest_2='service=orcl lgwr sync affirm valid_for= (online_logfiles,primary_role) db_unique_name=orcl'

* .memory_target=725614592

* .open_cursors=300

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

[oracle@oracle] $mkdir-p / u01/app/oracle/admin/orcldg/adump

[oracle@oracle] $mkdir-p / u01/app/oracle/oradata/orcldg/

[oracle@oracle] $mkdir-p / u01/app/oracle/fast_recovery_area/orcldg/

13. Modify the registration document

[oracle@oracle ~] $cat / etc/oratab

Orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

Orcldg:/u01/app/oracle/product/11.2.0/dbhome_1:N

14. Try to start the database to nomount state and create spfile from pfile

[oracle@oracle] $. Oraenv

ORACLE_SID = [oracle]? Orcldg

The Oracle base remains unchanged with value / u01/app/oracle

[oracle@oracle ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 3 16:56:59 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL > startup nomount pfile='/home/oracle/orcldgpfile.ora'

ORACLE instance started.

Total System Global Area 722366464 bytes

Fixed Size 2231872 bytes

Variable Size 432013760 bytes

Database Buffers 285212672 bytes

Redo Buffers 2908160 bytes

SQL > create spfile from pfile='/home/oracle/orcldgpfile.ora'

File created.

SQL > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL >

15. Start the database to nomount

SQL > startup nomount

ORACLE instance started.

Total System Global Area 722366464 bytes

Fixed Size 2231872 bytes

Variable Size 432013760 bytes

Database Buffers 285212672 bytes

Redo Buffers 2908160 bytes

16. Modify convert parameters

SQL > show parameter convert

NAME TYPE VALUE

-

Db_file_name_convert string

Log_file_name_convert string

SQL > alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile

System altered.

SQL > alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile

System altered.

Restart instance convert parameter takes effect

SQL > show parameter convert

NAME TYPE VALUE

-

Db_file_name_convert string

Log_file_name_convert string

SQL > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 722366464 bytes

Fixed Size 2231872 bytes

Variable Size 432013760 bytes

Database Buffers 285212672 bytes

Redo Buffers 2908160 bytes

SQL > show parameter convert

NAME TYPE VALUE

-

Db_file_name_convert string / u01/app/oracle/oradata/orcl

/ u01/app/oracle/oradata/orcldg

Log_file_name_convert string / u01/app/oracle/oradata/orcl

/ u01/app/oracle/oradata/orcldg

[oracle@oracle] $mkdir-p / u01/app/oracle/oradata/orcldg

[oracle@oracle] $mkdir-p / u01/app/oracle/oradata/orcldg

18. Configure the listening service name

[oracle@oracle1 ~] $tnsping orcldg-192.168.74.21

TNS Ping Utility for Linux: Version 11.2.0.3.0-Production on 04-MAR-2017 17:37:00

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.74.21) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcldg)

OK (10 msec)

[oracle@oracle1 ~] $

[oracle@oracle ~] $tnsping orcl-192.168.74.22

TNS Ping Utility for Linux: Version 11.2.0.3.0-Production on 03-MAR-2017 17:36:51

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.74.22) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)

OK (0 msec)

Configure static snooping

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcldg)

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = orcldg)

)

(SID_DESC =

(GLOBAL_DBNAME = Oracle8)

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = ORCL)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = / u01/app/oracle

The configuration service name () name is the same

[oracle@oracle admin] $cat tnsnames.ora

# tnsnames.ora Network Configuration File: / u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL=

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

ORCLDG =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcldg)

)

)

[oracle@oracle1 admin] $cat tnsnames.ora

# tnsnames.ora Network Configuration File: / u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL=

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

ORCLDG =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcldg)

)

)

[oracle@oracle admin] $vim listener.ora

[oracle@oracle admin] $lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0-Production on 03-MAR-2017 18:42:36

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=oracle) (PORT=1521)

The command completed successfully

[oracle@oracle admin] $lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0-Production on 03-MAR-2017 18:42:45

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=oracle) (PORT=1521)

STATUS of the LISTENER

-

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0-Production

Start Date 03-MAR-2017 18:20:25

Uptime 0 days 0 hr. 22 min. 19 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=oracle) (PORT=1521))

Services Summary...

Service "Oracle8" has 1 instance (s).

Instance "ORCL", status UNKNOWN, has 1 handler (s) for this service...

Service "orcldg" has 2 instance (s).

Instance "orcldg", status UNKNOWN, has 1 handler (s) for this service...

Instance "orcldg", status BLOCKED, has 1 handler (s) for this service...

The command completed successfully

[oracle@oracle admin] $rman target sys/oracle@orcl-192.168.74.22 auxiliary sys/oracle@orcldg nocatalog

Recovery Manager: Release 11.2.0.3.0-Production on Fri Mar 3 18:42:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: ORCL (DBID=1464936028)

Using target database control file instead of recovery catalog

Connected to auxiliary database: ORCL (not mounted)

RMAN > duplicate target database for standby from active database

Duplicate target database for standby from active database nofilenamecheck

Restore standby controlfile from'/ home/oracle/rman_backup/standby_05u0o7s4_1_1.ctl'

Restore database

Configure redologfile

SQL > select instance_name,status from v$instance

INSTANCE_NAME STATUS

--

Orcldg MOUNTED

Select member from v$logfile

SQL > select bytes/1024/1024 from v$log

SQL > alter database add standby logfile group 4 ('/ u01ax size size 50m)

Database altered.

SQL > alter database add standby logfile group 5 ('/ u01ax size size 50m)

Database altered.

SQL > alter database add standby logfile group 6 ('/ u01ax size size 50m)

Database altered.

SQL > alter database add standby logfile group 7 ('/ u01ax size size 50m)

Database altered.

Alter database add standby logfile group 4 size 50M, group 5 size 50M, group 6 size 50M, group 7 size 50M

Alter database add standby logfile group 4 ('/ u01amp size size 50m)

Alter database add standby logfile group 5 ('/ u01amp size size 50m)

Alter database add standby logfile group 6 ('/ u01amp size size 50m)

Alter database add standby logfile group 7 ('/ u01amp size size 50m)

Start the log application process:

SQL > alter database recover managed standby database parallel 2 using current logfile disconnect

Database altered.

Turn on synchronization switch configuration

SQL > alter system set log_archive_dest_state_2='enable'

System altered.

View the primary role of the database (protection level, mode, etc.)

SQL >

DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL

--

PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

View log application

SQL > select max (sequence#), applied from v$archived_log group by applied

MAX (SEQUENCE#) APPLIED

--

18 YES

SQL > select max (sequence#), applied from v$archived_log group by applied

MAX (SEQUENCE#) APPLIED

--

18 YES

SQL > /

MAX (SEQUENCE#) APPLIED

--

19 IN-MEMORY

18 YES

SQL > /

MAX (SEQUENCE#) APPLIED

--

19 YES

Open the database

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database open

Database altered.

SQL >

SQL > alter database recover managed standby database using current logfile disconnect

Database altered.

SQL > select max (sequence#), applied from v$archived_log group by applied

MAX (SEQUENCE#) APPLIED

--

20 YES

SQL > /

MAX (SEQUENCE#) APPLIED

--

21 YES

SQL > /

MAX (SEQUENCE#) APPLIED

--

21 YES

SQL > select * from T1

View the status of the database

SQL > select name,open_mode,protection_level,database_role,switchover#,switchover_status from v$database

NAME OPEN_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS

--

ORCL READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY 1464842362 NOT ALLOWED

The above active dataguard has been deployed

# #

Logcal database (logical standby)

3. Configure master / slave snooping (two-way snooping must be ensured, and two nodes of the RAC cluster must be configured)

4. Set initialization parameters (primary and standby)

Main library parameters

* .log_archive_config='dg_config= (kc,dg)'

* .log_archive_dest_2='service=dg db_unique_name=dg lgwr async'

* .log_archive_dest_state_2='ENABLE'

Slave parameters (net service name should be used in config, not sid or service name)

* .db_unique_name='dg'

* .undo_management='AUTO'

* .log_archive_config='dg_config= (kc,dg)'

* .db_file_name_convert='+DATA01/kc/datafile','+DATA01/dg/datafile'

* .log_file_name_convert='+DATA01/kc/onlinelog','DATA01/dg/onlinelog'

* .standby_file_management=auto

* .fal_client='kc'

* .fal_server='kc'

Second, detailed steps:

1. Create a backup in the main library (copy the data file manually or through RMAN)

2. Create the control file for the main library

Create a control file for the standby database with the following statement

SQL > alter database create standbycontrolfile as'd:\ backup\ jsspdg01.ctl'

3. Create and modify initialization parameter files in the main library

SQL > create pfile='d:\ backup\ initjsspdg.ora' from spfile

Pay attention to the attribute configuration of different roles of primary and standby, and pay attention to the file path.

4. Copy the file to the standby server

Data file, control file, modified initialization parameter file, pay attention to the path.

5. Configure standby database

6. Start standby

SQL > STARTUP MOUNT

3 standby log groups need to be created for each thread

SQL > alter database add standby logfile thread 1

Group 5 ('+ DATA01/dg/onlinelog/st_1_5.log') size 50m

Group 6 ('+ DATA01/dg/onlinelog/st_1_6.log') size 50m

Group 7 ('+ DATA01/dg/onlinelog/st_1_7.log') size 50m

SQL > alter database add standby logfile thread 2

Group 8 ('+ DATA01/dg/onlinelog/st_2_8.log') size 50m

Group 9 ('+ DATA01/dg/onlinelog/st_2_9.log') size 50m

Group 10 ('+ DATA01/dg/onlinelog/st_2_10.log') size 50m

Start the redo application

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Start real-time application

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

7. Stop standby

Under normal circumstances, we should stop the redo application first, by using the following statement:

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Then stop the standby database

SQL > SHUTDOWN IMMEDIATE

8. Common status query:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Select process,status from v$managed_standby

Show parameter instance_name

Select max (sequence#) from v$archived_log

9. Prepare to switch:

Select switchover_status from v$database

Alter database commit to switchover to physical standby

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.