Skip to main content

Move File System To ASM & ASM To File System



How to Move File System To ASM & ASM To File System
Here you go, guys!!!


if use 12c you can online move a datafile




SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      /oradata2
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_no_recovery_through_resetlogs       boolean     TRUE
db_recovery_file_dest                string      /oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 4560M
recovery_parallelism                 integer     0
SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME from dba_data_files;

   FILE_ID FILE_NAME                                                    TABLESPACE_NAME
---------- ------------------------------------------------------------ ------------------------------
         3 /oradata2/CLOUD13C/datafile/o1_mf_sysaux_g7dqsbbm_.dbf       SYSAUX
         7 /oradata2/CLOUD13C/datafile/o1_mf_mgmt_ad4_g7dr5z2z_.dbf     MGMT_AD4J_TS
         4 /oradata2/CLOUD13C/datafile/o1_mf_undotbs1_g7dr4jms_.dbf     UNDOTBS1
         1 /oradata2/CLOUD13C/datafile/o1_mf_system_g7dr13np_.dbf       SYSTEM
         2 /oradata2/CLOUD13C/datafile/o1_mf_mgmt_tab_g7drww9w_.dbf     MGMT_TABLESPACE
         5 /oradata2/CLOUD13C/datafile/o1_mf_mgmt_ecm_g7drljd0_.dbf     MGMT_ECM_DEPOT_TS
         6 /oradata2/CLOUD13C/datafile/o1_mf_users_g7dqq482_.dbf        USERS

7 rows selected.

SQL> alter system set db_create_file_dest='+DATA';

System altered.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CLOUD13C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1216     SYSTEM               YES     /oradata2/CLOUD13C/datafile/o1_mf_system_g7dr13np_.dbf
2    25668    MGMT_TABLESPACE      NO      /oradata2/CLOUD13C/datafile/o1_mf_mgmt_tab_g7drww9w_.dbf
3    3495     SYSAUX               NO      /oradata2/CLOUD13C/datafile/o1_mf_sysaux_g7dqsbbm_.dbf
4    420      UNDOTBS1             YES     /oradata2/CLOUD13C/datafile/o1_mf_undotbs1_g7dr4jms_.dbf
5    2580     MGMT_ECM_DEPOT_TS    NO      +DATA/CLOUD13C/DATAFILE/mgmt_ecm_depot_ts.258.1001414099
6    12       USERS                NO      +DATA/CLOUD13C/DATAFILE/users.257.1001413955
7    5005     MGMT_AD4J_TS         NO      /oradata2/CLOUD13C/datafile/o1_mf_mgmt_ad4_g7dr5z2z_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    400      TEMP2                32767       /oradata2/CLOUD13C/datafile/o1_mf_temp2_g7dvsvrm_.tmp



SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL> alter database move datafile 6 to '+DATA';

Database altered.

SQL> alter database move database 5 to '+DATA';
alter database move database 5 to '+DATA'
                    *
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter database move datafile 5 to '+DATA';

Database altered.

SQL> alter database move datafile 2 to '+DATA';

Database altered.

SQL> alter database move datafile 1 to '+DATA';

SQL> alter database move datafile 4 to '+DATA';

Database altered.

SQL> alter database move datafile 7 to '+DATA';

Database altered.

SQL> alter database move datafile 3 to '+DATA';

Database altered.

RMAN> report schema;

Report of database schema for database with db_unique_name CLOUD13C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1216     SYSTEM               YES     +DATA/CLOUD13C/DATAFILE/system.260.1001416205
2    25668    MGMT_TABLESPACE      NO      +DATA/CLOUD13C/DATAFILE/mgmt_tablespace.259.1001414365
3    3495     SYSAUX               NO      +DATA/CLOUD13C/DATAFILE/sysaux.263.1001416707
4    420      UNDOTBS1             YES     +DATA/CLOUD13C/DATAFILE/undotbs1.261.1001416317
5    2580     MGMT_ECM_DEPOT_TS    NO      +DATA/CLOUD13C/DATAFILE/mgmt_ecm_depot_ts.258.1001414099
6    12       USERS                NO      +DATA/CLOUD13C/DATAFILE/users.257.1001413955
7    5005     MGMT_AD4J_TS         NO      +DATA/CLOUD13C/DATAFILE/mgmt_ad4j_ts.262.1001416383

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    400      TEMP2                32767       /oradata2/CLOUD13C/datafile/o1_mf_temp2_g7dvsvrm_.tmp


SQL> create temporary tablespace TEMP;

Tablespace created.

RMAN>  report schema;

Report of database schema for database with db_unique_name CLOUD13C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1216     SYSTEM               YES     +DATA/CLOUD13C/DATAFILE/system.260.1001416205
2    25668    MGMT_TABLESPACE      NO      +DATA/CLOUD13C/DATAFILE/mgmt_tablespace.259.1001414365
3    3495     SYSAUX               NO      +DATA/CLOUD13C/DATAFILE/sysaux.263.1001416707
4    420      UNDOTBS1             YES     +DATA/CLOUD13C/DATAFILE/undotbs1.261.1001416317
5    2580     MGMT_ECM_DEPOT_TS    NO      +DATA/CLOUD13C/DATAFILE/mgmt_ecm_depot_ts.258.1001414099
6    12       USERS                NO      +DATA/CLOUD13C/DATAFILE/users.257.1001413955
7    5005     MGMT_AD4J_TS         NO      +DATA/CLOUD13C/DATAFILE/mgmt_ad4j_ts.262.1001416383

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 32767       +DATA/CLOUD13C/TEMPFILE/temp.264.1001417145
2    400      TEMP2                32767       /oradata2/CLOUD13C/datafile/o1_mf_temp2_g7dvsvrm_.tmp



SQL>  alter database default temporary tablespace TEMP;

Database altered.

SQL> drop tablespace TEMP2 including contents and datafiles;

Tablespace dropped.


MAN>  report schema;

Report of database schema for database with db_unique_name CLOUD13C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1216     SYSTEM               YES     +DATA/CLOUD13C/DATAFILE/system.260.1001416205
2    25668    MGMT_TABLESPACE      NO      +DATA/CLOUD13C/DATAFILE/mgmt_tablespace.259.1001414365
3    3495     SYSAUX               NO      +DATA/CLOUD13C/DATAFILE/sysaux.263.1001416707
4    420      UNDOTBS1             YES     +DATA/CLOUD13C/DATAFILE/undotbs1.261.1001416317
5    2580     MGMT_ECM_DEPOT_TS    NO      +DATA/CLOUD13C/DATAFILE/mgmt_ecm_depot_ts.258.1001414099
6    12       USERS                NO      +DATA/CLOUD13C/DATAFILE/users.257.1001413955
7    5005     MGMT_AD4J_TS         NO      +DATA/CLOUD13C/DATAFILE/mgmt_ad4j_ts.262.1001416383

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 32767       +DATA/CLOUD13C/TEMPFILE/temp.264.1001417145




SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/CLOUD13C/onlinelog/o1_mf_3_dpv66sy8_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_3_dpv66vs3_.log
/oradata/CLOUD13C/onlinelog/o1_mf_2_dpv66gk4_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_2_dpv66k36_.log
/oradata/CLOUD13C/onlinelog/o1_mf_1_dpv6623d_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_1_dpv663yk_.log

6 rows selected.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_no_recovery_through_resetlogs       boolean     TRUE
db_recovery_file_dest                string      /oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 4560M
recovery_parallelism                 integer     0
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4493
Current log sequence           4495
SQL> alter system set db_recovery_file_dest='+DATA';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_no_recovery_through_resetlogs       boolean     TRUE
db_recovery_file_dest                string      +DATA
db_recovery_file_dest_size           big integer 4560M
recovery_parallelism                 integer     0
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4493
Current log sequence           4495
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/CLOUD13C/onlinelog/o1_mf_3_dpv66sy8_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_3_dpv66vs3_.log
/oradata/CLOUD13C/onlinelog/o1_mf_2_dpv66gk4_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_2_dpv66k36_.log
/oradata/CLOUD13C/onlinelog/o1_mf_1_dpv6623d_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_1_dpv663yk_.log

6 rows selected.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL>
SQL> alter database drop logfile group 2;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         3 INACTIVE

SQL>  alter database add logfile group 2 '+DATA';

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 INACTIVE

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/CLOUD13C/onlinelog/o1_mf_3_dpv66sy8_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_3_dpv66vs3_.log
+DATA/CLOUD13C/ONLINELOG/group_2.265.1001420229
/oradata/CLOUD13C/onlinelog/o1_mf_1_dpv6623d_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_1_dpv663yk_.log

SQL> alter database drop logfile group 3;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/CLOUD13C/ONLINELOG/group_2.265.1001420229
/oradata/CLOUD13C/onlinelog/o1_mf_1_dpv6623d_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_1_dpv663yk_.log

SQL> alter database add logfile group 3 '+DATA,+DATA';
alter database add logfile group 3 '+DATA,+DATA'
*
ERROR at line 1:
ORA-15121: ASM file name '+DATA,+DATA' contains an invalid diskgroup name


SQL> alter database add logfile group 3 '+DATA';

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 UNUSED

SQL> alter system chickpoint ;
alter system chickpoint
             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> alter database checkpoint;
alter database checkpoint
                        *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> alter database checkpoint global;
alter database checkpoint global
                          *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> alter system  checkpoint global;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/CLOUD13C/ONLINELOG/group_3.266.1001420299
+DATA/CLOUD13C/ONLINELOG/group_2.265.1001420229
/oradata/CLOUD13C/onlinelog/o1_mf_1_dpv6623d_.log
/oradata/fast_recovery_area/CLOUD13C/onlinelog/o1_mf_1_dpv663yk_.log

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group  1 '+DATA';

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/CLOUD13C/ONLINELOG/group_3.266.1001420299
+DATA/CLOUD13C/ONLINELOG/group_1.267.1001420987
+DATA/CLOUD13C/ONLINELOG/group_2.265.1001420229

SQL> alter database add logfile group  4;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/CLOUD13C/ONLINELOG/group_3.266.1001420299
+DATA/CLOUD13C/ONLINELOG/group_1.267.1001420987
+DATA/CLOUD13C/ONLINELOG/group_2.265.1001420229
+DATA/CLOUD13C/ONLINELOG/group_4.268.1001421011
+DATA/CLOUD13C/ONLINELOG/group_4.269.1001421015


SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_cluster_by_rowid_control  integer     129
_optimizer_dsdir_usage_control       integer     126
_optimizer_extended_stats_usage_cont integer     192
rol
_optimizer_join_order_control        integer     3
control_file_record_keep_time        integer     7
control_files                        string      /oradata/CLOUD13C/controlfile/
                                                 o1_mf_dpv65znc_.ctl, /oradata/
                                                 fast_recovery_area/CLOUD13C/co
                                                 ntrolfile/o1_mf_dpv65zpl_.ctl


                                                                               
RMAN> startup nomount;
Total System Global Area 5368709120 bytes
Fixed Size                  6081440 bytes
Variable Size            1426064480 bytes
Database Buffers         3909091328 bytes
Redo Buffers               27471872 bytes
                                                                               
RMAN> restore controlfile to '+DATA' from '/oradata/CLOUD13C/controlfile/o1_mf_dpv65znc_.ctl';

Starting restore at 28-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=392 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 28-FEB-19
                                                                               


[root@cloudcontrol ~]# su - grid
Last login: Thu Feb 28 11:43:49 IRST 2019 on pts/2
[grid@cloudcontrol ~]$ asmcmd -p
ASMCMD [+] > find --type CONTROLFILE +DATA *
+DATA/CLOUD13C/CONTROLFILE/current.270.1001421287
ASMCMD [+] >

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oradata/CLOUD13C/controlfile/
                                                 o1_mf_dpv65znc_.ctl, /oradata/
                                                 fast_recovery_area/CLOUD13C/co
                                                 ntrolfile/o1_mf_dpv65zpl_.ctl
SQL> alter system set control_files='+DATA/CLOUD13C/CONTROLFILE/current.270.1001421287' scope=spfile;

System altered.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
                                                   


[oracle@cloudcontrol ~]$ srvctl start database -d  cloud13c -o mount;
                                                  
RMAN> run{
2> backup as backupset spfile;
3> restore spfile to '+DATA/CLOUD13C/spfilecloud13c.ora';
4> }

Starting backup at 28-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=587 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=665 device type=DISK
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 28-FEB-19
channel ORA_DISK_1: finished piece 1 at 28-FEB-19
piece handle=+DATA/CLOUD13C/BACKUPSET/2019_02_28/nnsnf0_tag20190228t124730_0.271.1001422051 tag=TAG20190228T124730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-FEB-19

Starting Control File and SPFILE Autobackup at 28-FEB-19
piece handle=+DATA/CLOUD13C/AUTOBACKUP/2019_02_28/s_1001421172.272.1001422053 comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-19

Starting restore at 28-FEB-19
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/CLOUD13C/spfilecloud13c.ora
channel ORA_DISK_1: reading from backup piece +DATA/CLOUD13C/AUTOBACKUP/2019_02_28/s_1001421172.272.1001422053
channel ORA_DISK_1: piece handle=+DATA/CLOUD13C/AUTOBACKUP/2019_02_28/s_1001421172.272.1001422053 tag=TAG20190228T124732
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-FEB-19
                                                                               
[oracle@cloudcontrol ~]$ srvctl modify database -d cloud13c -spfile '+DATA/CLOUD13C/spfilecloud13c.ora'
[oracle@cloudcontrol ~]$ srvctl config database -d cloud13c
Database unique name: cloud13c
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db
Oracle user: oracle
Spfile: +DATA/CLOUD13C/spfilecloud13c.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
OSDBA group:
OSOPER group:
Database instance: cloud13c
                                                                               
                                                                               
[oracle@cloudcontrol ~]$ srvctl stop database -d cloud13c
[oracle@cloudcontrol ~]$ ^stop^start
srvctl start database -d cloud13c
                                                                               
SQL> set lines 200
SQL> set pages 999
SQL> select name from v$datafile union all
select name from v$tempfile union all
select member from v$logfile union all
select name from v$controlfile;  2    3    4

NAME
------------------------------------------------------------------------------------------
+DATA/CLOUD13C/DATAFILE/system.260.1001416205
+DATA/CLOUD13C/DATAFILE/mgmt_tablespace.259.1001414365
+DATA/CLOUD13C/DATAFILE/sysaux.263.1001416707
+DATA/CLOUD13C/DATAFILE/undotbs1.261.1001416317
+DATA/CLOUD13C/DATAFILE/mgmt_ecm_depot_ts.258.1001414099
+DATA/CLOUD13C/DATAFILE/users.257.1001413955
+DATA/CLOUD13C/DATAFILE/mgmt_ad4j_ts.262.1001416383
+DATA/CLOUD13C/TEMPFILE/temp.264.1001417145
+DATA/CLOUD13C/ONLINELOG/group_3.266.1001420299
+DATA/CLOUD13C/ONLINELOG/group_1.267.1001420987
+DATA/CLOUD13C/ONLINELOG/group_2.265.1001420229
+DATA/CLOUD13C/ONLINELOG/group_4.268.1001421011
+DATA/CLOUD13C/ONLINELOG/group_4.269.1001421015
+DATA/CLOUD13C/CONTROLFILE/current.270.1001421287


**********************************************************************************************************************************
Errors in file /u01/app/oracle/diag/rdbms/cloud13c/cloud13c/trace/cloud13c_ora_28035.trc:
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA01"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: Read Failed. group:1 disk:0 AU:7097 offset:2834432 size:8192
path:Unknown disk
         incarnation:0x0 synchronous result:'I/O error'
         subsys:Unknown library krq:0x7fb257fa9ac8 bufp:0x142498000 osderr1:0x0 osderr2:0x0
         IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: failed to read mirror side 1 of virtual extent 1 logical extent 0 of file 260 in group [1.1745556349] from disk DATA_0000  allocation unit 7097 reason error; if possible, will try another mirror side
Thu Feb 28 11:55:00 2019
Errors in file /u01/app/oracle/diag/rdbms/cloud13c/cloud13c/trace/cloud13c_ora_28035.trc:
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA01"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/CLOUD13C/DATAFILE/system.260.1001416205'
ORA-15081: failed to submit an I/O operation to a disk
WARNING: Read Failed. group:1 disk:0 AU:7097 offset:2834432 size:8192
path:Unknown disk
         incarnation:0x0 synchronous result:'I/O error'
         subsys:Unknown library krq:0x7fb257fa9ac8 bufp:0x14253c000 osderr1:0x0 osderr2:0x0
         IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: failed to read mirror side 1 of virtual extent 1 logical extent 0 of file 260 in group [1.1745556349] from disk DATA_0000  allocation unit 7097 reason error; if possible, will try another mirror side
Thu Feb 28 11:55:01 2019
Errors in file /u01/app/oracle/diag/rdbms/cloud13c/cloud13c/trace/cloud13c_ora_28048.trc:
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA01"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: Read Failed. group:1 disk:0 AU:7097 offset:2834432 size:8192
path:Unknown disk
         incarnation:0x0 synchronous result:'I/O error'
         subsys:Unknown library krq:0x7f328f6f1ac8 bufp:0x13f700000 osderr1:0x0 osderr2:0x0
         IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: failed to read mirror side 1 of virtual extent 1 logical extent 0 of file 260 in group [1.1745556349] from disk DATA_0000  allocation unit 7097 reason error; if possible, will try another mirror side
Thu Feb 28 11:55:01 2019


--------------------------------------------------
[grid@cloudcontrol ~]$ cd $ORACLE_HOME/bin
[grid@cloudcontrol bin]$ ./setasmgidwrap o=/u01/app/oracle/product/12.1.0/db/bin/oracle
[grid@cloudcontrol bin]$ logout
[root@cloudcontrol source]# su - oracle
Last login: Thu Feb 28 11:41:40 IRST 2019 on pts/2
[oracle@cloudcontrol ~]$ ll /u01/app/oracle/product/12.1.0/db/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 323649880 Jul 11  2017 /u01/app/oracle/product/12.1.0/db/bin/oracle
[oracle@cloudcontrol ~]$ logout
[root@cloudcontrol source]# vi /etc/group
group   group-
[root@cloudcontrol source]# vi /etc/group
[root@cloudcontrol source]# usermod -G oinstall,dba,oper,asmdba,asmadmin,asmoper,racdba oracle
[root@cloudcontrol source]# id oracle
uid=1000(oracle) gid=1000(oinstall) groups=1000(oinstall),1001(dba),1002(oper),507(asmdba),508(asmoper),509(asmadmin),1006(racdba)



[oracle@cloudcontrol ~]$ srvctl add database -db cloud13c -oraclehome /u01/app/oracle/product/12.1.0/db -spfile /u01/app/oracle/product/12.1.0/db/dbs/spfilecloud13c.ora -role PRIMARY -diskgroup DATA
[oracle@cloudcontrol ~]$ srvctl config database -d cloud13c
Database unique name: cloud13c
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.1.0/db/dbs/spfilecloud13c.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
OSDBA group:
OSOPER group:
Database instance: cloud13c
oracle@cloudcontrol ~]$ ll "/dev/oracleasm/disks/DATA01"
brw-rw---- 1 grid asmdba 8, 17 Feb 28 11:54 /dev/oracleasm/disks/DATA01
[oracle@cloudcontrol ~]$ srvctl start database -d cloud13c
[oracle@cloudcontrol ~]$ logout
[root@cloudcontrol source]# id grid
uid=502(grid) gid=1000(oinstall) groups=1000(oinstall),1001(dba),507(asmdba),508(asmoper),509(asmadmin),1006(racdba)





[root@cloudcontrol source]# oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=grid
ORACLEASM_GID=asmdba
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
[root@cloudcontrol source]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [grid]:
Default group to own the driver interface [asmdba]: asmadmin
Start Oracle ASM library driver on boot (y/n) [y]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@cloudcontrol source]# ll /dev/oracleasm/disks/
total 0
brw-rw---- 1 grid asmdba 8, 17 Feb 28 12:01 DATA01

[root@cloudcontrol source]# systemctl restart oracleasm.service
[root@cloudcontrol source]# ll /dev/oracleasm/disks/
total 0
brw-rw---- 1 grid asmadmin 8, 17 Feb 28 12:03 DATA01




Below for less than 11g.  or 10g to 11g

ASM to filesystem 10g





COPY_FILE

The COPY_FILE procedure allows you to copy binary files from one location to another on the same server.
-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Switch a tablespace into read only mode so we can
-- use it for a test file transfer.
ALTER TABLESPACE users READ ONLY;

-- Copy the file.
BEGIN
  DBMS_FILE_TRANSFER.copy_file(
   source_directory_object      => 'DB_FILES_DIR1',
   source_file_name             => 'USERS01.DBF',
   destination_directory_object => 'DB_FILES_DIR2',
   destination_file_name        => 'USERS01.DBF');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;
Checking the destination directory will reveal that the file has been copied successfully.

GET_FILE

The GET_FILE procedure allows you to copy binary files from a remote server to the local server.
-- Login to the remote server.
CONN system/password@remote

-- Create the source directory object and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
ALTER TABLESPACE users READ ONLY;

-- Login to the local server.
CONN system/password@local

-- Create the destination directory object and a database link.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';

-- Get the file.
BEGIN
  DBMS_FILE_TRANSFER.get_file(
   source_directory_object      => 'DB_FILES_DIR1',
   source_file_name             => 'USERS01.DBF',
   source_database              => 'REMOTE',
   destination_directory_object => 'DB_FILES_DIR2',
   destination_file_name        => 'USERS01.DBF');
END;
/

-- Login to the remote server.
CONN system/password@remote

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;
Checking the destination directory on the local server will reveal that the file has been copied successfully.

PUT_FILE

The PUT_FILE procedure allows you to copy binary files from the local server to a remote server.
-- Login to the remote server.
CONN system/password@remote

-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '/u02/oradata/DB10G/';

-- Login to the local server.
CONN system/password@local

-- Create the source directory object, database link and switch mode of a tablespace.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '/u01/oradata/DB10G/';
CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY password USING 'REMOTE';
ALTER TABLESPACE users READ ONLY;

-- Put the file.
BEGIN
  DBMS_FILE_TRANSFER.put_file(
   source_directory_object      => 'DB_FILES_DIR1',
   source_file_name             => 'USERS01.DBF',
   destination_directory_object => 'DB_FILES_DIR2',
   destination_file_name        => 'USERS01.DBF',
   destination_database         => 'REMOTE');
END;
/

-- Switch the tablespace back to read write mode.
ALTER TABLESPACE users READ WRITE;





Any questions, you can drop me an email to svusuresh@me.com

Or

Telegram: @sureshdooly
FB: facebook.com/svusuresh
IG: @suresh_dooly
Twitter: @svusuresh

About me - Oracle DBA for Oracle RDBMS and Oracle RDB for OpenVMS,
Berkley Database and NoSQL Cloud at
Oracle Corporation - Bannerughatta Road, Koramangala, Banglore

Comments

Post a Comment

Popular posts from this blog

TOP 10 VENOMOUS SNAKES IN THE WORLD

            It’s important to point out here that there’s a difference between   venomous snakes   and   poisonous snakes . Venomous here implies that something is capable of injecting someone or something with Venom, while poisonous implies that it is capable of harming you by merely touching or eating it.    Now let see the rating from 10th to 1st place -  10. Rattle Snake         The Rattle Snake is a sub-family of the pit vipers, it has a very unique tail that makes it easy to be identified. At the end of it’s tail is located the rattle which makes so much noise when shaken, that’s where the name “rattle snake” comes from. Large number of it can be found in America particularly at the south, western part of United States and Mexico. One Striking feature of rattle snake that they possessed from the pit vipers families is that they have two organs that...
Post a Comment
Read more
Alexander Graham Bell, the teacher of the deaf and his deaf wife Alexander Graham Bell. Alexander Graham Bell  (3 March 1847 – 2 August 1922) was an eminent scientist, inventor and innovator who is widely credited with inventing the first practical telephone. Bell was deeply affected by his mother's gradual deafness, (she began to lose her hearing when he was 12) and learned a manual finger language so he could sit at her side and tap out silently the conversations swirling around the family parlour. He also developed a technique of speaking in clear, modulated tones directly into his mother's forehead wherein she would hear him with reasonable clarity. Bell's preoccupation with his mother's deafness led him to study Acoustics (study of all mechanical waves in gases, liquids, and solids including vibration, sound, ultrasound and infrasound). Alexander Graham Bell, his wife Mabel Gardiner Hubbard, and their daughters Elsie (left) an...
Post a Comment
Read more

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

ORA-30013 is the error reported when the specified Undo tablespace is in use. In other words, this implies there are some background processes still using the undo tablespace.   This error can be reported in both space management or transaction management.   Error ORA-30013 reported while dropping the Undo tablespace.   In this case, the Undo tablespace is used by other active transactions, leading to the error. In such cases, we have to wait until the transactions are committed or rolled back before dropping the Undo tablespace. This situation is similar to that of the ORA-1548 case. Error ORA-30013 reported while bringing up the database.   This happens for RAC databases while the undo tablespace mentioned for an instance is already in use. The solution will be to set separate Undo tablespace for all instances.   Since undo_tablespace is an instance local parameter, we have to ensure that the "Alter system set undo_tablespace" is being invo...
Post a Comment
Read more