----------------------------------------------------------------------------
Migration Non ASM to ASM
---------------------------------------------------------------------------
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 15:41:34 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 130024176 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control03.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
SQL> select group#,members from v$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
SQL> show parameter db_reco;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_create_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL>
SQL>
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL>
SQL> alter system set control_files='+DG1' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DG1';
System altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 30 15:52:25 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1355236758)
RMAN> backup current controlfile format '/u01/control.ctl';
Starting backup at 30-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 30-SEP-13
channel ORA_DISK_1: finished piece 1 at 30-SEP-13
piece handle=/u01/control.ctl tag=TAG20130930T155312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 30-SEP-13
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 134218480 bytes
Database Buffers 297795584 bytes
Redo Buffers 2973696 bytes
RMAN>
RMAN> restore controlfile from'/u01/control.ctl';
Starting restore at 30-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DG1/orcl/controlfile/current.256.827510381
Finished restore at 30-SEP-13
RMAN>
RMAN> backup as copy database format='+DG1';
Starting backup at 30-SEP-13
Starting implicit crosscheck backup at 30-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 30-SEP-13
Starting implicit crosscheck copy at 30-SEP-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-SEP-13
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output filename=+DG1/orcl/datafile/system.257.827510493 tag=TAG20130930T160133 recid=2 stamp=827510569
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output filename=+DG1/orcl/datafile/sysaux.258.827510569 tag=TAG20130930T160133 recid=3 stamp=827510593
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=+DG1/orcl/datafile/example.259.827510595 tag=TAG20130930T160133 recid=4 stamp=827510600
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output filename=+DG1/orcl/datafile/undotbs1.260.827510603 tag=TAG20130930T160133 recid=5 stamp=827510603
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output filename=+DG1/orcl/datafile/users.261.827510605 tag=TAG20130930T160133 recid=6 stamp=827510605
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-SEP-13
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
RMAN>
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf
2 30 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 230 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/orcl/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/orcl/datafile/system.257.827510493"
datafile 2 switched to datafile copy "+DG1/orcl/datafile/undotbs1.260.827510603"
datafile 3 switched to datafile copy "+DG1/orcl/datafile/sysaux.258.827510569"
datafile 4 switched to datafile copy "+DG1/orcl/datafile/users.261.827510605"
datafile 5 switched to datafile copy "+DG1/orcl/datafile/example.259.827510595"
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** +DG1/orcl/datafile/system.257.827510493
2 30 UNDOTBS1 *** +DG1/orcl/datafile/undotbs1.260.827510603
3 230 SYSAUX *** +DG1/orcl/datafile/sysaux.258.827510569
4 5 USERS *** +DG1/orcl/datafile/users.261.827510605
5 100 EXAMPLE *** +DG1/orcl/datafile/example.259.827510595
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN>
RMAN> recover database;
Starting recover at 30-SEP-13
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/orcl/redo02.log
archive log filename=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-SEP-13
RMAN> alter database open resetlogs;
database opened
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 16:07:00 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/system.257.827510493
+DG1/orcl/datafile/undotbs1.260.827510603
+DG1/orcl/datafile/sysaux.258.827510569
+DG1/orcl/datafile/users.261.827510605
+DG1/orcl/datafile/example.259.827510595
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' drop including datafiles;
Database altered.
SQL> alter tablespace temp add tempfile '+DG1' size 200m;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/tempfile/temp.262.827511053
SQL>
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/orcl/redo03.log
2 /u01/app/oracle/oradata/orcl/redo02.log
1 /u01/app/oracle/oradata/orcl/redo01.log
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 '+DG1' size 50m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DG1/orcl/onlinelog/group_3.264.827511547
2 /u01/app/oracle/oradata/orcl/redo02.log
1 +DG1/orcl/onlinelog/group_1.263.827511397
SQL> alter database drop logfile group 2;
Database altered.
SQL>
SQL>
SQL> alter database add logfile group 2 '+DG1' size 50m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DG1/orcl/onlinelog/group_3.264.827511547
2 +DG1/orcl/onlinelog/group_2.266.827512393
1 +DG1/orcl/onlinelog/group_1.263.827511397
4 +DG1/orcl/onlinelog/group_4.265.827511799
4 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog
/o1_mf_4_94lp13qf_.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 17:07:28 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora' ;
ORACLE instance started.
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 138412784 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile='+DG1/orcl/spfileorcl.ora' from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
-------------------------------------------------------------
drop old spfileorcl.ora
and open initorcl.ora and delete all content and type this
spfile='+DG1/orcl/spfileorcl.ora'
------------------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 138412784 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ----------------
spfile string +DG1/orcl/spfileorcl.ora
SQL>
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL>
SQL>
SQL> alter system set db_recovery_file_dest='+RECO';
System altered.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 2G
SQL> SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/controlfile/current.256.827510381
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DG1/orcl/onlinelog/group_3.264.827511547
+DG1/orcl/onlinelog/group_2.266.827512393
+DG1/orcl/onlinelog/group_1.263.827511397
+DG1/orcl/onlinelog/group_4.265.827511799
/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_94lp13qf_.log
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/system.257.827510493
+DG1/orcl/datafile/undotbs1.260.827510603
+DG1/orcl/datafile/sysaux.258.827510569
+DG1/orcl/datafile/users.261.827510605
+DG1/orcl/datafile/example.259.827510595
SQL> show parameter db_reco;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_create_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DG1
SQL>
Migration Non ASM to ASM
---------------------------------------------------------------------------
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 15:41:34 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 130024176 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control03.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
SQL> select group#,members from v$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
SQL> show parameter db_reco;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_create_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL>
SQL>
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL>
SQL> alter system set control_files='+DG1' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DG1';
System altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 30 15:52:25 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1355236758)
RMAN> backup current controlfile format '/u01/control.ctl';
Starting backup at 30-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 30-SEP-13
channel ORA_DISK_1: finished piece 1 at 30-SEP-13
piece handle=/u01/control.ctl tag=TAG20130930T155312 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 30-SEP-13
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 134218480 bytes
Database Buffers 297795584 bytes
Redo Buffers 2973696 bytes
RMAN>
RMAN> restore controlfile from'/u01/control.ctl';
Starting restore at 30-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DG1/orcl/controlfile/current.256.827510381
Finished restore at 30-SEP-13
RMAN>
RMAN> backup as copy database format='+DG1';
Starting backup at 30-SEP-13
Starting implicit crosscheck backup at 30-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 30-SEP-13
Starting implicit crosscheck copy at 30-SEP-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-SEP-13
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output filename=+DG1/orcl/datafile/system.257.827510493 tag=TAG20130930T160133 recid=2 stamp=827510569
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output filename=+DG1/orcl/datafile/sysaux.258.827510569 tag=TAG20130930T160133 recid=3 stamp=827510593
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=+DG1/orcl/datafile/example.259.827510595 tag=TAG20130930T160133 recid=4 stamp=827510600
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output filename=+DG1/orcl/datafile/undotbs1.260.827510603 tag=TAG20130930T160133 recid=5 stamp=827510603
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output filename=+DG1/orcl/datafile/users.261.827510605 tag=TAG20130930T160133 recid=6 stamp=827510605
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-SEP-13
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
RMAN>
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf
2 30 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 230 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/orcl/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/orcl/datafile/system.257.827510493"
datafile 2 switched to datafile copy "+DG1/orcl/datafile/undotbs1.260.827510603"
datafile 3 switched to datafile copy "+DG1/orcl/datafile/sysaux.258.827510569"
datafile 4 switched to datafile copy "+DG1/orcl/datafile/users.261.827510605"
datafile 5 switched to datafile copy "+DG1/orcl/datafile/example.259.827510595"
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** +DG1/orcl/datafile/system.257.827510493
2 30 UNDOTBS1 *** +DG1/orcl/datafile/undotbs1.260.827510603
3 230 SYSAUX *** +DG1/orcl/datafile/sysaux.258.827510569
4 5 USERS *** +DG1/orcl/datafile/users.261.827510605
5 100 EXAMPLE *** +DG1/orcl/datafile/example.259.827510595
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN>
RMAN> recover database;
Starting recover at 30-SEP-13
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/orcl/redo02.log
archive log filename=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-SEP-13
RMAN> alter database open resetlogs;
database opened
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 16:07:00 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/system.257.827510493
+DG1/orcl/datafile/undotbs1.260.827510603
+DG1/orcl/datafile/sysaux.258.827510569
+DG1/orcl/datafile/users.261.827510605
+DG1/orcl/datafile/example.259.827510595
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' drop including datafiles;
Database altered.
SQL> alter tablespace temp add tempfile '+DG1' size 200m;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/tempfile/temp.262.827511053
SQL>
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/orcl/redo03.log
2 /u01/app/oracle/oradata/orcl/redo02.log
1 /u01/app/oracle/oradata/orcl/redo01.log
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 '+DG1' size 50m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DG1/orcl/onlinelog/group_3.264.827511547
2 /u01/app/oracle/oradata/orcl/redo02.log
1 +DG1/orcl/onlinelog/group_1.263.827511397
SQL> alter database drop logfile group 2;
Database altered.
SQL>
SQL>
SQL> alter database add logfile group 2 '+DG1' size 50m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DG1/orcl/onlinelog/group_3.264.827511547
2 +DG1/orcl/onlinelog/group_2.266.827512393
1 +DG1/orcl/onlinelog/group_1.263.827511397
4 +DG1/orcl/onlinelog/group_4.265.827511799
4 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog
/o1_mf_4_94lp13qf_.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost ~]$ export ORACLE_SID=orcl
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 17:07:28 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora' ;
ORACLE instance started.
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 138412784 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile='+DG1/orcl/spfileorcl.ora' from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
-------------------------------------------------------------
drop old spfileorcl.ora
and open initorcl.ora and delete all content and type this
spfile='+DG1/orcl/spfileorcl.ora'
------------------------------------------------------------------
SQL> startup
ORACLE instance started.
Total System Global Area 436207616 bytes
Fixed Size 1219856 bytes
Variable Size 138412784 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ----------------
spfile string +DG1/orcl/spfileorcl.ora
SQL>
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL>
SQL>
SQL> alter system set db_recovery_file_dest='+RECO';
System altered.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 2G
SQL> SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/controlfile/current.256.827510381
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DG1/orcl/onlinelog/group_3.264.827511547
+DG1/orcl/onlinelog/group_2.266.827512393
+DG1/orcl/onlinelog/group_1.263.827511397
+DG1/orcl/onlinelog/group_4.265.827511799
/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_4_94lp13qf_.log
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/system.257.827510493
+DG1/orcl/datafile/undotbs1.260.827510603
+DG1/orcl/datafile/sysaux.258.827510569
+DG1/orcl/datafile/users.261.827510605
+DG1/orcl/datafile/example.259.827510595
SQL> show parameter db_reco;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 2G
SQL> show parameter db_create_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DG1
SQL>
No comments:
Post a Comment