Oracle add datafile on oracle standby

Oracle add datafile
standby add datafile



col FILE_NAME format a55
select FILE_NAME,TABLESPACE_NAME from dba_data_files
where TABLESPACE_NAME='DM_xxxPROD1_DOCBASE';



FILE_NAME TABLESPACE_NAME
------------------------------------------------------- ------------------------------
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase01.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase02.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase03.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase04.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase05.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase06.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase07.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase08.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase09.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase10.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase11.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase12.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase13.dbf DM_xxxPROD1_DOCBASE
/opt/oracle/oradata/xxxdb/dm_xxxprod1_docbase14.dbf DM_xxxPROD1_DOCBASE



alter tablespace add datafile '/data/oracle/xxx/oradata/dev9r2/questaims2.dbf' size 100M maxsize 2000M autoextend on;

SQL> ALTER TABLESPACE DM_xxxPROD1_DOCBASE ADD DATAFILE '/opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf' size 2G ;

Tablespace altered.




SQL> !ls -ltr /opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf
-rw-r----- 1 oracle dba 2147491840 Aug 17 13:10 /opt/oracle/oradata1/xxxdb/dm_xxxprod1_docbase15.dbf



SQL> alter system switch logfile; ---- this statement will create datafile on standby

System altered.


binary format:
SQL> alter database backup controlfile to '/xxxxx/xxxxx/file_name';



then dump the controlfile on human readable format


SQL> alter database backup controlfile to trace as '/opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt' ;

Database altered.

SQL> !ls -ltr /opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt
-rw-r--r-- 1 oracle dba 9990 Aug 17 13:27 /opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt




Now time to check on standby .


ssh on that box


xxx-ora-01dr# ls -ltr /opt/oracle/oradata1/xxxdb/
total 8294416
-rw-r----- 1 oracle dba 2147491840 Aug 17 13:34 dm_xxxprod1_docbase15.dbf <<<<<<<<<<<<<<<<---
-rw-r----- 1 oracle dba 2097160192 Aug 17 13:34 dm_xxxprod1_ind13.dbf




then dump the controlfile on human readable format on standby also


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> alter database backup controlfile to trace as '/opt/oracle/product/10.2/dbs/controlfiledump-17082011.txt' ;

Database altered.