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

Because the customer needs to deploy the 19c DG environment, which used to be 11g and 12c, so test the 19c deployment

1. DG basic environment

ORACLE host library IP:111.111.111.203 SID:DT db_name='DT' hostname: test19c

ORACLE slave library IP:111.111.111.204 SID:dgtest db_name='DT' hostname: testdg19c

Physical path of the main library archive directory:

SQL > archive log list

/ u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch

Main library datafile physical path

/ u01/app/oradata/

/ u01/app/oradata/DT

Main library redo physical path

/ u01/app/oradata/DT

Parameter * log_archive_config='dg_config (pri,std)'to ensure that the primary and standby database can identify each other.

Check to see if there are any errors in the archive.

Select status,error from v$archive_dest

2. Modify the configuration file initTESTDB.ora of the main library

Here, the relevant parameters are modified in the database, and the parameters related to DG are only related to a few parameters, that is, the log, the conversion of the location of the file, and the processing of GAP. In fact, GAP has been automatically processed, but here we still introduce the configuration of FAL_SERVER,FAL_CLIENT parameters. Recreate the pfile file after modification

First create a spfile, then modify it and regenerate the pfile

SQL > alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG= (DT,dgtest)'

SQL > alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DT'

SQL > alter system set LOG_ARCHIVE_DEST_2='SERVICE=dgtest LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest'

SQL > alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE

SQL > alter system set FAL_SERVER=dgtest

SQL > alter system set FAL_CLIENT=DT

SQL > alter system set DB_FILE_NAME_CONVERT='/u01/app/oradata/dgtest','/u01/app/oradata/DT' scope=spfile

SQL > alter system set LOG_FILE_NAME_CONVERT='/u01/app/oradata/dgtest','/u01/app/oradata/DT' scope=spfile

SQL > alter system set STANDBY_FILE_MANAGEMENT=AUTO

SQL > create pfile from spfile

DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters must restart the database to take effect.

The following is the regenerated pfile file after modification

DT.__data_transfer_cache_size=0

DT.__db_cache_size=247463936

DT.__inmemory_ext_roarea=0

DT.__inmemory_ext_rwarea=0

DT.__java_pool_size=4194304

DT.__large_pool_size=20971520

DT.__oracle_base='/u01/app'#ORACLE_BASE set from environment

DT.__pga_aggregate_target=293601280

DT.__sga_target=549453824

DT.__shared_io_pool_size=16777216

DT.__shared_pool_size=243269632

DT.__streams_pool_size=0

DT.__unified_pga_pool_size=0

* .audit_file_dest='/u01/app/admin/DT/adump'

* .audit_trail='db'

* .compatible='19.0.0'

* .control_files='/u01/app/oradata/DT/control01.ctl','/u01/app/oradata/DT/control02.ctl'

* .db_block_size=8192

* .db_file_name_convert='/u01/app/oradata/dgtest','/u01/app/oradata/DT'

* .db_name='DT'

* .db_unique_name='DT'

* .diagnostic_dest='/u01/app'

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

* .fal_client='DT'

* .fal_server='dgtest'

* .local_listener='LISTENER_DT'

* .log_archive_config='DG_CONFIG= (DT,dgtest)'

* .log_archive_dest_1='LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DT'

* .log_archive_dest_2='SERVICE=dgtest LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest'

* .log_archive_dest_state_1='ENABLE'

* .log_file_name_convert='/u01/app/oradata/dgtest','/u01/app/oradata/DT'

* .memory_max_target=903741824

* .memory_target=839524096

* .nls_language='AMERICAN'

* .nls_territory='AMERICA'

* .open_cursors=300

* .processes=480

* .remote_login_passwordfile='EXCLUSIVE'

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

3. Modify the configuration file of the slave database to: initdgtest.ora

DT.__data_transfer_cache_size=0

DT.__db_cache_size=339738624

DT.__inmemory_ext_roarea=0

DT.__inmemory_ext_rwarea=0

DT.__java_pool_size=4194304

DT.__large_pool_size=20971520

DT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

DT.__pga_aggregate_target=201326592

DT.__sga_target=641728512

DT.__shared_io_pool_size=12582912

DT.__shared_pool_size=247463936

DT.__streams_pool_size=0

DT.__unified_pga_pool_size=0

* .audit_file_dest='/u01/app/admin/dgtest/adump'

* .audit_trail='db'

* .compatible='19.0.0'

* .control_files='/u01/app/oradata/dgtest/control01.ctl','/u01/app/oradata/dgtest/control02.ctl'

* .db_block_size=8192

* .db_file_name_convert='/u01/app/oradata/DT','/u01/app/oradata/dgtest'

* .db_name='DT'

* .db_unique_name='dgtest'

* .diagnostic_dest='/u01/app'

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

* .fal_client='dgtest'

* .fal_server='DT'

* .local_listener='LISTENER_dgtest'

* .log_archive_config='DG_CONFIG= (DT,dgtest)'

* .log_archive_dest_1='LOCATION=/u01/arch VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest'

* .log_archive_dest_2='SERVICE=DT LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DT'

* .log_archive_dest_state_1='ENABLE'

* .log_file_name_convert='/u01/app/oradata/DT','/u01/app/oradata/dgtest'

* .memory_max_target=903741824

* .memory_target=839524096

* .nls_language='AMERICAN'

* .nls_territory='AMERICA'

* .open_cursors=300

* .processes=480

* .remote_login_passwordfile='EXCLUSIVE'

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

Note:

1 > in the pfile file configured on the Linux side, all windows paths should be uppercase, because in the duplication process, the windows side is transferred according to the uppercase path!

If you use lowercase or mixed case, the path will not be recognized and there will be a problem!

2 > in the duplication process, although the operation is in the main library, the path conversion of datafile and logfile is recognized as the conversion path in the pfile file of the standby library!

4. Modify the listener.ora file of the main library-if it is not configured, an error rman-04006 ora-12514 will be reported.

# listener.ora Network Configuration File: / u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = DT)

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

(SID_NAME = DT)

)

)

ADR_BASE_LISTENER = D:\ app\ Administrator

5. Modify the tnsnames.ora file of the main library

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

# Generated by Oracle configuration tools.

LISTENER_DT =

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

DT =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = DT)

)

)

Dgtest =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgtest)

)

)

6. Modify the listener.ora file of the library

# listener.ora Network Configuration File: / u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

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

(PROGRAM = extproc)

# (ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/product/19.2.0/dbhome_1/oraclr11.dll")

)

(SID_DESC =

(GLOBAL_DBNAME = dgtest)

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

(SID_NAME = dgtest)

)

)

7. Modify the tnsnames.ora file of the repository

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

# Generated by Oracle configuration tools.

LISTENER_DT =

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

DT =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = DT)

)

)

Dgtest =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgtest)

)

)

8. Prepare the library to generate orapwd files

Orapwd file=orapwdgtest password=oracle

Note: in order to prevent inaccessibility caused by password problems, it is best to copy the orapw file of the main library directly and rename it orapwSID.

9. Make sure that both the master database and slave database start listening: lsnrctl start

10. The main library is set to archive mode.

Alter database archivelog

Set the master database to log forced write state

Alter database force logging

View the status log to force the write status to YES

Select log_mode,force_logging from v$database

LOG_MODE FOR

ARCHIVELOG YES

11. Check the number and size of log groups in the master database, because the number of standby log groups created is the product of the number of original log groups + 1 and thread, and size cannot be less than the size of the original log file.

SQL > select group#,THREAD#,bytes/1024/1024 from v$log

GROUP# THREAD# BYTES/1024/1024

1 1 200

2 1 200

3 1 200

SQL > select member from v$logfile

MEMBER

-

/ u01/app/oradata/DT/redo03.log

/ u01/app/oradata/DT/redo02.log

/ u01/app/oradata/DT/redo01.log

12. Create standby log groups, the number of which is the product of the number of original log groups + 1 and the number of instances. Size cannot be less than the size of the original log file.

Note: there are several examples of RAC environment attention. The path of the new standby log group can be the same as the original log group.

SQL > alter database add standby logfile'/ u01According to oradata size 200m

Database altered.

Alter database add standby logfile'/ u01 size size 200m

Alter database add standby logfile'/ u01 size size 200m

Alter database add standby logfile'/ u01 _ size _ size _ 200m

Alter database add standby logfile'/ u01 size size 200m

Whether the query is successful after creation

SQL > select group#,status,type,member from v$logfile

GROUP# STATUS TYPE MEMBER

3 ONLINE / u01/app/oradata/DT/redo03.log

2 ONLINE / u01/app/oradata/DT/redo02.log

1 ONLINE / u01/app/oradata/DT/redo01.log

4 STANDBY / u01/app/oradata/DT/standby01.log

5 STANDBY / u01/app/oradata/DT/standby02.log

6 STANDBY / u01/app/oradata/DT/standby03.log

7 STANDBY / u01/app/oradata/DT/standby04.log

7 rows selected.

13. Start the backup library to NOMOUNT

$sqlplus / as sysdba

SQL > startup nomount pfile='/home/oracle/backup/INITtestdb.ORA'

Note: if the pfile file is placed in the default path and the file name is correct. You don't need to specify a pfile path, just startup nomount it.

14. Duplicate starts

Copy the slave library through rman on the primary library (note that you must exit all connections to the slave library before this step, otherwise an error will be reported)

Rman target sys/oracle auxiliary sys/oracle@dgtest

Rman > duplicate target database for standby nofilenamecheck from active database

After querying the data, it is found that during duplicate transmission, all uppercase characters under windows are all uppercase characters, so it is necessary to modify

Paths of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters, all in uppercase

* error message *

19C during the test, the slave database was always unable to connect to the slave database due to the misconfiguration of the environment variable ORACLE_HOME of the slave database. The error is as follows:

[oracle@test19c admin] $rman target sys/oracle auxiliary sys/oracle@dgtest

Recovery Manager: Release 19.0.0.0.0-Production on Mon Jun 17 18:13:10 2019

Version 19.2.0.0.0

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

Connected to target database: DT (DBID=1254913786)

Connected to auxiliary database (not started)

RMAN > exit

Check found that in the .bash _ profile file, the path of ORACLE_BASE was finally added a /, resulting in an extra / in ORACLE_HOME.

ORACLE_BASE=/u01/app/oracle/; export ORACLE_BASE-- / u01/app/oracle shouldn't have that much.

ORACLE_HOME=$ORACLE_BASE/product/19.2.0/dbhome_1; export ORACLE_HOME

The path you see when you end up using echo $ORACLE_HOME is as follows:

/ u01/app/oracle//product/19.2.0/dbhome_1

But the most helpless thing is that when you type cd $ORACLE_HOME, you can enter the correct path, and then pwd will display: / u01/app/oracle/product/19.2.0/dbhome_1

So when I checked before, I always thought that there was nothing wrong with the environment variables. Finally, change the ORACLE_BASE to / u01/app/oracle, then restart the listening and restart the backup library.

Summary: use echo to check various environment variables instead of cd directly.

-dividing line-

Pay attention to the size of db_recovery_file_dest_size. The last time you deployed 12c production environment, there was a problem with the size, which can be solved after modification:

SQL > alter system set db_recovery_file_dest_size=60G

* * End**

15. Open prepares the library and returns to the state of automatic recovery

Confirm the status of standby database:

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

Start the slave library under open only:

SQL > alter database open read only

Start the database to recovery management mode on the standby library and start preparing to accept the transfer of archive logs from the primary library:

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

* error message *

An error is reported when the slave database starts:

SQL > alter database open read only

Alter database open read only

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1:'/ u01ActionPlacement oradata DG TestUniverse system01.dbf'

Check the alert log on the master / slave database and find that the slave database alert log contains the following information:

PR00 (PID:7481): Please verify that primary database is transporting redo logs to the standby database

According to the information check, it was found that redo didn't come at all, because I didn't see an archive file in the repository archive directory!

Currently, the archive cannot be transferred, so check whether there is an error in the archive in the master / slave database:

Select status,error from v$archive_dest

An error was found in the main library:

SQL > select status,error from v$archive_dest

STATUS ERROR

--

VALID

ERROR ORA-16086: Redo data cannot be written to the standby redo log

This kind of error report is generally related to the configuration file, and there are also relevant documents and troubleshooting ideas online.

After many tests, it is found that it is related to the lack of configuration of the db_unique_name parameter:

Check and find that the db_unique_name of the slave database is also called DT by default without configuration, which is the same as that of the main database.

This causes the primary and secondary libraries to be indistinguishable because the db_unique_name is consistent, which results in the redo cannot be transferred and there is no archiving.

SQL > show parameter name

NAME TYPE VALUE

-

Cdb_cluster_name string

Cell_offloadgroup_name string

Db_file_name_convert string / u01/app/oradata/DT, / u01/app/oradata/dgtest

Db_name string DT

Db_unique_name string DT

Global_names boolean FALSE

Instance_name string dgtest

Lock_name_space string

Log_file_name_convert string / u01/app/oradata/DT, / u01/app/oradata/dgtest

Pdb_file_name_convert string

Processor_group_name string

Service_names string DT

SQL >

The above information can be seen that instance_name is dgtest, but db_unique_name is still DT, so redo cannot be transmitted.

Finally, the problem is solved by modifying the pfile file on the repository and adding the configuration of db_unique_name.

It is best to define db_unique_name in both the master and backup libraries in pfile!

In this deployment environment, after the 19c main library is installed, the generated pfile does not define db_unique_name, so this problem is ignored.

-split line. If there is a problem with the following steps, you should first open the library-

Go back to the original master library and start STANDBY to receive and recover the logs of the master library.

Alter database recover managed standby database using current logfile disconnect from session

Enter the above statement in the mount state, resulting in the database cannot be open, so you need to exit this state.

Alter database recover managed standby database cancel

* * End**

16. The main library checks the LNS process:

Select process,status from v$managed_standby

Check the process to see if there are any LNS processes. If not, check the DG environment.

SQL > select process,status from v$managed_standby

PROCESS STATUS

--

DGRD ALLOCATED

ARCH CLOSING

DGRD ALLOCATED

ARCH OPENING

ARCH CONNECTED

ARCH CONNECTED

LNS OPENING

DGRD ALLOCATED

LNS WRITING

9 rows selected.

17. Check the roles of the master and standby libraries and other information

Check whether its role is physical standby on the slave side, and check the mode PROTECTION_MODE of the current slave.

SQL > select DATABASE_ROLE,protection_mode,open_mode from v$database

DATABASE_ROLE PROTECTION_MODE OPEN_MODE

PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY

View its role in the main library

SQL > select DATABASE_ROLE,open_mode from gv$database

DATABASE_ROLE OPEN_MODE

--

PRIMARY READ WRITE

Check which log is accepted by data guard in the repository.

SQL > select process,client_process,sequence#,status from v$managed_standby

PROCESS CLIENT_P SEQUENCE# STATUS

--

ARCH ARCH 0 CONNECTED

DGRD N/A 0 ALLOCATED

DGRD N/A 0 ALLOCATED

ARCH ARCH 0 CONNECTED

ARCH ARCH 0 CONNECTED

ARCH ARCH 0 CONNECTED

RFS Archival 0 IDLE

RFS LGWR 31 IDLE

RFS UNKNOWN 0 IDLE

MRP0 N/A 31 APPLYING_LOG

10 rows selected.

18. Check the master and backup database sequence#

Select max (sequence#) from v$archived_log

Select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc

Main library:

SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

thirty

Prepare the library

SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

thirty

19. Test whether the log queue can be transmitted properly

Check the queue status of logs in the repository.

SQL > select sequence#,applied,first_time,next_time from v$archived_log order by sequence#

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME

--

30 YES 18-JUN-19 18-JUN-19

Mandatory archiving in the main library

ALTER SYSTEM ARCHIVE LOG CURRENT

Alter system switch logfile

Check the queue of the logs in the repository to see if the new archive logs have been transferred normally.

SQL > select sequence#,applied,first_time,next_time from v$archived_log order by sequence#

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME

--

30 YES 18-JUN-19 18-JUN-19

31 NO 18-JUN-19 18-JUN-19

32 IN-MEMORY 18-JUN-19 18-JUN-19

Check the log synchronization on both sides

Select sequence# from v$archived_log where applied='YES'

Check to see if there is a redo log written:

Select sequence#,applied from v$archived_log

20. Check whether DG is working properly. This step mainly depends on whether an error has been reported in the archive.

Select dest_id,error,status from v$archive_dest where status='ERROR'

SQL > select dest_id,error,status from v$archive_dest where status='ERROR'

No rows selected

You can also view all archive directory information directly:

Select dest_id,error,status from v$archive_dest

21. Create new tablespaces, users, tables in the main database, and insert data to test whether the standby database can synchronize the data in time.

The main library creates test tablespaces:

SQL > create tablespace test datafile'/ u01qapplash oradata size DT _ 01.dbf'DT 50m

The master and slave libraries view the status of data files.

Select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile

Main library:

SQL > select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile

FILE# CREATION_ STATUS NAME BYTES

1 04-FEB-19 SYSTEM / u01/app/oradata/DT/system01.dbf 943718400

3 04-FEB-19 ONLINE / u01/app/oradata/DT/sysaux01.dbf 555745280

4 04-FEB-19 ONLINE / u01/app/oradata/DT/undotbs01.dbf 68157440

5 18-JUN-19 ONLINE / u01/app/oradata/DT/dt01.dbf 52428800

7 04-FEB-19 ONLINE / u01/app/oradata/DT/users01.dbf 5242880

Prepare the library:

SQL > select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile

FILE# CREATION_ STATUS NAME BYTES

1 04-FEB-19 SYSTEM / u01/app/oradata/dgtest/system01.dbf 943718400

3 04-FEB-19 ONLINE / u01/app/oradata/dgtest/sysaux01.dbf 555745280

4 04-FEB-19 ONLINE / u01/app/oradata/dgtest/undotbs01.dbf 68157440

5 18-JUN-19 ONLINE / u01/app/oradata/dgtest/dt01.dbf 52428800

7 04-FEB-19 ONLINE / u01/app/oradata/dgtest/users01.dbf 5242880

Main library operation:

1 > create a user

Create user dgtest default tablespace test identified by oracle

Grant dba to dgtest

2 > switching users

Sqlplus dgtest/oracle

3 > create tables and insert test data under dgtest users

-- create tables

Create table dgtest (

Id number (9) not null primary key

Classname varchar2 (40) not null

);

-- insert data

Insert into dgtest values (28 insert into dgtest values class one')

Insert into dgtest values (29djindetest one')

Commit

Execute the query in the repository:

Select * from dgtest.dgtest

SQL > select * from dgtest.dgtest

ID CLASSNAME

28 class one

29 detest one

Delete test data:

Drop tablespace test including contents and datafiles

Drop user dgtest cascade

When the DG environment is deployed, the data can be synchronized normally.

-active and standby library switching test-

# two ways to switch between switchover and failover

Switchover switching: switching between master database and slave database data synchronization under normal conditions, mainly used for active / standby maintenance, switching drills, etc.

Failover switchover: forced handover between master and slave database when the data is not synchronized. It is mainly used for switching in case of downtime or failure of the primary database.

1. Switchover

Oracle physical DG handoff

Before switching the physical STANDBY of DATA GUARD, you should note:

Make sure that the network connection between the main library and the standby database is smooth.

Confirm that there are no active session connections in the database

Make sure the STANDBY database is in ARCHIVELOG mode

If the delay for REDO applications is set, remove this setting

Make sure that the initialization parameters of the main library and standby library are configured, so that after the switch is completed, the DATA GUARD mechanism can run smoothly.

# preparations:

Confirm that the current main library has only the current session connection:

Set pages 100 linesize 1000

Select SWITCHOVER_STATUS from v$database

SQL > select SWITCHOVER_STATUS from v$database

SWITCHOVER_STATUS

-

TO STANDBY

Select count (*) from v$session where username is not null

SQL > select count (*) from v$session where username is not null

COUNT (*)

-

two

If there is more than one session:

Select sid,serial# from v$session where username is not null

SQL > select sid,serial# from v$session where username is not null

SID SERIAL#

--

2 10746

621 13864

Select userenv ('sid') from dual

SQL > select userenv ('sid') from dual

USERENV ('SID')

-

six hundred and twenty one

Then execute the following statement to kill: alter system kill session 'sid,serial#'

SQL > alter system kill session '621 Magi 13864'

Alter system kill session '621, 13864'

*

ERROR at line 1:

ORA-00027: cannot kill current session

It turns out that 621 is the current session and can be ignored.

# formal switching:

The data of the main database and the standby database are synchronized and run normally. The roles of the main database and the standby database can be exchanged, and they can also be exchanged back.

Before switching, be sure to check whether the archives of the current master and standby are synchronized, and then perform the switch after confirming the synchronization

Main library cut-off library

1. Check the role of the library

Main library:

SQL > select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE

CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

CURRENT READ WRITE PRIMARY TO STANDBY

SWITCHOVER_STATUS is TO STANDBY, which means that the master library can be switched to a standby library.

Prepare the library:

SQL > select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE

CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

STANDBY READ ONLY PHYSICAL STANDBY NOT ALLOWED

SWITCHOVER_STATUS is NOT ALLOWED, which is the normal state of slave database.

After the main database is switched first, we query the status of the standby database and find that the SWITCHOVER_STATUS has changed.

2. Switch to the main library

SQL > alter database commit to switchover to physical standby with session shutdown

At this time, the main library has started the process of switching to the standby database, and the SWITCHOVER_STATUS of the standby library has become TO PRIMARY.

3. At this time, the main library has been closed, and the status of read only or synchronization is reached.

SQL > startup mount

ORACLE instance started.

Total System Global Area 905967800 bytes

Fixed Size 8902840 bytes

Variable Size 788529152 bytes

Database Buffers 100663296 bytes

Redo Buffers 7872512 bytes

Database mounted.

SQL > alter database open read only

Database altered.

4. Check the role of the main database again and confirm that it has been switched to a standby database.

SQL > select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE

CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

STANDBY READ ONLY PHYSICAL STANDBY TO PRIMARY

Note: after this step is completed, both the master and slave databases are in open read only status, and the SWITCHOVER_STATUS is TO PRIMARY.

At this time, there is no real master library. The master library has been switched to the standby library, and the standby library has not been switched to the main library.

That is to say, both the master and backup libraries can be switched to the real master library at this time.

5. Start the database to recovery management mode on the standby database, and begin to prepare to accept the transfer of archived logs from the main database.

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

Note: if this step is not performed, the OPEN_MODE status will always be READ ONLY and cannot become a normal READ ONLY WITH APPLY.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT

Looking up the data shows that the above sentence is used to APPLY after 12c, and the yellow part is the difference from 11gR2.

6. Check the role of the master database again, and confirm that it has been switched to standby database, and OPEN_MODE is READ ONLY WITH APPLY.

SQL > select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE

CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY

Prepare the library and cut the main library

1. View the role of slave database:

SQL > select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE

CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY

SWITCHOVER_STATUS is TO PRIMARY, which means that the slave library can be switched to the master library.

2. Prepare the main command

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

3. Confirm that the slave database is in mount status.

SQL > select status,instance_name from v$instance

STATUS INSTANCE_NAME

--

MOUNTED dgtest

4. Call to OPEN status

SQL > ALTER DATABASE OPEN

Now the main library switches log files several times, and the standby library can synchronize log files.

Note: only after the slave library OPEN has been switched and becomes the new master library, the SWITCHOVER_STATUS status of the original master library will be changed from TO PRIMARY to NOT ALLOWED.

5. Check the role of the library again and confirm that it has been switched to the main library

SQL > select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE

CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--

CURRENT READ WRITE PRIMARY TO STANDBY

-at this point, the master / slave database has been switched, and the standby to primary process has been completed-

II. Failover disaster switching

The main library is down and cannot be started, so the standby library is enabled urgently. Operate directly on the slave database to change the standby database into the primary database role

Execute the following four commands on the standby database:

SQL > alter database recover managed standby database finish

SQL > alter database commit to switchover to primary

SQL > shutdown immediate

SQL > startup

Note: this operation is irreversible. ARCH counts from 1. Complete preparation and deployment of DG should be done again.

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.