Askdbaonline

• Added datafile in file system instead of ASM. Missed ‘+’ while adding datafile
• How to migrate individual tablespaces to ASM, when the database is online.
• Create Additional controlfiles in the ASM diskgroup
• ASM

This following procedure can be used to create additional controlfiles in the ASM diskgroup.

1. Create a new directory in your disk group using commands similar to the following ones (this is optional).

alter diskgroup <DG_name> add directory ‘+<DG_name>/<DB_name>’;
alter diskgroup <DG_name> add directory’+<DG_name>/<DB_name>/controlfile’;

2. Then, edit the control file entry in your SPFILE/init.ora to point to the new
controlfile. Where the new controlfile is +DATA2/ora10g/controlfile/control03.ctl’

*.control_files=’+DATA/0ra10g/controlfile/current.260.3,
                                  ’+FLASH/Ora10G/controlfile/o1_mf_0fh3t83f_.ctl,
                                  ’+DATA2/ora10g/controlfile/control03.ctl’
    
3. Shutdown cleanly your database (all instances if using RAC)
shutdown immediate;

4. Startup the database in nomount mode
startup nomount;

5. Using RMAN, restore the controlfile from one of the existing locations to the new one.
restore controlfile to ‘DATA2/Ora10g/controlfile/control03.ctl’ from
‘+FLASH/Ora10g/controlfile/current.260.3′;

6. Mount the database and recover the database if necessary.
SQL> alter database mount;

7. Open the database.
SQL> alter database open;

This illustration assumes that there is a tablespace named AKDB located on a mounted filesystem.
Additionally, this same procedure can also be used to move a datafile from one diskgroup to another
diskgroup.

1. Connect to RMAN
RMAN> connect target

2. Make the target tablespace offline or read-only
RMAN> sql “alter tablespace AKDB offline”;

3. Copy the AKDB tablespace to the DATA diskgroup
RMAN> backup as copy tablespace AKDB format ‘+DATA’;

4. On successful copy of the tablespace, switch all the datafiles in the AKDB tablespace to ASM.
Determine the file number of the files to be switched (use v$datafile).

RMAN> switch datafile 6 to copy;

When the following message is received, it is deemed that the tablespace is
migrated successfully.

datafile 6 switched to datafile copy “+DATA/orcl/datafile/AKDB.314.1″

Note, the original filesystem copy still exists, and can be deleted.

Added datafile in file system instead of ASM. Missed ‘+’ while adding datafile

SQL> alter tablespace test01 add datafile ‘DATA1′ size 8G; 

Tablespace altered. 

SQL> select file_id,file_name from dba_data_files where tablespace_name=’test01′; 

FILE# NAME 
———- ————————————————– 
11 +DATA1/askdbaonline/datafile/test01.276.662164269 
17 +DATA1/askdbaonline/datafile/test01.283.660433979 
82 /opt/oracle/product/10.2.0/db/dbs/DG1 

2) We took following step to make it correct. 

SQL> alter database datafile 82 offline; 

rman target /

RMAN> copy datafile 82 to ‘+DATA1/’; 

RMAN> switch datafile 82 to copy;

SQL> alter database datafile 82 online; 

alter database datafile 82 online 
* 
ERROR at line 1: 
ORA-01113: file 82 needs media recovery 
ORA-01110: data file 82: ‘+DATA1/askdbaonline/datafile/test01.372.779132951′ 

SQL> recover datafile 82; 

Media recovery complete. 

SQL> alter database datafile 82 online; 

Database altered. 

SQL> select * from v$recover_file; 

no rows selected 

SQL> select file_id,file_name from dba_data_files where tablespace_name=’test01′; 

FILE# NAME 
———- ————————————————– 
11 +DATA1/askdbaonline/datafile/test01.276.662164269 
17 +DATA1/askdbaonline/datafile/test01.283.660433979 
82 +DATA1/askdbaonline/datafile/test01.372.779132951