Tag Archives: datafile

Database

I added a new datafile to the primary database, but not automatically added on standby

oracle

I added a new datafile to the primary database, but not automatically added on standby.

ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 129 is unknown – rename to correct file
ORA-01110: data file 129: ‘C:\ORACLE\ORA10G\DATABASE\UNNAMED00129’
ORA-01157: cannot identify/lock data file 129 – see DBWR trace file
ORA-01111: name for data file 129 is unknown – rename to correct file
ORA-01110: data file 129: ‘C:\ORACLE\ORA10G\DATABASE\UNNAMED00129’

Solution:

Note: It can run on umount state

1- Check the name of the datafile on standby database
SQL> select name from v$datafile; —- This command should show the datafile name as UNNAMEDxxxxx

2- On the standby database
SQL> alter system set standby_file_management=’manual’;

3- On the standby database rename the datafile
SQL> alter database create datafile ‘C:\ORACLE\ORA10G\DATABASE\UNNAMED00129’ as ‘C:\ORACLE\ORA10G\DATABASE\actual_dbfile_name.dbf’

4- On the standby database
SQL> alter system set standby_file_management=’auto’;

5- On the standby database
SQL> recover managed standby database disconnect;

Database

Size Oracle Database for Data File Type

Size Oracle Database for Data File Type

 

To select the size of Oracle database by type of data file, simply turn the SQL below:

on September serveroutput;
declare
dbf number;
tmpdbf number;
lgf number;
ctl number;
sum number;
begin
select trunc (sum (bytes / 1024/1024), 2) into DBF from v $ datafile;
select trunc (sum (bytes / 1024/1024), 2) into tmpdbf from v $ tempfile;
select trunc (sum (bytes / 1024/1024), 2) into lgf from v $ log l, v $ logfile lf where l.group # = # lf.group;
select trunc (sum (block_size * file_size_blks / 1024/1024), 2) into ctl from v $ controlfile;
select trunc ((DBF tmpdbf + + + lgf ctl) / 1024.2) into sum from dual;
DBMS_OUTPUT.PUT_LINE (chr (10));
DBMS_OUTPUT.PUT_LINE (‘Datafiles:’ dbf || || ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘tempfiles:’ || || tmpdbf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Logfiles:’ || || lgf ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Controlfiles:’ || || ctl ‘MB’);
DBMS_OUTPUT.PUT_LINE (chr (0));
DBMS_OUTPUT.PUT_LINE (‘Total Size’ || || sum ‘GB’);
end;

 

Back to previous menu