Creating A Physical Standby With RMAN Active Duplicate In 11.2.0.3

Stella981
• 阅读 513

Creating A Physical Standby With RMAN Active Duplicate In 11.2.0.3

by Rene Antunez

May 28, 2013

Posted in: Technical Track

Tags: Oracle

Other DBAs have written about this topic, but I wanted it to be available on Pythian’s blog. When I searched for how this was done, other sites were either not very clear on the steps they did, assumed that you already knew what you are doing, or went through the steps too quickly.

If this is your first time building a standby, there is some terminology you need to know before going into any of the steps in creating your physical standby. It will help you to better understand your dataguard environment and what is being done, instead of simply copying a number of steps. These are just the definitions in Oracle’s documentation, but they will help you avoid the arduous search.

  • LOG_ARCHIVE_DEST_n .- It controls different aspects of how redo transport services transfer redo data from primary database destination to a standby.
    This parameter has several attributes that are needed to setup your Dataguard environment, I will only mention the critical ones:

    • ASYNC .-This is the default, the redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit.
      or
    • SYNC .-The redo data generated by a transaction must have been received by every enabled destination that has this attribute before that transaction can commit.
    • AFFIRM and NOAFFIRM .- Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.
    • DB_UNIQUE_NAME .- Specifies a unique name for the database at this destination. You must specify a name; there is no default value.
    • VALID_FOR .-Identifies when redo transport services can transmit redo data to destinations based on the following factors:
      • redo_log_type .-whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination
      • database_role .-whether the database is currently running in the primary or the standby role
  • FAL_SERVER .-Specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name.

  • FAL_CLIENT .-Specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client.
    The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).

  • LOG_ARCHIVE_CONFIG .- Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs.
    This parameter has several attributes, the most important for this exercise is below:

    • DG_CONFIG .- Specifies a list of up to 30 unique database names (defined with the DB_UNIQUE_NAME initialization parameter) for all of the databases in the Data Guard configuration.

Now that we have the definitions out of the way (which you can find at Oracle 11.2. Documentation), we will continue with the setup of our Physical Standby.

For this exercise, I have the following :

  • Primary : testgg1   Server : dlabvm13
  • Standby : testgg2   Server : dlabvm14

The first thing that we need to do is find where the Redo Logs and Datafiles reside in the Primary and where will they reside in the Standby so that you can set your parameters LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT properly. Make sure that these directories have the necessary space to hold the Primary database. If you don’t have this space, then do not continue.

Next, assure that you are in archivelog mode, and that force logging is enabled in your primary.

If your database is not in archive log mode, do the following:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2230072 bytes

Variable Size 163580104 bytes

Database Buffers 364904448 bytes

Redo Buffers 3747840 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open``;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> select log_mode,force_logging from v$``database``;

LOG_MODE FOR

------------ ---

ARCHIVELOG YES

Now that we are running in archive log mode and force logging is set for the primary, make sure that the Listener/ Tns entries are set correctly and that you can tnsping them both from the primary/standby.

For more details on how to set them up, go to Kamran’s blog entry ( Step 2 ).

The result in both the primary and standby servers should be similar to below:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin

oracle $ tnsping testgg1

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:34

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

Used parameter files:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm13.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg1)))

OK (0 msec)

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin

oracle $ tnsping testgg2

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:40

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

Used parameter files:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm14.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg2)))

OK (10 msec)

Then, create and replicate the password file from the primary $ORACLE_HOME/dbs, and rename it to the standby database name. The password file name must match the ORACLE_SID used at the standby site, not the DB_NAME.

1

2

3

4

5

6

7

oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11``.2.0``/dbhome_1/dbs

oracle $ orapwd file``=orapwtestgg1 password=``test entries=5

oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11``.2.0``/dbhome_1/dbs

oracle $ scp orapwtestgg1 dlabvm14:``/u01/app/oracle/product/11``.2.0``/dbhome_1/dbs/orapwtestgg2

orapwtestgg1 100% 2048 2.0KB``/s 00:00

Now that the password file has been created, you can setup the init file for the Primary Database. As you can see at the end, it’s the parameters we explained at the beginning of this entry.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

SQL> !cat dg.ora

*.audit_file_dest=``'/u01/app/oracle/oradata/dump/testgg1/adump'

*.audit_trail=``'db'

*.compatible=``'11.2.0'

*.control_files=``'/u01/app/oracle/oradata/testgg1/control/control01.ctl'``,``'/u01/app/oracle/oradata/testgg1/control/control02.ctl'

*.db_block_size=8192

*.db_cache_size=128M

*.db_domain=``''

*.db_name=``'testgg1'

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

*.db_recovery_file_dest_size=2G

*.db_unique_name=``'testgg1'

*.diagnostic_dest=``'/u01/app/oracle/oradata/dump/testgg1'

*.log_archive_dest_1=``'location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg1'

*.log_archive_dest_2=``'SERVICE=testgg2 ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testgg2'

*.log_archive_dest_state_1=``'enable'

*.log_archive_dest_state_2=``'enable'

*.open_cursors=300

*.pga_aggregate_target=128M

*.processes=150

*.remote_login_passwordfile=``'EXCLUSIVE'

*.sga_target=512M

testgg11.UNDO_TABLESPACE=``'UNDOTBS1'

FAL_SERVER=testgg2;

FAL_CLIENT=testgg1;

DB_FILE_NAME_CONVERT=``'/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'``,``'/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'

LOG_FILE_NAME_CONVERT=``'/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'``,``'/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'

LOG_ARCHIVE_CONFIG=``'DG_CONFIG=(testgg1,testgg2)'

One of the coolest things about this method is that almost all of the work we will do will be in the Primary database server. The only thing you have to do in the Standby Server is create the locations of my diagnostic files/redo/datafiles/control files, verify the connectivity between the Primary and the Standby and just start the Standby Instance, which is our next step.

The next step is to set the ORACLE_SID, ORACLE_HOME, and ORACLE_BASE  for the Standby Instance and open it with minimal options:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin

oracle $ export ORACLE_HOME=``"/u01/app/oracle/product/11.2.0/dbhome_1"

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin

oracle $ export ORACLE_BASE=/u01/app/oracle

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin

oracle $ export ORACLE_SID=testgg2

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin

oracle $ cat dg.ora

DB_NAME=testgg1

DB_UNIQUE_NAME=testgg2

DB_BLOCK_SIZE=8192

oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin

oracle $ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 09:53:30 2013

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

Enter user``-``name``: /``as sysdba

Connected to an idle instance.

SQL> startup nomount pfile=``'/home/oracle/bin/dg.ora'``;

ORACLE instance started.

Total System Global Area  238034944 bytes

Fixed Size              2227136 bytes

Variable Size            180356160 bytes

Database Buffers        50331648 bytes

Redo Buffers              5120000 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now let’s get back to the Primary database server, where the next thing to do is add the standby logs. In this case, I created two with the same size as the Online Redo Logs that I have in my Primary.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME

---------------- ----------------------------------------------------------------

testgg1 dlabvm13.dlab.pythian.com

SQL> select name from v$``database``;

NAME

---------

TESTGG1

SQL> select GROUP``#,THREAD#,MEMBERS,BYTES FROM V$LOG;

GROUP``# THREAD# MEMBERS BYTES

---------- ---------- ---------- ----------

1 1 1 104857600

2 1 1 104857600

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;

Database altered.

SQL> SELECT GROUP``#, BYTES FROM V$STANDBY_LOG;

GROUP``# BYTES

---------- ----------

3 104857600

4 104857600

Once we have created the Standby Logs in my Primary Database, we will create the following RMAN run commands and  the changes to the standby’s spfile in this RMAN block.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin

oracle $ cat duplicate.rmn

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'testgg1'``,``'testgg2'

set db_unique_name=``'testgg2'

set db_file_name_convert=``'/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'``,``'/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'

set log_file_name_convert=``'/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'``,``'/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'

set control_files=``'/u01/app/oracle/oradata/testgg2/control/control01.ctl'``,``'/u01/app/oracle/oradata/testgg2/control/control02.ctl'

set log_archive_max_processes=``'10'

set fal_client=``'testgg2'

set fal_server=``'testgg1'

set standby_file_management=``'AUTO'

set log_archive_config=``'dg_config=(testgg1,testgg2)'

set log_archive_dest_2=``'service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'

;

}

Now that the the RMAN command file has been created, you just need to run this command in the target database, which is the Primary Database , connecting to the Standby as the auxiliary.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin

oracle $ rman target sys/test@testgg1 auxiliary sys/test@testgg2

Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 16 08:42:46 2013

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

connected to target database``: TESTGG1 (DBID=839852638)

connected to auxiliary database``: TESTGG1 (``not mounted)

RMAN> @duplicate.rmn

RMAN>

RMAN> run {

2> allocate channel prmy1 type disk;

3> allocate channel prmy2 type disk;

4> allocate channel prmy3 type disk;

5> allocate channel prmy4 type disk;

6> allocate auxiliary channel stby type disk;

7> duplicate target database for standby from active database

8> spfile

9> parameter_value_convert 'testgg1'``,``'testgg2'

10> set db_unique_name=``'testgg2'

11> set db_file_name_convert=``'/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'``,``'/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'

12> set log_file_name_convert=``'/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'``,``'/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'

13> set control_files=``'/u01/app/oracle/oradata/testgg2/control/control01.ctl'``,``'/u01/app/oracle/oradata/testgg2/control/control02.ctl'

14> set log_archive_max_processes=``'10'

15> set fal_client=``'testgg2'

16> set fal_server=``'testgg1'

17> set standby_file_management=``'AUTO'

18> set log_archive_config=``'dg_config=(testgg1,testgg2)'

19> set log_archive_dest_2=``'service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'

20> ;

21> }

using target database control file instead of recovery catalog

allocated channel: prmy1

channel prmy1: SID=32 device type=DISK

allocated channel: prmy2

channel prmy2: SID=33 device type=DISK

allocated channel: prmy3

channel prmy3: SID=34 device type=DISK

allocated channel: prmy4

channel prmy4: SID=35 device type=DISK

allocated channel: stby

channel stby: SID=19 device type=DISK

Starting Duplicate Db at 16-MAY-13

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg1' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2' targetfile

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg1.ora' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora' ;

sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora''"``;

}

executing Memory Script

Starting backup at 16-MAY-13

Finished backup at 16-MAY-13

sql statement: alter system set spfile= ''``/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestgg2.ora``''

contents of Memory Script:

{

sql clone "alter system set audit_file_dest =

''/u01/app/oracle/oradata/dump/testgg2/adump'' comment=

'''' scope=spfile"``;

sql clone "alter system set diagnostic_dest =

''/u01/app/oracle/oradata/dump/testgg2'' comment=

'''' scope=spfile"``;

sql clone "alter system set log_archive_dest_1 =

''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg2'' comment=

'''' scope=spfile"``;

sql clone "alter system set db_unique_name =

''testgg2'' comment=

'''' scope=spfile"``;

sql clone "alter system set db_file_name_convert =

''/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'' comment=

'''' scope=spfile"``;

sql clone "alter system set log_file_name_convert =

''/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'', ''/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'' comment=

'''' scope=spfile"``;

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/testgg2/control/control01.ctl'', ''/u01/app/oracle/oradata/testgg2/control/control02.ctl'' comment=

'''' scope=spfile"``;

sql clone "alter system set log_archive_max_processes =

10 comment=

'''' scope=spfile"``;

sql clone "alter system set fal_client =

''testgg2'' comment=

'''' scope=spfile"``;

sql clone "alter system set fal_server =

''testgg1'' comment=

'''' scope=spfile"``;

sql clone "alter system set standby_file_management =

''AUTO'' comment=

'''' scope=spfile"``;

sql clone "alter system set log_archive_config =

''dg_config=(testgg1,testgg2)'' comment=

'''' scope=spfile"``;

sql clone "alter system set log_archive_dest_2 =

''service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'' comment=

'''' scope=spfile"``;

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set audit_file_dest = ''``/u01/app/oracle/oradata/dump/testgg2/adump``'' comment= ''``'' scope=spfile

sql statement: alter system set diagnostic_dest = ''``/u01/app/oracle/oradata/dump/testgg2``'' comment= ''``'' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''``location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg2``'' comment= ''``'' scope=spfile

sql statement: alter system set db_unique_name = ''``testgg2``'' comment= ''``'' scope=spfile

sql statement: alter system set db_file_name_convert = ''``/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/``''``, ''``/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/``'' comment= ''``'' scope=spfile

sql statement: alter system set log_file_name_convert = ''``/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/``''``, ''``/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/``'' comment= ''``'' scope=spfile

sql statement: alter system set control_files = ''``/u01/app/oracle/oradata/testgg2/control/control01.ctl``''``, ''``/u01/app/oracle/oradata/testgg2/control/control02.ctl``'' comment= ''``'' scope=spfile

sql statement: alter system set log_archive_max_processes = 10 comment= ''``'' scope=spfile

sql statement: alter system set fal_client = ''``testgg2``'' comment= ''``'' scope=spfile

sql statement: alter system set fal_server = ''``testgg1``'' comment= ''``'' scope=spfile

sql statement: alter system set standby_file_management = ''``AUTO``'' comment= ''``'' scope=spfile

sql statement: alter system set log_archive_config = ''``dg_config=(testgg1,testgg2)``'' comment= ''``'' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''``service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1``'' comment= ''``'' scope=spfile

Oracle instance shut down

connected to auxiliary database (``not started)

Oracle instance started

Total System Global Area 534462464 bytes

Fixed Size 2230072 bytes

Variable Size 163580104 bytes

Database Buffers 364904448 bytes

Redo Buffers 3747840 bytes

allocated channel: stby

channel stby: SID=18 device type=DISK

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/testgg2/control/control01.ctl'``;

restore clone controlfile to '/u01/app/oracle/oradata/testgg2/control/control02.ctl' from

'/u01/app/oracle/oradata/testgg2/control/control01.ctl'``;

}

executing Memory Script

Starting backup at 16-MAY-13

channel prmy1: starting datafile copy

copying standby control file

output file name``=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testgg1.f tag=TAG20130516T084319 RECID=7 STAMP=815561001

channel prmy1: datafile copy complete, elapsed time``: 00:00:07

Finished backup at 16-MAY-13

Starting restore at 16-MAY-13

channel stby: copied control file copy

Finished restore at 16-MAY-13

contents of Memory Script:

{

sql clone 'alter database mount standby database'``;

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_temp_8qbok7fk_.tmp"``;

switch clone tempfile all``;

set newname for datafile 1 to

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf"``;

set newname for datafile 2 to

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf"``;

set newname for datafile 3 to

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf"``;

set newname for datafile 4 to

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf"``;

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf" ;

sql 'alter system archive log current'``;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_temp_8qbok7fk_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 16-MAY-13

channel prmy1: starting datafile copy

input datafile file number=00001 name``=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_system_8ocl7bho_.dbf

channel prmy2: starting datafile copy

input datafile file number=00002 name``=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_sysaux_8ocl7k6n_.dbf

channel prmy3: starting datafile copy

input datafile file number=00003 name``=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf

channel prmy4: starting datafile copy

input datafile file number=00004 name``=/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/o1_mf_users_8qbolh3g_.dbf

output file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf tag=TAG20130516T084335

channel prmy3: datafile copy complete, elapsed time``: 00:00:45

output file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf tag=TAG20130516T084335

channel prmy4: datafile copy complete, elapsed time``: 00:00:55

output file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf tag=TAG20130516T084335

channel prmy1: datafile copy complete, elapsed time``: 00:01:05

output file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf tag=TAG20130516T084335

channel prmy2: datafile copy complete, elapsed time``: 00:01:05

Finished backup at 16-MAY-13

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all``;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=815561103 file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_system_8ocl7bho_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=815561104 file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sysaux_8ocl7k6n_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=815561104 file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_sys_undo_8ocl7q6c_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=10 STAMP=815561104 file name``=/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/o1_mf_users_8qbolh3g_.dbf

Finished Duplicate Db at 16-MAY-13

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

RMAN> **``end``-``of``-file**

RMAN> exit

As you can see, that was as easy as pie. Now we can just start the recovery process in the Standby Database. In this case, I used Active Dataguard so that I could show you that it is actually working, but just be aware that this is a Licensable Option.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

SQL> alter database open``;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select protection_mode,protection_level,database_role,``name from v$``database``;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME

-------------------- -------------------- ---------------- ---------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME

---------------- ----------------------------------------------------------------

testgg2 dlabvm14.dlab.pythian.com

SQL> select * from sender.test;

select * from sender.test

*

ERROR at line 1:

ORA-00942: table or view does not exist

Now, I go back to the primary and create a table called test in the schema sender. As you can see above, this table doesn’t exist in the Standby database.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME

---------------- ----------------------------------------------------------------

testgg1 dlabvm13.dlab.pythian.com

SQL> select protection_mode,protection_level,database_role,``name from v$``database``;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME

-------------------- -------------------- ---------------- ---------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TESTGG1

SQL> create table sender.test(id number);

Table created.

SQL> insert into sender.test values``(1);

1 row created.

SQL> commit``;

Commit complete.

SQL> alter system switch logfile;

System altered.

I go back to the Standby, and we can see that we have the table that we just created.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SQL> select protection_mode,protection_level,database_role,``name from v$``database``;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME

-------------------- -------------------- ---------------- ---------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME

---------------- ----------------------------------------------------------------

testgg2 dlabvm14.dlab.pythian.com

SQL> select * from sender.test;

ID

----------

1

There are various ways to see if this is working or not. You can use it in the following query to see what was the last archive received/applied in the Standby Database.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SQL> SELECT 'Last Applied : ' Logs,

TO_CHAR(next_time,``'DD-MON-YY:HH24:MI:SS'``) TIME``,thread#,``sequence``#

FROM v$archived_log

WHERE sequence``# =

(``SELECT MAX``(``sequence``#) FROM v$archived_log WHERE applied=``'YES'

)

UNION

SELECT 'Last Received : ' Logs,

TO_CHAR(next_time,``'DD-MON-YY:HH24:MI:SS'``) TIME``,thread#,``sequence``#

FROM v$archived_log

WHERE sequence``# =

(``SELECT MAX``(``sequence``#) FROM v$archived_log );

LOGS TIME THREAD# SEQUENCE``#

---------------- ------------------ ---------- ----------

Last Applied : 16-MAY-13:11:42:52 1 65

Last Received : 16-MAY-13:11:42:52 1 65

While in the Primary Database, you can check what is the current sequence and what is the pending sequence to be applied.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

SQL> SELECT

(``SELECT name FROM V$``DATABASE

) name``,

(``SELECT MAX (``sequence``#) FROM v$archived_log WHERE dest_id = 1

) Current_primary_seq,

(``SELECT MAX (``sequence``#)

FROM v$archived_log

WHERE TRUNC(next_time) > SYSDATE - 1

AND dest_id = 2

) max_stby,

(``SELECT NVL (

(``SELECT MAX (``sequence``#) - MIN (``sequence``#)

FROM v$archived_log

WHERE TRUNC(next_time) > SYSDATE - 1

AND dest_id = 2

AND applied = 'NO'

), 0)

FROM DUAL

) "To be applied"``,

(

(``SELECT MAX (``sequence``#) FROM v$archived_log WHERE dest_id = 1

) -

(``SELECT MAX (``sequence``#) FROM v$archived_log WHERE dest_id = 2

)) "To be Shipped"

FROM DUAL;

NAME CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped

--------- ------------------- ---------- ------------- -------------

TESTGG1 65 65 0 0

I hope this little guide helps you out when you are trying to build your Physical Standby from an Active Duplicate. As always, test anything that I have said or mentioned before trying it in a production environment.

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
3年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
Java获得今日零时零分零秒的时间(Date型)
publicDatezeroTime()throwsParseException{    DatetimenewDate();    SimpleDateFormatsimpnewSimpleDateFormat("yyyyMMdd00:00:00");    SimpleDateFormatsimp2newS
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这