23
Feb

Curioso dato de sentencia CREATE DATABASE

Durante la creación de una base de datos con la sentencia "CREATE DATABASE" me topé con un curioso dato. Para evitar olvidar algún parámetro en la sentencia "CREATE DATABASE" preferí tomar el ejemplo desde la documentación oficial de Oracle y despues modificarlo con los valores de las rutas donde se crearán los datafiles, los redo logs, etc. A continuación los pongo en contexto:

El ambiente que estoy usando es el siguiente:

oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 23 17:22:58 2014

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

La documentación de Oracle 11.2 que estuve siguiendo es la siguiente:

http://docs.oracle.com/cd/E18283_01/server.112/e17120/create003.htm

Para el día de hoy la documentación luce así:

 

create database

Vean que en la parte del undo tablespace dice "UNDO TABLESPACE undotbs"

Bueno, al hacer uso de esta sentencia (despues de cambiarle mis directorios) obtuve lo siguiente:


SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             671089544 bytes
Database Buffers          390070272 bytes
Redo Buffers                5517312 bytes
SQL> CREATE DATABASE orcl
   USER SYS IDENTIFIED BY manager1
  2    3     USER SYSTEM IDENTIFIED BY manager1
  4     LOGFILE GROUP 1 ('+DATA') SIZE 50M BLOCKSIZE 512,
  5             GROUP 2 ('+DATA') SIZE 50M BLOCKSIZE 512,
  6             GROUP 3 ('+DATA') SIZE 50M BLOCKSIZE 512
  7     MAXLOGFILES 5
  8     MAXLOGMEMBERS 5
  9     MAXLOGHISTORY 1
 10     MAXDATAFILES 100
 11     CHARACTER SET AL32UTF8
 12     NATIONAL CHARACTER SET AL16UTF16
 13     EXTENT MANAGEMENT LOCAL
 14     DATAFILE '+DATA' SIZE 325M REUSE
   SYSAUX DATAFILE '+DATA' SIZE 325M REUSE
 15   16     DEFAULT TABLESPACE users
 17        DATAFILE '+DATA'
 18        SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 19     DEFAULT TEMPORARY TABLESPACE tempts1
 20        TEMPFILE '+DATA'
 21        SIZE 20M REUSE
 22     UNDO TABLESPACE undotbs
 23        DATAFILE '+DATA'
 24        SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE DATABASE orcl
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 4119
Session ID: 1 Serial number: 3


SQL> SQL>

Obtuve un curioso y místico nombre para el undo tablespace: UNDOTBS1. ¿Por qué? ¡yo claramente indiqué "undotbs"! angry ¿de donde salió ese "1" de más?, me dió curiosidad y fui a ver el supuesto script en el cual se generó el error "$ORACLE_HOME/rdbms/admin/dtxnspc.bsq'". Lo unico que ví relacionado con el UNDO fué lo siguiente:

REM   jklein    08/01/05 – creation
REM
rem Create default undo tablespace
"D_DUTSCR"
/

Nota: El datafile del undotbs sí se crea:

ASMCMD> cd datafile
ASMCMD> ls
UNDOTBS.261.840307145
SYSAUX.258.840307129
SYSTEM.259.840307105

Este error no es tan amigable como nosotros quisieramos, Gracias a Javier Morales Carrera se estableció que este error no amigable es la forma que oracle utiliza para decir "usted especificó otro valor en el parámetro UNDO_TABLESPACE" en el pfile o spfile que está utilizando.

Saludos!

 

Facebooktwitterlinkedinmailby feather
18
Feb

Prefijo personalizado para crear usuarios comunes en 12c

Oracle dice: Todo usuario común debe iniciar con "C##" o "c##"

prefijo

¿y si creamos nuestro propio prefijo?

¿Qué tal si quiero que los usuarios comunes inicien con "DEIBY"?

¿Que tal si quiero que los usuarios comunes inicien con "oraclefromguatemala"?

Bueno, recien ví un artículo en el que "se le muestra al mundo" como hacerlo:

 

SQL>  create user DEIBYUSER1 identified by "commonuser" container=all;
 create user COMMONUSER identified by "commonuser" container=all
             *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user C##USER1  identified by "commonuser" container=all;

User created.

SQL> alter system set "_common_user_prefix"="DEIBY" scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size                  2288584 bytes
Variable Size             503317560 bytes
Database Buffers          989855744 bytes
Redo Buffers                7737344 bytes
Database mounted.
Database opened.

QL> create user C##COMMONROLE3 identified by lll;
create user C##COMMONROLE3 identified by lll
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


SQL> create user DEIBYuser1 identified by llll container=all;

User created.

Salud! wink

Nota: No hacer esto en un ambiente de producción. 

Facebooktwitterlinkedinmailby feather
17
Feb

Drop Common User: Error de diseño en 12.1.0.1 (Bug documentado)

Indagando entre las cosas "raras" que trae Oracle 12c, me encontre algo nuevo (más cosas raras aqui), este "error de diseño" ya ha sido catalogado como un bug en la nota de metalink 1619287.1. Me es dificil aceptar que al Gigante Oracle se le ha olvidado programar algo que a mi parecer es muy obvio, es como decir que se te ha olvidado programar un "cuadrito" de un totito. Veamos las siguientes posibilidades:

pdbcerrada

Con unicamente 4 posibilidades, ¿es aceptable que se te haya olvidado programar una? Bueno…. en lo personal, para mi no es aceptable.

Veamos cada posibilidad a detalle:

SQL> select name,CON_ID, open_mode from v$pdbs;

NAME                               CON_ID OPEN_MODE
—————————— ———- ———-
PDB$SEED                                2 READ ONLY
PDB1                                    3 
READ WRITE
PDB2                                    4 READ WRITE

Crear  un usuario común cuando  las PDBs estén abiertas:

pdbabierta

SQL> create user C##COMMONUSER identified by "commonuser" container=all;

User created.

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            4
C##COMMONUSER            3
C##COMMONUSER            1

Como vemos, el usuario se creó en el CDB$ROOT, en la PDB1 y en la PDB2. Es decir, en todas las PDBs pues todas estaban abiertas.

Crear  un usuario común cuando una PDB este cerrada:

pdbcerrada2

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> select name,CON_ID, open_mode from v$pdbs;

NAME                               CON_ID OPEN_MODE
—————————— ———- ———-
PDB$SEED                                2 READ ONLY
PDB1                                    3 MOUNTED
PDB2                                    4 READ WRITE

SQL> create user C##COMMONUSER identified by "commonuser" container=all;

User created.

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            1
C##COMMONUSER            4

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            1
C##COMMONUSER            4
C##COMMONUSER            3

Como vemos, cuando PDB1 se abrió el usuario fué creado automáticamente.

Borrar un usuario común cuando las PDBs esten abiertas:

droppdbabierta

SQL> select name,CON_ID, open_mode from v$pdbs;

NAME                               CON_ID OPEN_MODE
—————————— ———- ———-
PDB$SEED                                2 READ ONLY
PDB1                                    3 READ WRITE
PDB2                                    4 READ WRITE

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            4
C##COMMONUSER            1
C##COMMONUSER            3

SQL> drop user C##COMMONUSER;

User dropped.

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

no rows selected

SQL>

Tal como se ve, el usuario fué borrado de todas las PDBs. 

Borrar  un usuario común cuando una PDB este cerrada:

droppdbcerrada

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            4
C##COMMONUSER            1
C##COMMONUSER            3

QL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> select name,CON_ID, open_mode from v$pdbs;

NAME                               CON_ID OPEN_MODE
—————————— ———- ———-
PDB$SEED                                2 READ ONLY
PDB1                                    3 MOUNTED
PDB2                                    4 READ WRITE

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            1
C##COMMONUSER            4

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            1
C##COMMONUSER            4

SQL> drop user C##COMMONUSER;

User dropped.

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

no rows selected

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select name,CON_ID, open_mode from v$pdbs;

NAME                               CON_ID OPEN_MODE
—————————— ———- ———-
PDB$SEED                                2 READ ONLY
PDB1                                    3 READ WRITE
PDB2                                    4 READ WRITE

SQL> select username, con_id from cdb_users where username='C##COMMONUSER';

USERNAME            CON_ID
————— ———-
C##COMMONUSER            3

frownfrown

Bueno, para los "CREATE USER" sí programaron todo, para "DROP USER" no. 

Usuario de tipo "Zombie"

zombie

Definición: Es un estado donde el usuario común no existe, pero, sí existe.

No existe para el CDB$ROOT…:

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

SQL> grant connect, create table to C##COMMONUSER;
grant connect, create table to C##COMMONUSER
                               *
ERROR at line 1:
ORA-01917: user or role 'C##COMMONUSER' does not exist

… pero sí existe para la PDB:

SQL> alter session set container=pdb1;

Session altered.

SQL> grant connect, create table to C##COMMONUSER;

Grant succeeded.

Interesante…. ¿Por qué el usuario se bloquea en su primer login?

SQL> conn C##COMMONUSER/commonuser@pdb1;
ERROR:
ORA-28000: the account is locked

Bueno, liberemos el usuario:

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb1;

Session altered.

SQL> alter user C##COMMONUSER account unlock;

User altered.

Volvamos a conectarnos con el usuario Zombie cool:

SQL> conn C##COMMONUSER/commonuser@pdb1;
ERROR:
ORA-00600: internal error code, arguments: [kziaVrfyAcctStatinRootCbk: !user],
[C##COMMONUSER], [], [], [], [], [], [], [], [], [], []


Warning: You are no longer connected to ORACLE.

Tipico, como bien saben, el ORA-600 es como un "CATCH ALL" que tiene oracle, donde van a caer todos los errores que no han sido programados puntualmente.

Bueno, en lo personal, quisiera que Oracle sacara un usuario de tipo "Batman" en la próxima versión, ojalá se me cumpla cool

 

 

Facebooktwitterlinkedinmailby feather
3
Feb

Move Database to another Diskgroup

Hello everybody, today we will move a database from +DATA1 to +DATA2 using Oracle Database Software 11.2.0.4.

Let's begin…

Check the Diskgroups:

[oracle@o1 ~]$ export ORACLE_SID=orcl
[oracle@o1 ~]$ sqlplus / as sysdba

SQL> select state, name from v$asm_diskgroup;

STATE       NAME
———– ——————————
CONNECTED   DATA1
MOUNTED     DATA2

CONNECTED: Diskgroup used by a database.

MOUNTED: Diskgroup available.

Check the current location of the datafiles:

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
+DATA1/orcl/datafile/users.268.838546481
+DATA1/orcl/datafile/undotbs1.261.838546481
+DATA1/orcl/datafile/sysaux.260.838546481
+DATA1/orcl/datafile/system.270.838546481

Check the database name:

SQL> show parameters db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      orcl

Create a backup of the Controlfile:

SQL> alter database backup controlfile to '+DATA2';

Database altered.

Restart to nomount the database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             788529168 bytes
Database Buffers          436207616 bytes
Redo Buffers                8970240 bytes
SQL>

Check the current location of the current controlfile:

SQL> show parameters control_file

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     7
control_files                        string      +DATA1/orcl/controlfile/curren
                                                 t.269.838549755

Restore the current control file to the new Diskgroup:

[oracle@o1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Mon Feb 3 09:46:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore controlfile to '+DATA2' from '+data1/orcl/controlfile/Current.267.838546563';

Starting restore at 03-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 03-FEB-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Find the new location of the restored controlfile:

[grid@o1 ~]$ asmcmd
ASMCMD> ls
DATA1/
DATA2/
ASMCMD> cd data2
ASMCMD> ls
ORCL/
ASMCMD> cd orcl
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd controlfile
ASMCMD> ls
Backup.256.838546931
current.257.838550097
ASMCMD> pwd
+data2/orcl/controlfile

Set the control_file parameter to the new location:

[oracle@o1 ~]$ sqlplus / as sysdba

SQL> alter system set control_files='+data2/orcl/controlfile/current.257.838550097' scope=spfile;

System altered.

Restart the database to mount state:

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

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             788529168 bytes
Database Buffers          436207616 bytes
Redo Buffers                8970240 bytes
Database mounted.

Move data files to the new Diskgroup:

[oracle@o1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Mon Feb 3 10:37:05 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1366327299, not open)

RMAN> backup as copy database format '+DATA2';

Starting backup at 03-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA1/orcl/datafile/system.270.838546481
output file name=+DATA2/orcl/datafile/system.258.838550239 tag=TAG20140203T103718 RECID=2 STAMP=838550299
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA1/orcl/datafile/sysaux.260.838546481
output file name=+DATA2/orcl/datafile/sysaux.259.838550303 tag=TAG20140203T103718 RECID=3 STAMP=838550342
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA1/orcl/datafile/undotbs1.261.838546481
output file name=+DATA2/orcl/datafile/undotbs1.260.838550349 tag=TAG20140203T103718 RECID=4 STAMP=838550351
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA2/orcl/controlfile/backup.261.838550353 tag=TAG20140203T103718 RECID=5 STAMP=838550353
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=+DATA1/orcl/datafile/users.268.838546481
output file name=+DATA2/orcl/datafile/users.262.838550355 tag=TAG20140203T103718 RECID=6 STAMP=838550355
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 03-FEB-14
channel ORA_DISK_1: finished piece 1 at 03-FEB-14
piece handle=+DATA2/orcl/backupset/2014_02_03/nnsnf0_tag20140203t103718_0.263.838550357 tag=TAG20140203T103718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-FEB-14

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA2/orcl/datafile/system.258.838550239"
datafile 2 switched to datafile copy "+DATA2/orcl/datafile/sysaux.259.838550303"
datafile 3 switched to datafile copy "+DATA2/orcl/datafile/undotbs1.260.838550349"
datafile 4 switched to datafile copy "+DATA2/orcl/datafile/users.262.838550355"

RMAN> recover database;

Starting recover at 03-FEB-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 03-FEB-14

RMAN> alter database open resetlogs;

database opened

Check the temp files used by the database:

[oracle@o1 ~]$ sqlplus / as sysdba

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
+DATA1/orcl/tempfile/temp.263.838546581

Recreate the tempfile in the new Diskgroup:

SQL> alter tablespace temp add tempfile '+DATA2' size 50M;

Tablespace altered.

SQL> alter database tempfile '+DATA1/orcl/tempfile/temp.263.838546581' drop;

Database altered.

Check the current logfiles:

SQL> select group#, member, status from v$logfile;

GROUP# MEMBER                                             STATUS
—— ————————————————– ——-
     3 +DATA1/orcl/onlinelog/group_3.264.838550475
     2 +DATA1/orcl/onlinelog/group_2.265.838550473
     1 +DATA1/orcl/onlinelog/group_1.266.838550471

Note: STATUS NULL means "in use".

Create new logfiles in the new Diskgroup:

SQL> alter database add logfile member '+DATA2' to group 1;

Database altered.

SQL>  alter database add logfile member '+DATA2' to group 2;

Database altered.

SQL>  alter database add logfile member '+DATA2' to group 3;

Database altered.

Check the current logfiles:

SQL> select group#, member, status from v$logfile;

GROUP# MEMBER                                             STATUS
—— ————————————————– ——-
     3 +DATA1/orcl/onlinelog/group_3.264.838550475
     2 +DATA1/orcl/onlinelog/group_2.265.838550473
     1 +DATA1/orcl/onlinelog/group_1.266.838550471
     1 +DATA2/orcl/onlinelog/group_1.265.838550747        INVALID
     2 +DATA2/orcl/onlinelog/group_2.266.838550757        INVALID
     3 +DATA2/orcl/onlinelog/group_3.267.838550763        INVALID

6 rows selected.

Note: INVALID STATUS is the default status after creation.

How to change the "INVALID STATUS" to "NULL"? 

SQL> alter system switch logfile;

System altered.

SQL> select group#, member, status from v$logfile;

GROUP# MEMBER                                             STATUS
—— ————————————————– ——-
     3 +DATA1/orcl/onlinelog/group_3.264.838550475
     2 +DATA1/orcl/onlinelog/group_2.265.838550473
     1 +DATA1/orcl/onlinelog/group_1.266.838550471
     1 +DATA2/orcl/onlinelog/group_1.265.838550747        INVALID
     2 +DATA2/orcl/onlinelog/group_2.266.838550757
     3 +DATA2/orcl/onlinelog/group_3.267.838550763        INVALID

6 rows selected.

SQL>  alter system switch logfile;

System altered.

SQL> select group#, member, status from v$logfile;

GROUP# MEMBER                                             STATUS
—— ————————————————– ——-
     3 +DATA1/orcl/onlinelog/group_3.264.838550475
     2 +DATA1/orcl/onlinelog/group_2.265.838550473
     1 +DATA1/orcl/onlinelog/group_1.266.838550471
     1 +DATA2/orcl/onlinelog/group_1.265.838550747        INVALID
     2 +DATA2/orcl/onlinelog/group_2.266.838550757
     3 +DATA2/orcl/onlinelog/group_3.267.838550763

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select group#, member, status from v$logfile;

GROUP# MEMBER                                             STATUS
—— ————————————————– ——-
     3 +DATA1/orcl/onlinelog/group_3.264.838550475
     2 +DATA1/orcl/onlinelog/group_2.265.838550473
     1 +DATA1/orcl/onlinelog/group_1.266.838550471
     1 +DATA2/orcl/onlinelog/group_1.265.838550747
     2 +DATA2/orcl/onlinelog/group_2.266.838550757
     3 +DATA2/orcl/onlinelog/group_3.267.838550763

6 rows selected.

Delete the old logfiles:

SQL> alter database drop logfile member '+DATA1/orcl/onlinelog/group_3.264.838550475';

Database altered.

SQL>  alter database drop logfile member '+DATA1/orcl/onlinelog/group_2.265.838550473';

Database altered.

SQL> alter database drop logfile member '+DATA1/orcl/onlinelog/group_1.266.838550471';
alter database drop logfile member '+DATA1/orcl/onlinelog/group_1.266.838550471'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 – cannot drop members
ORA-00312: online log 1 thread 1: '+DATA1/orcl/onlinelog/group_1.266.838550471'
ORA-00312: online log 1 thread 1: '+DATA2/orcl/onlinelog/group_1.265.838550747'

I tried delete the current logfile, so "switch logfile" is required:

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member '+DATA1/orcl/onlinelog/group_1.266.838550471';

Database altered.

Check the final status of logfiles:

SQL> select group#, member, status from v$logfile;

GROUP# MEMBER                                             STATUS
—— ————————————————– ——-
     1 +DATA2/orcl/onlinelog/group_1.265.838550747
     2 +DATA2/orcl/onlinelog/group_2.266.838550757
     3 +DATA2/orcl/onlinelog/group_3.267.838550763

Check the environment:

select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile;

NAME
——————————————————————————–
+DATA2/orcl/controlfile/current.257.838550097
+DATA2/orcl/datafile/sysaux.259.838550303
+DATA2/orcl/datafile/system.258.838550239
+DATA2/orcl/datafile/undotbs1.260.838550349
+DATA2/orcl/datafile/users.262.838550355
+DATA2/orcl/onlinelog/group_1.265.838550747
+DATA2/orcl/onlinelog/group_2.266.838550757
+DATA2/orcl/onlinelog/group_3.267.838550763
+DATA2/orcl/tempfile/temp.264.838550561

9 rows selected.

OPTIONAL: Move the spfile from the new Diskgroup;

SQL> show parameters spfile;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA1/orcl/spfileorcl.ora

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

SQL> create spfile='+DATA2'  from pfile='/home/oracle/pfile.ora';

File created.

Find the new location:

ASMCMD> cd data2
ASMCMD> ls
ORCL/
ASMCMD> cd orcl
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd parameterfile
ASMCMD> ls
spfile.268.838551147
ASMCMD> pwd
+data2/orcl/parameterfile

Change the path of the new spfile inside pfile:

[oracle@o1 ~]$ cd $ORACLE_HOME/dbs

[oracle@o1 dbs]$ ls
hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl
[oracle@o1 dbs]$ vi initorcl.ora

Before:
[oracle@o1 dbs]$ cat initorcl.ora
SPFILE='+DATA1/orcl/spfileorcl.ora'
After:
[oracle@o1 dbs]$ cat initorcl.ora
SPFILE='+data2/orcl/parameterfile/spfile.268.838551147'

Thank you for visit my blog!cool

Facebooktwitterlinkedinmailby feather