Problem Description Using Rman to backup a NOARCHIVELOG failed with error : channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/20/2013 16:48:31 ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode Cause Startup mount force cause datafile was not closed cleanly Solution Following commands will guarantee that the database is in a consistent state for a backup: RMAN> shutdown immediate; RMAN> STARTUP FORCE DBA; RMAN> shutdown immediate; RMAN> STARTUP MOUNT; RMAN> backup database;
Thursday, June 20, 2013
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
Friday, June 14, 2013
migrate oracle database to ASM using RMAN
1. Create pfile from spfile;
2. Back up the data files to the Oracle ASM disk group.
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP AS COPY
INCREMENTAL LEVEL 0
DATABASE
FORMAT '+DATA'
TAG 'ORA_ASM_MIGRATION';
}
3. Archive online log
RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
4. Backup spfile
RMAN> BACKUP AS BACKUPSET SPFILE;
5. Restore spfile in ASM
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RESTORE SPFILE TO '+DATA/spfilesid.ora';
RMAN> SHUTDOWN IMMEDIATE;
6. Set Oracle Managed Files initialization parameters to Oracle ASM locations.
SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FLASH' SID='*';
7. Set the CONTROL_FILES initialization parameter to Oracle ASM locations.
SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA','+FLASH' SCOPE=SPFILE SID='*';
8. Migrate the control file to Oracle ASM and mount the control file.
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM 'original_cf_name';
RMAN> ALTER DATABASE MOUNT;
9. Migrate the data files to Oracle ASM.
SWITCH DATABASE TO COPY;
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
RECOVER DATABASE;
}
10. Drop the tempfiles and re-create them in Oracle ASM.
If you get following error, bounce the database and redo it.
ORA-25152: TEMPFILE cannot be dropped at this time
SQL> ALTER DATABASE TEMPFILE 'tempfile_name' DROP;
SQL> ALTER TABLESPACE temp_tbs_name ADD TEMPFILE;
11. Migrate the online redo log files.
SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/
Reference : http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#BABJAFIF
Using Rman to Migrate database from windows(11.2.0.1) to linux(11.2.0.3)
1 Verify following information on Winodow platform:
SQL> select * from v$version;
SQL> select platform_id, platform_name from v$database;
SQL> show parameter compatible;
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
2 Check platform compatibility between source and target OS(Make sure ENDIAN_FORMAT are same):
SQL> col platform_name format a40
SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' or platform_name = 'Microsoft Windows x86 64-bit' order by 2;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
13 Linux x86 64-bit Little
12 Microsoft Windows x86 64-bit Little
3 Start the source database in read only mode:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open read only;
4. Check database readiness for transport from Windows to Linux:
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux x86 64-bit');
5 end;
6 /
5. Check if there are any external objects:
PL/SQL procedure successfully completed.
SQL>
SQL> set serveroutput on
SQL> declare
2 external boolean;
3 begin
4 external := dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR, SYS.DATA_FILE_DIR,
SYS.LOG_FILE_DIR, SYS.MEDIA_DIR, SYS.SS_OE_XMLDIR, SYS.SUBDIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA
PL/SQL procedure successfully completed.
6. Create a directory(make sure have enough space) and save the pfile there
Mkdir C:\to_linux
SQL> create pfile='c:\to_linux\initLINXORCL.ora' from spfile;
7. Use rman convert database command:
rman target /
RMAN> CONVERT DATABASE NEW DATABASE 'LINXORCL'
2> transport script 'C:\to_linux\script.sql'
3> to platform 'Linux x86 64-bit'
4> db_file_name_convert 'C:\APP\YCHEN\ORADATA\WINORCL\' 'C:\to_linux\';
File created
8. Transfer C:\to_linux to your target system and create directory for target database
9. Modify the pfile(initLINXORCL and script.sql to reflect the correct structure in target system.
10. Don't run script.sql directly manaully execute following commands from script.sql and upgrade to 11.2.0.3
STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/LINXORCL/initLINXORCL.ora'
CREATE spfile from pfile='/u01/app/oracle/admin/LINXORCL/initLINXORCL.ora';
CREATE CONTROLFILE REUSE SET DATABASE "LINXORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/admin/LINXORCL/REDO/REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/admin/LINXORCL/REDO/REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/admin/LINXORCL/REDO/REDO03.LOG' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/admin/LINXORCL/DATA/SYSTEM01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/SYSAUX01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/UNDOTBS01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/USERS01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS UPGRADE; (make sure to open database in upgrade mode)
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/admin/LINXORCL/DATA/TEMP01.DBF'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@catupgrd.sql
SQL> STARTUP
SQL> @utlu112s.sql
SQL> @catuppst.sql
SQL> @utlrp.sql
Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thursday, June 13, 2013
Why Execution Plan changes
Execution plans can differ due to the following: 1. Schema change(like new index... etc) 2. Data volume and statistic change. 3. Bind variable types and values change. 4. Initialization parameters change, following are examples: shared_pool_size —The memory region allocated for the library cache and internal control structures db_block_size —The size of each data block db_cache_size —The number of data buffers to allocate for the instance sort_area_size —The amount of RAM reserved for each user to perform sorting operations optimizer_mode —The default mode in which all SQL statements will be optimized for an execution plan(all_row, first_rows, first_rows_n) db_file_multiblock_read_count —The parameter that controls the number of blocks that are read asynchronously in full-table scan operations
Tuesday, June 11, 2013
RAC One Node database online relocation
1. Create RAC One Node database using DBCA 2. Verify the configuration and status of RAC One Node database: $ srvctl status database -d MYRONE Instance MYRONE_2 is running on node oratest2-lnx Online relocation: INACTIVE $ srvctl config database -d MYRONE Database unique name: MYRONE Database name: MYRONE Oracle home: /u01/app/oracle/product/11.2.0.3/db_1 Oracle user: oracle Spfile: +DATA/MYRONE/spfileMYRONE.ora Domain: intdata.com Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: MYRONE Database instances: Disk Groups: DATA,FLASH Mount point paths: Services: MYRONE_S Type: RACOneNode Online relocation timeout: 30 Instance name prefix: MYRONE Candidate servers: oratest1-lnx,oratest2-lnx Database is administrator managed 3. Online database relocation: $ srvctl relocate database -d MYRONE -n oratest1-lnx 4. Verify database stauts: $ srvctl status database -d MYRONE Instance MYRONE_2 is running on node oratest2-lnx Online relocation: ACTIVE Source instance: MYRONE_2 on oratest2-lnx Destination instance: MYRONE_1 on oratest1-lnx $ srvctl status database -d MYRONE Instance MYRONE_1 is running on node oratest1-lnx Online relocation: INACTIVE
Monday, June 10, 2013
Add another ASM diskgroup for OCR and voting disks.
During the installation of 11gr2 Grid Infrastructure. Oracle will ask for a ASM diskgroup to store OCR and voting disk
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2760
Available space (kbytes) : 259360
ID : 2003410762
Device/File Name : +OCR_VOTING1
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 8a1dd0786a194fefbf40b91e857093d4 (ORCL:OCRVOTED01) [OCR_VOTING1]
2. ONLINE 1f00a1ae71424fb7bfa53b99c07c4e17 (ORCL:OCRVOTED02) [OCR_VOTING1]
3. ONLINE 94336ab660584f87bf66e4abbca6f537 (ORCL:OCRVOTED03) [OCR_VOTING1]
It is recommended by Oracle to add another loation for OCR and voting disks
1, Create a new asm diskgroup named as OCR_VOTING2
2, Add an OCR location to the new asm diskgroup
/u01/app/grid/11.2.0.3/bin/ocrconfig -add +OCR_VOTING2
3, Verify
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2776
Available space (kbytes) : 259344
ID : 2003410762
Device/File Name : +OCR_VOTING1
Device/File integrity check succeeded
Device/File Name : +OCR_VOTING2
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
Saturday, June 8, 2013
How to located and dump a data block in oracle
SQL> create table mytest(cl1 number, cl2 varchar2(32));
Table created.
SQL> insert into mytest values (1, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
1 row created.
SQL> insert into mytest values (2, 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
1 row created.
SQL> commit;
Commit complete.
SQL> select ora_rowscn scn, dbms_rowid.rowid_block_number(rowid) block from mytest;
SCN BLOCK
---------- ----------
325082188 175177
325082188 175177
SQL> select header_file, header_block, blocks from dba_segments where segment_name = 'MYTEST';
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
1 175176 8
SQL> alter session set tracefile_identifier=mytest;
Session altered.
SQL> alter system dump datafile 1 block 175177;
System altered.
Below are the dump from cache, you should be see scn(last change SCN), csc(last clean SCN), itl... etc information in block header
==================================================================
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4369481
BH (0x7ebc98c0) file#: 1 rdba: 0x0042ac49 (1/175177) class: 1 ba: 0x7ea1a000
set: 11 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 139983 objn: 139983 tsn: 0 afn: 1
hash: [0x9de2e2d0,0x9de2e2d0] lru: [0x7dbc7428,0x823cdd38]
obj-flags: object_ckpt_list
ckptq: [0x9e78f9a8,0x7dbc73d8] fileq: [0x9e78f9c8,0x9e78f9c8] objq: [0x9a7d9080,0x9a7d9080]
st: XCURRENT md: NULL tch: 3 le: 0x777e7d58
flags: buffer_dirty redo_since_read gotten_in_current_mode
LRBA: [0x49a.237.0] LSCN: [0x0.13605c21] HSCN: [0x0.13605c4c] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 0 rdba: 0x0042ac49 (1/175177)
scn: 0x0000.13605c4c seq: 0x01 flg: 0x02 tail: 0x5c4c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007EA1A000 to 0x000000007EA1C000
07EA1A000 0000A206 0042AC49 13605C4C 02010000 [....I.B.L\`.....]
07EA1A010 00000000 00000001 000222CF 13605C21 [........."..!\`.]
07EA1A020 00000000 00030002 00000000 0008000A [................]
07EA1A030 00029F08 00C006F0 00128556 00002002 [........V.... ..]
07EA1A040 13605C4C 00000000 00000000 00000000 [L\`.............]
07EA1A050 00000000 00000000 00000000 00020100 [................]
07EA1A060 0016FFFF 1F3C1F52 00001F3C 1F790002 [....R.<.<.....y.]
07EA1A070 00001F52 00000000 00000000 00000000 [R...............]
07EA1A080 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
07EA1BFA0 00000000 00000000 00000000 012C0000 [..............,.]
07EA1BFB0 03C10202 42424220 42424242 42424242 [.... BBBBBBBBBBB]
07EA1BFC0 42424242 42424242 42424242 42424242 [BBBBBBBBBBBBBBBB]
07EA1BFD0 42424242 02012C42 2002C102 41414141 [BBBBB,..... AAAA]
07EA1BFE0 41414141 41414141 41414141 41414141 [AAAAAAAAAAAAAAAA]
07EA1BFF0 41414141 41414141 41414141 5C4C0601 [AAAAAAAAAAAA..L\]
Block header dump: 0x0042ac49
Object id on Block? Y
seg/obj: 0x222cf csc: 0x00.13605c21 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Trace file /u02/oracle/diag/DBAPRD/diag/rdbms/dbaprd/DBAPRD1/trace/DBAPRD1_ora_8207_MYTEST.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name: Linux
Node name: racprod1-lnx
Release: 2.6.18-53.el5
Version: #1 SMP Wed Oct 10 16:34:19 EDT 2007
Machine: x86_64
Instance name: DBAPRD1
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 8207, image: oracle@racprod1-lnx (TNS V1-V3)
*** 2013-06-08 22:19:47.221
*** SESSION ID:(214.14265) 2013-06-08 22:19:47.221
*** CLIENT ID:() 2013-06-08 22:19:47.221
*** SERVICE NAME:(SYS$USERS) 2013-06-08 22:19:47.221
*** MODULE NAME:(sqlplus@racprod1-lnx (TNS V1-V3)) 2013-06-08 22:19:47.221
*** ACTION NAME:() 2013-06-08 22:19:47.221
Start dump data blocks tsn: 0 file#:1 minblk 175177 maxblk 175177
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4369481
BH (0x7ebc98c0) file#: 1 rdba: 0x0042ac49 (1/175177) class: 1 ba: 0x7ea1a000
set: 11 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 139983 objn: 139983 tsn: 0 afn: 1
hash: [0x9de2e2d0,0x9de2e2d0] lru: [0x7dbc7428,0x823cdd38]
obj-flags: object_ckpt_list
ckptq: [0x9e78f9a8,0x7dbc73d8] fileq: [0x9e78f9c8,0x9e78f9c8] objq: [0x9a7d9080,0x9a7d9080]
st: XCURRENT md: NULL tch: 3 le: 0x777e7d58
flags: buffer_dirty redo_since_read gotten_in_current_mode
LRBA: [0x49a.237.0] LSCN: [0x0.13605c21] HSCN: [0x0.13605c4c] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 0 rdba: 0x0042ac49 (1/175177)
scn: 0x0000.13605c4c seq: 0x01 flg: 0x02 tail: 0x5c4c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007EA1A000 to 0x000000007EA1C000
07EA1A000 0000A206 0042AC49 13605C4C 02010000 [....I.B.L\`.....]
07EA1A010 00000000 00000001 000222CF 13605C21 [........."..!\`.]
07EA1A020 00000000 00030002 00000000 0008000A [................]
07EA1A030 00029F08 00C006F0 00128556 00002002 [........V.... ..]
07EA1A040 13605C4C 00000000 00000000 00000000 [L\`.............]
07EA1A050 00000000 00000000 00000000 00020100 [................]
07EA1A060 0016FFFF 1F3C1F52 00001F3C 1F790002 [....R.<.<.....y.]
07EA1A070 00001F52 00000000 00000000 00000000 [R...............]
07EA1A080 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
07EA1BFA0 00000000 00000000 00000000 012C0000 [..............,.]
07EA1BFB0 03C10202 42424220 42424242 42424242 [.... BBBBBBBBBBB]
07EA1BFC0 42424242 42424242 42424242 42424242 [BBBBBBBBBBBBBBBB]
07EA1BFD0 42424242 02012C42 2002C102 41414141 [BBBBB,..... AAAA]
07EA1BFE0 41414141 41414141 41414141 41414141 [AAAAAAAAAAAAAAAA]
07EA1BFF0 41414141 41414141 41414141 5C4C0601 [AAAAAAAAAAAA..L\]
Block header dump: 0x0042ac49
Object id on Block? Y
seg/obj: 0x222cf csc: 0x00.13605c21 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.008.00029f08 0x00c006f0.8556.12 --U- 2 fsc 0x0000.13605c4c
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0042ac49
data_block_dump,data header at 0x7ea1a05c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x7ea1a05c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f52
avsp=0x1f3c
tosp=0x1f3c
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f79
0x14:pri[1] offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f79
tl: 39 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [32]
41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
41 41 41 41 41 41 41
tab 0, row 1, @0x1f52
tl: 39 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [32]
42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42
42 42 42 42 42 42 42
end_of_block_dump
switchover failed with ORA-16775: target standby database in broker operation has potential data loss
Problem Description
I switchover from primary to standby successfully but when I swithback to primary I got following error:
DGMGRL> show configuration
Configuration
Name: DBAPRD.intdata.com
Enabled: YES
Protection Mode: MaxPerformance
Databases:
DBALC - Primary database
DBAPRD - Physical standby database
DBADR - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DBAPRD.intdata.com":
SUCCESS
DGMGRL> switchover to 'DBAPRD'
DGMGRL> Performing switchover NOW, please wait...
Error: ORA-16775: target standby database in broker operation has potential data loss
Failed.
Unable to switchover, primary database is still "DBALC"
DGMGRL> show configuration
Configuration
Name: DBAPRD.intdata.com
Enabled: YES
Protection Mode: MaxPerformance
Databases:
DBALC - Primary database
DBAPRD - Physical standby database
DBADBDR - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DBAPRD.intdata.com":
Warning: ORA-16607: one or more databases have failed
Following error was found in alertlog of primary database:
Errors in file /u02/oracle/diag/dbaPRD/diag/rdbms/dbalc/dbaLC/trace/dbaLC_lns1_21171.trc:
ORA-03135: connection lost contact
LGWR: Error 3135 closing archivelog file '(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=racprod1-v)(PORT=1540)))(CONNECT_DATA=(SERVICE_NAME=dbaPRD.intdata.com)(SERVER=DEDICATED)))'
Sat Jun 08 19:20:51 2013
Deleted Oracle managed file +dba_FLASH/dbalc/archivelog/2013_04_14/thread_1_seq_130382.8527.812711313
Waiting for all non-current ORLs to be archived...
Waiting for the ORL for thread 1 sequence 136062 to be archived...
Sat Jun 08 19:20:59 2013
ARC3: Archiving not possible: failed standby destinations
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance dbaLC - Archival Error
ORA-16014: log 2 sequence# 136062 not archived, no available destinations
ORA-00312: online log 2 thread 1: '+dba_DATA/dbalc/onlinelog/group_2.311.794395241'
ORA-00312: online log 2 thread 1: '+dba_FLASH/dbalc/onlinelog/group_2.303.794395255'
Errors in file /u02/oracle/diag/dbaPRD/diag/rdbms/dbalc/dbaLC/trace/dbaLC_arc3_14518.trc:
ORA-16014: log 2 sequence# 136062 not archived, no available destinations
ORA-00312: online log 2 thread 1: '+dba_DATA/dbalc/onlinelog/group_2.311.794395241'
ORA-00312: online log 2 thread 1: '+dba_FLASH/dbalc/onlinelog/group_2.303.794395255'
......
ORA-16416 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN...
Cause:
The target standby database in the broker operation did not have all the redo logs from the primary database.
Solution:
I tried "alter system swith logfile" but it didnt' work.
Restart primary database solve the problem.
Conclusion
Before swithover , run a few log switchover to make sure log can ship to standby.
Understand SCN movement during online user managed backup;
Before online backup, check current scn, system and datafile scn in controlfile and scn in datafile header. As expected checkpoint scn are same in controlfile and datafile headers, and it is behind current scn. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011036 325009912 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325009912 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325009912 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325009912 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325009912 +DBA_DATA/dbaprd/datafile/users.263.675355189 325009912 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325009912 SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325009912 Start online tablespace backup Oracle did a checkpoint on USERS tablespace and datafile only, and freeze the checkpoint scn on datafile header. SQL> alter tablespace users begin backup; Tablespace altered. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011196 325009912 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325009912 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325009912 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325009912 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325009912 +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325009912 SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 during the online backup checkpoint scn is freeze on datafile belongs to USERS tablespace SQL> alter system checkpoint; System altered. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011272 325011243 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325011243 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325011243 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325011243 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325011243 +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325011243 SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 END online tablespace backup; Oracle advanced checkpoint scn on USERS tablespace and datafile only to be same as system checkpoint scn SQL> alter tablespace users end backup; Tablespace altered. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011488 325011243 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325011243 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325011243 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325011243 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325011243 +DBA_DATA/dbaprd/datafile/users.263.675355189 325011243 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325011243 9 rows selected. SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325011243
Friday, June 7, 2013
Use rman increamental backup to re-sync a physical standby database
Problem description: Got following error in standby alert log: Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Media Recovery Log +DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977 Errors with log +DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977 MRP0: Background Media Recovery terminated with error 328 Errors in file /u02/oracle/diag/DBAPRD/diag/rdbms/dbalc/DBALC/trace/DBALC_mrp0_2283.trc: ORA-00328: archived log ends at change 324705843, need later change 324707097 ORA-00334: archived log: '+DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977' Managed Standby Recovery not using Real Time Apply Shutting down recovery slaves due to error 328 Recovery interrupted! Errors in file /u02/oracle/diag/DBAPRD/diag/rdbms/dbalc/DBALC/trace/DBALC_mrp0_2283.trc: ORA-00328: archived log ends at change 324705843, need later change 324707097 ORA-00334: archived log: '+DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977' MRP0: Background Media Recovery process shutdown (DBALC) Cause: The issue can be due to many reasons related to archived logs and SCN having wrong meta data. Standby Oracle control file is looking for a specific Archivelog specific SCN which it thinks is required for Recovery is not available in the archived log. Action Plan: Using RMAN Incremental Backups to Roll Forward a Physical Standby Database http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#CIHIAADC 1 Stop Redo apply and get the SCN on standby database SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 324714843 2 Backup incremental from SCN on primary database RMAN> BACKUP INCREMENTAL FROM SCN 324714843 DATABASE FORMAT '/datapump/ForStandby_%U' tag 'FORSTANDBY'; 3 Copy backup from primary to standby 4 Catalog the backup on standby database RMAN> CATALOG START WITH '/datapump/ForStandby'; 5 Recover the standby database with the cataloged incremental backup RMAN> RECOVER DATABASE NOREDO; you may need to restore standby control first if above command failed. RMAN> STARTUP FORCE NOMOUNT; RMAN> RESTORE STANDBY CONTROLFILE FROM TAG 'FORSTANDBY'; RMAN> ALTER DATABASE MOUNT; RMAN> RECOVER DATABASE NOREDO; 6 Restart Standby database and start redo apply.
Thursday, June 6, 2013
Re-create corrupted ASM disks
Problem description: Following 3 disks can see via OS but not ASM: OCRVOTED01 OCRVOTED02 OCRVOTED03 # multipath -l|grep OCR shared_asm_OCRVOTED01 (1HITACHI_770140123364) dm-24 HITACHI,DF600F shared_asm_OCRVOTED02 (1HITACHI_770140123365) dm-25 HITACHI,DF600F shared_asm_OCRVOTED03 (1HITACHI_770140123366) dm-26 HITACHI,DF600F shared_asm_OCRVOTED04 (1HITACHI_770140123367) dm-27 HITACHI,DF600F shared_asm_OCRVOTED05 (1HITACHI_770140123368) dm-28 HITACHI,DF600F # /etc/init.d/oracleasm listdisks |grep OCR OCRVOTED04 OCRVOTED05 Cause: Disks are corrupted Action Plan: 1. Try to recreate asm disk /etc/init.d/oracleasm createdisk OCRVOTED01 /dev/mapper/shared_asm_OCRVOTED01 Marking disk "OCRVOTED01" as an ASM disk: [FAILED] found following message from /var/log/oracleasm Device "/dev/mapper/shared_asm_OCRVOTED01" is already labeled for ASM disk "" 2. Overwrite the ASM header information on the disk. This can be achieved with the UNIX command dd. The following command will write 100x1024b blocks to the specified raw device: dd if=/dev/zero of=/dev/mapper/shared_asm_OCRVOTED01 bs=1024 count=100 dd if=/dev/zero of=/dev/mapper/shared_asm_OCRVOTED02 bs=1024 count=100 dd if=/dev/zero of=/dev/mapper/shared_asm_OCRVOTED03 bs=1024 count=100 3. Recreate asm disks /etc/init.d/oracleasm createdisk OCRVOTED01 /dev/mapper/shared_asm_OCRVOTED01 /etc/init.d/oracleasm createdisk OCRVOTED02 /dev/mapper/shared_asm_OCRVOTED02 /etc/init.d/oracleasm createdisk OCRVOTED03 /dev/mapper/shared_asm_OCRVOTED03 4. Run oracleasm scandisks on both nodes /etc/init.d/oracleasm scandisks 5. Verify # /etc/init.d/oracleasm listdisks |grep OCR OCRVOTED01 OCRVOTED02 OCRVOTED03 OCRVOTED04 OCRVOTED05
Find mapping of ASM disks to Physical Devices
Query asmdisk to get the major - minor numbers $ /etc/init.d/oracleasm querydisk -d OCRVOTED04 Disk "OCRVOTED04" is a valid ASM disk on device [253, 27] Match the number to physical device $ ls -l /dev|grep 253|grep 27 brw-rw---- 1 root root 253, 27 Jun 2 00:50 dm-27
Tuesday, June 4, 2013
Prerequest check before switchover to physical standby
Verify standby has received all redo from primary
If you are running in Maximum Protection or Maximum Availability mode, running following query on primary database to check if the target of you switchover is synchronized.
SQL> select db_unique_name, protection_mode, synchronization_status, synchronized from v$archive_dest_status;
If Synchronized does not say YES or you are running Maximum Performance mode, running following query on target standby database to get current log sequence that the primary is sending.
SQL> select client_process, process, thread#, sequence#, status from v$managed_standby where client_process = 'LGWR';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 718 IDLE
LGWR RFS 2 11 IDLE
Running following query on primary database to get the current redo sequence#
SQL> select thread#, sequence#, status from v$log where status = 'CURRENT';
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 718 CURRENT
2 11 CURRENT
You can not switchover if standby is not receiving the current redo.
Check the apply is caught up.
Running following query on target standby database, make sure status fo MRP0 is "APPLYING_LOG"
SQL> select inst_id, client_process, process, thread#, sequence#, status from gv$managed_standby where process like 'MRP%';
INST_ID CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
---------- -------- --------- ---------- ---------- ------------
1 N/A MRP0 1 718 APPLYING_LOG
Stop Rman backup and cancel jobs
Subscribe to:
Posts (Atom)