d-h-n.de
Blog about Oracle, Linux..

Create Oracle user using OS Authentication

August 16th, 2012 . by admin

as root create a new OS user:

# useradd -g dba -G oinstall tim

 
Add a database user:

[oracle@ol62 ~]$ sqlplus / as sysdba

First see parameter os_authent_prefix, remote_os_authent:

SYS@orcl> show parameter os_authent

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix		     string	 ops$
remote_os_authent		     boolean	 FALSE

remote_os_authent default is FALSE
 
 
Create user tim using OS Authentication:

SQL> alter system set remote_os_authent=true scope=spfile;
SQL> create user ops$tim identified externally;
SQL> grant dba to ops$tim;

 
 
Login tim as OS user:

[root@ol62 ~]# cp /home/oracle/.bash_profile /home/tim/.bash_profile
cp: overwrite `/home/tim/.bash_profile'? y
[root@ol62 ~]# su - tim

Connect DB local:

[tim@ol62 ~]$ sqlplus /
...
SQL> show user
USER is "OPS$TIM"

 
Restart DB:

SYS@orcl> shutdown immediate
SYS@orcl> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
..

The REMOTE_OS_AUTHENT initialization parameter is deprecated in 11g
 
 
Connect DB remote:

[tim@ol62 ~]$ sqlplus /@orcl

or

[tim@ol62 ~]$ sqlplus /@ol62/orcl.mydom.com

orcl is net_service_name (in tnsnames.ora)
ol62 is hostname
orcl.mydom.com is service_name

Posted in Oracle | No Comments »


Duplicate database to a remote server from active database

August 8th, 2012 . by admin

Duplicate a datbase orcl to orcl2 on a remote server

Prepare on source server

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.mydom.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol62.mydom.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol62)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.mydom.com)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl2.mydom.com)
    )
  )

 
 

Prepare on destination server

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2.mydom.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pctest.mydom.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

 
tnsnames.ora

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pctest)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl2.mydom.com)
    )
  )

 
on destination server create new pfile initorcl2.ora (in $ORACLE_HOME/dbs)

db_name="orcl2"

 
on destination server create password file:

$ orapwd file=orapworcl2 password=foo entries=20 ignorecase=n

 
on destination server make directories: 

mkdir -p /u01/app/oracle/oradata/orcl2
mkdir -p /u01/app/oracle/fast_recovery_area/orcl2
mkdir -p /u01/app/oracle/admin/orcl2/adump
mkdir -p /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
mkdir -p /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
mkdir -p /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace

 

Start DBs and run duplicate

from source server startup DBs:
start target db:

$ rlwrap sqlplus sys/foo@orcl as sysdba
SYS@orcl> startup
SYS@orcl> exit

start auxiliary db:

$ rlwrap sqlplus sys/foo@orcl2 as sysdba
SYS@orcl2> startup nomount
SYS@orcl2> exit

 
from source server start rman and connect DBs:

[oracle@ol62 ~]$ rlwrap rman target sys/foo@orcl auxiliary sys/foo@orcl2

connected to target database: ORCL (DBID=1317794828)
connected to auxiliary database: ORCL2 (not mounted)

 
Run duplicate command:

RMAN> duplicate target database to orcl2
from active database
spfile
parameter_value_convert =
'/orcl/', '/orcl2/'
set log_file_name_convert =
'/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/orcl2/'
db_file_name_convert =
'/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/orcl2/';

File $ORACLE_HOME/dbs/spfileorcl2.ora is created after duplicate.
Also remove pfile initorcl2.ora

Posted in Oracle | No Comments »


Duplicate Oracle database on the same server

August 1st, 2012 . by admin

Duplicate database from backups

TDO: on the same server duplicate a database named orcl to a new database name orcl2 from a full backup

 

1. Backup database

[oracle@ol62 ~]$ rlwrap rman target /
RMAN> backup database plus archivelog;

 

2. Setup static listener

Edit file listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2.mydom.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.mydom.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol62.mydom.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

 
start or reload listener

LSNRCTL> reload
LSNRCTL> stat
..Services Summary...
Service "orcl.mydom.com" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl2.mydom.com" has 1 instance(s).
  Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB.mydom.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

 

3. Config tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol62)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.mydom.com)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol62)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl2.mydom.com)
    )
  )

 

4. Create a new pfile initorcl2.ora (in $ORACLE_HOME/dbs/)

db_name='orcl2'
control_files='/u01/app/oracle/oradata/orcl2/control01.ctl',
'/u01/app/oracle/fast_recovery_area/orcl2/control02.ctl'
db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl2/'
log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl2/'

 

5. Create new directories

for: data files, control file, audit_file_dest, background_dump_dest, core_dump_dest, user_dump_dest 

mkdir -p /u01/app/oracle/oradata/orcl2
mkdir -p /u01/app/oracle/fast_recovery_area/orcl2
mkdir -p /u01/app/oracle/admin/orcl2/adump
mkdir -p /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
mkdir -p /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
mkdir -p /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace

 

6. Create password file for orcl2

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapworcl2 password=foo2 entries=20 ignorecase=n

-> Password file orapworcl2 created
 
or just copy file orapworcl to orapworcl2 if using the same password

$ cp orapworcl orapworcl2

 

7. Starting DBs

Start auxiliary (duplicate) DB orcl2

$ sqlplus sys/foo2@orcl2 as sysdba
SYS@orcl2> startup nomount

Start target (source) DB orcl

$ sqlplus sys/foo@orcl as sysdba
SYS@orcl> startup open

 

8. Starting rman and run duplicate

conect target (source) DB orcl and auxiliary (duplicate) DB orcl2

[oracle@ol62 ~]$ rlwrap rman target sys/foo@orcl auxiliary sys/foo2@orcl2
..
connected to target database: ORCL (DBID=1317794828)
connected to auxiliary database: ORCL2 (not mounted)

 

RMAN> duplicate target database to orcl2;

 
 

Duplicate database from active database

 

1. Backup database

no need a backup
 

2. Setup static listener

same as above
 

3. Config tnsnames.ora

same as above
 

4. Create a new pfile initorcl2.ora (in $ORACLE_HOME/dbs/)

with only this line:

db_name="orcl2"

 

5. Create new directories

same as above
 

6. Create password file for orcl2

same as above
 

7. Starting DBs

same as above
 

8. Starting rman and run duplicate

[oracle@ol62 ~]$ rlwrap rman target sys/foo@orcl auxiliary sys/foo2@orcl2

..

RMAN> duplicate target database to orcl2
from active database
spfile
parameter_value_convert =
'/orcl/', '/orcl2/'
set log_file_name_convert =
'/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/orcl2/'
db_file_name_convert =
'/u01/app/oracle/oradata/orcl/', '/u01/app/oracle/oradata/orcl2/';

Posted in Oracle | No Comments »


RMAN – Multisection Backups

July 30th, 2012 . by admin

What is Multisection Backups?

  • is a new feature in 11g
  • enables to back up and restore large files in more sections
  • section is a contiguous set of blocks in a file
  • each backup piece in a backup set will contain a section

 

Example

RMAN> configure device type disk parallelism 2;
RMAN> backup SECTION size 200M tablespace example tag "with sections"

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
5       B  F  A DISK        30-JUL-12       2       1       NO         WITH SECTIONS

RMAN> list backupset 5;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    67.90M     DISK        00:00:02     30-JUL-12
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1067922    30-JUL-12 /u01/app/oracle/oradata/orcl/example01.dbf

  Backup Set Copy #1 of backup set 5
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:02     30-JUL-12       NO         WITH SECTIONS

    List of Backup Pieces for backup set 5 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    10      1   AVAILABLE   /u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_07_30/o1_mf_nnndf_WITH_SECTIONS_81f93v9f_.bkp
    9       2   AVAILABLE   /u01/app/oracle/fast_recovery_area/ORCL/backupset/2012_07_30/o1_mf_nnndf_WITH_SECTIONS_81f93vh0_.bkp

Posted in Oracle | No Comments »


ORA-00214: inconsistent control files

July 13th, 2012 . by admin

when you start the database and got caught with this error message:

[oracle@oel62 ~]$ rlwrap sqlplus / as sysdba
SQL> startup
ORACLE instance started.

..
ORA-00214: control file '+DATA/orcl/controlfile/current.276.788282653' version
966 inconsistent with file '+DATA/orcl/controlfile/current.257.788280419'
version 964

 

SMCMD [+data/orcl/controlfile] > ls -l
Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     JUL 10 15:00:00  Y    current.257.788280419
CONTROLFILE  UNPROT  FINE     JUL 10 15:00:00  Y    current.276.788282653

 
 
TODO:

Set parameter control_files with only one control file

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

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

SQL> shutdown immediate

 
 

Recover database

RMAN> startup mount
RMAN> recover database;

 
 

Resetlogs

RMAN> alter database open resetlogs;

SQL> select status from v$instance;

STATUS
------------
OPEN

 
 

Remove obsolete control file

SQL> show parameter control_files

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files			     string	 +DATA/orcl/controlfile/current
						 .257.788280419

ASMCMD [+data/orcl/controlfile] > ls -l
Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     JUL 10 16:00:00  Y    current.257.788280419
CONTROLFILE  UNPROT  FINE     JUL 10 16:00:00  Y    current.276.788282653

ASMCMD [+data/orcl/controlfile] > rm current.276.788282653

 
 

Multiplex control files in ASM

Optional: Multiplex control file: see here

Posted in Oracle | No Comments »


Multiplex control files in ASM

July 10th, 2012 . by admin

TODO: Multiplex from 1 to 2 control files in ASM
 

Before

There is for example only one control file:

SQL> startup nomount
SQL> show parameter control_files

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files			     string	 +DATA/orcl/controlfile/current
						 .257.788280419

 
 

Change

Now try add the second control file on the same disk group:

SQL> alter system set control_files='+DATA/orcl/controlfile/current.257.788280419', '+DATA' scope=spfile;
SQL> shutdown immediate

[oracle@oel62 ~]$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from '+DATA/orcl/controlfile/current.257.788280419';

Starting restore at 10-JUL-12
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
output file name=+DATA/orcl/controlfile/current.257.788280419
output file name=+DATA/orcl/controlfile/current.276.788286299
Finished restore at 10-JUL-12

 
 

After

..and the result is:

[oracle@oel62 ~]$ rlwrap sqlplus / as sysdba

SQL> show parameter control_files

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files			     string	 +DATA/orcl/controlfile/current
						 .257.788280419, +DATA/orcl/con
						 trolfile/current.276.788286299
SQL> alter database mount;
SQL> alter database open;

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.257.788280419
+DATA/orcl/controlfile/current.276.788286299

Posted in Oracle | No Comments »


Move Oracle database from filesystem to ASM

June 29th, 2012 . by admin
    Enviroment

  • OS: Oracle Linux 6.2 run on VMWare
  • A simple Oracle 11.2.0.3 database named orcl on filesystem

 

Steps to move database from file system to a ASM diskgroup

 
 

Install Grid for Standalone Server on the same machine


Create directory grid parallel to directory of oracle and install Grid Infrastructure

[root@oel62 ~]#  mkdir -p /u01/app/grid
[root@oel62 ~]# chown grid:oinstall /u01/app/grid

more see: Install Grid for Standalone Server
Additional create 2 ASM disks using UDEV then create a new diskgroup named FRA for Fast Recovery Area.
Howto: see here
 
 

Modify user oracle


before:

[root@oel62 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

run:

[root@oel62 ~]# usermod -G oinstall,dba,oper,asmdba oracle

after:

[root@oel62 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54325(asmdba)

for more see: here
 
 

Change owner ship of binary oracle

[oracle@oel62 ~]$ su - grid

before:

[grid@oel62 ~]$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399123 Jun  5 17:18 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

run:

[grid@oel62 ~]$ /u01/app/grid/product/11.2.0/grid/bin/setasmgidwrap
o=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

after:

[grid@oel62 ~]$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 232399123 Jun  5 17:18 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

for more see: here
 
 

Backup database

RMAN> startup
RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ORA_ASM_MIGRATION';
RMAN> shutdown immediate

This is the crux of the story. We say via RMAN backup the database, but AS COPY(not backupset!), and with the format +DATA which mean all copies are stored in ASM diskgroup DATA
And next we say via command switch database to copy, which means: all data files of the datbase are now their copies!
 
 

Move spfile to ASM

RMAN> startup mount
RMAN> restore spfile to '+DATA/spfileorcl.ora';
RMAN> shutdown immediate

Delete file spfileorcl.ora and initorcl.ora in $ORACLE_HOME/dbs/dbs:
Create a new pfile initorcl.ora which points to the spfile in ASM with this line:

SPFILE='+DATA/spfileorcl.ora'

 
 

Set relevant parameter point to ASM

SQL> startup nomount;
SQL> alter system set DB_CREATE_FILE_DEST='+DATA' sid ='*';
SQL> alter system set DB_RECOVERY_FILE_DEST='+FRA' sid='*';
SQL> alter system set CONTROL_FILES='+DATA','+FRA' scope=spfile sid='*';
SQL> shutdown immediate

 
 

Move control files to ASM

RMAN> startup nomount
..
RMAN>  restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl';

Starting restore at 26-JUN-12
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
output file name=+DATA/orcl/controlfile/current.276.786977643
output file name=+FRA/orcl/controlfile/current.256.786977643
Finished restore at 26-JUN-12

 
 

Migration data files to ASM


All copies of datafiles are ready in ASM disgroup (see Backup).
Migration data files to ASM is simple: just a switch all data files to their image copies (in ASM diskgroup)!

RMAN> alter database mount;
RMAN> SWITCH DATABASE TO COPY;
RMAN> recover database;
RMAN> alter database open;

What mean: SWITCH DATABASE TO COPY? 
 
 

Migration temp file

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'  DROP;
SQL> ALTER TABLESPACE temp ADD TEMPFILE;

 
 

Migration online redo log files to ASM

SQL> select a.group#, a.status, b.member from v$log a,v$logfile b where a.group#=b.group# order by group#;

Create new log group:

SQL> alter database add logfile group 4 size 50M;
SQL> alter database add logfuile group 5 size 50M;
SQL> alter database add logfile group 6 size 50M;

Switch to new log group until old log group 1,2,3 have status INACTIVE:

SQL> alter system switch logfile;
SQL> alter system switch logfile;
..

Drop old INACTIVE log groups 1,2,3:

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

 
 

Migration archive log, backupset, copies

Optionally move archive log, backupset, copies from old FRA

RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
RMAN> BACKUP BACKUPSET ALL DELETE INPUT;
RMAN> BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;

 
 

Check


Where are the files, now?
Control files, Data files, Online Log Files, Tempfile, Archiv Log files?
 
Control files:

SQL> show parameter control_files

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files			     string	 +DATA/orcl/controlfile/current
						 .276.786977643, +FRA/orcl/cont
						 rolfile/current.256.786977643

SQL> select name from v$controlfile;

NAME
------------------------------------------------------------
+DATA/orcl/controlfile/current.276.786977643
+FRA/orcl/controlfile/current.256.786977643

 
Online log files:

SQL>  select a.group#, a.status, b.member from v$log a,v$logfile b where a.group#=b.group# order by group#;

    GROUP# STATUS	    MEMBER
---------- ---------------- --------------------------------------------------
	 4 INACTIVE	    +DATA/orcl/onlinelog/group_4.266.786980949
	 4 INACTIVE	    +FRA/orcl/onlinelog/group_4.259.786980949
	 5 CURRENT	    +DATA/orcl/onlinelog/group_5.262.786980955
	 5 CURRENT	    +FRA/orcl/onlinelog/group_5.258.786980957
	 6 INACTIVE	    +DATA/orcl/onlinelog/group_6.264.786980963
	 6 INACTIVE	    +FRA/orcl/onlinelog/group_6.257.786980963

6 rows selected.

 
Datafiles:

SQL> select name from v$datafile;

NAME
------------------------------------------------------------
+DATA/orcl/datafile/system.270.786932029
+DATA/orcl/datafile/sysaux.273.786932071
+DATA/orcl/datafile/undotbs1.279.786932105
+DATA/orcl/datafile/users.286.786932109

SQL> select name from v$datafile_header;

NAME
------------------------------------------------------------
+DATA/orcl/datafile/system.270.786932029
+DATA/orcl/datafile/sysaux.273.786932071
+DATA/orcl/datafile/undotbs1.279.786932105
+DATA/orcl/datafile/users.286.786932109

 
Tempfile:

SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------
+DATA/orcl/tempfile/temp.265.786980893

 
 
Diskgroup

[oracle@oel62 ~]$ su - grid
[grid@oel62 ~]$ rlwrap asmcmd -p
ASMCMD [+] > ls
DATA/
FRA/

 
Online log files:

ASMCMD [+] > find --type onlinelog . *
+DATA/ORCL/ONLINELOG/group_4.266.786980949
+DATA/ORCL/ONLINELOG/group_5.262.786980955
+DATA/ORCL/ONLINELOG/group_6.264.786980963
+FRA/ORCL/ONLINELOG/group_4.259.786980949
+FRA/ORCL/ONLINELOG/group_5.258.786980957
+FRA/ORCL/ONLINELOG/group_6.257.786980963

 
Control files:

ASMCMD [+] > find --type controlfile . *
+DATA/ORCL/CONTROLFILE/Backup.260.786932109
+DATA/ORCL/CONTROLFILE/current.276.786977643
+FRA/ORCL/CONTROLFILE/current.256.786977643

 
Datafiles:

ASMCMD [+] > find --type datafile . *
+DATA/ORCL/DATAFILE/SYSAUX.273.786932071
+DATA/ORCL/DATAFILE/SYSTEM.270.786932029
+DATA/ORCL/DATAFILE/UNDOTBS1.279.786932105
+DATA/ORCL/DATAFILE/USERS.286.786932109
+FRA/ORCL/DATAFILE/SYSAUX.263.786981205
+FRA/ORCL/DATAFILE/SYSTEM.264.786981149
+FRA/ORCL/DATAFILE/UNDOTBS1.262.786981251
+FRA/ORCL/DATAFILE/USERS.261.786981257

 
Archive log files:

ASMCMD [+] > find --type archivelog . *
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_10.271.786981119
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_11.270.786981119
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_4.267.786981111
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_5.266.786981113
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_6.265.786981113
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_7.274.786981115
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_8.273.786981115
+FRA/ORCL/ARCHIVELOG/2012_06_26/thread_1_seq_9.272.786981117

 
Tempfiles:

ASMCMD [+] > find --type tempfile . *
+DATA/ORCL/TEMPFILE/TEMP.265.786980893

 
 

Delete the remaining files

[oracle@oel62 ~]$ ls -r /u01/app/oracle/oradata/orcl/
temp01.dbf  redo03.log  redo02.log  redo01.log  control01.ctl
[oracle@oel62 ~]$ ls -r /u01/app/oracle/fast_recovery_area/orcl/
control02.ctl

 
 
Source: http://localhost/oradoc/server.112/e16102/asm_rman.htm#i1022780

Posted in Oracle | No Comments »


What mean: SWITCH DATABASE TO COPY?

June 26th, 2012 . by admin

Let create image copies of datafiles:

RMAN> startup
RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ORA_ASM_MIGRATION';
Starting backup at 26-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=+DATA/orcl/datafile/system.270.786932029 tag=ORA_ASM_MIGRATION RECID=1 STAMP=786932063
.....

 
 

Before switch

Datafiles Image copies 
SQL> select name from v$datafile;

NAME
--------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
1       1    A 26-JUN-12       1034213    26-JUN-12
        Name: +DATA/orcl/datafile/system.270.786932029
        Tag: ORA_ASM_MIGRATION

2       2    A 26-JUN-12       1035262    26-JUN-12
        Name: +DATA/orcl/datafile/sysaux.273.786932071
        Tag: ORA_ASM_MIGRATION

3       3    A 26-JUN-12       1036534    26-JUN-12
        Name: +DATA/orcl/datafile/undotbs1.279.786932105
        Tag: ORA_ASM_MIGRATION

5       4    A 26-JUN-12       1036713    26-JUN-12
        Name: +DATA/orcl/datafile/users.286.786932109
        Tag: ORA_ASM_MIGRATION
the data files are in OS filesystem and the image copies are in ASM diskgroup

 
 

Now run SWITCH DATABASE TO COPY

RMAN> shutdown immediate
RMAN> startup mount
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.270.786932029"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.273.786932071"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.279.786932105"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.286.786932109"

 
 

After switch

Datafiles Image copies 
SQL> select name from v$datafile;

NAME
------------------------------------------
+DATA/orcl/datafile/system.270.786932029
+DATA/orcl/datafile/sysaux.273.786932071
+DATA/orcl/datafile/undotbs1.279.786932105
+DATA/orcl/datafile/users.286.786932109

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
6       1    A 26-JUN-12       1037282    26-JUN-12
        Name: /u01/app/oracle/oradata/orcl/system01.dbf

7       2    A 26-JUN-12       1037282    26-JUN-12
        Name: /u01/app/oracle/oradata/orcl/sysaux01.dbf

8       3    A 26-JUN-12       1037282    26-JUN-12
        Name: /u01/app/oracle/oradata/orcl/undotbs01.dbf

9       4    A 26-JUN-12       1037282    26-JUN-12
        Name: /u01/app/oracle/oradata/orcl/users01.dbf
the data files are now in ASM diskgroup and image copies are now the old datafiles OS filesystem

 
 

Conclusion

What mean SWITCH DATABASE TO COPY?

http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta049.htm
Renames the datafiles and control files to use the filenames of image copies of these files.
RMAN switches to the latest image copy of each database file.
After a database switch, RMAN considers the previous database files as datafile copies.
 
Short: SWITCH DATABASE TO COPY mean: Switch all data files to their images copies
 
 
..and what is when we run switch database to copy again?
Correct! We become the state Before again.
The datafiles are in files system and their images are in ASM as before.

Just playing around with switch database to copy!

Posted in Oracle | No Comments »


ORA-15025, ORA-27041 and setasmgidwrap

June 19th, 2012 . by admin

With job role separation(oracle, grid) when run manual create database or when try move a database to ASM
like here restore conrolfile to ASM:

[oracle@oel62 ~]$ rman target /
..
RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl';
..
RMAN-03002: failure of restore command at 06/13/2012 11:55:21
RMAN-10038: database session for channel ORA_DISK_1 terminated unexpectedly

 
and in /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log:

ORA-15025: could not open disk "/dev/asm-disk1"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/asm-disk2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
SUCCESS: diskgroup DATA was mounted

 
 
Solution:
Change owner of binary $ORACLE_HOME/bin/oracle (of user oracle):
 
Before:

[grid@oel62 ~]$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399123 Jun  5 17:18 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

Run:

[grid@oel62 ~]/u01/app/grid/product/11.2.0/grid/bin/setasmgidwrap
o=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

After:

[grid@oel62 ~]$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 232399123 Jun  5 17:18 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

Posted in Oracle | No Comments »


User oracle in group asmdba?

June 16th, 2012 . by admin

without grid infrastructure, user oracle typical is:

[root@oel62 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

and now when you want migration a database to ASM, you installed grid infrastructure and
try first to restore controlfile to ASM for example:

RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/control01.ctl';

you got this:

Starting restore at 13-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/13/2012 11:52:45
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-01031: insufficient privileges
ORA-19600: input file is control file  (/u01/app/oracle/oradata/orcl/control01.ctl)
ORA-19601: output file is control file  (+DATA)

 
 
TODO:
Before:

[root@oel62 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

 
Run:

[root@oel62 ~]# usermod -G oinstall,dba,oper,asmdba oracle

 
After:

[root@oel62 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54325(asmdba)

 
 
 
=> user oracle must be in group asmdba

Posted in Oracle | No Comments »


« Previous Entries