Мой блог: 26 окт. 2010 г.



1. На primary:

[oradb]$ more listener.ora
SID_LIST_LISTENERDGMGRL=
   (SID_LIST=
     (SID_DESC=
         (SID_NAME=testasm)
         (GLOBAL_DBNAME=testasm_p_DGMGRL.mydomain.com)
         (ORACLE_HOME=/u01/app/oradb/product/10.2.0/db_1)
     )
   )

LISTENERDGMGRL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-1.mydomain.com)(PORT = 1522))
    )
  )

[oradb]$ more tnsnames.ora
TESTASM_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-1.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = testasm_p_DGMGRL.mydomain.com)
    )
  )

TESTASM_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-2.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testasm_s_DGMGRL.mydomain.com)
    )
  )

[oradb]$


2. На standby

[oradb]$ more listener.ora
SID_LIST_LISTENERDGMGRL=
   (SID_LIST=
     (SID_DESC=
         (SID_NAME=testasm)
         (GLOBAL_DBNAME=testasm_s_DGMGRL.mydomain.com)
         (ORACLE_HOME=/u01/app/oradb/product/10.2.0/db_1)
     )
   )

LISTENERDGMGRL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-2.mydomain.com)(PORT = 1522))
    )
  )
[oradb]$

[oradb]$ more tnsnames.ora
TESTASM_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-1.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = testasm_p_DGMGRL.mydomain.com)
    )
  )

TESTASM_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-2.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testasm_s_DGMGRL.mydomain.com)
    )
  )

[oradb]$


3. Создание конфигурации

 на primary:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.
 
SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

SQL> exit

на standby:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

SQL>


[oradb]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@TESTASM_P
Connected.
DGMGRL> create configuration 'brokerdg' as
> primary database is testasm_p
> connect identifier is TESTASM_P;
Configuration "brokerdg" created with primary database "testasm_p"
DGMGRL>


DGMGRL> add database testasm_s as
> connect identifier is TESTASM_S
> maintained as physical;
Database "testasm_s" added
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                brokerdg
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    testasm_p - Primary database
    testasm_s - Physical standby database

Current status for "brokerdg":
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                brokerdg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    testasm_p - Primary database
    testasm_s - Physical standby database

Current status for "brokerdg":
SUCCESS
DGMGRL>

DGMGRL> switchover to testasm_p
Performing switchover NOW, please wait...
Operation requires shutdown of instance "testasm" on database "testasm_s"
Shutting down instance "testasm"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "testasm" on database "testasm_p"
Shutting down instance "testasm"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "testasm" on database "testasm_s"
Starting instance "testasm"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "testasm" on database "testasm_p"
Starting instance "testasm"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "testasm_p"
DGMGRL>

4.Удаление конфигурации

[oradb]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@TESTASM_P
Connected.
DGMGRL> show configuration;

Configuration
  Name:                brokerdg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    testasm_p - Primary database
    testasm_s - Physical standby database

Current status for "brokerdg":
SUCCESS

DGMGRL> REMOVE DATABASE testasm_s;
Removed database "testasm_s" from the configuration
DGMGRL> REMOVE CONFIGURATION;
Removed configuration
DGMGRL> show configuration;
Error: ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL>

1. На primary:

[oradb]$ more inittestasm.ora
*.audit_file_dest='/u01/app/oradb/admin/testasm/adump'
*.background_dump_dest='/u01/app/oradb/admin/testasm/bdump'
*.core_dump_dest='/u01/app/oradb/admin/testasm/cdump'
*.user_dump_dest='/u01/app/oradb/admin/testasm/udump'
*.compatible='10.2.0.3.0'
*.control_files='+DATA/testasm/controlfile/current.256.730810345','+FRA/testasm/controlfile/current.256.730810345'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOA'
*.db_create_online_log_dest_2='+REDOB'
*.db_domain='mydomain.com'
*.db_file_multiblock_read_count=16
*.db_name='testasm'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=209715200000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testasmXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=198180864
*.processes=150
*.sga_target=595591168
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 26 14:11:56 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 176163984 bytes
Database Buffers 411041792 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile='+DATA' from pfile='/tmp/inittestasm.ora';

SQL> alter system set DB_UNIQUE_NAME='testasm_p' SCOPE=SPFILE;

SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open
SQL> archive log list

SQL> alter database force logging;
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testasm_p, testasm_s)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=testasm_p';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=testasm_s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_s';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set remote_login_passwordfile='EXCLUSIVE';
SQL> alter system set FAL_SERVER=testasm_s;
SQL> alter system set FAL_CLIENT=testasm_p;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;

SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;

[oradb]$ export ORACLE_SID=testasm
[oradb]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Oct 1 09:59:15 2010

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

connected to target database: TESTASM (DBID=28698405)

RMAN> sql 'alter system archive log current';

using target database control file instead of recovery catalog
sql statement: alter system archive log current

RMAN> backup database format '/mnt/u00/oradb/backups/%U';

Starting backup at 01-OCT-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA/testasm/datafile/system.260.730810349
input datafile fno=00002 name=+DATA/testasm/datafile/undotbs1.261.730810359
input datafile fno=00003 name=+DATA/testasm/datafile/sysaux.262.730810363
input datafile fno=00004 name=+DATA/testasm/datafile/users.264.730810367
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0qlpbkp8_1_1 tag=TAG20101001T100008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0rlpbkq2_1_1 tag=TAG20101001T100008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01-OCT-10

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN> backup archivelog all format '/mnt/u00/oradb/backups/%U';
Starting backup at 01-OCT-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=125 recid=131 stamp=731238743
input archive log thread=1 sequence=126 recid=132 stamp=731238751
input archive log thread=1 sequence=127 recid=133 stamp=731238757
input archive log thread=1 sequence=128 recid=134 stamp=731239175
input archive log thread=1 sequence=129 recid=135 stamp=731239265
input archive log thread=1 sequence=130 recid=136 stamp=731239278
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0slpbkre_1_1 tag=TAG20101001T100118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01-OCT-10

RMAN> backup current controlfile for standby format '/mnt/u00/oradb/backups/%U';
Starting backup at 01-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0tlpbkrv_1_1 tag=TAG20101001T100135 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-OCT-10

RMAN> exit

Recovery Manager complete.
[oradb]$

[oradb]$ ls -l
total 652540
-rw-r----- 1 oradb oinstall 608378880 Oct 1 10:00 0qlpbkp8_1_1
-rw-r----- 1 oradb oinstall 7372800 Oct 1 10:00 0rlpbkq2_1_1
-rw-r----- 1 oradb oinstall 44431360 Oct 1 10:01 0slpbkre_1_1
-rw-r----- 1 oradb oinstall 7340032 Oct 1 10:01 0tlpbkrv_1_1

Копируем backup на сервер server-2
[oradb]$ scp -pr * server-2:/mnt/u00/oradb/backups/
oradb's password:
0qlpbkp8_1_1 100% 580MB 41.4MB/s 00:14
0rlpbkq2_1_1 100% 7200KB 7.0MB/s 00:00
0slpbkre_1_1 100% 42MB 42.4MB/s 00:01
0tlpbkrv_1_1 100% 7168KB 7.0MB/s 00:00
[oradb]$

Копируем pfile с primary на snandby

[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 26 12:05:41 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/tmp/inittestasm.ora' from spfile;

File created.

SQL> exit

scp -pr /tmp/inittestasm.ora server-2:/tmp/inittestasm.ora

[oradb]$ vi /tmp/inittestasm.ora
*.audit_file_dest='/u01/app/oradb/admin/testasm/adump'
*.background_dump_dest='/u01/app/oradb/admin/testasm/bdump'
*.compatible='10.2.0.3.0'
*.control_files='+REDOA/testasm_s/controlfile/current.268.731087529','+REDOB/testasm_s/controlfile/current.268.731087533'
*.core_dump_dest='/u01/app/oradb/admin/testasm/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOA'
*.db_create_online_log_dest_2='+REDOB'
*.db_domain='msk.vbrr.loc'
*.db_file_multiblock_read_count=16
*.db_name='testasm'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=209715200000
*.db_unique_name='testasm_s'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testasmXDB)'
*.fal_client='TESTASM_S'
*.fal_server='TESTASM_P'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(testasm_p, testasm_s)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=testasm_s'
*.log_archive_dest_2='SERVICE=testasm_p LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_p'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=198180864
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=595591168
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oradb/admin/testasm/udump'

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 1 10:09:53 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/inittestasm.ora';
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 167775376 bytes
Database Buffers 419430400 bytes
Redo Buffers 6299648 bytes
SQL> create spfile='+DATA' from pfile='/tmp/inittestasm.ora';

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oradb]$

[oradb]$ export ORACLE_SID=testasm
[oradb]$ rman nocatalog;

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Oct 1 10:11:46 2010

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

RMAN> connect target sys/oracle@TESTASM_P

connected to target database: TESTASM (DBID=28698405)
using target database control file instead of recovery catalog

RMAN> connect auxiliary / ;

connected to auxiliary database: TESTASM (not mounted)

RMAN> duplicate target database for standby NOFILENAMECHECK;

Starting Duplicate Db at 01-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 01-OCT-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /mnt/u00/oradb/backups/0tlpbkrv_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/u00/oradb/backups/0tlpbkrv_1_1 tag=TAG20101001T100135
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+REDOA/testasm_s/controlfile/current.268.731087529
output filename=+REDOB/testasm_s/controlfile/current.268.731087533
Finished restore at 01-OCT-10

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=159 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /mnt/u00/oradb/backups/0qlpbkp8_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/u00/oradb/backups/0qlpbkp8_1_1 tag=TAG20101001T100008
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-OCT-10

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=731240824 filename=+DATA/testasm_s/datafile/system.287.731240809
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=731240824 filename=+DATA/testasm_s/datafile/undotbs1.288.731240809
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=731240824 filename=+DATA/testasm_s/datafile/sysaux.289.731240809
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=731240824 filename=+DATA/testasm_s/datafile/users.290.731240809
Finished Duplicate Db at 01-OCT-10

RMAN> exit

Recovery Manager complete.
[oradb@x4450-s2 backups]$

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 1 10:35:51 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
129 01-OCT-10 01-OCT-10
130 01-OCT-10 01-OCT-10

SQL>

На primary сервере:

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 1 10:40:06 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;

System altered.

SQL>

На standby:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
129 01-OCT-10 01-OCT-10
130 01-OCT-10 01-OCT-10
131 01-OCT-10 01-OCT-10
132 01-OCT-10 01-OCT-10
133 01-OCT-10 01-OCT-10
134 01-OCT-10 01-OCT-10

6 rows selected.

SQL>

Все работает

Останавливать резервную базу так:

SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

Стартовать резервную базу так :

SQL> startup nomount
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 167775376 bytes
Database Buffers 419430400 bytes
Redo Buffers 6299648 bytes
SQL> alter database mount standby database;
Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Добавим на резервную базу Standby Redo Log (как и на Primary)
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50m;

Database altered.

SQL> SELECT GROUP#,ThREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

GROUP# BYTES MEMBER
---------- ---------- -------------------------------------------------------
4 104857600 +REDOA/testasm_s/onlinelog/group_4.278.731243271
4 104857600 +REDOB/testasm_s/onlinelog/group_4.278.731243271
5 104857600 +REDOA/testasm_s/onlinelog/group_5.279.731243279
5 104857600 +REDOB/testasm_s/onlinelog/group_5.279.731243279
6 104857600 +REDOA/testasm_s/onlinelog/group_6.280.731243287
6 104857600 +REDOB/testasm_s/onlinelog/group_6.280.731243287

6 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Перевод standby в режим read only;

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

В этом режиме можно добавлять временные файлы и запускать отчеты.

SQL> alter tablespace temp add tempfile '+DATA' size 100M reuse;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/testasm_s/tempfile/temp.291.731245461
+DATA/testasm_s/tempfile/temp.292.731245479

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open read only;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>

Проверка отставания Standby:

Выполнить на Primary

COL DB_NAME FORMAT A8
COL PRIMARY_DB FORMAT A23
COL STANDBY_DB FORMAT A23
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A19

SELECT (SELECT NAME FROM V$DATABASE) DB_NAME,
       (SELECT VALUE from V$PARAMETER where name='db_unique_name') PRIMARY_DB,
       (SELECT DB_UNIQUE_NAME STANDBY_DB from v$ARCHIVE_DEST where TARGET='STANDBY' and DEST_ID=S.DEST_ID) STANDBY_DB,
       P.DEST_ID PRIMARY_DEST_ID,
       S.DEST_ID STANDBY_DEST_ID,
       P.THREAD#,
       P.LOG_ARCHIVED,
       S.LOG_APPLIED,
       APPLIED_TIME,
       LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
       (SYSDATE-APPLIED_TIME)*3600*24 TIME_GAP_SEC,
       round((SYSDATE-APPLIED_TIME)*3600*24/60) TIME_GAP_MIN
FROM (SELECT DEST_ID, THREAD#, MAX(SEQUENCE#) LOG_ARCHIVED, MAX(COMPLETION_TIME) ARCHIVED_TIME FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'NO' AND ARCHIVED='YES' and resetlogs_id=(select distinct resetlogs_id from gv$database_incarnation where status='CURRENT') GROUP BY DEST_ID, THREAD#) P,
     (SELECT DEST_ID, THREAD#, MAX(SEQUENCE#) LOG_APPLIED, MAX(COMPLETION_TIME) APPLIED_TIME FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND APPLIED='YES' and resetlogs_id=(select distinct resetlogs_id from gv$database_incarnation where status='CURRENT') GROUP BY DEST_ID, THREAD#) S
WHERE P.DEST_ID!=S.DEST_ID
AND P.THREAD#=S.THREAD#;

Запуск Apply Process на standby:

(start redo apply)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

(start redo apply using real-time apply)
SQL> alter database recover managed standby database using current logfile disconnect;

Остановка Apply Process на standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Apply с отставанием 90 мин.:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 90 DISCONNECT FROM SESSION;
проверяем:
select delay_mins from v$archive_dest where dest_id = 2;

Отменяем задержку:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Проверка apply:

на primary
alter database open;
alter system set log_archive_dest_state_2=enable

Пробуем тестировать
на primary:
alter system switch logfile;
убедится что логфайл был успешно применен на standby
select SEQUENCE#,FIRST_TIME,NEXT_TIME,ARCHIVED,APPLIED,creator from v$archived_log where status = 'A' order by sequence# ;

Switchover

На primary:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Освобождаем базу от активных сессий

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Теперь можно переключаться.

На primary:

SQL> alter database commit to switchover to physical standby;
Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database using current logfile disconnect;

На standby:

SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

На new_primary:

SQL> select max(sequence#) from v$archived_log;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;

Смотрим на new_standby:

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

Failover:

select * from v$archive_gap;
alter database register logfile '';
select unique thread# as "Thread", max(sequence#) over (partition by thread#) as "Last" from v$archived_log;

alter database recover managed standby database finish;
(или если нет standby redo logs)
alter database recover managed standby database finish skip standby logfile;

alter database commit to switchover to primary;
shutdown immediate;
startup;

select switchover status from v$database;

NOT ALLOWED - database no primary
PREPARING DICTIONARY - Logical standby preparing switch
PREPARING SWITCHOVER - Logical standby preparing switch.
RECOVERY NEEDED - Standby
SESSION ACTIVE - end active session before switchover
SWITHOVER PENDING - Standby
SWITCHOVER LATENT - switchover not end
TO LOGICAL STANBY - Logical standby
TO STANDBY

The following fixed views can be used to monitor the DataGuard Broker and Standby proc:

V$ARCHIVE_DEST Describes for the current instance, all the archived redo log destinations, their current value, mode, and status.
V$ARCHIVE_DEST_STATUS Displays runtime and configuration information for the archived redo log destinations.
V$ARCHIVED_LOG Displays archived redo log information from the control file, including archived log names.
V$DATABASE Provides database information from the control file. Including the status of the database.
V$LOG The view contains log file information from the online redo logs.

V$ARCHIVED_LOG Displays archived redo log information from the control file, including archived log names.
V$DATABASE Provides database information from the control file. Including the status of the database.
V$LOGFILE Contains information about the online/dataguard redo logs.
V$MANAGED_STANDBY Displays current and status information for some Oracle database server processes related to Data Guard.
V$STANDBY_LOG Display information about the dataguard redo logs.
V_$STANDBY_APPLY_SNAPSHOT
V_$DATAGUARD_CONFIG
V_$DATAGUARD_STATS
V_$DATAGUARD_STATUS

Догоняем standby :

1. On the standby database, stop the managed recovery process (MRP):
(1. Остановка наката на стендбае)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2.On the standby database, find the SCN which will be used for the incremental backup at the primary database:
(определяем SCN, от которого нужно делать на примари инкрементальный бэкап)

SQL> SELECT CURRENT_SCN FROM V$DATABASE; # 8708213563871

3. In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:
(Подключившись RMAN'ом к примари делаем инкрементальный бэкап от SCN, полученного в предыдущем шаге)

RMAN> BACKUP INCREMENTAL FROM SCN 8708213563871 DATABASE FORMAT '/flashback/incr_back/inc_%U' tag 'FORSTANDBY';

4.Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:
(переносим полученную инкрементальную копию на хост стендбая. Например:

SCP /flashback/incr_back/inc_* sbl-db-nw:/sblflashback/incr_back

5. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:
(подключаемся RMAN к стэндбаю и "каталогизируем" все части этого инкрементального бэкапа)

RMAN> CATALOG START WITH '/sblflashback/incr_back';

6.Recover the standby database with the cataloged incremental backup:
(Накатываем этот бэкап)

RMAN> RECOVER DATABASE NOREDO;

7. In RMAN, connect to the primary database and create a standby control file backup:
(Подключившись RMAN к примари, делаем стендбайную копию контролфайла):

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/flashback/incr_back/ctrl_%U.stb';

8. Copy the standby control file backup to the standby system. For example:
(копируем её на стендбай)
SCP /flashback/incr_back/ctrl_*.stb sbl-db-nw:/sblflashback/incr_back

9,10,11. Восстанавливаем контролфайл на стендбае из свежей копии:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/sblflashback/incr_back/ctrl_4umj23o5_1_1.stb';
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
_____________________________________________________
12,13 - Если местоположение датафайлов или redo-логов на стендбае отличается от примари:
If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/';
RMAN> SWITCH DATABASE TO COPY;

If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.
___________________________________________________

14. On the standby database, clear all standby redo log groups (there may be more than 3):
(на стендбае очищаем гурппы редо-логов)
ALTER DATABASE CLEAR LOGFILE GROUP 11;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 6;
ALTER DATABASE CLEAR LOGFILE GROUP 7;

15. On the standby database, restart Flashback Database:
(если используется FlashBack, то на стендбае перезапускаем его, чтобы "очистить");
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

15.1 Накатываем часть логов
SYS@SIBDB> recover standby database until cancel;

16. On the standby database, restart MRP:
(На стендбае рестартуем накат логов)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Или лучше из консоли Data Guard'а

dgmgrl /
DGMGRL> show configuration # - смотрим, с какими именами базы сконфигурированы.

# Для SIBDB :   Databases:
#    sibdb_pr - Primary database
#    sibdb_st - Physical standby database

DGMGRL> edit database sibdb_st set state='APPLY-ON';

Succeeded.

   Проверяем
DGMGRL> show database verbose sibdb_st
Database
Name:            sibdb_st
Role:            PHYSICAL STANDBY
Enabled:         YES
Intended State: APPLY-ON
Instance(s):     ^^^^^^^__________!!!!!!!!
    SIBDB
............

startup nomount;
alter database mount standby database;
recover standby database until cancel;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++
На стэндбае упал накат с 600 ошибкой. Сделали оффлайн дроп файлу, на который он ругнулся и продолжили накат:

1. остановка наката логов на стендбай
2. создание копии файла
RMAN> backup as compressed backupset datafile 237 format '/flashback2/backup/bk_%s_%p_%t' ;
#3. создание нового стэндбайного контролфайла:
#SQL> alter database create standby controlfile as '/flashback2/backup/ctl_SIBDB';
4. копирование
scp -p bk_86885_1_774530065 10.84.160.194:/oracle/arch/SIBDB/back/
#scp -p ctl_SIBDB 10.84.160.194:/oracle/admin/SIBDB/
5. Определяем каталог бэкапа для восстановления:
RMAN> CATALOG START WITH '/oracle/arch/SIBDB/back';
6. и восстанавливаем файл
RMAN> restore datafile 237;
7. переводим в онлайн
SQL> alter database datafile 237 online;
8. ручной накат
recover standby database until cancel;
9. как упадет с сообщение о неполном.отсутсвующем логе - толкаем накат
Alter database recover managed standby database disconnect;

I Переключение ролей (switchower)

При настроенном dataguard broker:

dgmgrl sys/

show configuration

Configuration - itsmprod

Protection Mode: MaxProtection
Databases:
    itsmprod     - Primary database
    itsmres      - Physical standby database
    itsmprod_stb - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

(переключаем primary на itsmres)

DGMGRL> switchover to itsmres;

по результатам отработки команды будут выданы "подсказки", что нужно сделать с переключаемыми инстансами:

Стартовать инстансы руками в нужном режиме (mount - для нового стендбая, open - для нового праймери)

Переподключится утилитой dgmgrl и еще раз проверить конфигурацию:

dgmgrl sys/

show configuration

Configuration - itsmprod

Protection Mode: MaxProtection
Databases:
    itsmprod     - Physical standby database
    itsmres      - Primary database
    itsmprod_stb - Physical standby database

------------------------------------------------------
Без dataguard broker

1.
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

+++
-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

++++++++++++++++++++++++++++++++++++++++++++++++

II Failover - открытие стендбая как праймери при недоступности текущего праймери:

При настроенном dataguard broker:

dgmgrl sys/

DGMGRL> failover to itsmres;

Стендбай автоматически открывается как праймери, конфигурация gataguard разрушается.

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

Без dataguard broker

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

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

Мой блог

вторник, 26 октября 2010 г.

Data Guard Broker

Data Guard

Проверка отставания Standby:

Перемещение SPFILE в ASM

ASMCMD

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

Мой блог

вторник, 26 октября 2010 г.

Data Guard Broker

Data Guard

Проверка отставания Standby:

Перемещение SPFILE в ASM

ASMCMD

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

вторник, 26 октября 2010 г.