Oracle-重命名数据文件-2023年2月2日

Oracle-重命名数据文件-2023年2月2日

表空间信息查询

set linesize 400 pagesize 500
col name for a70
col tablespace for a20
select online_status "Status",
       tablespace_name "Tablespace",
       t1.file_id,
       file_name "Name",
       round(t1.bytes / 1024 / 1024, 2) "Size(MB)",
       round((t1.bytes - nvl(t2.bytes, 0)) / 1024 / 1024, 2) "Used(MB)",
       round((t1.bytes - nvl(t2.bytes, 0)) / t1.bytes * 100, 2) "Used%",
       autoextensible "Autoextensible",
       t1.increment_by "Inc (bs)",
       t1.online_status "Status"
  from dba_data_files t1
  left join (select file_id, sum(bytes) bytes
               from dba_free_space
              group by file_id) t2
    on t1.file_id = t2.file_id
 order by t1.tablespace_name, t1.file_id;

Status	Tablespace		FILE_ID Name									 Size(MB)   Used(MB)	  Used% Aut   Inc (bs) Status
------- -------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- ---------- --- ---------- -------
ONLINE	DDL_AUDIT		      5 /oradata/orcl/ddl_audit_01.dbf						      200	1.63	    .81 YES	     1 ONLINE
ONLINE	SYSAUX			      2 /oradata/orcl/sysaux01.dbf						      600     542.94	  90.49 YES	  1280 ONLINE
SYSTEM	SYSTEM			      1 /oradata/orcl/system01.dbf						      700     383.13	  54.73 YES	  1280 SYSTEM
ONLINE	UNDOTBS1		      3 /oradata/orcl/undotbs01.dbf						      415      16.25	   3.92 YES	   640 ONLINE
ONLINE	USERS			      4 /oradata/orcl/users01.dbf							5	1.06	  21.25 YES	   160 ONLINE

set linesize 400 pagesize 500
col TABLESPACE_NAME for a20
col CONTENTS for a10
select status,
       t1.tablespace_name,
       contents,
       extent_management "Extent Management",
       segment_space_management "Segment Space Management",
       allocation_type "Allocation Type",
       logging,
       force_logging,
       round(t1.maxsize / 1024 / 1024 / 1024, 2) "MaxSize(GB)",
       round(t1.bytes / 1024 / 1024 / 1024, 3) "File Size(GB)",
       round((t1.bytes - t2.bytes) / 1024 / 1024 / 1024, 3) "Used(GB)",
       round((t1.bytes - t2.bytes) / t1.bytes * 100, 2) "Used%",
       round((t1.maxsize - t1.bytes + t2.bytes) / 1024 / 1024 / 1024, 2) "AVL Size(GB)",
       round((t1.maxsize - t1.bytes + t2.bytes) / (t1.bytes - t2.bytes), 1) x,
       t2.fsfi,
       t2.frags
  from (select tablespace_name, sum(bytes) bytes, sum(maxsize) maxsize
          from (select tablespace_name, bytes, maxbytes maxsize
                  from dba_data_files
                 where autoextensible = 'YES'
                union all
                select tablespace_name, bytes, bytes maxsize
                  from dba_data_files
                 where autoextensible = 'NO')
         group by tablespace_name) t1,
       (select tablespace_name,
               sum(bytes) bytes,
               round(sqrt(max(blocks) / sum(blocks)) /
                     sqrt(sqrt(count(blocks))) * 100,
                     0) fsfi,
               count(blocks) frags
          from dba_free_space
         group by tablespace_name) t2,
       dba_tablespaces
 where dba_tablespaces.tablespace_name = t1.tablespace_name
   and dba_tablespaces.tablespace_name = t2.tablespace_name
 order by "Used%" desc;

STATUS	  TABLESPACE_NAME      CONTENTS   Extent Man Segmen Allocatio LOGGING	FOR MaxSize(GB) File Size(GB)	Used(GB)      Used% AVL Size(GB)	  X	  FSFI	    FRAGS
--------- -------------------- ---------- ---------- ------ --------- --------- --- ----------- ------------- ---------- ---------- ------------ ---------- ---------- ----------
ONLINE	  SYSAUX	       PERMANENT  LOCAL      AUTO   SYSTEM    LOGGING	NO	     32 	 .586	     .53      90.49	   31.47       59.4	    45	       15
ONLINE	  SYSTEM	       PERMANENT  LOCAL      MANUAL SYSTEM    LOGGING	NO	     32 	 .684	    .374      54.73	   31.63       84.5	    84		2
ONLINE	  USERS 	       PERMANENT  LOCAL      AUTO   SYSTEM    LOGGING	NO	     32 	 .005	    .001      21.25	      32    30839.5	   100		1
ONLINE	  UNDOTBS1	       UNDO	  LOCAL      MANUAL SYSTEM    LOGGING	NO	     32 	 .405	    .016       3.92	   31.98     2015.5	    59		8
ONLINE	  DDL_AUDIT	       PERMANENT  LOCAL      AUTO   SYSTEM    LOGGING	NO	     32 	 .195	    .002	.81	      32    20163.9	    71		4

附上批量修改数据文件名的语句

set pagesize 999
set linesize 999

--日志文件
select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$logfile ;

--临时文件
select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$tempfile ;

--数据文件
--第一种
select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files;

--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||name||''''||' to '||chr(39)||replace(name,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$datafile ;
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name='SYSTEM';
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name in ('SYSTEM');
--select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name not in  ('SYSAUX','SYSTEM','USERS','UNDOTBS1');

--第二种
select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files;

--select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/oradata/orcl/','/oradata2/orcl/')||''';' from v$datafile ;
--select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name='SYSTEM';
--select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name in ('SYSTEM');
--select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name not in  ('SYSAUX','SYSTEM','USERS','UNDOTBS1');

第一种

这种方式需要数据库处于open状态，表空间在offline的状态下才能更改。

--alter tablespace users rename datafile 'xxxx' to ‘xxxx';

alter tablespace users offline;

select 'cp -a  ' ||''''||file_name||''''||'  '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||'''' from dba_data_files where tablespace_name in ('TEST');

--cp /opt/ora10g/oradata/orcl/user01aa.dbf /opt/ora10g/oradata/orcl/user01bb.dbf

select 'alter tablespace ' ||tablespace_name|| ' rename datafile '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name in ('TEST');

alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user01aa.dbf' to '/opt/ora10g/oradata/orcl/user01bb.dbf' ;
alter tablespace users online;
alter system switch logfile;

select * from dba_data_files;

--备库操作
--alter tablespace test add datafile '/oradata3/orcl/test_03.dbf' size 200M autoextend on;

show parameter name

alter system set db_file_name_convert='/oradata/orcl/','/oradata/orcldg/','/oradata2/orcl/','/oradata/orcldg/' scope=spfile;

alter database recover managed standby database cancel;

shu immediate
startup

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY PARALLEL 2 USING CURRENT LOGFILE DISCONNECT FROM SESSION;

show parameter name

--alter system set standby_file_management=manual;
--alter database create datafile  '/u01/app/oracle/product/11G_R2/db/dbs/UNNAMED00008'  as  '/oradata/orcldg/test_03.dbf';
--alter system set standby_file_management=auto;

第二种

这种方式需要数据库处于mount状态

--alter database rename file 'xxxx' to ‘xxxx';

set line 400 pagesize 500

select 'cp -a  ' ||''''||file_name||''''||'  '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||'''' from dba_data_files where tablespace_name in ('TEST');

select 'alter database rename file '||''''||file_name||''''||' to '||chr(39)||replace(file_name,'/oradata/orcl/','/oradata2/orcl/')||''';' from dba_data_files where tablespace_name='SYSTEM';

lsnrctl stop
lsnrctl status

shu immediate
startup mount

--cp /opt/ora10g/oradata/orcl/user01bb.dbf /opt/ora10g/oradata/orcl/user01aa.dbf

alter database rename file '/opt/ora10g/oradata/orcl/user01bb.dbf' to '/opt/ora10g/oradata/orcl/user01aa.dbf';


alter database open;