Physical Standby тохируулах

oracle

#1

Data Guard-ийн талаар оруулсан танилцуулга мэдээллийг энд дарж үзнэ үү.

Уг тохиргоог хэд хэдэн янзаар хийж болох боловч би энэ удаад өөрийн мэдэх байж болох хамгийн товч хувилбарыг орууллаа.

Үндсэн бааз - Primary (node111.oracle.me) - ORCL
Хуулбар бааз Standby (node112.oracle.me) - SBY1

Primary тохиргоо (node111.oracle.me)

Archivelog идэвхжүүлээгүй бол идэвжүүлээрэй.

-- pfile гаргаж авах
SQL> create pfile='/tmp/initSBY1.ora' from spfile;
File created.

-- archivelog той холбоотой тохиргоонууд
SQL> alter system set log_archive_dest_2='service=SBY1 valid_for=(primary_role, online_logfiles) db_unique_name=SBY1'
System altered.

SQL> alter system set standby_file_management=auto;
System altered.

SQL> alter system set log_archive_config='dg_config=(ORCL,SBY1)';
System altered.

Лог файлууд архивлагдсан эсэхийг дараах коммандаар шалгана.

SQL> select sequence#,applied,status, to_char(first_time,'dd-mm-yyyy hh24:mi:ss') from v$archived_log;

 SEQUENCE# APPLIED   S TO_CHAR(FIRST_TIME,
---------- --------- - -------------------
	 9 NO	     A 28-12-2016 02:39:11
	10 NO	     A 28-12-2016 02:41:44
	11 NO	     A 29-12-2016 00:02:45
	12 NO	     A 29-12-2016 00:05:49
	13 NO	     A 29-12-2016 00:07:22

Primary серверийг зааж өгнө. Oracle 11g дээр тохируулах шаардлагагүй бөгөөд Oracle 12c дээр харин заавал тохируулж өгнө.

SQL> alter system set fal_server=ORCL;
System altered.

pfile-ийг standby-д зориулж засварлана.

[oracle@node111 ~]$ vi /tmp/initSBY1.ora

*.audit_file_dest='/u01/app/oracle/admin/SBY1/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/SBY1/control01.ctl','/u01/app/oracle/fast_recovery_area/SBY1/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=4800m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SBY1XDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=190m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=470m
*.undo_tablespace='UNDOTBS1'


db_unique_name='SBY1'
service_names=SBY1
standby_file_management=auto
db_file_name_convert='ORCL','SBY1'
log_file_name_convert='ORCL','SBY1'

log_archive_config='dg_config=(ORCL,SBY1)'
log_archive_dest_2='service=ORCL valid_for=(primary_role,online_logfiles) db_unique_name=ORCL'

Засварлаж тохируулсан pfile-ийг standby сервер рүү хуулах

[oracle@node111 ~]$ scp /tmp/initSBY1.ora oracle@node112.oracle.me:/tmp/

Сүлжээнээс холбогдох тохиргоог хийх

[oracle@node111 ~]$ cd $ORACLE_HOME/network/admin/
[oracle@node111 ~]$ vi tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node111.oracle.me)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node112.oracle.me)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SBY1)
    )
  )


[oracle@node111 ~]$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = SBY1)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = SBY1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = SBY1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node111.oracle.me)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@node111 ~]$ lsnrctl reload

Standby тохиргоо (node112.oracle.me)

SBY1-ийг oratab файлд нэмж өгөх

[oracle@node112 ~]$ cat /etc/oratab
SBY1:/u01/app/oracle/product/12.1.0/db_1:N

Орчныг тохируулах

[oracle@node112 ~]$ . oraenv
ORACLE_SID = [SBY1] ? SBY1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@node112 ~]$

Баазын хавтсуудыг үүсгэх

[oracle@node112 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/SBY1
[oracle@node112 ~]$ mkdir -p /u01/app/oracle/oradata/SBY1
[oracle@node112 ~]$ mkdir -p /u01/app/oracle/admin/SBY1/adump

Standby instance асаах

[oracle@node112 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 29 05:31:33 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile='/tmp/initSBY1.ora';
File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  492707840 bytes
Fixed Size		    2289928 bytes
Variable Size		  260050680 bytes
Database Buffers	  226492416 bytes
Redo Buffers		    3874816 bytes
SQL>

Сүлжээнээс холбогдох тохиргоо

[oracle@node112 ~]$ cd /u01/app/oracle/product/12.1.0/db_1/network/admin/
[oracle@node112 admin]$ vi tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node111.oracle.me)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node112.oracle.me)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SBY1)
    )
  )

listener тохируулах

[oracle@node112 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = SBY1)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = SBY1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = SBY1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node112.oracle.me)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

node111.oracle.me (primary)

Нууц үгний файлыг хуулах. Ингэснээр standby бааз руу primary баазын sys хэрэглэгчийн нууц үгийг ашиглан хандах боломжтой болно

[oracle@node111 ~]$ cd /u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@node111 dbs]$ scp orapwORCL node112.oracle.me:/u01/app/oracle/product/12.1.0/db_1/dbs/orapwSBY1

Үндсэн баазыг RMAN ашиглаж хуулбарлах

[oracle@node111 admin]$ rman target sys@ORCL auxiliary sys@SBY1

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Dec 29 00:20:34 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1459527240)
connected to auxiliary database (not started)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 29-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwORCL' auxiliary format
 '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwSBY1'   ;
}
executing Memory Script

Starting backup at 29-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
Finished backup at 29-DEC-16

contents of Memory Script:
{
   restore clone from service  'ORCL' standby controlfile;
}
executing Memory Script

Starting restore at 29-DEC-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORCL
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/SBY1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/SBY1/control02.ctl
Finished restore at 29-DEC-16

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/SBY1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/SBY1/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/SBY1/example01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/SBY1/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/SBY1/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/SBY1/users01.dbf";
   restore
   from service  'ORCL'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/SBY1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 29-DEC-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORCL
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/SBY1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORCL
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/SBY1/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORCL
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/SBY1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORCL
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/SBY1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:14
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORCL
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/SBY1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-DEC-16

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=1 STAMP=931844747 file name=/u01/app/oracle/oradata/SBY1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=931844747 file name=/u01/app/oracle/oradata/SBY1/example01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=931844747 file name=/u01/app/oracle/oradata/SBY1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=931844747 file name=/u01/app/oracle/oradata/SBY1/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=931844747 file name=/u01/app/oracle/oradata/SBY1/users01.dbf
Finished Duplicate Db at 29-DEC-16

RMAN> exit

node112.oracle.me (standby)

Standby log файл нэмэх. Нийт одоогийн байгаа нийт лог файлаас нэгээр их байна.

SQL> alter system set fal_server=ORCL;
System altered.

SQL> col member format a40
SQL> select group#,status,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------
	 3	   /u01/app/oracle/oradata/ORCL/redo03.log
	 2	   /u01/app/oracle/oradata/ORCL/redo02.log
	 1	   /u01/app/oracle/oradata/ORCL/redo01.log

SQL>
SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/SBY1/re&a') size 50m;
Enter value for a: 4
Enter value for a: 4.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/SBY1/re&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SBY1/re4.log') size 50m

Database altered.

SQL> /
Enter value for a: 5
Enter value for a: 5.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/SBY1/re&a') size 50m
new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SBY1/re5.log') size 50m

Database altered.

SQL> /
Enter value for a: 6
Enter value for a: 6.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/SBY1/re&a') size 50m
new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SBY1/re6.log') size 50m

Database altered.

SQL> /
Enter value for a: 7
Enter value for a: 7.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/SBY1/re&a') size 50m
new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SBY1/re7.log') size 50m

Database altered.

Standby-ийг ажиллуулах

SQL> recover managed standby database disconnect;
Media recovery complete.

Дараах коммандыг бүх сервер дээр ажиллуулж харьцуулж харж шалгаж болно.

-- standby
 SQL> select sequence#,applied,status, to_char(first_time,'dd-mm-yyyy hh24:mi:ss') from v$archived_log;

 SEQUENCE# APPLIED   S TO_CHAR(FIRST_TIME,
---------- --------- - -------------------
	15 IN-MEMORY A 29-12-2016 00:23:37
	14 YES	     A 29-12-2016 00:11:34
	16 YES	     A 29-12-2016 00:23:53
	17 NO	     A 29-12-2016 00:25:08


-- primary
SQL> select sequence#,applied,status, to_char(first_time,'dd-mm-yyyy hh24:mi:ss') from v$archived_log;

 SEQUENCE# APPLIED   S TO_CHAR(FIRST_TIME,
---------- --------- - -------------------
	 9 NO	     A 28-12-2016 02:39:11
	10 NO	     A 28-12-2016 02:41:44
	11 NO	     A 29-12-2016 00:02:45
	12 NO	     A 29-12-2016 00:05:49
	13 NO	     A 29-12-2016 00:07:22
	14 NO	     A 29-12-2016 00:11:34
	15 NO	     A 29-12-2016 00:23:37
	15 YES	     A 29-12-2016 00:23:37
	14 YES	     A 29-12-2016 00:11:34
	16 NO	     A 29-12-2016 00:23:53
	16 YES	     A 29-12-2016 00:23:53
	17 NO	     A 29-12-2016 00:25:08
	17 NO	     A 29-12-2016 00:25:08

13 rows selected.

Standby-ийг зогсоох, болиулахдаа дараах коммандыг ажиллуулна.

SQL> recover managed standby database cancel;
Media recovery complete.

Уг процесс эхэлсэн эсэхийг мөн дараах байдлаар шалгаж болно.

[oracle@node112 ~]$ ps aux | grep mrp
oracle    5640  0.0  1.9 826808 40324 ?        Ss   05:49   0:00 ora_mrp0_SBY1
oracle    6674  0.0  0.0  61180   760 pts/1    S+   06:57   0:00 grep mrp
[oracle@node112 ~]$

#2

Энэ заавраар windows server, oralce 11g r2 дээр хийхэд асуух зүйл байна:
1.

энэ миний тохиолдолд C:\app\Administrator\flash_recovery_area гэж байна зөв үү?
2.

Энэ /tmp/ directory-г windows дээр олдоггүй ээ


#3

Энэ өмнө асуусан 2-оо учрыг нь олчихлоо одоо
3.

Нууц үгний файлыг хуулах. Ингэснээр standby бааз руу primary баазын sys хэрэглэгчийн нууц үгийг ашиглан хандах боломжтой болно
[oracle@node111 ~]$ cd /u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@node111 dbs]$ scp orapwORCL node112.oracle.me:/u01/app/oracle/product/12.1.0/db_1/dbs/orapwS

Энэ дээр л манараад байна. Энэ path дээр дараах file-ууд байна аль нь юм болоо?
init.ora
opctrn11.h
opctrn11
oracle11
perf11.ora
Эсвэл үүсгэдэг юм уу? Бас хуулахдаа дарж хуулах уу?


#4

Ийм алдаа зааж байгаа
auxiliary


#5

orapw{SID} файл 2 сервер дээр байх шаардлагатай. Гаднаас хандахад ашиглагддаг файл.

$ORACLE_HOME/dbs хавтсанд байрлана


#6

Минийх windows server yumaa

энэ хавтасанд: orapw{SID} ийм file алга

дараах file-ууд л байна


#7

orapwd коммандын тусламжтайгаар үүсгэж болно.


#8

Видео хичээл бэлтгэж орууллаа.


#9

duplicate хийхэд ийм алдаа гарч яах ёстой юм бол

image


#10

set newname нөхцлийг ашиглаад файлуудын нэрээ зөв замруу хөрвүүлчих


#11

Dupilcate нь энгийнээр “restore control file”, “restore database”, recover хийх үйлдлүүдийг parameter, tns, listener файлуудийн дагуу bundle - аар нь хийдэг. Өөрөөр хэлбэл бүх config duplicate хийхээс өмнө зөв байх ёстой. Доорх мэдээллүүд, коммандын үр дүнг явуулаач хө.

  • init файл
  • tnsnames.ora
  • lsnrctl services
  • select name from v$datafile;
  • rman connect хийж байгаа комманд

#12

Өдөр RAC to Single DG тохируулж байхад гарч байсан алдаа байнаа. ASM -с файлсистем рүү хөрвүүлж чадахгүй болохоор нь set newname ашиглаад явуулчихсан. Араас нь duplicate коммандаа залгуулаад явуулна .

run{
    set newname for datafile 1 to '/u01/app/oracle/oradata/ORCL/datafile/01.dbf';
    set newname for datafile 2 to '/u01/app/oracle/oradata/ORCL/datafile/02.dbf';
    duplicate target database for standby from active database nofilenamecheck;
}

19505 яг санаж байна :smiley: db_file_name_convert болохгүй байна лээ. 11.2.0.4 байсан билүү дээ.