Move Datafile (.dbf)


bash-3.2$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Thu May 24 11:36:53 2012




Copyright (c) 1982, 2011, Oracle.  All rights reserved.




Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options




SQL> alter database backup controlfile to trace as '/data/ANARDB/uat.txt';




Database altered.




SQL> create pfile from spfile;




File created.




SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

bash-3.2$ cd /u01/app/oracle/

admin/              checkpoints/        fast_recovery_area/ initANARDEV.ora     initKBAZER.ora      product/           

cfgtoollogs/        diag/               init.ora            initANARTEST.ora      oradata/           

bash-3.2$ cd /u01/app/oracle/oradata/ANARDB/

bash-3.2$ ls

control01.ctl  redo01.log     redo02.log     redo03.log     sysaux01.dbf   system01.dbf   temp01.dbf     undotbs01.dbf  users01.dbf

bash-3.2$ mv * /data/ANARDB/data/

bash-3.2$ cd ..

bash-3.2$ ls

ANARDEV  ANARDB

bash-3.2$ cd ..

bash-3.2$ ls

admin               checkpoints         fast_recovery_area  initANARKDEV.ora          product

cfgtoollogs         diag                init.ora            oradata

bash-3.2$ cd fast_recovery_area/

bash-3.2$ ls

ANARDEV  ANARDB

bash-3.2$ cd ANARDB/

bash-3.2$ ls

archivelog     control02.ctl  onlinelog

bash-3.2$ mv control02.ctl /data/ANARDB/data/

bash-3.2$ cd $ORACLE_HOME

bash-3.2$ cd dbs/

bash-3.2$ vi initANARDB.ora

"initANARDB.ora" 27 lines, 1019 characters

ANARDB.__db_cache_size=10972299264

ANARDB.__java_pool_size=33554432

ANARDB.__large_pool_size=33554432

ANARDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ANARDB.__pga_aggregate_target=6878658560

ANARDB.__sga_target=12884901888

ANARDB.__shared_io_pool_size=0

ANARDB.__shared_pool_size=1744830464

ANARDB.__streams_pool_size=33554432

*.audit_file_dest='/u01/app/oracle/admin/ANARDB/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/data/ANARDB/data/control01.ctl','/data/ANARDB/data/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ANARDB'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ANARDBXDB)'

*.open_cursors=300

*.pga_aggregate_target=6871318528

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=12884901888

*.sga_target=12884901888

*.undo_tablespace='UNDOTBS1'

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

~

"initANARDB.ora" 27 lines, 982 characters

bash-3.2$ cd /data/ANARDB/

bash-3.2$ vi uat.txt

"uat.txt" 143 lines, 5834 characters

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="ANARDB"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=4

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE




--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

--     Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.




-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE




STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ANARDB" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/ANARDB/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/ANARDB/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/ANARDB/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/ANARDB/system01.dbf',

  '/u01/app/oracle/oradata/ANARDB/sysaux01.dbf',

  '/u01/app/oracle/oradata/ANARDB/undotbs01.dbf',

  '/u01/app/oracle/oradata/ANARDB/users01.dbf'

CHARACTER SET AL32UTF8

;




-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ANARDB/archivelog/2012_05_24/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ANARDB/archivelog/2012_05_24/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE




-- Database can now be opened normally.

:q

bash-3.2$ sqlplus '/as sysdba'




SQL*Plus: Release 11.2.0.3.0 Production on Thu May 24 11:42:27 2012




Copyright (c) 1982, 2011, Oracle.  All rights reserved.




Connected to an idle instance.




SQL> create spfile from pfile;




File created.




SQL> startup nomount

ORACLE instance started.




Total System Global Area 1.2831E+10 bytes

Fixed Size                  2171296 bytes

Variable Size            1845501536 bytes

Database Buffers         1.0972E+10 bytes

Redo Buffers               11231232 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ANARDB" RESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/data/ANARDB/data/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/data/ANARDB/data/redo02.log'  SIZE 50M BLOCKSIZE 512,

10    GROUP 3 '/data/ANARDB/data/redo03.log'  SIZE 50M BLOCKSIZE 512

11  -- STANDBY LOGFILE

12  DATAFILE

13    '/data/ANARDB/data/system01.dbf',

14    '/data/ANARDB/data/sysaux01.dbf',

15    '/data/ANARDB/data/undotbs01.dbf',

16    '/data/ANARDB/data/users01.dbf'

17  CHARACTER SET AL32UTF8

18  ;




Control file created.




SQL> alter database open resetlogs;




Database altered.




SQL>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: