Oracle Db Rname datafile/redofile on ASM-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Oracle Db Rname datafile/redofile on ASM

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:37 PM

oracle

oracle db在omf自动管理文件下并在文件存放在ASM里,如果手工rename 一个文件OMF文件,oracle会自动删除原有文件,以下是测试 SQL SELECT file#,name FROM v$datafile; FILE# NAME---------- ----------------------------------------------------------------

oracle db在omf自动管理文件下并在文件存放在ASM里,如果手工rename 一个文件OMF文件,oracle会自动删除原有文件,以下是测试

SQL> SELECT file#,name FROM v$datafile;
 
     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +ASMDATA/racdb/datafile/system.256.814462679
         2 +ASMDATA/racdb/datafile/sysaux.257.814462681
         3 +ASMDATA/racdb/datafile/undotbs1.258.814462681
         4 +ASMDATA/racdb/datafile/users.259.814462681
         5 +ASMDATA/racdb/datafile/undotbs2.264.814462949
         6 +ASMDATA/racdb/datafile/test.268.815753637
         7 +ASMDATA/racdb/datafile/goldengate.341.820339679
         8 +ASMDATA/racdb/datafile/ccdata.375.844202017
         9 +ASMDATA/racdb/datafile/soe.460.844203543
        10 +ASMDATA/racdb/datafile/big_test.661.852999379
 
10 ROWS selected.

这里先通过rman backup as copy 备份一个文件

[oracle@rac1 ~]$ rman target /
 
Recovery Manager: Release 11.2.0.3.0 - Production ON Fri Jul 18 11:59:24 2014
 
Copyright (c) 1982, 2011, Oracle AND/OR its affiliates.  ALL rights reserved.
 
connected TO target DATABASE: RACDB (DBID=812079559)
 
RMAN> backup AS copy datafile 10 format '+ASMDATA/racdb/datafile/big_test01.dbf';
 
Starting backup at 18-JUL-14
USING target DATABASE control file instead OF recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 instance=racdb1 device TYPE=DISK
channel ORA_DISK_1: starting datafile copy
INPUT datafile file NUMBER=00010 name=+ASMDATA/racdb/datafile/big_test.661.852999379
output file name=+ASMDATA/racdb/datafile/big_test01.dbf tag=TAG20140718T115951 RECID=4 STAMP=853243196
channel ORA_DISK_1: datafile copy complete, elapsed TIME: 00:00:08
Finished backup at 18-JUL-14
 
RMAN>

[grid@rac1 ~]$ asmcmd
ls -lASMCMD>  
 
State    TYPE    Rebal  Name
MOUNTED  EXTERN  N      ASMDATA/
MOUNTED  EXTERN  N      DATA/
ASMCMD> ASMCMD> 
ASMCMD> ls -l +ASMDATA/racdb/datafile/big_test01.dbf
TYPE      Redund  Striped  TIME             Sys  Name
                                            N    big_test01.dbf => +ASMDATA/RACDB/DATAFILE/BIG_TEST.664.853243193
ASMCMD> ls -l +ASMDATA/racdb/datafile/big_test.661.852999379
TYPE      Redund  Striped  TIME             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 18 11:00:00  Y    big_test.661.852999379
ASMCMD> ls -l +ASMDATA/racdb/datafile/big_test*
TYPE      Redund  Striped  TIME             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 18 11:00:00  Y    BIG_TEST.661.852999379
DATAFILE  UNPROT  COARSE   JUL 18 11:00:00  Y    BIG_TEST.664.853243193
                                            N    big_test01.dbf => +ASMDATA/RACDB/DATAFILE/BIG_TEST.664.853243193

这里直切swtich datafile 看下

RMAN> switch  datafile 10 TO copy;
 
USING target DATABASE control file instead OF recovery catalog
datafile 10 switched TO datafile copy "+ASMDATA/racdb/datafile/big_test01.dbf"
RMAN> recover datafile 10;
 
Starting recover at 18-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 instance=racdb1 device TYPE=DISK
 
starting media recovery
media recovery complete, elapsed TIME: 00:00:02
 
Finished recover at 18-JUL-14
 
RMAN> SQL 'alter database datafile 10 online';
 
SQL statement: ALTER DATABASE datafile 10 online
 
ASMCMD> ls -l +ASMDATA/racdb/datafile/big_test*
TYPE      Redund  Striped  TIME             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 18 12:00:00  Y    BIG_TEST.661.852999379
DATAFILE  UNPROT  COARSE   JUL 18 12:00:00  Y    BIG_TEST.664.853243193
                                            N    big_test01.dbf => +ASMDATA/RACDB/DATAFILE/BIG_TEST.664.853243193

从这里在看两个文件都存在,所以说在文件OMF命名格式下，rman的switch datafile 不会删除文件

下面看下在sqlplus下直接rename

SQL> ALTER DATABASE datafile 10 offline;
 
DATABASE altered.
 
SQL> 
SQL> ALTER DATABASE RENAME file '+ASMDATA/racdb/datafile/big_test01.dbf' TO '+ASMDATA/racdb/datafile/big_test.661.852999379';
 
DATABASE altered.
 
SQL> recover datafile 10;
Media recovery complete.
SQL> ALTER DATABASE datafile 10 online;
 
DATABASE altered.
 
 
Completed: ALTER DATABASE RENAME file '+ASMDATA/racdb/datafile/big_test01.dbf' TO '+ASMDATA/racdb/datafile/big_test.661.852999379'
Fri Jul 18 12:08:21 2014
ALTER DATABASE RECOVER  datafile 10  
Media Recovery START
Serial Media Recovery started
WARNING! Recovering DATA file 10 FROM a fuzzy backup. It might be an online
backup taken WITHOUT entering the BEGIN backup command.
Recovery OF Online Redo Log: Thread 1 GROUP 2 Seq 337 Reading mem 0
  Mem# 0: +ASMDATA/racdb/onlinelog/group_2.262.814462797
Media Recovery Complete (racdb1)
Completed: ALTER DATABASE RECOVER  datafile 10  
ALTER DATABASE datafile 10 online
Completed: ALTER DATABASE datafile 10 onlin

ASMCMD> ls -l +ASMDATA/racdb/datafile/big_test*
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 18 12:00:00  Y    BIG_TEST.661.852999379
DATAFILE  UNPROT  COARSE   JUL 18 12:00:00  Y    BIG_TEST.664.853243193
                                            N    big_test01.dbf => +ASMDATA/RACDB/DATAFILE/BIG_TEST.664.853243193

这里看到从手工命令的文件切换到OMF命令的文件，原文件也没用被删除。

看下omf切换到手工命令文件

SQL> ALTER DATABASE datafile 10 offline;
 
DATABASE altered.
 
 
SQL> ALTER DATABASE RENAME file '+ASMDATA/racdb/datafile/big_test.661.852999379' TO '+ASMDATA/racdb/datafile/big_test01.dbf';
 
DATABASE altered.
 
SQL> recover datafile 10;
Media recovery complete.
SQL>  ALTER DATABASE datafile 10 online;
 
DATABASE altered.

alter日志文件如下

alter database rename file '+ASMDATA/racdb/datafile/big_test.661.852999379' to '+ASMDATA/racdb/datafile/big_test01.dbf'
Deleted Oracle managed file +ASMDATA/racdb/datafile/big_test.661.852999379
Completed: alter database rename file '+ASMDATA/racdb/datafile/big_test.661.852999379' to '+ASMDATA/racdb/datafile/big_test01.dbf'
Fri Jul 18 12:12:35 2014

明显看出文件被delete ,Deleted Oracle managed file

ASMCMD> ls -l +ASMDATA/racdb/datafile/big_test*
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUL 18 12:00:00  Y    BIG_TEST.664.853243193
                                            N    big_test01.dbf => +ASMDATA/RACDB/DATAFILE/BIG_TEST.664.853243193

从asmcmd看到只剩下一个文件

原文地址：Oracle Db Rname datafile/redofile on ASM, 感谢原作者分享。

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What Are the Limitations of Using Views in MySQL?May 14, 2025 am 12:10 AM

MySQLviewshavelimitations:1)Theydon'tsupportallSQLoperations,restrictingdatamanipulationthroughviewswithjoinsorsubqueries.2)Theycanimpactperformance,especiallywithcomplexqueriesorlargedatasets.3)Viewsdon'tstoredata,potentiallyleadingtooutdatedinforma

Securing Your MySQL Database: Adding Users and Granting PrivilegesMay 14, 2025 am 12:09 AM

ProperusermanagementinMySQLiscrucialforenhancingsecurityandensuringefficientdatabaseoperation.1)UseCREATEUSERtoaddusers,specifyingconnectionsourcewith@'localhost'or@'%'.2)GrantspecificprivilegeswithGRANT,usingleastprivilegeprincipletominimizerisks.3)

What Factors Influence the Number of Triggers I Can Use in MySQL?May 14, 2025 am 12:08 AM

MySQLdoesn'timposeahardlimitontriggers,butpracticalfactorsdeterminetheireffectiveuse:1)Serverconfigurationimpactstriggermanagement;2)Complextriggersincreasesystemload;3)Largertablesslowtriggerperformance;4)Highconcurrencycancausetriggercontention;5)M

MySQL: Is it safe to store BLOB?May 14, 2025 am 12:07 AM

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

MySQL: Adding a user through a PHP web interfaceMay 14, 2025 am 12:04 AM

Adding MySQL users through the PHP web interface can use MySQLi extensions. The steps are as follows: 1. Connect to the MySQL database and use the MySQLi extension. 2. Create a user, use the CREATEUSER statement, and use the PASSWORD() function to encrypt the password. 3. Prevent SQL injection and use the mysqli_real_escape_string() function to process user input. 4. Assign permissions to new users and use the GRANT statement.

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

See all articles