异地clone RAC数据库 +ASM USE RMAN

 ###sample 

如何在本地生成数据库的备份，并复制到DG库新环境（高级）

1. 首先确定本地文件系统（存放备份集）足够大，可以使用如下语句查询当前数据库实际的使用总大小

 

Rman 备份进度：

select sum("已使用空间(M)") from

(

SELECT Upper(F.TABLESPACE_NAME)         "表空间名",

       D.TOT_GROOTTE_MB                 "表空间大小(M)",

       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')

       || '%'                           "使用比",

       F.TOTAL_BYTES                    "空闲空间(M)",

       F.MAX_BYTES                      "最大块(M)"

FROM   (SELECT TABLESPACE_NAME,

               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,

               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES

        FROM   SYS.DBA_FREE_SPACE

        GROUP  BY TABLESPACE_NAME) F,

       (SELECT DD.TABLESPACE_NAME,

               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB

        FROM   SYS.DBA_DATA_FILES DD

        GROUP  BY DD.TABLESPACE_NAME) D

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER  BY 1)

2． 开始在本地备份,备份目录需要自定义如/oracle/10g/backup

Rman target /

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

2.1 将备份存放为脚本 backup.sh

man trace reco1.log <<eof

connect target /

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

run{

allocate channel c1 type disk format '/oracle/10g/backup/%U';    

BACKUP INCREMENTAL LEVEL 0 DATABASE;

     BACKUP ARCHIVELOG ALL;

backup current controlfile for standby;

}

exit

eof

2.2 oracle用户后台执行

Nohup sh backup.sh &

2.3.检查备份的进度：

SELECT SID,
       SERIAL#,
       CONTEXT,
       SOFAR,
       TOTALWORK,
       ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
  FROM gV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND
       TOTALWORK != 0 AND SOFAR <> TOTALWORK;

3． 在本地创建pfile 文件(/tmp/dba/pfile.ora),使用生产的参数文件，检查以下参数是否要随着DG库实际环境调整

audit_file_dest, background_dump_dest, control_files, core_dump_dest, log_archive_dest_1, user_dump_dest, local_listener, log_archive_config, db_unique_name, control_files, fal_client etc

sqlplus / as sysdba

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

File created.

4．

4.1 移动备份片和参数文件到新的主机，最好能新主机(DG) 和旧主机使用相同的目录。

（还有一种简单的方法，使用存储映射的方法，在新主机重新挂在相同的NAS device）

scp pfile.ora  root@58.2.104.11:/tmp/dba

4.2   移动密码文件到新主机 DG

Cd $ORACLE_HOME/dbs

scp  orapwcore*  root@58.2.104.11:/tmp/dba

登陆新主机DG,修改文件权限为oracle用户，并且移动到对应目录

Cd /tmp/dba

chown opcore:oinstall *

su – opcore

cd /tmp/dba

cp orapwcore* $ORACLE_HOME/dbs

cp pfile.ora  $ORACLE_HOME/dbs/initcore1.ora

5．编辑新主机DG库的tnsnames.ora （在$ORACLE_HOME/network/admin 目录下）

 5.1 参考旧主机的tnsname.ora 加入网络信息，方便新主机 DG传日志

5.2 同时旧主机的tnsnames 也有加入新主机DG信息

6. 新主机上使用上面创建的 pfile 启动新主机(DG) 上的数据库

sqlplus "/ as sysdba"

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218292 bytes
Variable Size              62916876 bytes
Database Buffers           96468992 bytes
Redo Buffers                7168000 bytes

7. 恢复备库的控制文件

(RMAN>backup current controlfile for standby format '备份路径‘；) (if backup db is primary)

7.1

旧主机上备份( if backup db is standby)

RMAN>backup current controlfile format ‘/tmp/dba/standby.ctl’;

cd /tmp/dba

scp standby.ctl  root@58.2.104.11:/tmp/dba

7.2

新主机DG上恢复控制文件

备库重启到nomount状态，恢复控制文件，启动到mount

（for fix 10g with nas bug ）

SQL>alter system set events '10298 trace name context forever, level 32';

RMAN>restore standby controlfile from '/tmp/dba/standby.ctl'

SQL>alter database mount standby database;

 

 

8. 新主机检查可以用来恢复的归档日志文件的最大序列号

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

RMAN>list backup of archivelog time between "to_date('2019-03-01 00:00:00','yyyy-mm-dd,hh24:mi:ss')"  and "to_date('2019-03-01 06:00:00','yyyy-mm-dd hh24:mi:ss')";

检查所有重做线程的最后一个存档序列, 并选择其中有 "下一个 scn(Next SCN)" 的最小存档序列。如38833，

 

选择完最小的存档序列，使用38833+1

（Device Type DISK）

Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  1    38833   114944541420 2019-03-01 00:38:59 114944807665 2019-03-01 00:41:40

  2    38198   114944543989 2019-03-01 00:39:01 114945682491 2019-03-01 00:55:11

9．新主机准备restore.sh

###以下脚本是用来 将数据文件 指向新主机的新目录的拼凑

set linesize 999 linesize 999 head off feedback off

select 'set newname for datafile '||''||FILE#||''||' to '||chr(39)||replace(name,'+NEW_DATA/rcore/datafile','+NEW_DATA/rcore_new/datafile')||''||'.dbf'';' from v$datafile;

spool off

10．在新环境下运行resotore.sh

10.1 resotore.sh (ASM newname的路径都要与db_uniq_name 一样，如果不一样，创建目录将会失败)

rman target / log='/tmp/rcore_new.log'  <<EOF

run{

sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';

set until sequence 38834 thread 1;

set newname for datafile 1 to '+NEW_DATA/rcore_new/datafile/system.422.983499857.dbf;

set newname for datafile 2 to '+NEW_DATA/rcore_new/datafile/undotbs1.454.983492257.dbf;

……

restore database;

switch datafile all;

switch tempfile all;

recover database;

release channel ch00;

}

EOF

10,2 运行

Nohup sh resotore.sh &

10.3 观察恢复的进度

 SELECT sid, spid, client_info  
     FROM v$process p, v$session s  
     WHERE p.addr = s.paddr  
     AND client_info LIKE '%rman%';
   

SELECT SID,
       SERIAL#,
       CONTEXT,
       SOFAR,
       TOTALWORK,
       ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
  FROM gV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND
       TOTALWORK != 0 AND SOFAR <> TOTALWORK;

观察数据库alert.log

11．恢复完成之后，rename online_log

11.1 生成rename logfile 并clear 脚本

set linesize 999 linesize 999 head off feedback off

select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'+NEW_FRA/rcore/onlinelog','+NEW_FRA/rcore_new/onlinelog')||''';' from v$logfile;

spool off

spool clear_log.sql

set linesize 999 linesize 999 head off feedback off

select 'alter database clear logfile group '|| GROUP#||';' from v$logfile;

spool off

 

11.2 After renaming the redolog files, the database can be opened

SQL>alter system set STANDBY_FILE_MANAGEMENT =MANUAL;

SQL> select member from v$logfile;

SQL>@rename_log.sql

11.3

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-16004: backup database requires recovery

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

ORA-01110: data file 1: '+NEW_DATA/rcore_new/datafile/system.422.983499857.dbf'

12.旧主机追加归档日志，新主机应用归档日志  

12.1 旧主机追加归档日志

connect target /

run{

allocate channel c1 type disk format '/bakfs/rman/arhive_03_01%U';

allocate channel c2 type disk format '/bakfs/rman/arhive_03_01%U';

allocate channel c3 type disk format '/bakfs/rman/arhive_03_01%U';

allocate channel c4 type disk format '/bakfs/rman/arhive_03_01%U';

backup archivelog from time 'sysdate-2';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

12,2 将备份文件拷贝到新主机，新主机应用归档日志

è 确定当前的最大归档：

SQL> select max(SEQUENCE#)-1,THREAD# from v$log group by THREAD#;

MAX(SEQUENCE#)-1    THREAD#

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

           38933          1

           38293          2

è 开始恢复到当前最大的归档

rman target /

Rman> catalog start with '/bakfs/rman';

###恢复实例1的归档

Rman>

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

restore archivelog from logseq 38833 until logseq 38933 thread 1;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

###恢复实例2的归档

Rman>

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

restore archivelog from logseq 38198   until logseq 38293 thread 2;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

è 开启恢复

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

è 观察数据库日志alert*.log

è 停下恢复

alter database recover managed standby database cancel;

SQL〉@clear_log.sql

13. 调整参数 STANDBY_FILE_MANAGEMENT

SQL> alter database open

SQL〉alter system set STANDBY_FILE_MANAGEMENT =auto;

13.1 ．调整所有现有环境的数据库参数 log_archive_config

13.2. 检查ASM PFILE 和rdbms  spfile 设置  

-〉2个节点ASM 使用 pfile 配置

-〉RDBMS 使用spfile 配置 ，放在ASM盘上

Sqlplus / as sysdba

Create spfile='+NEW_DATA/rcore_new/spfilecore.ora' from pfile;

配置init 文件一行：

spfile='+NEW_DATA/rcore_new/spfilecore.ora'

13.3 调整主库的参数log_archive_dest_*

alter system set log_archive_dest_5='SERVICE=new lgwr async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=new' sid='*';

14.调整tempfile

spool /tmp/dba/re-tempfile.sql

set linesize 999 linesize 999 head off feedback off

--FOR BYTES IS NOT 0M

select 'ALTER TABLESPACE '||t.name||' add tempfile '||''''||replace(f.name,'+NEW_DATA/core','+NEW_DATA/rcore_new')||''''||' size '||f.bytes/1024/1024||'M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;' from v$tempfile f ,(select ts#,name from v$tablespace t where included_in_database_backup='NO') t

where f.ts#= t.ts#

--For Bytes is 0M

select 'ALTER TABLESPACE '||t.name||' add tempfile '||''''||replace(f.name,'+NEW_DATA/core','+NEW_DATA/rcore_new')||''''||' size '||'10'||'M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;' from v$tempfile f ,(select ts#,name from v$tablespace t where included_in_database_backup='NO') t

where f.ts#= t.ts#

/

select 'alter database tempfile '||''''||name||''' drop;' from v$tempfile;

spool off

@/tmp/dba/re-tempfile.sql

è 开启恢复

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

最后调整rman 策略 for standby

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

附录：手工挂载NAS device in AIX

umount  /bakfs

1.mount 格式 （for ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS） FOR AIX and  NAS device

mount -F nfs -o cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600 "nas.sw1.crb":/ifs/data/bak   /bakfs

2.数据库参数设置 (只是在当前实例级别生效，重启instance丢失)：（for ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS）FOR AIX

alter system set events '10298 trace name context forever, level 32';

3.change to asynch to directio,  for (RMAN-00600 [8083], LFI-00005 Free some memory failed in lfibrdt(), LFI-00004 Call to lfibgl() failed)  AIX and  NAS device

SQL> show parameter filesystemio_options

NAME

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

TYPE

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

VALUE

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

filesystemio_options

string

asynch

alter system set filesystemio_options=directio scope=spfile;

4． ORA-19870: error reading backup piece (for restore in ASM disk issue)

export ORACLE_SID=+ASM1

show parameter shared_pool_size

show parameter large_pool

show parameter db_cache_size

edit init+ASM1.ora

shared_pool_size = 5G

large_pool_size = 1G

db_cache_size = 1G

sga_max_size=8192M

processes=70

sessions=80

( Increase the PROCESSES parameter in the ASM parameter file

Processes = 25 + 15n, where n is the number of instances on the box using ASM for their storage.)

5．

ORA-19870: error reading backup piece /bakfs/rman/CORE_LVL0_20190228_pgtr15p1_s88880_p1

ORA-19504: failed to create file "+NEW_DATA/core/datafile/rb_data01.dbf.dbf"

ORA-17502: ksfdcre:4 Failed to create file +NEW_DATA/core/datafile/rb_data01.dbf.dbf

ORA-15173: entry 'core' does not exist in directory '/'

ASM 只能自动创建跟db_uniq_name相同的目录。

Change restore.sh 脚本

5.10g restore to ASM file 即使

即使使用了set newname ,但是重命名OMF 时候会出现如下报错,但是恢复仍然成功，因为OMF FILE 还是会自动创建

原因是10g BUG

Note that the datafile name is not an OMF name. If it were an OMF name a different error is raised:

        Errors in file /db/db01/home/oracle/admin/dm2gp/bdump/dm2gp1_dbw0_26350.trc:
        ORA-01157: cannot identify/lock data file 45 - see DBWR trace file
        ORA-01110: data file 45: '+DISKGRP1/dm2gp/datafile/undotbs02.281.562308511'
        ORA-17503: ksfdopn:2 Failed to open file +DISKGRP1/dm2gp/datafile/undotbs02.281.562308511
        ORA-15012: ASM file '+diskgrp1.281.562308511' does not exist

But the restore succeeds because the file is an OMF file.

6．问题：RMAN恢复restore database中断后 ，再启动RMAN，会产出什么样的后果？

--==========================================================

答案：

如果使用上次恢复使用的控制文件，应该会跳过已经恢复的数据文件。。。

但是第二次restore并不减少你所预期的时间~

比如

你全备份 1T 数据库 1000各数据文件。 单通道备份，1000个数据文件产生一个备份集~

你第一次restore ，restore到999个文件，就差一个文件，被你中断了。假如这次使用了999分钟那么你第二次restore的时候，会自动跳过前999个数据文件，直接restore最后一个数据文件，但是会需要很长时间，而不是你期望了1分钟就能结束，因为就算你只恢复一个数据文件，rman也许要读取整个备份集~

但是综合来看，效率仍然高于 将文件全部删除在重新恢复的时间。

7. renaming the redolog files 出现报错

ORA-17503: ksfdopn: ASM file '+NEW_FRA/rcore_new/onlinelog/group_6.329.983526345'

解决办法1：进入ASM,手工创建目录

Asmcmd>cd NEW_FRA

Asmcmd>mkdir rcore_new

Asmcmd>cd rcore_new

Asmcmd>mkdir onlinelog

解决办法2：如果办法1不行，可以尝试解决办法2

alter database clear unarchived logfile group 9;

alter database rename file '+NEW_FRA/rcore/onlinelog/redolog09_1.log' to '+NEW_FRA/rcore_new/onlinelog/redolog09_1.log'

alter database rename file '+NEW_FRA/rcore/onlinelog/redolog09_1.log' to '+NEW_FRA/rcore_new/onlinelog/redolog09_1.log'

alter database clear unarchived logfile group 14;

alter database rename file '+NEW_FRA/rcore/onlinelog/group_14.444.983454527' to '+NEW_FRA/rcore_new/onlinelog/group_14.444.983454527'

附录：手工挂载NAS device in AIX

umount  /bakfs

1.mount 格式 （for ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS） FOR AIX and  NAS device

mount -F nfs -o cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600 "nas.sw1.crb":/ifs/data/bak   /bakfs

2.数据库参数设置 (只是在当前实例级别生效，重启instance丢失)：（for ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS）FOR AIX

alter system set events '10298 trace name context forever, level 32';

3.change to asynch to directio,  for (RMAN-00600 [8083], LFI-00005 Free some memory failed in lfibrdt(), LFI-00004 Call to lfibgl() failed)  AIX and  NAS device

SQL> show parameter filesystemio_options

NAME

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

TYPE

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

VALUE

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

filesystemio_options

string

asynch

alter system set filesystemio_options=directio scope=spfile;

4． ORA-19870: error reading backup piece (for restore in ASM disk issue)

export ORACLE_SID=+ASM1

show parameter shared_pool_size

show parameter large_pool

show parameter db_cache_size

edit init+ASM1.ora

shared_pool_size = 5G

large_pool_size = 1G

db_cache_size = 1G

sga_max_size=8192M

processes=70

sessions=80

( Increase the PROCESSES parameter in the ASM parameter file

Processes = 25 + 15n, where n is the number of instances on the box using ASM for their storage.)

5．

ORA-19870: error reading backup piece /bakfs/rman/db_LVL0_20190228_pgtr15p1_s88880_p1

ORA-19504: failed to create file "+NEW_DATA/db/datafile/rb_data01.dbf.dbf"

ORA-17502: ksfdcre:4 Failed to create file +NEW_DATA/db/datafile/rb_data01.dbf.dbf

ORA-15173: entry 'db' does not exist in directory '/'

ASM 只能自动创建跟db_uniq_name相同的目录。

Change restore.sh 脚本

附录：

https://blog.csdn.net/hw_libo/article/details/6878885

如何kill掉RMAN备份进程

本文的目的就是在紧急状态下，需要立即终止正在进行的RMAN备份进程。

（1）查看RMAN分配的各个通道的进程号
SQL> SELECT sid, spid, client_info 
     FROM v$process p, v$session s 
     WHERE p.addr = s.paddr 
     AND client_info LIKE '%rman%';
  
       SID SPID       CLIENT_INFO
---------- ------------------------ -------------------------
       525 26244      rman channel=t1
      1023 26245      rman channel=t2
       699 26246      rman channel=t3

（2）根据第（1）中得到的进程号，终止RMAN备份
注：这里既要kill 掉RMAN备份脚本的PID，也要kill 掉RMAN中分配的各个通道的PID
subsdb1:~ # ps -ef | grep 26244
oracle   26244 26224  7 17:12 ?        00:01:49 oraclesubsdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      9877  9603  0 17:34 pts/11   00:00:00 grep 26244
subsdb1:~ # kill -9 26244
subsdb1:~ # ps -ef | grep 26245
oracle   26245 26224  5 17:12 ?        00:01:13 oraclesubsdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      9968  9603  0 17:35 pts/11   00:00:00 grep 26245
subsdb1:~ # kill -9 26245
subsdb1:~ # ps -ef | grep 26246
oracle   26246 26224  4 17:12 ?        00:01:03 oraclesubsdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root     10009  9603  0 17:35 pts/11   00:00:00 grep 26246
subsdb1:~ # kill -9 26246
subsdb1:~ # ps -ef | grep rman
oracle   26224 25962  0 17:11 pts/3    00:00:03 rman target / nocatalog
root     10061  9603  0 17:35 pts/11   00:00:00 grep rman
subsdb1:~ # kill -9 26224
subsdb1:~ # ps -ef | grep rman
root     10102  9603  0 17:36 pts/11   00:00:00 grep rman
subsdb1:~ # ps -ef | grep 26246
root     10213  9603  0 17:36 pts/11   00:00:00 grep 26246

此时RMAN备份操作已经被终止。查看（1）中的SQL语句时，结果为空。

说明：如果单单kill掉RMAN的进程号，那么RMAN备份并没有停止，而是要连channel进程也一起掉才可以！

 
---------------------
作者：bosco1986
来源：CSDN
原文：https://blog.csdn.net/hw_libo/article/details/6878885
版权声明：本文为博主原创文章，转载请附上博文链接！

2. 10g RAC有一个BUG  ASMCMD  lsdg 显示的free_mb 不准确，只要在dg 磁盘组触发了rm -rf操作的话

 10g规避办法：

Login to ASM instance, Run the below command

SQL> ALTER DISKGROUP <DG_NAME> CHECK ALL  REPAIR;

Similar issue occurs using ASMCMD as well. If any of the files / directories deleted using "rm -rf" command in a diskgroup at asm command prompt will lead to this issue having diskgroup compatibility pre 11.2.

 v$asm_disk Shows FREE_MB as 0 After Datafiles/Tablespaces Dropped

v$asm_disk shows FREE_MB as 0MB for all the disks which are part of the affected diskgroup. 

When executed check all norepair on the diskgroup. it shows Warning messages as Disk directory mismatch on the disks.

===

Thu May 29 19:04:57 2014
SQL> /* ASMCMD */ALTER DISKGROUP P0302_DG_FLASH CHECK  NOREPAIR 
NOTE: starting check of diskgroup P0302_DG_FLASH
Thu May 29 18:04:58 2014
GMON checking disk 1 for group 2 at 13 for pid 35, osid 14440
NOTE: disk P0302_ASMDISK009, used AU total mismatch: DD={4294966447, 0} AT={216, 0} >>> 
SUCCESS: check of diskgroup P0302_DG_FLASH found no errors
SUCCESS: /* ASMCMD */ALTER DISKGROUP P0302_DG_FLASH CHECK  NOREPAIR
Thu May 29 19:05:35 2014
SQL> /* ASMCMD */ALTER DISKGROUP P0302_DG_FLASH CHECK  NOREPAIR 
NOTE: starting check of diskgroup P0302_DG_FLASH
Thu May 29 18:05:35 2014
GMON checking disk 1 for group 2 at 14 for pid 35, osid 14440
NOTE: disk P0302_ASMDISK009, used AU total mismatch: DD={4294966447, 0} AT={216, 0}
SUCCESS: check of diskgroup P0302_DG_FLASH found no errors
SUCCESS: /* ASMCMD */ALTER DISKGROUP P0302_DG_FLASH CHECK  NOREPAIR

CAUSE

 There is a mismatch between used space in the disk directory and the actual used space. This is a known issue in pre 11.2 asm-compatible diskgroups (where Used Space Directory does not exist). This is possible because we batch the update to used space in disk directory. So, if we perform allocations/de-allocations during rebalance, we will run into this. We created USD to fix this. Unfortunately, it cannot be backported.

The fix for that created USD. It cannot be backported. Users will have to advance the compatibility of the diskgroup to 11.2 to ensure that this will not happen again. 

Bug 8451024   - V$ASM_DISK REPORTS ZERO FOR FREE SPACE : SPACE MISMATCH FOR ALL DISKS 
Base Bug 5077325   - FREESPACE LIST TO TRACK THE FREE SPACE IN AN ASM DISK

NOTE: Similar issue occurs using ASMCMD as well. If any of the files / directories deleted using "rm -rf" command in a diskgroup at asm command prompt will lead to this issue having diskgroup compatibility pre 11.2.

SOLUTION

 Initially run the check all repair to fix the issue. 

Login to ASM instance, Run the below command

SQL> ALTER DISKGROUP <DG_NAME> CHECK ALL  REPAIR;

v$asm_disk Shows FREE_MB as 0 After Datafiles/Tablespaces Dropped

###########asm filesystem file 文件实际使用情况

check asm file usage:

ort ORACLE_SID=+ASM1
ARCHIVE_LOG/
------------------------- 
set linesize 240

col g_n format 999 
col f_n format 9999 
col bytes format 999,999,999,999 
col space format 999,999,999,999 
col a_i format 999 
col blocks format 999,999 
col block_size format 999,999 
col ftype format a16 
col dir format a3 
col s_created format a10 
col full_alias_path format a60 
col striped format a6 
col rdun format a6 
col au format 99999999 
col calculated format 999,999,999,999

select x.gnum g_n, 
x.filnum f_n, 
substr(x.full_alias_path,1, 60) full_alias_path, 
f.striped, 
f.redundancy rdun, 
f.bytes, 
f.space, 
case when calculated / x.au > 60 then calculated + 3 * x.au -- Over 60 direct extents consideration 
else calculated 
end calculated 
from ( SELECT gnum,filnum,au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path 
from ( SELECT g.name gname, 
g.allocation_unit_size au, 
a.parent_index pindex, 
a.name aname, 
a.reference_index rindex, 
a.group_number gnum, 
a.file_number filnum 
FROM v$asm_alias a, v$asm_diskgroup g 
WHERE a.group_number = g.group_number) 
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x, 
(select f.group_number gnum, 
f.file_number filnum, 
f.type ftype , 
f.bytes, 
f.space, 
f.blocks, 
f.block_size, 
f.striped, 
f.redundancy, 
case f.striped when 'FINE' 
then ceil(((f.blocks * f.block_size + 1) / g.allocation_unit_size) / 8) 
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * 8 * g.allocation_unit_size 
else ceil((f.blocks * f.block_size + 1) / g.allocation_unit_size) 
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * g.allocation_unit_size 
end calculated 
from v$asm_file f , v$asm_diskgroup g 
where f.group_number = g.group_number
and g.name='ARCHIVE_LOG' 
order by f.group_number,file_number) f 
where x.filnum != 4294967295 
and x.gnum=f.gnum and x.filnum=f.filnum 
order by full_alias_path 

3.RMAN backup AND RESOTER I/O 检查 和进度检查脚本

alter session set nls_date_format='dd-mon-rr hh24:mi:ss';
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10

prompt  "disk i/o checking"

prompt   '02-28-19 14:00:00(sample)‘’

select s.inst_id, a.sid, CLIENT_INFO Ch, a.STATUS,
open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2)
TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a, gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid and open_time > to_date('&1', 'dd-mm-rr hh24:mi:ss') order by 2,7;

prompt  "rman progress checking"

SELECT SID,
       SERIAL#,
       CONTEXT,
       SOFAR,
       TOTALWORK,
       ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
  FROM gV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND
       TOTALWORK != 0 AND SOFAR <> TOTALWORK;

4.

问题：RMAN恢复restore database中断后 ，再启动RMAN，会产出什么样的后果？

--==========================================================
答案：

如果使用上次恢复使用的控制文件，应该会跳过已经恢复的数据文件。。。
但是第二次restore并不减少你所预期的时间~

比如
你全备份 1T 数据库 1000各数据文件。 单通道备份，1000个数据文件产生一个备份集~

你第一次restore ，restore到999个文件，就差一个文件，被你中断了。假如这次使用了999分钟
那么你第二次restore的时候，会自动跳过前999个数据文件，直接restore最后一个数据文件，但是会需要很长时间，而不是你期望了1分钟就能结束，因为就算你只恢复一个数据文件，rman也许要读取整个备份集~

datafile 8 is already restored to file +NEW_DATA/rdb_new/datafile/fm_data.272.983480425.dbf

处理方法：重新恢复控制文件，删除干净文件，重新发起恢复
---------------------
作者：没大没小
来源：CSDN
原文：https://blog.csdn.net/jiangzeqiang/article/details/8724725
版权声明：本文为博主原创文章，转载请附上博文链接！

6.

磁盘组删磁盘时报 ORA-15032 ORA-15054

SQL> alter diskgroup backup drop disk '/dev/mapper/backup6';    --删磁盘
alter diskgroup backup drop disk '/dev/mapper/backup6'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15054: disk "/DEV/MAPPER/BACKUP6" does not exist in diskgroup "BACKUP"

SQL> select name,path,state from v$asm_disk;


NAME                           PATH                                     STATE
------------------------------ ---------------------------------------- --------
DATA_0005                      /dev/mapper/data6p1                      NORMAL
DATA_0000                      /dev/mapper/data1p1                      NORMAL
BACKUP_0000                    /dev/mapper/backupp1                     NORMAL
BACKUP_0002                    /dev/mapper/backup1                      NORMAL
DATA_0001                      /dev/mapper/data2p1                      NORMAL
BACKUP_0003                    /dev/mapper/backup2                      NORMAL
DATA_0006                      /dev/mapper/data5p1                      NORMAL
BACKUP_0006                    /dev/mapper/backup5                      NORMAL
DATA_0002                      /dev/mapper/data3p1                      NORMAL
BACKUP_0005                    /dev/mapper/backup4                      NORMAL
BACKUP_0007                    /dev/mapper/backup6                      NORMAL
CRS_0002                       /dev/mapper/crs4p1                       NORMAL
BACKUP_0004                    /dev/mapper/backup3                      NORMAL
CRS_0004                       /dev/mapper/crs2p1                       NORMAL
CRS_0000                       /dev/mapper/crs6p1                       NORMAL
CRS_0005                       /dev/mapper/crs1p1                       NORMAL
CRS_0003                       /dev/mapper/crs3p1                       NORMAL
CRS_0001                       /dev/mapper/crs5p1                       NORMAL

 

SQL> select group_number,name,state,total_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE         TOTAL_MB
------------ ------------------------------ ----------- ----------
           1 BACKUP                         MOUNTED        1433597
           2 CRS                            MOUNTED           6096
           3 DATA                           MOUNTED        1021961


SQL> alter diskgroup backup drop disk backup_0007;
Diskgroup altered.
SQL> alter diskgroup backup drop disk backup_0006;
Diskgroup altered.

总结： 删除磁盘时只能跟磁盘的名字，不能跟path。
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                           STATE         TOTAL_MB
------------ ------------------------------ ----------- ----------
           1 BACKUP                         MOUNTED        1023997
           2 CRS                            MOUNTED           6096
           3 DATA                           MOUNTED        1021961
删除两个磁盘后磁盘大小减少了400G。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22969361/viewspace-1084565/，如需转载，请注明出处，否则将追究法律责任。

 

 

###########asm filesystem file

check asm file usage:

ort ORACLE_SID=+ASM1
ARCHIVE_LOG/
------------------------- 
set linesize 240

col g_n format 999 
col f_n format 9999 
col bytes format 999,999,999,999 
col space format 999,999,999,999 
col a_i format 999 
col blocks format 999,999 
col block_size format 999,999 
col ftype format a16 
col dir format a3 
col s_created format a10 
col full_alias_path format a60 
col striped format a6 
col rdun format a6 
col au format 99999999 
col calculated format 999,999,999,999

select x.gnum g_n, 
x.filnum f_n, 
substr(x.full_alias_path,1, 60) full_alias_path, 
f.striped, 
f.redundancy rdun, 
f.bytes, 
f.space, 
case when calculated / x.au > 60 then calculated + 3 * x.au -- Over 60 direct extents consideration 
else calculated 
end calculated 
from ( SELECT gnum,filnum,au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path 
from ( SELECT g.name gname, 
g.allocation_unit_size au, 
a.parent_index pindex, 
a.name aname, 
a.reference_index rindex, 
a.group_number gnum, 
a.file_number filnum 
FROM v$asm_alias a, v$asm_diskgroup g 
WHERE a.group_number = g.group_number) 
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x, 
(select f.group_number gnum, 
f.file_number filnum, 
f.type ftype , 
f.bytes, 
f.space, 
f.blocks, 
f.block_size, 
f.striped, 
f.redundancy, 
case f.striped when 'FINE' 
then ceil(((f.blocks * f.block_size + 1) / g.allocation_unit_size) / 8) 
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * 8 * g.allocation_unit_size 
else ceil((f.blocks * f.block_size + 1) / g.allocation_unit_size) 
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * g.allocation_unit_size 
end calculated 
from v$asm_file f , v$asm_diskgroup g 
where f.group_number = g.group_number
and g.name='ARCHIVE_LOG' 
order by f.group_number,file_number) f 
where x.filnum != 4294967295 
and x.gnum=f.gnum and x.filnum=f.filnum 
order by full_alias_path 
/