---- MIGRATION FROM NON ASM TO ASM DATABASE (11g)----
sql> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
sql> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/kings/king/users01.dbf
/u01/kings/king/undotbs01.dbf
/u01/kings/king/sysaux01.dbf
/u01/kings/king/system01.dbf
sql> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/kings/king/temp01.dbf
sql> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/1120/d
b_home/dbs/spfileking.ora
sql> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/kings/king/control01.ctl,
/u01/app/oracle/flash_recover
y_area/king/control02.ctl
sql> alter system set control_files='+DATA' scope=spfile;
System altered.
sql> alter system set db_create_file_dest='+DATA' scope=spfile;
System altered.
sql> shutdown immediate;
sql> startup nomount;
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 23 19:14:34 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: KING (not mounted)
RMAN> restore controlfile from '/u01/kings/king/control01.ctl';
Starting restore at 23-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/king/controlfile/current.256.942088523
Finished restore at 23-APR-17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 23-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/kings/king/system01.dbf
output file name=+DATA/king/datafile/system.257.942088645 tag=TAG20170423T191723 RECID=1 STAMP=942088688
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/kings/king/sysaux01.dbf
output file name=+DATA/king/datafile/sysaux.258.942088689 tag=TAG20170423T191723 RECID=2 STAMP=942088720
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/kings/king/undotbs01.dbf
output file name=+DATA/king/datafile/undotbs1.259.942088725 tag=TAG20170423T191723 RECID=3 STAMP=942088727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/king/controlfile/backup.260.942088731 tag=TAG20170423T191723 RECID=4 STAMP=942088734
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/kings/king/users01.dbf
output file name=+DATA/king/datafile/users.261.942088735 tag=TAG20170423T191723 RECID=5 STAMP=942088735
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-APR-17
channel ORA_DISK_1: finished piece 1 at 23-APR-17
piece handle=+DATA/king/backupset/2017_04_23/nnsnf0_tag20170423t191723_0.262.942088735 tag=TAG20170423T191723 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-APR-17
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/king/datafile/system.257.942088645"
datafile 2 switched to datafile copy "+DATA/king/datafile/sysaux.258.942088689"
datafile 3 switched to datafile copy "+DATA/king/datafile/undotbs1.259.942088725"
datafile 4 switched to datafile copy "+DATA/king/datafile/users.261.942088735"
RMAN> ALTER DATABASE OPEN;
database opened
RMAN> EXIT
Recovery Manager complete.
$ sqlplus / as sysdba
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/king/datafile/users.261.942088735
+DATA/king/datafile/undotbs1.259.942088725
+DATA/king/datafile/sysaux.258.942088689
+DATA/king/datafile/system.257.942088645
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/kings/king/temp01.dbf
SQL> alter database tempfile '/u01/kings/king/temp01.dbf' drop including datafiles;
Database altered.
SQL> select file_name from dba_temp_files;
no rows selected
SQL> alter tablespace temp add tempfile size 300M;
Tablespace altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/king/tempfile/temp.263.942088985
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/kings/king/redo03.log
/u01/kings/king/redo02.log
/u01/kings/king/redo01.log
SQL> alter database add logfile group 4 '+DATA' size 10M;
alter database add logfile group 5 '+DATA' size 10M;
alter database add logfile group 6 '+DATA' size 10M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> SQL>
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/kings/king/redo03.log
/u01/kings/king/redo02.log
/u01/kings/king/redo01.log
+DATA/king/onlinelog/group_4.264.942089039
+DATA/king/onlinelog/group_5.265.942089041
+DATA/king/onlinelog/group_6.266.942089041
6 rows selected.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
5 UNUSED
6 UNUSED
6 rows selected.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED
SQL> alter database drop logfile group 1;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> alter database drop logfile group 2;
Database altered.
SQL>
sql> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
sql> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/kings/king/users01.dbf
/u01/kings/king/undotbs01.dbf
/u01/kings/king/sysaux01.dbf
/u01/kings/king/system01.dbf
sql> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/kings/king/temp01.dbf
sql> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/1120/d
b_home/dbs/spfileking.ora
sql> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/kings/king/control01.ctl,
/u01/app/oracle/flash_recover
y_area/king/control02.ctl
sql> alter system set control_files='+DATA' scope=spfile;
System altered.
sql> alter system set db_create_file_dest='+DATA' scope=spfile;
System altered.
sql> shutdown immediate;
sql> startup nomount;
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 23 19:14:34 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: KING (not mounted)
RMAN> restore controlfile from '/u01/kings/king/control01.ctl';
Starting restore at 23-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/king/controlfile/current.256.942088523
Finished restore at 23-APR-17
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 23-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/kings/king/system01.dbf
output file name=+DATA/king/datafile/system.257.942088645 tag=TAG20170423T191723 RECID=1 STAMP=942088688
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/kings/king/sysaux01.dbf
output file name=+DATA/king/datafile/sysaux.258.942088689 tag=TAG20170423T191723 RECID=2 STAMP=942088720
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/kings/king/undotbs01.dbf
output file name=+DATA/king/datafile/undotbs1.259.942088725 tag=TAG20170423T191723 RECID=3 STAMP=942088727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/king/controlfile/backup.260.942088731 tag=TAG20170423T191723 RECID=4 STAMP=942088734
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/kings/king/users01.dbf
output file name=+DATA/king/datafile/users.261.942088735 tag=TAG20170423T191723 RECID=5 STAMP=942088735
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-APR-17
channel ORA_DISK_1: finished piece 1 at 23-APR-17
piece handle=+DATA/king/backupset/2017_04_23/nnsnf0_tag20170423t191723_0.262.942088735 tag=TAG20170423T191723 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-APR-17
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/king/datafile/system.257.942088645"
datafile 2 switched to datafile copy "+DATA/king/datafile/sysaux.258.942088689"
datafile 3 switched to datafile copy "+DATA/king/datafile/undotbs1.259.942088725"
datafile 4 switched to datafile copy "+DATA/king/datafile/users.261.942088735"
RMAN> ALTER DATABASE OPEN;
database opened
RMAN> EXIT
Recovery Manager complete.
$ sqlplus / as sysdba
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/king/datafile/users.261.942088735
+DATA/king/datafile/undotbs1.259.942088725
+DATA/king/datafile/sysaux.258.942088689
+DATA/king/datafile/system.257.942088645
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/kings/king/temp01.dbf
SQL> alter database tempfile '/u01/kings/king/temp01.dbf' drop including datafiles;
Database altered.
SQL> select file_name from dba_temp_files;
no rows selected
SQL> alter tablespace temp add tempfile size 300M;
Tablespace altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/king/tempfile/temp.263.942088985
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/kings/king/redo03.log
/u01/kings/king/redo02.log
/u01/kings/king/redo01.log
SQL> alter database add logfile group 4 '+DATA' size 10M;
alter database add logfile group 5 '+DATA' size 10M;
alter database add logfile group 6 '+DATA' size 10M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> SQL>
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/kings/king/redo03.log
/u01/kings/king/redo02.log
/u01/kings/king/redo01.log
+DATA/king/onlinelog/group_4.264.942089039
+DATA/king/onlinelog/group_5.265.942089041
+DATA/king/onlinelog/group_6.266.942089041
6 rows selected.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
5 UNUSED
6 UNUSED
6 rows selected.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED
SQL> alter database drop logfile group 1;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
SQL> alter database drop logfile group 2;
Database altered.
SQL>
No comments:
Post a Comment