Clone database from ACTIVE standby database.

Clone database from ACTIVE standby database.

below steps are useful when you already have ORCL_T datbase  ::

SOURCE : STANDBY DATABASE (ORCLSTDBY)

TARGET : TEST DATABASE (ORCL_T)

-- on Testing ( ORCL_T DB) DB

alter system set log_file_name_convert='+DATA_GROUP/orclstby/onlinelog/','+DATA_GROUP/orcl_t/onlinelog/' scope=spfile;
alter system set db_file_name_convert='+DATA_GROUP/orclstby/datafile/','+DATA_GROUP/orcl_t/datafile/' scope=spfile;
alter system set db_unique_name=orcl_t scope=spfile;

create pfile='/tmp/initorcl_t.ora' from spfile;
alter system set cluster_database=false scope=spfile;
shut immediate;
STARTUP NOMOUNT RESTRICT;
ALTER DATABASE MOUNT;
DROP DATABASE;

-- on Standby DB

alter database flashback off;
alter database recover managed standby database cancel;

-- on Testing(For Clone DB) DB

STARTUP NOMOUNT pfile='/tmp/initorcl_t.ora';
Create spfile from pfile='/tmp/initorcl_t.ora';
Startup mount
rman  target  sys/oracle@orclstby auxiliary   sys/oracle@orcl_t

--activerestore_final.cmd

run {
allocate channel C1 device type disk;
allocate channel C2 device type disk;
allocate channel C3 device type disk;
allocate channel C4 device type disk;
allocate channel C5 device type disk;
allocate auxiliary channel C6 device type disk;
allocate auxiliary channel C7 device type disk;
duplicate target database to orcl_t from active database;
release channel C1 ;
release channel C2;
release channel C3;
release channel C4;
release channel C5;
}

-- Make no archivelog on cloned DB.

-- on Standby DB
alter database flashback on;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Archive log gap Resolve on standby using RMAN incremental backup

Resolve Archive log gap on standby using RMAN incremental backup if missing of logs from standby and  primary.


--on standby

SQL>  select distinct checkpoint_change# from v$datafile_header order by 1;

note down minimums SCN number for incremental  backup


--on Primary

RMAN> run
 {
 BACKUP as compressed backupset device type disk INCREMENTAL FROM SCN 6504939829410 DATABASE FORMAT '/var/opt/backups/incremental_orcl_%U' tag
  'FOR_STANDBY_24July_2015';
 }

RMAN>  backup current controlfile for standby format '/var/opt/backups/new_orcl_contorl_stb.ctl';

sftp oracle backup to standby database


--on standby

NOTE down data FILE ID name FILE name of datafile on standby in case of data file rename.
NOTE down temp FILE ID and name FILE name of datafile on standby in case of temp data file rename.
NOTE down redo name FILE name of datafile on standby.



SQL>set head off pages 0 feed off echo off verify off
SQL>set lines 200
SQL>spool rename_datafiles.lst
SQL>select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '+asm_data_dg/dbci1sb/datafile/' || substr(name,instr(name,'/',-1)+1) || ''';' from v$datafile;
SQL>spool off

SQL>set head off pages 0 feed off echo off
SQL>spool rename_logfiles.lst
SQL>SELECT 'SQL "ALTER DATABASE RENAME FILE '''''||  MEMBER ||'''''' ||chr(10)||'to ''''' || member || '''''" ;' FROM V$LOGFILE;

SQL> create pfile='/tmp/init.ora' from spfile;
SQL> alter database backup controlfile to trace as '/tmp/col_ctrl.sql'


RMAN> report schema
RMAN> list incarnation of database;
RMAN> backup current controlfile  format '/var/opt/backups/orcl_contorl_stb_old.ctl';

SQL> shut immediate
shut down database and remove old controlfile

SQL>  startup nomount
RMAN> restore contorlfile from '/var/opt/backups/new_orcl_contorl_stb.ctl';
RMAN>  run
{
SET NEWNAME FOR DATAFILE 1 TO '+ASM_DATA_DG/orcldbci1sb/datafile/system.1590.859964471';
SET NEWNAME FOR DATAFILE 2 TO '+ASM_DATA_DG/orcldbci1sb/datafile/sysaux.1081.859965099';
SET NEWNAME FOR DATAFILE 3 TO '+ASM_DATA_DG/orcldbci1sb/datafile/undotbs1.1079.859975849';
SET NEWNAME FOR DATAFILE 4 TO '+ASM_DATA_DG/orcldbci1sb/datafile/undotbs2.2493.859958385';
SET NEWNAME FOR DATAFILE 5 TO '+ASM_DATA_DG/orcldbci1sb/datafile/users.1532.859968013';
switch datafile all;
switch tempfile all;
}

RMAN> recover database noredo;

RAC useful commands.

Status of database and Node

             srvctl status database -d PRDDB




Start database / Node ::::

               srvctl start nodeapps -n HOST01
               srvctl start nodeapps -n HOST02
               srvctl start asm -n HOST01
               srvctl start asm -n HOST02
               srvctl start database -d PRDDB
               srvctl start instance -d dbname -i instancename

Start Database in mount stage :
 
              srvctl start database -d db_name -o mount

Stop database / Node :::::

               srvctl stop database -d PRDDB
               srvctl stop asm -n HOST01
               srvctl stop asm -n HOST02
               srvctl stop nodeapps -n HOST01
               srvctl stop nodeapps -n HOST02
               srvctl stop instance -d dbname -i instancename
               srvctl stop database -d dbname -o immediate

rsctl start cluster
crsctl status resource -t
crsctl stop cluster -all

DB Upgrading Error with solution

Useful  link for 11g DB Upgrading Issues/Errors with solution


https://code.google.com/p/oracleinfrastructure/wiki/R12_2_Upgrading_DB_to_11_2_0_4#Upgrading_DB_to_11_2_0_4

Some Useful ASM commands and Query.

chk_asm_mapping.sh

The following is a script I found somewhere on the web. Unfortunately it was not totally correct for newer asm libraries. The script itself:

/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` |
cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' |
while read v_asmdisk v_minor v_major
do
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor, $v_major]"
done

When started it produces the following output:

ASM disk DISK001A based on /dev/sdc1 [8, 33]
ASM disk DISK001B based on /dev/sdb1 [8, 17]
ASM disk DISK002A based on /dev/sdd1 [8, 49]
ASM disk DISK002B based on /dev/sde1 [8, 65]
ASM disk DISK003A based on /dev/sdg1 [8, 97]
ASM disk DISK003B based on /dev/sdf1 [8, 81]

Disk Group Information

set pages 40000 lines 120
col NAME for a15
select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,
TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

DG# NAME  AU_SZ   STATE    TYPE   TOTAL_MB FREE_MB OFFLINE_DISKS
--- ----- ------- -------- ------ ------   ------- -------------
 1  DATA2 4194304 MOUNTED  NORMAL 40952    4168    0
 2  DATA  1048576 MOUNTED  NORMAL 30717    25667   0

ASM Disk Information

set pages 40000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,
PATH FROM V$ASM_DISK;

DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH
----------- ------- ------------ ------- -------- -------------
0           CLOSED  MEMBER       ONLINE  NORMAL   ORCL:DISK001A
3           CLOSED  MEMBER       ONLINE  NORMAL   ORCL:DISK003B
1           CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK001B
2           CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK002A
3           CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK002B

Combined ASM Disk and ASM Diskgroup information

The following query combines ASM disk and diskgroup information. You can edit this query to suit your needs easily. If you use ASM files instead of disks you have to use v$asm_file instead of v$asm_disk. If you use ASM files you have to add v$asm_file to the query.

col PATH for a15
col DG_NAME for a15
col DG_STATE for a10
col FAILGROUP for a10

select dg.name dg_name, dg.state dg_state, dg.type, d.disk_number dsk_no,
d.path, d.mount_status, d.FAILGROUP, d.state 
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by dg_name, dsk_no;

DG_NAME  DG_STATE TYPE   DSK_NO PATH          MOUNT_S FAILGROUP  STATE
-------- -------- ------ ------ ------------- ------- ---------- --------
DATA     MOUNTED  NORMAL 1      ORCL:DISK001B CACHED  DISK001B   NORMAL
DATA     MOUNTED  NORMAL 2      ORCL:DISK002A CACHED  DISK002A   NORMAL
DATA     MOUNTED  NORMAL 3      ORCL:DISK002B CACHED  DISK002B   NORMAL
DATA2    MOUNTED  NORMAL 1      ORCL:DISK003A CACHED  DISK003A   NORMAL
DATA2    MOUNTED  NORMAL 2      ORCL:DISK003B CACHED  DISK003B   NORMAL

Monitoring ASM disk operations

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation;

If there is a operating going on (like rebalancing) the query will return some rows. For instance for our just added disk the query yields:

GROUP_NUMBER OPERA STAT    ACTUAL  SOFAR      EST_MINUTES
------------ ----- ---- ---------- ---------- -----------
2            REBAL RUN           1         49          16

***********************  On ASMCMD **********************************************lsdg --> list diskgroup
lsdg -g  ---> for RAC to check the 
lsct : which particual DG is accessed or connected with database
lsattr -G DG_NAME -l   ---> it will show the attribues with values of Disk Group
lsof -G DG_NAME --> will show all the DB and DB file wihch are used in this DG group
ls -s  --> size of file
du   --> size details disk used
lsdsk -g DG_NAME -k --> the disk name which is attached to with this disk group
 

===>change the attributes of DiskGroup
export ORACLE_SID=+ASM
sqlplus /as sysdba
alter diskgr DG_NAME attribute='compatible.rdbms'='11.2.0'; 

==> to add the disk in the diskgroup
export ORACLE_SID=+ASM
sqlplus / as sysdba
alter diskgriup DG_NAME disk 'path_disk';

*********************************************************************

==> Rename the diskgroup and the datafile
export ORACLE_SID=orcl
sqlplus / as sysdba
select name frmo v$datafile;  --Note the file path

export ORACLE_SID=+ASM
$asmcmd
lsdg
umount DG_NAME  (OLD_DG_NAME)
renamedg dgname=OLD_DG_NAME newdgname='NEW_DG_NAME'
mount NEW_DG_NAME

export ORACLE_SID=orcl
sqlplus / as sysdba
alter datafile rename file '+OLD_DG_NAME/filename' to new '+NEW_DG_NAME/filename';
select name frmo v$datafile;

ERROR 45 Initializing SQL*PLUS Internal Error

ERROR 45 Initializing SQL*PLUS Internal Error

Oracle Tips by Burleson Consulting

Question:  I'm getting the "ERROR 45 INITIALIZING SQL*PLUS INTERNAL ERROR" error message.  How do you resolve the ERROR 45 Initializing SQL*PLUS Internal Error error?  

Answer:  MOSC 281450.1 notes:

    Error 45 Initializing In SQL*Plus When Running A Sql Script From OCFS
    Doc ID: Note:281450.1


    Steps to reproduce are shown by example as follows:

    1. Create a test.sql script on OCFS filesystem containing sql:
    startup nomount

    2. Check that test.sql script is in correct directory:
    $ ls -l /ocfs/scripts
    total 1
    -rw-r--r-- 1 apcluste dba 16 Aug 25 18:11 test.sql

    3. Invoke SQL*Plus:
    $ sqlplus /nolog

    SQL*Plus: Release 10.1.0.2.0 - Production on Wed Aug 25 18:17:41 2004
    Copyright ? 1982, 2004, Oracle. All rights reserved.

    4. Connect to database:
    SQL> connect / as sysdba
    Connected.

    5. Execute script:
    SQL> @/ocfs/scripts/test.sql
    Error 45 initializing SQL*Plus
    Internal error


    Cause
    The issue is identified as Bug:3039738. This typically reproduces when SQL script file is on a OCFS filesystem created on shared device (for example, SAN storage).

    Fix
    The only workaround, until bug is fixed, is to copy the SQL script file to a local disk (in case of Linux, a ext3 filesystem).

    Note: You can find the filesystem mount points available on system using command "mount".
    References
    Bug 3039738 - Error 45 Initializing Sql*Plus Running A Shell Script

Convert Single Instance Database To RAC

Convert Single Instance Database To RAC  ::


We need  to keep below points while converting Single Instance Database To RAC


1. Create redo and undo for second instance
2. Add cluster related parameters
3. Update the environment and start the database
4. Register the RAC instances with CRS
5. Create the spfile in ASM 



========== 1. Create redo and undo for second instance

alter database add logfile thread 2 group 4 ('+REDO_GROUP') size 100m ;
alter database add logfile thread 2 group 5 ('+REDO_GROUP') size 100m ;
alter database add logfile thread 2 group 6 ('+REDO_GROUP') size 100m ;
alter database enable public thread 2;

-- create undo tablespace UNDOTBS2 datafile  '+DG_DATA' size 500M;




========== 2. Add cluster related parameters

*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener='LISTENERS_ORADB’
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.thread=1
orcl2.thread=2
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
#update the actual controlfile path
*.control_files='+DG01/ORCL/controlfile/current.256.666342941','+FRA01/ORCL/controlfile/current.256.662312941'


========== 5. Create the spfile in ASM 


SQL>  create spfile='+DG_DATA03/orcl/PARAMETERFILE/spfile.ora' from pfile;


mv initORADB.ora initORADB1.ora
mv initORADB.ora initORADB2.ora

localhost:/opt/oracle/product/12.1.0.2/dbhome_1/dbs>cat initORCL.ora
    spfile='+DG_DATA02/ORCL/PARAMETERFILE/spfile.ora'




========== 3. Update the environment and start the database


export ORACLE_SID= orcl1
export ORACLE_HOME=/home/oracle/product/12.0.1/db_1
orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=sys123




========== 4. Register the RAC instances with CRS


srvctl add database -d orcl -o /opt/oracle/product/12.1.0.2/dbhome_1
srvctl add instance -d orcl -i orcl1 -n usdfw23db01cn01
srvctl add instance -d orcl -i orcl2 -n usdfw23db01cn02
srvctl modify database -d orcl  -p '+DG_DATA02/orcl/PARAMETERFILE/spfile.ora' -s open
srvctl modify database -d orcl -a 'DG_DATA02,DG_DATA03,REDO_GROUP'
srvctl config database -d $DB_NAME



   

Startup Fails With ORA-01012: Not Logged On

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.

 $ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 23 10:59:09 2016

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

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

SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged on

SQL> desc v$instance
ERROR:
ORA-01012: not logged on

SQL> startup
ORA-01012: not logged on


Changes
Oracle has been forcefully shutdown at OS level or crashed.

 CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.

 $ sysresv

IPC Resources for ORACLE_SID "TEST" :

Shared Memory:
ID              KEY
5963794         0x00000000
5996563         0x00000000
6029332         0xb2e3c9ac

Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "TEST"


Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

$ ipcrm -m 5963794
$ ipcrm -m 5996563
$ ipcrm -m 6029332

$sqlplus '/as sysdba'

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area   10689474560 bytes
Fixed Size                     2237776 bytes
Variable Size               6375344816 bytes
Database Buffers            4294967296 bytes
Redo Buffers                  16924672 bytes
Oracle Instance Started.
Oracle Database Opened.

ORA-07445: exception encountered: core dump [kcrf_val_log_bsz()+534] [SIGSEGV] [ADDR:0x4] [PC:0x214906E] [Address not mapped to object] []

Error --> during standby creation I got below error in RAC database.

after restore of standby controlfile, standby instance getting down with below error when executed  "ALTER DATABASE MOUNT"


ARC2: Becoming the active heartbeat ARCH
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x214906E, kcrf_val_log_bsz()+534] [flags: 0x0, count: 1]
Errors in file /opt/oracle/diag/rdbms/orcl/orcl_1/trace/orcl_1_lgwr_34167.trc  (incident=3785):
ORA-07445: exception encountered: core dump [kcrf_val_log_bsz()+534] [SIGSEGV] [ADDR:0x4] [PC:0x214906E] [Address not mapped to object] []
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/redo1b.log
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-00312: online log 1 thread 1: '+DG_FRA01/orcl/redo1a.log'
ORA-17503: ksfdopn:2 Failed to open file +DG_FRA01/orcl/redo1a.log
ORA-15173: entry 'redo1a.log' does not exist in directory 'orcl'
Incident details in: /opt/oracle/diag/rdbms/orcl/orcl_1/incident/incdir_3785/orcl_1_lgwr_34167_i3785.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Completed: alter database mount
Fri Jan 22 05:32:59 2016
Dumping diagnostic data in directory=[cdmp_20160122053259], requested by (instance=1, osid=34167 (LGWR)), summary=[incident=3785].



Cause : 

Might be log_file_name_convert is incorrect and not getting +DATA disk group on standby server.

Solution:

CHECK all Diskgroup name on Primary database and set correct log_file_name_convert parameter in Standby database.

ORA-19563: header validation failed for file

Error : 

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/18/2016 08:07:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database:
ORA-19563:  header validation failed for file

Cause :

This error may occurs during migration of database from NON-ASM to ASM/RAC using duplicate or restore database command.
because on NON-ASM environment database having on same name on different different mount points.

Soultion:

Check alerts log in target and get the file number/name for which throwing this error .

SELECT FILE#, NAME FROM V$DATAFILE where  NAME like '%system%';

FILE#           NAME
-----------      -------------
1                   /u01/orcl/datafile/system01.dbf
20                 /u02/orcl/datafile/system01.dbf

Rename the file ( /u02/orcl/datafile/system01.dbf) on primary database


set line 200
col file_name for a60
set pages 1000
select TABLESPACE_NAME,STATUS from dba_tablespaces where STATUS NOT IN ('ONLINE');
select TABLESPACE_NAME, FILE_NAME,STATUS from dba_data_files where FILE_NAME like '%system%';
ALTER TABLESPACE system OFFLINE;
cp  /u02/orcl/datafile/system01.dbf  /u02/orcl/datafile/system02.dbf
sqlplus / as sysdba
ALTER DATABASE RENAME FILE ' /u02/orcl/datafile/system01.dbf' TO '/u02/orcl/datafile/system01.dbf';
ALTER TABLESPACE system ONLINE;
select TABLESPACE_NAME,STATUS from dba_tablespaces where STATUS NOT IN ('ONLINE');

NOW try to  restore/duplicate database on ASM at target server.

rman  target sys/sys_pwd@orcl auxiliary  sys/sys_pwd@orcl_dev

run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
allocate auxiliary channel C3 device type disk;
allocate auxiliary channel C4 device type disk;
allocate auxiliary channel C5 device type disk;
#duplicate target database for standby from active database;
#DUPLICATE TARGET DATABASE TO orcl_dev FROM ACTIVE DATABASE;
}