Move datafile from File system to ASM (or) Move datafile from NON-ASM to ASM

One of our application team has deployed new version of application, and we gave privilege to create tabelspace for their deployment.

As usual application team created the datafile in File System but our database is using ASM. So We need to Move datafile from File system to ASM.

##########################

#  Task 
##########################

Move Datafile From File system to ASM Diskgroup.

##########################
#  Pre checks 
##########################

1. File Name and Tablespace Name where Datafiles are Created.
2. Check Whether Diskgroup is having Sufficient Space
3. Intimate Application team that data's will not be available during this maintenance.

##########################

## Solution
##########################

File Name and Tablespace Name where Datafiles are Created.

set lines 200
col file_name for a60
col "size MB" for 9,99,999
col tablespace_name for a20

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where file_id=69;

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='TBS_NEW';

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Check Whether Diskgroup is having Sufficient Space

Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                             TOTAL_MB    FREE_MB

------------------------------ ---------- ----------

ORALIN_DATA                      2294348     495983

ORALIN_RECO                       184335     161864

Now Place the Tablespace OFFLINE.

SQL> alter tablespace TBS_NEW offline;

Tablespace altered.

Check Tablespace Status  

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS
-------------------- ---------
TBS_NEW           OFFLINE


Copy the Datafile from File System to ASM using RMAN command.

Copy Datafile 69 to '+ORALIN_DATA';

ORALIN_1 @ host01:/u01/app/oracle/diag/rdbms/ORALIN/ORALIN_1/trace

> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 4 00:03:30 2013

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

connected to target database: ORALIN (DBID=4269551271)

RMAN> copy datafile 69 to '+ORALIN_DATA';

Starting backup at 04-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4848 instance=ORALIN_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00069 name=/u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF

output file name=+ORALIN_DATA/ORALIN/datafile/TBS_NEW.352.806457841 tag=TAG20130204T000400 RECID=68 STAMP=806457842

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 04-FEB-13

Starting Control File and SPFILE Autobackup at 04-FEB-13

piece handle=+ORALIN_RECO/ORALIN_gvl/autobackup/2013_02_04/s_806457844.487.806457845 comment=NONE

Finished Control File and SPFILE Autobackup at 04-FEB-13

RMAN>

Now Switch the Datafile to Update this new location in controlfile.

switch datafile 69 to copy;

RMAN> switch datafile 69 to copy;
datafile 69 switched to datafile copy "+ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841"

Place Tablespace ONLINE.

SQL> alter tablespace TBS_NEW online;

Tablespace altered.

Check Tablespace Status  & Datafile Location :

SQL>  Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS

-------------------- ---------

TBS_NEW           ONLINE

SQL> Select file_id,file_name,status from dba_data_files where tablespace_name='TBS_NEW';

   FILE_ID FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ ---------
        69 +ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841  AVAILABLE

DIM-00014: Cannot open the Windows NT Service Control Manager.

After i took class on how to create database using CREATE database command in windows,

One of My Student tried creating a service using oradim and got the below error.

##########################
#  Error
##########################

##########################
#  Error Occurred
##########################

Error occurs when tried to create a database service using oradim in Windows 7

##########################
## Cause
##########################

1. User Access Control is Enabled
2. Owner which ran the Command is not the owner of the oracle software.

##########################

## Solution
##########################

1.  Disable User Access Control by 

• Click Start, and then click Control Panel.
• In Control Panel, click User Accounts.
• In the User Accounts window, click User Accounts.
• In the User Accounts tasks window, click Turn User Account Control on or off.

2.  Run the command prompt by logging to the owner of the software

3.  Start -> Accessories ->  Right click on Command Prompt 

and select "Run as Administrator".

ORA-19815 & ORA-16055 errors comes for different reasons also.

Whenever we see the error ORA-19815, we always assume that FRA is Nearing 100% and that is the reason we received the error.

But today i came across this and found that its not true all the times.

We used to get frequent ORA- errors from one of our Primary Dataguard database and the error recorded in Primary is ORA-16055 : FAL request rejected.

At the same time when we check the standby Alert log, error recorded in Standby is ORA-19815 which indicates that FRA is getting filled and reached 100%

##########################
#  Error
##########################

ORA-00270: error creating archive log
ORA-16055: FAL request rejected
ORA-16041: Remote File Server fatal error
ORA-19815: WARNING: db_recovery_file_dest_size of 424128020480 bytes is 96.93% used, and has 13021216768 remaining bytes available.

##########################
#  Errors Recorded in Primary 
##########################

Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc2_5171.trc:
ORA-00270: error creating archive log
Mon Jan  7 16:35:49 2013
FAL[server, ARC2]: FAL archive failed, see trace file.
Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc2_5171.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Mon Jan  7 16:35:49 2013
ORACLE Instance primdb - Archival Error. Archiver continuing.
Mon Jan  7 16:36:03 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc1_5169.trc:
ORA-16041: Remote File Server fatal error

##########################
#  Errors Recorded in Standby 
##########################

Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/udump/primdb_rfs_17693.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 424128020480 bytes is 96.93% used, and has 13021216768 remaining bytes available.
Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/udump/primdb_rfs_17693.trc:
ORA-00270: error creating archive log +primdb_RECO
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1062207488 bytes disk space from 424128020480 limit

##########################
#  Error Occurred
##########################

Error occurs randomly when RFS process tries to receive redo from Primary.

##########################
## Cause
##########################

Problem is actually caused by the parallel archive options set for Standby database destination in Primary Database.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stdbydb OPTIONAL ARCH SYNC NOAFFIRM REOPEN=30 MAX_CONNECTIONS=2 
NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb

Eventhough we have log_archive_max_processes was 4 which can very well handle the parallel archiving option, error occurs.

I have also tried setting upto 10 archiver process which didnt help.

##########################

## Solution
##########################

As the problem is mainly because of the Parallel archiving option, i have modified to use only one connection for archiving by setting MAX_CONNECTIONS = 1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stdbydb OPTIONAL ARCH SYNC NOAFFIRM REOPEN=30 MAX_CONNECTIONS = 1
 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb

1. Modify MAX_CONNECTIONS=1 in log_archive_dest_2
2. Defer and Enable log_archive_dest_state_2

This helped me and the false errors stopped recording in my primary and standby alert logs.

Location of OCR Files

Oracle Cluster Registry (OCR) File location :

Need to find the location of OCR but your CRS is down. Then how we can find?

Below are the methods from which we can find out where your OCR files are stored.

1. When CRS is up & running
2. When CRS is down or UP

##########################
#  1) When CRS is Up & Running
##########################

Set ASM environment or crs environment to run the below command.

 ocrcheck 

+ASM3 @ myhost03:/
>  ocrcheck 
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3788
Available space (kbytes) : 258332
ID : 519574392
Device/File Name :  +OCR_VOTE 
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user

So here OCR files are stored in    +OCR_VOTE  Diskgroup

##########################
#  2) When CRS is Down or Up
##########################

Look into  ocr.loc  file, Location of this file changes depending on the OS,

In Linux Platforms,    /etc/oracle/ocr.loc  

In Solaris Platforms,  /var/opt/oracle/ocr.loc 

+ASM3 @ myhost03:/

> more  /etc/oracle/ocr.loc  

ocrconfig_loc=  +OCR_VOTE 

local_only=FALSE

CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating

We had a Hardware problem in one of our 3 Node RAC Cluster and the 3rd Node needs to be rebooted. So before rebooting the server, we need to bring down all the oracle process to have a clean shutdown.

So i started shutting down all the oracle database instances using srvctl. Finally i was left with ASM instance.

When i tried to shutdown the asm instance got the below error.

##########################
#  Error
##########################

CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.MYDB_RECO.dg', but the force option was not specified

##########################
#  Full Error 
##########################


> srvctl stop asm -n srvnode03
PRCR-1014 : Failed to stop resource ora.asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.MYDB_RECO.dg', but the force option was not specified

##########################
#  Error Occurred
##########################

When i tried to stop the ASM Instance

Command Executed

srvctl stop asm -n srvnode03

Where,

srvnode03  -- is the name of my server which is the 3rd node in my Cluster

##########################
## Cause
##########################

ASM instance cannot be shutdown if the OCR files are stored in ASM. 

How to Find OCR Files Location?

http://stepintooracledba.blogspot.com/2013/01/location-of-ocr-files.html

##########################

## Solution
##########################

So the only possible way is to stop crs in 3rd node which will automatically bring down the ASM instance also.

crsctl stop crs

Above command can be executed only by root user and the executable is in $GI_HOME/bin path 

ORA-02095: specified initialization parameter cannot be modified

##########################
#  Error
##########################

ORA-02095: specified initialization parameter cannot be modified

ORA-02095 Error may mislead, check the below post,

http://stepintooracledba.blogspot.com/2013/02/ora-02095-misleading.html

##########################
#  Error Occurred
##########################

When i tried to change processes parameter as SYS user.

Command Executed

alter system set processes=300;

(or)

alter system set processes=300 scope=both;

##########################
## Cause
##########################

Processes parameter is a STATIC Parameter which cannot be changed at instance level.

##########################

## Solution
##########################

ALTER SYSTEM command with,

SCOPE=both  --- Applies the changes in the instance level as well as the spfile used for Startup

If SCOPE is not used, then it applies the changes at instance level.

• Check Database is Using SPFILE

SQL>sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DB1_DATA/db1/spfiledb1.ora

From above command we can confirm that database is using spfile, so next we need to check whether the parameter we are going to change can be modified at instance level.


set lines 200
col name for a35
col value for a25

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
processes                           600                       FALSE     FALSE FALSE

From the Above output we can find that whether a parameter can be modified at INSTANCE level.

ISSES_MODIFIABLE	VARCHAR2(5)	TRUE - the parameter can be changed with ALTER SESSION FALSE - the parameter cannot be changed with ALTER SESSION
ISSYS_MODIFIABLE	VARCHAR2(9)	IMMEDIATE - the parameter can be changed with ALTER SYSTEM DEFERRED - the parameter cannot be changed until the next session FALSE - the parameter cannot be changed with ALTER SYSTEM

So our processes parameter cannot be modified at instance level. So we need to update only the spfile, which will be reflected in the next instance startup.


SQL>alter system set processes=300 scope=spfile;

System altered.



SQL>sho parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150


See, Here parameter value is not changed at the INSTANCE level, but its updated in SPFILE. Once we bounce the database it will be reflected.


SQL>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1426065608 bytes
Database Buffers          671088640 bytes
Redo Buffers               38502400 bytes
Database mounted.
Database opened.
SQL>sho parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300


For Eg :


NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
log_archive_max_processes           4                         TRUE      FALSE IMMEDIATE

log_archive_max_processes parameter can be changed immediately via ALTER SYSTEM command and it will be applicable at instance level.

ORA-09925: Unable to create audit trail file & SVR4 Error: 49: Disc quota exceeded

##########################
#  Error
##########################

ORA-09925: Unable to create audit trail file

##########################
#  Full Error 
##########################

ORA-09925: Unable to create audit trail file
SVR4 Error: 49: Disc quota exceeded
Additional information: 9925
ORA-01075: you are currently logged on

##########################
#  Error Occurred
##########################

When i tried to login to the database as SYS user.

Command Executed

sqlplus / as sysdba

##########################
## Cause
##########################

There may be many reasons for this ORA-09925 Error. Possibly because of,

1. FRA Become Full
2. Number of Files Quota Exceeded 
3. ORACLE_HOME is 100% full

##########################

## Solution
##########################

• FRA Become Full

Some times this happens when FRA becomes 100%, check the usage of FRA and clear it. Run below query to check FRA usage.

Select * from v$flash_recovery_area_usage;

If Archive logs occupied FRA, then run a backup of archivelogs with delete input option.

• Number of Files Quota Exceeded 

SVR4 Error: 49: Disc quota exceeded

The above error accompanied ORA-09925 Error, so there is a possibility that the disk quota might have exceeded. Run the below command to check quota for the User.


mydb @server01:/opt/oracle/admin/mydb/bdump

quota -v

Disk quotas for oracle (uid 1001):

Filesystem     usage  quota  limit    timeleft  files  quota  limit    timeleft

/home/username

               898688 819200 1024000                380     -1     -1

From the above output we can see that usage(898688) is which is higher than the quota(819200). So reduce the number of files created. Some times Audit files generation will be high with a bunch of files. So clearing the audit files will help to overcome this error.

There are chances that some times the quota might have not exceeded.

In the below output, usage is very less still Disk quota Exceeded Error.

mydb @server01:/opt/oracle/admin/mydb/bdump

quota -v

Disk quotas for oracle (uid 1001):

Filesystem     usage  quota  limit    timeleft  files  quota  limit    timeleft

/home/username

               89688 819200 1024000                380     -1     -1

• ORACLE_HOME is 100% full

In the above case problem is ORACLE_HOME has 100% FULL. 

Run df -h and Clear the Files.

Some times Space will be not be cleared after removing files also. This is because the deleted FILES are being used by ORALCE or Other Process. Use lsof command to check the List of Open Files.

If the files are used by database, then bouncing the database will release the space used.

ERROR: NMO not setuid-root (Unix-only)

##########################
#  Error
##########################

ERROR: NMO not setuid-root (Unix-only)

##########################
#  Full Error 
##########################

ERROR: NMO not setuid-root (Unix-only)

##########################
#  Cause
##########################

Error occurred while running a shell script from enterprise manager. We have a shell scripts which will delete, archive database log files (dump, trace, alert log files).

##########################
#  Command Executed
##########################

Ran a shell script from Enterprise Manager

##########################
## Solution
##########################

### 11.2.0.1.0

nmo and nmb Agent Binary files are owned by Oracle user.

Actually these files needs to have root ownership. After installing Agent home in the server, the permission from oracle user will be changed by root.sh script which will be ran at the end of the Agent home installation.

Before :


agent11g @ server01:/opt/oracle/product/11.2/agent11g/bin
> ls -al nm?
-rws--x---   1 oracle   dba        22472 Mar 16  2012 nmb
-rws--x---   1 oracle   dba        29360 Mar 16  2012 nmo

Reason for improper file ownership may be because,

1. Root.sh script is not run at the end
2. there may be a interruption while root.sh script was ran.

Simple solution to fix this error is, change the ownership from oracle to root user.

Run below commands as Root User :

sudo chown root /opt/oracle/product/11.2/agent11g/bin/nmo

sudo chown root /opt/oracle/product/11.2/agent11g/bin/nmb

After :

no_sid @ server01:/opt/oracle/product/10.2/agent11g/bin

> ls -al nm?

-rws--x---   1 root     dba        22472 Mar 16  2012 nmb

-rws--x---   1 root     dba        29360 Mar 16  2012 nmo

Above is for 11g Agent Home.

A 10g Home Should look like below,

no_sid @ server01:/opt/oracle/product/10.2/agent10g/bin

> ls -al nm?

-rwsr-s---   1 root     dba        22472 Mar 16  2012 nmb

-rwsr-s---   1 root     dba        29360 Mar 16  2012 nmo

Note :

If root.sh script is not run then recommended option is to run root.sh script rather than giving just permission to these files, becuase root.sh does relinking of binaries and other activities.

ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [4], [H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL], [16384], [1], [0]

##########################
#  Error
##########################

ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [4], [H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL], [16384], [1], [0],

##########################
#  Full Error 
##########################


ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [4], [H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL], [16384], [1], [0], [], [], [], [], [], []
ORA-27046: file size is not a multiple of logical block size
OSD-04000: logical block size mismatch (OS 16384)
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL'
ORA-27048: skgfifi: file header information is invalid
OSD-04001: invalid logical block size (OS 538984480)


##########################
#  Cause
##########################

Error occurred while trying to startup the database of 11.2.0.1.0 version. Database is not mounted and its saying that Controlfile cant be opened.

##########################
#  Command Executed
##########################

Startup

##########################
## Solution
##########################

### 11.2.0.1.0

Initially i tried to investigate with the error, ORA-600 which led me saying that this is Bug 13256753  in this version of the database which is because of the INVALID file name. This answer doesnt satisfy me, so i tried to look into the other errors associated with the ORA-600.

So when i looked into the other errors, it shows that Controlfile header is corrupted. I tried to confirm it by running DBV which also notified that controlfile header is corrupted.



C:\Users\Administrator>dbv file=H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Dec 27 20:04:30 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBV-00107: Unknown header format (194) (2054913149)

I'm lucky enough to have mirrored controlfiles, so shutdown the database, copied the mirrored controlfiles to the corrupted file location and renamed it to the corrupted one.

Database is now Up and Running :D

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

##########################
#  Error
##########################

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

##########################
#  Full Error 
##########################

ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

##########################
#  Cause
##########################

Error occurred while trying to import a schema using datapump of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################

impdp -- Schema Import

##########################
## Solution
##########################

### 11.2.0.1.0

1) Index maintenance activity will be performed when impdp import is in progress, so disable the primary key constraints in the tables, then undo generation will be less and import will be successful.

2) In case if import is still failling after disabling primary constraints then try to add some more space to UNDO tablespace.

For me 1st solution worked.

RMAN-06617: UNTIL TIME (****) is ahead of last NEXT TIME in archived logs (****)

##########################
#  Error
##########################

RMAN-06617: UNTIL TIME (12-DEC-12) is ahead of last NEXT TIME in archived logs (12-DEC-12)

##########################
#  Full Error 
##########################


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:41:01
RMAN-06617: UNTIL TIME (12-DEC-12) is ahead of last NEXT TIME in archived logs (12-DEC-12)


##########################
#  Cause
##########################

Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################

duplicate database to dup11r2

until time "To_Date('12-DEC-2012 07:40:38','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';


##########################
## Solution
##########################

### 11.2.0.1.0


Set Until time used is ahead of the next_time of the last backed up archivelog.

select sequence#,completion_time,next_time from v$archived_log order by 2

 SEQUENCE# COMPLETION_TIME      NEXT_TIME
---------- -------------------- --------------------
       176 12-DEC-2012 07:33:31 12-DEC-2012 07:33:27
       177 12-DEC-2012 07:40:29 12-DEC-2012 07:40:28

So the next_time of the last backed up sequence 177 is "12-DEC-2012 07:40:28". Duplication is successful with this timestamp.

duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

RMAN-06457: UNTIL SCN (****) is ahead of last SCN in archived logs (****)

##########################
#  Error
##########################

RMAN-06457: UNTIL SCN (3680420) is ahead of last SCN in archived logs (3680404)

##########################
#  Full Error 
##########################


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:09:45
RMAN-06457: UNTIL SCN (3680420) is ahead of last SCN in archived logs (3680404)


##########################
#  Cause
##########################

Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################


duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:38','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';


##########################
## Solution
##########################

### 11.2.0.1.0


Set Until time used is ahead of the next_time of the last backed up archivelog.

select sequence#,completion_time,next_time from v$archived_log order by 2

 SEQUENCE# COMPLETION_TIME      NEXT_TIME
---------- -------------------- --------------------
       176 12-DEC-2012 07:33:31 12-DEC-2012 07:33:27
       177 12-DEC-2012 07:40:29 12-DEC-2012 07:40:28

So the next_time of the last backed up sequence 177 is "12-DEC-2012 07:40:28". Duplication is successful with this timestamp.

duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

##########################
#  Error
##########################


RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

##########################
#  Full Error 
##########################


Starting Duplicate Db at 12-DEC-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:27:52
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

##########################
#  Cause
##########################


Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################


duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:29','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';


##########################
## Solution
##########################

### 11.2.0.1.0


Auxiliary Database (Dup11r2) is started up with spfile. Here we are doing duplication without connecting to target or catalog database.

While starting the duplication, RMAN will restore the spfile from the backup location we have used and it will set the dbname, controlfiles, log_file_name_convert and db_file_name_convert parameters in the newly restored spfile and then it will startup using the spfile.


C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 12 08:28:21 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      H:\APP\ADMINISTRATOR\PRODUCT\1
                                                 1.2.0\DBHOME_1\DATABASE\SPFILE
                                                 DUP11R2.ORA
SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>


So when duplicating a database without connecting to target or catalog, we should start the auxiliary database with pfile only.

RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

##########################
#  Error
##########################


RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

##########################
#  Full Error 
##########################


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:35:35
RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

##########################
#  Cause
##########################


Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################


duplicate database to dup11r2
until scn 3587956
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

##########################
## Solution
##########################

### 11.2.0.1.0

Here we are doing duplication without connecting to target or catalog database. So we have to use UNTIL TIME parameter.

After changing UNTIL SCN to UNTIL TIME parameter, duplication process was successful.

duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

Create a new listener with different port numbers (Non Default Port)

##########################
# Create a new listener with different port numbers 
##########################

We are going to add a new listener named lsnr_10g_1533 Which is going to use port 1533

##########################
#  1) Check how Many Listeners are Running
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> ps -ef|grep tns
  oracle  4933     1   0   Jul 17 ?          16:23 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g -inherit
  oracle  4949     1   0   Jul 17 ?          33:49 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1532 -inherit
  oracle  4946     1   0   Jul 17 ?           6:17 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1531 -inherit
  oracle  4517  4062   0 23:27:17 pts/2       0:00 grep tns

##########################
#  2) Find the home where other Listeners are Running
##########################

Here Our listeners are running in ASM HOME

##########################
#  3) Edit Listener.ora
##########################

Add below entry in LISTENER.ORA file.

Here we are using a different Port number 1533. Default port is 1521. And also in SID_LIST_LSNR_10G_1533, we are specifying for which databases the listener has to listen.

LSNR_10G_1533 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_server.com)(PORT = 1533)(QUEUESIZE=99))
    )
  )

SID_LIST_LSNR_10G_1533 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_1.com)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_a)
      (SID_NAME = mydb_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_2.com)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_a)
      (SID_NAME = mydb_2)
    )
  )

##########################
## 4) Start the Listener
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> lsnrctl start LSNR_10G_1533

LSNRCTL for Solaris: Version 10.2.0.3.0 - Production on 05-DEC-2012 23:28:10

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Starting /opt/oracle/product/10.2/db_a/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.3.0 - Production
System parameter file is /opt/oracle/product/10.2/db_a/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2/db_a/network/log/lsnr_10g_1533.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_server)(PORT=1533)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server.com)(PORT=1533)(QUEUESIZE=99)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_10G_1533
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 - Production
Start Date                05-DEC-2012 23:28:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2/db_a/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2/db_a/network/log/lsnr_10g_1533.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_server)(PORT=1533)))
Services Summary...
Service "mydb_2.com" has 1 instance(s).
  Instance "mydb_2", status UNKNOWN, has 1 handler(s) for this service...
Service "mydb_1.com" has 1 instance(s).
  Instance "mydb_1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

##########################
## 5) check Listener is running or not
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> ps -ef|grep tns
  oracle  4933     1   0   Jul 17 ?          16:23 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g -inherit
  oracle  4949     1   0   Jul 17 ?          33:49 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1532 -inherit
  oracle  4946     1   0   Jul 17 ?           6:17 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1531 -inherit
  oracle  4551     1   0 23:28:11 ?           0:00 /opt/oracle/product/10.2/db_a/bin/tnslsnr LSNR_10G_1533 -inherit

##########################
## 6) Test whether new listener working or not
##########################

Add below TNS entry in TNSNAMES.ora and try connecting the database with this newly created listener.

mydb_2_1533.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my_server.com)(PORT = 1533))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb_2.com)
    )
  )

##########################
## 7) Establish SQL session and check
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> sqlplus system/password@mydb_2_1533.com

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 5 23:35:18 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
mydb_2    READ WRITE

SQL> exit

TABLESPACE Status Not showing as OFFLINE when brought to OFFLINE state from READ ONLY mode

##########################
## TABLESPACE Status Not showing as OFFLINE when brought to OFFLINE state from READ ONLY mode 
##########################

Yesterday one of my friend was asking that when a TABLESPACE was brought into OFFLINE mode from READ ONLY mode, Command succeeded but still DBA_TABLESPACE shows as READ ONLY and DBA_DATA_FILES shows as AVAILABLE.

So i wanna check this. Lets see how we can find the correct status of the tablespace.

##########################
## Place the TABLESPACE in READ ONLY mode
##########################


SQL> Alter tablespace users read only;

Tablespace altered.

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE



##########################
## Place the TABLESPACE in OFFLINE mode
##########################

SQL> alter tablespace users offline;

Tablespace altered.

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE

So When a TABLESPACE mode is changed from READ-ONLY to OFFLINE, it doesnt show up in DBA_DATA_FILES or DBA_TABLESPACES.

##########################

## How to check the correct Status of the TABLESPACE
##########################

1. Use V$DATAFILE to know the correct status as the status of the tablespace is recorded in Controlfile.

Select tablespace_name,df.status, name "Datafile"from dba_data_files ddf,v$datafile df where ddf.file_id=df.file# and ddf.tablespace_name='USERS';

TABLESPACE_NAME                STATUS  Datafile

------------------------------ ------- ---------------------------------------------------------------------------

USERS                          OFFLINE H:\APP\ADMINISTRATOR\ORADATA\ORA11G\USERS01.DBF

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS

------------------------------ ---------

USERS                          AVAILABLE

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS

------------------------------ ---------

USERS                          READ ONLY

Create Database Links in another User's name

##########################
## Create Database Links in another User's name
##########################

Here we are going to create a private database link in MY_ACC user account by logging as SYS user.

DB link name is MY_RPT.

Db link will be created in database remote_db and its username is remote_user.

##########################
## DB Link With Tnsnames entry
##########################

declare
uid number;
sqltext varchar2(1000) := 'create database link MY_RPT connect to remote_user identified by password using 'remote_db'';
myint integer;
begin
select user_id into uid from dba_users where username like 'MY_ACC';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
/

##########################
## DB Link Without Tnsnames entry
##########################

Here TNS entry is not present, so creating the database link using tns entry address.

declare
uid number;
sqltext varchar2(1000) := 'create database link MY_RPT connect to remote_user identified by password using ''(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02-vip)(PORT = 1531))
    (ADDRESS = (PROTOCOL = TCP)(HOST = host03-vip)(PORT = 1531))
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db)
    )
  )''';
myint integer;
begin
select user_id into uid from dba_users where username like 'MY_ACC';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
/

Create Blackout using Emcli

Mostly We used to login to the Enterprise Manager to create blackout which will be slow when we are trying to access Client EM from Local machine.

##########################
## Easier Way to Create A blackout Using EMCLI :
##########################

Logon to the server where database is running and set the Agent envionment .

##########################
## Server Blackout
##########################

emcli create_blackout -name="Blackout_Name" -reason="reboot due to memory errors" -add_targets="server01:host" -schedule="duration:-1" 

##########################
## Server & and its associated databases Blackout
##########################

Use propagate_targets to blackout the host and its associated oracle services (database, agent, asm, etc.)

emcli create_blackout -name="Blackout_Name" -reason="reboot due to memory errors" -add_targets="server01:host" -schedule="duration:-1"  -propagate_targets

##########################
## Single Database Blackout
##########################

emcli create_blackout -name="Blackout_Name" -add_targets="db_name:oracle_database" -schedule="duration:30"

##########################
## Multiple Database Blackout
##########################

emcli create_blackout -name="Blackout_Name"-reason="Failover" -add_targets="db1_name:oracle_database;db2_name:oracle_database" -schedule="duration:30"

##########################
## Blackout for RAC Database
##########################

emcli create_blackout -name="Blackout_Name"-reason="Failover" -add_targets="instance_name:oracle_database;cluster_name:rac_database" -schedule="duration:30"

##########################
## Unlimited Blackout
##########################

emcli create_blackout -name="Blackout_Name"-reason="Failover" -add_targets="instance_name:oracle_database;cluster_name:rac_database" -schedule="frequency:once;duration:-1"

If we give duration = "-1" then it denotes that unlimited Duration

##########################
## Show List of Targets in the Blackout
##########################

emcli get_blackout_targets -name=Blackout_Name

agent11g @ myserver03:/u01/home/oracle/admin/scripts
> emcli get_blackout_targets -name=Blackout_Name
Target Name  Target Type      Status       Status ID
mydb_2     oracle_database  In Blackout  1
mydb1_2   oracle_database  In Blackout  1
mydb2_2     oracle_database  In Blackout  1
mydb3_2     oracle_database  In Blackout  1

##########################
## Check Whether database is in blackout
##########################

emcli get_blackouts -target=mydb_1:oracle_database

agent11g @ myserver03:/u01/home/oracle
> emcli get_blackouts -target=mydb_1:oracle_database
Name                              Created By  Status   Status ID  Next Start           Duration  Reason  Frequency  Repeat  Start Time           End Time  Previous End
 TZ Region        TZ Offset

Blackout_name     SYSMAN      Started  4          2012-08-28 16:42:19  -00:01    none    once       none    2012-08-28 16:42:19  none      none      
 America/Chicago  +00:00

If the above command produces output like above then db is under blackout. If no output then db is not under blackout

##########################
## Blackout Details
##########################

emcli get_blackout_details  -name=failover_03-dec-2012

agent11g @ myserver03:/u01/home/oracle/admin/scripts
> emcli get_blackout_details  -name=failover_03-dec-2012
Status             Status ID  Run Jobs  Next Start           Duration  Reason    Frequency  Repeat  Days  Months  Start Time           End Time  TZ Region   TZ Offset
Start In Progress  1          no        2012-12-03 04:35:24  -00:01    Failover  once       none    none  none    2012-12-03 04:35:24  none      US/Central  +00:00
agent11g @ myserver03:/u01/home/oracle/admin/scripts

##########################
## Stop Blackout
##########################

emcli stop_blackout -name="Blackout_name"

agent11g @ myserver03:/u01/home/oracle
>emcli stop_blackout -name="Blackout_name"
Blackout "Blackout-Aug 28  2012 4:42:11 PM" stopped successfully

##########################
## Delete Blackout
##########################

emcli delete_blackout -name=Blackout_name