Oracle 12.1.0.2 silent install and de-install


After Oracle Enterprise Linux setup we must set up some settings shown bellow:

Edit “/etc/hosts” file and add fully qualified name of your server.

[root@agcns Desktop]# vi /etc/hosts
127.0.0.1      localhost localhost.localdomain 
192.168.8.157  agcns agcns.localdomain

Then install oracle-rdbms-server-12cR1-preinstall package to perform all your prerequisite setup

[root@agcns Desktop]# yum install oracle-rdbms-server-12cR1-preinstall -y

Add lines bellow to the /etc/security/limits.conf  file.

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

Install  packages bellow if they are not already installed.

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

Set the password for the oracle user.

[root@agcns Desktop]# passwd oracle
Changing password for user oracle.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

change   /etc/security/limits.d/90-nproc.conf  like below.

*     -   nproc 16384
root soft nproc unlimited

Set  SELINUX to permissive, disable firewall and then reboot server:

[root@agcns]# vi /etc/selinux/config
SELINUX=permissive
[root@agcns]# service iptables stop
[root@agcns]# chkconfig iptables off
[root@agcns]# reboot

Create the ORACLE_HOME directory in which the Oracle software will be installed.

[root@agcns ~]# mkdir -p /u01/app/oracle/product/12.1.0.2/db_1
[root@agcns ~]# chown -R oracle:oinstall /u01
[root@agcns ~]# chmod -R 775 /u01

Switch to oracle account and unzip Oracle Installation zip files:

[oracle@agcns]$ unzip linuxamd64_12c_database_1of2.zip
[oracle@agcns]$ unzip linuxamd64_12c_database_2of2.zip

Edit bash_profile and add lines  bellow to this file:

[oracle@agcns]$ vi /home/oracle/.bash_profile
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=agcns.localdomain
export ORACLE_UNQNAME=gcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=gcdb
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

To perform silent installation  we must create and  use oraInst.loc and response file.

[root@agcns ~]# vi /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

[root@agcns ~]# chown oracle:oinstall /etc/oraInst.loc 
[root@agcns ~]# chmod 664 /etc/oraInst.loc

[oracle@agcns database]$ ./runInstaller -silent \
> -responseFile /tmp/database/response/db_install.rsp \
> oracle.install.option=INSTALL_DB_SWONLY \
> UNIX_GROUP_NAME=oinstall \
> INVENTORY_LOCATION=/u01/app/oracle/oraInventory \
> SELECTED_LANGUAGES=en \
> ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 \
> ORACLE_BASE=/u01/app/oracle \
> oracle.install.db.InstallEdition=EE \
> oracle.install.db.isCustomInstall=false \
> oracle.install.db.DBA_GROUP=dba \
> oracle.install.db.OPER_GROUP=dba \
> oracle.install.db.BACKUPDBA_GROUP=dba \
> oracle.install.db.DGDBA_GROUP=dba \
> oracle.install.db.KMDBA_GROUP=dba \
> SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
> DECLINE_SECURITY_UPDATES=true
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 18421 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3919 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-09-07_08-06-29PM. Please wait ...[oracle@agcns database]$ 
[oracle@agcns database]$ 
[oracle@agcns database]$ You can find the log of this install session at:
 /u01/app/oracle/oraInventory/logs/installActions2016-09-07_08-06-29PM.log
The installation of Oracle Database 12c was successful.
Please check '/u01/app/oracle/oraInventory/logs/silentInstall2016-09-07_08-06-29PM.log' for more details.

As a root user, execute the following script(s):
 1. /u01/app/oracle/product/12.1.0.2/db_1/root.sh


Successfully Setup Software.



[oracle@agcns logs]$ su -
Password: 
[root@agcns ~]# /u01/app/oracle/product/12.1.0.2/db_1/root.sh
Check /u01/app/oracle/product/12.1.0.2/db_1/install/root_agcns.localdomain_2016-09-07_20-12-10.log for the output of root script
[root@agcns ~]# cat /u01/app/oracle/product/12.1.0.2/db_1/install/root_agcns.localdomain_2016-09-07_20-12-10.log 
Performing root user operation for Oracle 12c 

The following environment variables are set as:
 ORACLE_OWNER= oracle
 ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/db_1
 Copying dbhome to /usr/local/bin ...
 Copying oraenv to /usr/local/bin ...
 Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
[root@agcns ~]# 


[oracle@agcns ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 7 20:14:43 2016

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

Connected to an idle instance.

SQL> 

 

De-Installing Oracle Database

To remove installed oracle software, you must run the de-install utility by invoking the runInstaller with the deinstall keyword:.

[oracle@agcns database]$ ./runInstaller -deinstall -home /u01/app/oracle/product/12.1.0.2/db_1/
Checking for required space in /tmp directory ...
Please wait ...
./runInstaller: line 167: [: 61%: integer expression expected
Space check on /tmp directory passed...
Bootstrapping the deinstall components...Done
Location of logs /tmp/deinstall_bootstrap/logs/
                      <<<<< OUTPUT TRIMMED >>>>>>
####################### CLEAN OPERATION SUMMARY #######################
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully deleted directory '/u01/app/oracle/product/12.1.0.2/db_1' on the local node.
Successfully deleted directory '/u01/app/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.


Run 'rm -rf /etc/oraInst.loc' as root on node(s) 'agcns' at the end of the session.

Run 'rm -rf /opt/ORCLfmap' as root on node(s) 'agcns' at the end of the session.
Run 'rm -rf /etc/oratab' as root on node(s) 'agcns' at the end of the session.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################

[oracle@agcns database]$ su -
Password: 
[root@agcns ~]# rm -rf /opt/ORCLfmap
[root@agcns ~]# rm -rf /etc/oratab
[root@agcns ~]# rm -rf /etc/oraInst.loc






 

 

 

 

ORA-02062 Error (Purging Pending Rows from the Data Dictionary)


After forced shutdown of operating system without shutting down of Oracle database in test environment we faces with error  shown bellow:

*** 2016-08-16 18:06:24.465
ERROR, tran=10.12.494450, session#=1, ose=0:
ORA-02062: distributed recovery received DBID 7472eb0b, expected d13ce73

This issue encountered when one system went down for maintenance (maybe abort mode) and left a 2 phase commit trx in limbo. Automatic recovery normally deletes entries in these states. The only exception is when recovery discovers a forced transaction that is in a state inconsistent with other sites in the transaction. Oracle will try periodically to recover (even though it can’t).  In this case, the entry can be left in the table and the MIXED column in DBA_2PC_PENDING has a value of YES.

The information in DBA_2PC_PENDING  describes distributed transactions awaiting recovery and will never be deleted. To fix this problem i used   DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY  procedure..

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('transaction_id'); 

EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.12.494450');

Automatically and Manual Purging Alert Log and Trace files with Automatic Diagnostic Repository Command Interpreter (ADRCI)


Automatically purging

Sometimes we are facing with space related issues due to the huge number of trace file generation. Automatic purging can help us in this situations if set short retention time for trace files.

  1. Type show homes to see current homes supported by ADRCI and set homepath.
    adrci> show homes
    ADR Homes: 
    diag/rdbms/db12c_stby/db12c_stby
    diag/rdbms/db12c_stby/db12c
    diag/rdbms/db12c_stby/DB12c
    diag/tnslsnr/Standby/listener
    diag/clients/user_oracle/host_2436394132_82
     
    adrci> set homepath diag/rdbms/db12c_stby/db12c_stby
    


  2. The automatic purging runs on schedule defined in retention policy .To check current policy for home, we can use two methods :
    adrci> show control
    
    ADR Home = /u01/app/oracle/diag/rdbms/db12c_stby/db12c_stby:
    *************************************************************************
    ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                              
    -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 
    3514698554           720                  8760                 2016-05-31 00:54:57.566484 +05:00        2016-07-07 23:27:37.789953 +05:00                                                 1                    2                    82                   1                    2016-05-31 00:54:57.566484 +05:00       
    1 rows fetched

    or

    adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;
    
    ADR Home = /u01/app/oracle/diag/rdbms/db12c_stby/db12c_stby:
    *************************************************************************
    SHORTP_POLICY        LONGP_POLICY         
    -------------------- -------------------- 
    720                  8760                
    1 rows fetched

    By default retention is 720 hours for short policy and 8760 hours for long policy.
    Short policy include the following files :(Trace files, Core dump files, Packaging information)
    Long policy include the following files:(Incident information, Incident dumps, Alert logs)

  3. To change the retention policy :
    adrci> set control (SHORTP_POLICY=120); -- 5 days
    
    adrci> set control (LONGP_POLICY=720); -- 1 week
    
    
    --check changes
    
    adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL; 
    
    ADR Home = /u01/app/oracle/diag/rdbms/db12c_stby/db12c_stby:
    *************************************************************************
    SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME LAST_MANUPRG_TIME 
    -------------------- -------------------- ---------------------------------------- ---------------------------------------- 
    120 720 2016-07-07 23:27:37.789953 +05:00 2016-07-16 22:37:52.700123 +05:00 
    1 rows fetched
    
    adrci> 
    
    

    Initially automatic purging runs 2 days after instance startup, then by policy defined interval.

 


Manual purging

  1. Type show homes to see current homes supported by ADRCI and set homepath.
    [oracle@MAINDB ~]$ adrci
    
    ADRCI: Release 12.1.0.2.0 - Production on Tue Feb 23 19:18:42 2016
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
    
    ADR base = "/u01/app/oracle"
    
    adrci> show homes
    ADR Homes: 
    diag/tnslsnr/MAINDB/listener
    diag/rdbms/cdb1/cdb1
    
    adrci> set homepath diag/rdbms/cdb1/cdb1
    
    

     

  2. To purge diagnostic data that is older than the amount of time (minutes) given in the purge command. For ex to purge diagnostic data that is over 1 day old (1440 minutes).
    adrci> PURGE -age 1440 -type ALERT
    
    --You can also pruge TRACE files with same method:
    adrci> PURGE -age 1440 -type TRACE
    
    
  3. To remove all data older than one minute use:
    adrci> PURGE -age 1 -type ALERT
    adrci> SHOW ALERT
    
    ADR Home = /u01/app/oracle/diag/rdbms/cdb1/cdb1:
    *************************************************************************
    
    No alert log in selected home
    adrci> exit

Dataguard commands and SQL scripts


PHYSICAL STANDBY COMMANDS

To start redo apply in foreground:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

To stop redo apply process on the Standby database (to stop MRP):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

To start real-time redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

To start redo apply in background:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;

To check redo apply service status:

SQL> SELECT THREAD#, SEQUENCE#, PROCESS, CLIENT_PROCESS, STATUS, BLOCKS FROM V$MANAGED_STANDBY;

If managed standby recovery is not running or not started with real-time apply, restart managed recovery with real-time apply enabled:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

To gather Data Guard configuration information(standby)

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE 

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE RECOVERY_MODE!='IDLE';

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

To calculate the Redo bytes per second

SQL> SELECT SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 60 / 60 / 30 REDO_MB_PER_SEC
 FROM GV$ARCHIVED_LOG
 WHERE FIRST_TIME BETWEEN TO_DATE ('01.05.2016', 'DD.MM.YYYY')
 AND TO_DATE ('01.06.2016', 'DD.MM.YYYY')

To check status of Data Guard synchronization(standby):

SQL> SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

NAME VALUE
--------------------- -------------------------------
transport lag          +00 00:00:00
apply lag              +00 00:00:00
apply finish time      +00 00:00:00.000
estimated startup time 32

To verify there is no log file gap between the primary and the standby database:

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 3;

STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP

 

To verify that the primary database can be switched to the standby role:

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 

SWITCHOVER_STATUS
--------------------
TO STANDBY

To convert the primary database into a physical standby :

Before switchover the current control file is backed up to the current SQL session trace file and it possible to reconstruct a current control file, if necessary.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

To verify Managed Recovery is running on the standby :

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%'; 

PROCESS
---------
MRP0

To show information about the protection mode, the protection level, the role of the database, and switchover status:

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE     INSTANCE    OPEN_MODE    PROTECTION_MODE     PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ---------- ------------ -------------------- -------------------- -------------------- --------------------
PRIMARY           TESTCDB    READ WRITE    MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE   TO STANDBY

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 

To determine which log files were not received by the standby site.

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
FROM (SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2 AND THREAD# = LOCAL.THREAD#);

 

Archivelog difference: Run this on the primary database. (not for real-time apply):

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> 
SELECT A.THREAD#,
 B.LAST_SEQ,
 A.APPLIED_SEQ,
 A.LAST_APP_TIMESTAMP,
 B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF
 FROM ( SELECT THREAD#,
 MAX (SEQUENCE#) APPLIED_SEQ,
 MAX (NEXT_TIME) LAST_APP_TIMESTAMP
 FROM GV$ARCHIVED_LOG
 WHERE APPLIED = 'YES'
 GROUP BY THREAD#) A,
 ( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ
 FROM GV$ARCHIVED_LOG
 GROUP BY THREAD#) B
 WHERE A.THREAD# = B.THREAD#;

 THREAD#   LAST_SEQ    APPLIED_SEQ  LAST_APP_TIMESTAMP   ARC_DIFF
---------- ---------- -----------   --------------------- ----------
 1         21282      21281         09-IYUL-2016 12:06:5     1
 2         23747      23746         09-IYUL-2016 12:16:13    1

2 rows selected.

 

To check archive log apply  on primary database:

SQL> SET LINESIZE 150
SET PAGESIZE 999
COL NAME FORMAT A60
COL DEST_TYPE FORMAT A10
COL ARCHIVED FORMAT A10
COL APPLIED FORMAT A10

SELECT SEQUENCE#,
NAME,
DEST_ID ,
CASE WHEN STANDBY_DEST = 'YES' THEN 'Standby' ELSE 'Local' END
AS DEST_TYPE ,
ARCHIVED ,
APPLIED
FROM V$ARCHIVED_LOG
WHERE SEQUENCE# > (SELECT MAX (SEQUENCE#)
FROM V$ARCHIVED_LOG
WHERE STANDBY_DEST = 'YES' AND APPLIED = 'YES')
ORDER BY SEQUENCE# , DEST_ID ;


 SEQUENCE#  NAME                                                          DEST_ID  DEST_TYPE  ARCHIVED APPLIED
---------- -------------------------------------------------------------- -------  ---------- -------- --------
 23748      +FRA/TESTCDB/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10041.9   1      Local        YES       NO
 23748      +DATA/TESTCDB/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10062.   2      Local        YES       NO
 23748      TESTSTB                                                         3      Standby      YES       NO

3 rows selected.


 

DG BROKER COMMANDS

 

How to configure Data Guard broker:

1.Start the DMON process on both the primary and standby databases:

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.

2.Set the log_archive_dest_2 settings from both the Primary and Standby databases
to be nothing , then try to create the broker configuration (it will automatically 
set the log_archive_dest_n when you'll add a database to the configuration)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';
System altered.

Connect DGMGRL on the primary DB and create the configuration 

[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/test
Connected as SYSDG.

DGMGRL> CREATE CONFIGURATION 'TEST' AS PRIMARY DATABASE IS 'DB12C' CONNECT IDENTIFIER IS DB12C;
Configuration "TEST" created with primary database "DB12C"

Next add a standby database to the Data Guard broker configuration:

DGMGRL> ADD DATABASE 'db12c_stby' AS CONNECT IDENTIFIER IS 'db12c_stby';
Database "db12c" added

Enable dataguard broker configuration 

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - TEST

 Protection Mode: MaxPerformance
 Members:
 db12c - Primary database
 DB12C_STBY - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 40 seconds ago)

 

To remove DG broker configuration:

DGMGRL> remove configuration;
Removed configuration

Rename the database name in the Data Guard broker as follows:

DGMGRL> edit database 'db12c_stby' rename to 'STBY';

To turn off redo transport to all remote destinations on the primary database:

 DGMGRL> edit database 'DB12C' SET STATE="LOG-TRANSPORT-OFF";

To stop and start redo transport services to specific standby databases:

DGMGRL> edit database 'db12c_stby' SET PROPERTY 'LogShipping'='OFF';
Property "LogShipping" updated
DGMGRL> SHOW DATABASE 'db12c_stby' 'LogShipping';
 LogShipping = 'OFF'
DGMGRL> edit database 'db12c_stby' SET PROPERTY 'LogShipping'='ON';
Property "LogShipping" updated
DGMGRL> SHOW DATABASE 'db12c_stby' 'LogShipping';
 LogShipping = 'ON'

To change the state of the standby database to read-only and back APPLY-ON:

DGMGRL> EDIT DATABASE 'db12c' SET STATE='READ-ONLY';
Succeeded.
DGMGRL> show database db12c

Database - db12c

 Role: PHYSICAL STANDBY
 Intended State: READ-ONLY
<<OUTPUT TRIMMED>>
Database Status:
SUCCESS

To change back:

DGMGRL> shutdown 
DGMGRL> startup mount;
DGMGRL> show database db12c

Database - db12c
 Role: PHYSICAL STANDBY
 Intended State: OFFLINE
  <<OUTPUT TRIMMED>>

DGMGRL> EDIT DATABASE DB12C SET STATE = APPLY-ON;
Succeeded.
DGMGRL> show database db12c

Database - db12c

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 <<OUTPUT TRIMMED>>

 

 



LOGICAL STANDBY COMMANDS

To Restart SQL apply on logical standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

To Stop SQL apply on logical standby

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Run the following SQL against the logical standby to start real-time SQL apply if the SQL apply failed with an error, and you are 100% certain that the transaction is safe to skip

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE SKIP FAILED TRANSACTION;

To see unsupported tables for logical standby:

SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE ORDER BY OWNER, TABLE_NAME;

To know which archive log sequences are at what stage for logical standby?

SQL> SELECT 'RESTART' "TYPE",
 P.RESTART_SCN "SCN",
 TO_CHAR (P.RESTART_TIME, 'yyyy/mm/dd hh24:mi:ss') "TIME",
 L.SEQUENCE# "SEQ#"
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.RESTART_SCN >= L.FIRST_CHANGE# AND P.RESTART_SCN < L.NEXT_CHANGE#
UNION
SELECT 'RESTART',
 P.RESTART_SCN,
 TO_CHAR (P.RESTART_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.RESTART_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#
UNION
SELECT 'APPLIED',
 P.APPLIED_SCN,
 TO_CHAR (P.APPLIED_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.APPLIED_SCN >= L.FIRST_CHANGE# AND P.APPLIED_SCN < L.NEXT_CHANGE#
UNION
SELECT 'APPLIED',
 P.APPLIED_SCN,
 TO_CHAR (P.APPLIED_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.APPLIED_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#
UNION
SELECT 'MINING',
 P.MINING_SCN,
 TO_CHAR (P.MINING_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.MINING_SCN >= L.FIRST_CHANGE# AND P.MINING_SCN < L.NEXT_CHANGE#
UNION
SELECT 'MINING',
 P.MINING_SCN,
 TO_CHAR (P.MINING_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.MINING_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#
UNION
SELECT 'SHIPPED',
 P.LATEST_SCN,
 TO_CHAR (P.LATEST_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.LATEST_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN < L.NEXT_CHANGE#
UNION
SELECT 'SHIPPED',
 P.LATEST_SCN,
 TO_CHAR (P.LATEST_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.LATEST_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#;

To know is the SQL Apply up to date

SQL> SELECT TO_CHAR(LATEST_TIME,'yyyy/mm/dd hh24:mi:ss') "LATEST_TIME", 
TO_CHAR(APPLIED_TIME,'yyyy/mm/dd hh24:mi:ss') "APPLIED_TIME", 
APPLIED_SCN, LATEST_SCN 
FROM V$LOGSTDBY_PROGRESS;

To know is the Logical standby applying changes? Run the following SQL against the Logical standby database:

SQL> SELECT REALTIME_APPLY, STATE FROM V$LOGSTDBY_STATE;

If the value of STATE is “NULL” or “SQL APPLY NOT ON” then the Sql Apply is not running.The value of REALTIME_APPLY should be Y to allow for real time apply from the standby redo logs. To know what major Sql Apply events have occurred, run the following SQL against the Logical standby database:

SQL> SELECT TO_CHAR (EVENT_TIME, 'YYYY/MM/DD HH24:MI:SS') "EVENT_TIME",
STATUS, EVENT
FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIME;

To know what major Dataguard events have occurred, run the following SQL against the Logical standby database:

SQL> SELECT TO_CHAR (TIMESTAMP, 'yyyy/mm/dd hh24:mi:ss') "TIME",
ERROR_CODE "ERROR", DEST_ID "DEST", MESSAGE
FROM V$DATAGUARD_STATUS
WHERE timestamp > TRUNC (SYSDATE + 6 / 24)
ORDER BY timestamp DESC;

 

To know where are the archive logs going and are there any achieving issues, run the following SQL against either the logical standby or primary database:

SQL> SELECT DEST_ID "DID",
STATUS, DESTINATION, ARCHIVER, VALID_NOW, VALID_TYPE, VALID_ROLE, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS <> 'INACTIVE';


 

How to automatically copy archivelogs from ASM to local disc


This script will create folder on local disk, copy archivelogs of yesterday to this folder and compress folder with zip

#!/bin/bash
. /home/oracle/.bash_profile
cd /home/oracle/backup/rman/archivelogs/
mkdir `date -d "1 day ago" "+%Y_%m_%d"`
. /home/oracle/grid_env
for i in $(asmcmd ls +FRA/TESTDB/ARCHIVELOG/`date -d "1 day ago" "+%Y_%m_%d"`);
do asmcmd cp +FRA/TESTDB/ARCHIVELOG/`date -d "1 day ago" "+%Y_%m_%d"`/$i /home/oracle/backup/rman/archivelogs/`date -d "1 day ago" "+%Y_%m_%d"`;
done
zip -r `date -d "1 day ago" "+%Y_%m_%d"`.zip `date -d "1 day ago" "+%Y_%m_%d"`
rm -r `date -d "1 day ago" "+%Y_%m_%d"`

Restore the RMAN backup of a Oracle 12c RAC to a non ASM Single Instance database


We have a RMAN full backup Oracle 12c RAC ( 1 CDB +3 PDB )  and we want to restore it to a non ASM single instance database.

First take database full backup +(archive log, controlfile, spfile), then copy backups and parameter file of the RAC database to the destination server. Edit copied parameter file to create parameter file for the single instsance database like above:

TESTCDB.__db_cache_size=12569803776
TESTCDB.__java_pool_size=459524096
TESTCDB.__large_pool_size=1081701376
TESTCDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTCDB.__pga_aggregate_target=12038239232
TESTCDB.__sga_target=19131100672
TESTCDB.__shared_io_pool_size=266870912
TESTCDB.__shared_pool_size=5100223320
TESTCDB.__streams_pool_size=128435456
*.audit_file_dest='/u01/app/oracle/admin/TESTCDB/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/TESTCDB/CONTROLFILE/control10.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/TESTCDB/ONLINELOG','/u01/app/oracle/oradata'
*.db_name='TESTCDB'
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.db_recovery_file_dest_size=100G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTCDBXDB)'
*.enable_pluggable_database=true
*.log_archive_format='arc_%t_%s_%r.dbf'
*.memory_target=45G
*.open_cursors=300
*.processes=900
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.undo_tablespace='UNDOTBS1'

If we restore backup to the server which have a old restored copy of the database, then we must drop database and remove all data on Flash Recovery Area:

SQL> shut abort;
ORACLE instance shut down.

SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.3891E+10 bytes
Database Buffers     2.4293E+10 bytes
Redo Buffers          128917504 bytes
Database mounted.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@testdb4 bkp]$ cd /u01/app/oracle/fra/
[oracle@testdb4 fra]$ ll
total 4
drwxr-x---. 4 oracle oinstall 4096 Apr 20 19:11 TESTCDB
[oracle@testdb4 fra]$ rm -r *

 

Start the database with edited parameter file in nomount state:

SQL> startup nomount pfile=/u01/initTESTCDB.ora;
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.6307E+10 bytes
Database Buffers     2.1877E+10 bytes
Redo Buffers          128917504 bytes
SQL>

Create directories that where we will restore datafiles and controlfile

[oracle@TESTdb4]$ mkdir -p /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED
[oracle@TESTdb4]$ cd /u01/app/oracle/oradata/TESTCDB
[oracle@TESTdb4]$ mkdir PDB1 PDB2 PDB3 CONTROLFILE

Connect to the RMAN and restore control file:

[oracle@testdb4 oradata]$ rman target /

RMAN> set decryption identified by 'yourpassword';
executing command: SET decryption
using target database control file instead of recovery catalog

RMAN> restore controlfile from '/home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp';

Starting restore at 20-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=948 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TESTCDB/CONTROLFILE/control10.ctl
Finished restore at 20-APR-16

Then create spfile and pfile from our init.ora file , startup mount the database and catalog the copied  backups .:

RMAN> create spfile from pfile='/u01/initTESTCDB.ora';             
Statement processed
RMAN> create pfile from spfile;
Statement processed

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


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

Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.6307E+10 bytes
Database Buffers     2.1877E+10 bytes
Redo Buffers          128917504 bytes
Database mounted.
SQL>

SQL>
[oracle@TEST4 fra]$ rman target /
RMAN>  catalog start with '/home/oracle/backup/rman/bkp2';


Starting implicit crosscheck backup at 20-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=948 device type=DISK
Crosschecked 24 objects
Finished implicit crosscheck backup at 20-APR-16

Starting implicit crosscheck copy at 20-APR-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-APR-16

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/backup/rman/bkp2

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_20r3h7q8_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_arch_22r3h7r4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1sr3h7gg_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_spfile_24r3h7rh_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1tr3h7ma_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1vr3h7p4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1ur3h7o1_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)?
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_20r3h7q8_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_arch_22r3h7r4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1sr3h7gg_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_spfile_24r3h7rh_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1tr3h7ma_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1vr3h7p4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1ur3h7o1_1_1.bkp
RMAN>

You can generate RMAN restore scripts using queries below:

--For Daatafiles
SELECT    'set newname for datafile '
       || file#
       || ' to ''/your_new_datafile_location/'
       || SUBSTR (name, 17, LENGTH (name) - 16)
       || ''';'
  FROM v$datafile;

--For Tempfiles
SELECT    'set newname for datafile '
       || file#
       || ' to ''/your_new_tempfile_location/'
       || SUBSTR (name, 17, LENGTH (name) - 16)
       || ''';'
  FROM v$tempfile;

--For Redologs
SELECT    'sql "alter database rename file '''''
       || MEMBER
       || ''''' to '
       || '''''/u01/app/oracle/oradata/logfile_'
       || ROWNUM
       || ''''' ";'
  FROM v$logfile

Set new name for all datafiles,tempfiles and logfile and then restore them

RMAN> run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/system.299.903908697';
set newname for datafile 3 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/sysaux.285.903908663';
set newname for datafile 4 to '/u01/app/oracle/2> 3> oradata/TESTCDB/TESTCDB/DATAFILE/undotbs1.281.903908743';
set newname for datafile 5 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_system01';
set newname for datafile 6 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/users.282.903908743'4> 5> 6> 7> ;
set newname for datafile 7 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_sysaux01';
set newname for datafile 8 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs2.293.903909187';
set newname for datafile 22 to '/u01/app/oracle/o8> 9> 10> radata/PDB1/system01.dbf';
set newname for datafile 23 to '/u01/app/oracle/oradata/PDB1/sysaux01.dbf';
set newname for datafile 24 to '/u01/app/oracle/oradata/PDB1/PDB1_users01.dbf';
set newname for datafile 25 to '/u01/app/oracle/oradata/PDB1/tbs_inz11> 12> 13> 02.dbf';
set newname for datafile 26 to '/u01/app/oracle/oradata/PDB1/tbs_inz03.dbf';
set newname for datafile 27 to '/u01/app/oracle/oradata/PDB1/tbs_inz01.dbf';
set newname for datafile 34 to '/u01/app/oracle/oradata/PDB2/system01.dbf';
set newname for14> 15> 16> 17>  datafile 35 to '/u01/app/oracle/oradata/PDB2/sysaux01.dbf';
set newname for datafile 36 to '/u01/app/oracle/oradata/PDB2/PDB2_users01.dbf';
set newname for datafile 37 to '/u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf';
set newname for datafile 38 to '/u01/18> 19> 20> app/oracle/oradata/PDB2/tbs_PDB202.dbf';
set newname for datafile 39 to '/u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf';
set newname for datafile 40 to '/u01/app/oracle/oradata/PDB3/system01.dbf';
set newname for datafile 41 to '/u01/app/oracle/oradata/PDB3/sy21> 22> 23> saux01.dbf';
set newname for datafile 42 to '/u01/app/oracle/oradata/PDB3/PDB3_users01.dbf';
set newname for datafile 43 to '/u01/app/oracle/oradata/PDB3/tbs_archive05.dbf';
set newname for datafile 44 to '/u01/app/oracle/oradata/PDB3/tbs_archive04.dbf';
set n24> 25> 26> 27> ewname for datafile 45 to '/u01/app/oracle/oradata/PDB3/tbs_archive03.dbf';
set newname for datafile 46 to '/u01/app/oracle/oradata/PDB3/tbs_archive02.dbf';
set newname for datafile 47 to '/u01/app/oracle/oradata/PDB3/tbs_archive01.dbf';
set newname for dataf28> 29> 30> ile 48 to '/u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf';
set newname for datafile 49 to '/u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf';
set newname for datafile 50 to '/u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf';
set newname for tempfile 3 to '/u01/app/oracle/or31> 32> 33> adata/PDB3/temp012016-02-06_03-13-05-pm.dbf';
set newname for tempfile 4 to '/u01/app/oracle/oradata/PDB1/temp012016-02-06_03-13-05-pm.dbf';
set newname for tempfile 6 to '/u01/app/oracle/oradata/PDB2/temp022016-02-20_03-13-05-pm.dbf';
set newname for temp34> 35> 36> file 7 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/cdb_temp01';
set newname for tempfile 8 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_temp01';
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_2.284.903908803'' to '37> 38> '/u01/app/oracle/oradata/logfile_1'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_2.258.903908803'' to ''/u01/app/oracle/oradata/logfile_2'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_1.283.903908803'' to ''39> 40> /u01/app/oracle/oradata/logfile_3'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_1.259.903908803'' to ''/u01/app/oracle/oradata/logfile_4'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_5.1266.904278355'' to ''41> 42> /u01/app/oracle/oradata/logfile_5'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_3.286.903909317'' to ''/u01/app/oracle/oradata/logfile_6'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_3.257.903909317'' to ''/43> 44> u01/app/oracle/oradata/logfile_7'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_4.300.903909317'' to ''/u01/app/oracle/oradata/logfile_8'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_4.256.903909317'' to ''/u45> 46> 01/app/oracle/oradata/logfile_9'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_5.265.904278355'' to ''/u01/app/oracle/oradata/logfile_10'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_6.1264.904278355'' to ''/47> 48> u01/app/oracle/oradata/logfile_11'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_6.264.904278355'' to ''/u01/app/oracle/oradata/logfile_12'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_7.1263.904278357'' to '49> 50> '/u01/app/oracle/oradata/logfile_13'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_7.263.904278357'' to ''/u01/app/oracle/oradata/logfile_14'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_8.1262.904278357'' to51> 52>  ''/u01/app/oracle/oradata/logfile_15'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_8.262.904278357'' to ''/u01/app/oracle/oradata/logfile_16'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_9.1261.904278357'' 53> 54> to ''/u01/app/oracle/oradata/logfile_17'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_9.261.904278357'' to ''/u01/app/oracle/oradata/logfile_18'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_10.1260.90427839355> 56> '' to ''/u01/app/oracle/oradata/logfile_19'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_10.267.904278393'' to ''/u01/app/oracle/oradata/logfile_20'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_11.1258.9042757> 58> 8393'' to ''/u01/app/oracle/oradata/logfile_21'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_11.266.904278393'' to ''/u01/app/oracle/oradata/logfile_22'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_12.1259.959> 60> 04278393'' to ''/u01/app/oracle/oradata/logfile_23'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_12.268.904278393'' to ''/u01/app/oracle/oradata/logfile_24'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_13.1261> 62> 55.904278395'' to ''/u01/app/oracle/oradata/logfile_25'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_13.269.904278395'' to ''/u01/app/oracle/oradata/logfile_26'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_163> 64> 4.1257.904278395'' to ''/u01/app/oracle/oradata/logfile_27'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_14.270.904278395'' to ''/u01/app/oracle/oradata/logfile_28'' ";
restore database;
switch datafile all;
switch tempfile all;
65> 66> 67> 68> 69> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_2.284.903908803'' to ''/u01/app/oracle/oradata/logfile_1''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_2.258.903908803'' to ''/u01/app/oracle/oradata/logfile_2''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_1.283.903908803'' to ''/u01/app/oracle/oradata/logfile_3''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_1.259.903908803'' to ''/u01/app/oracle/oradata/logfile_4''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_5.1266.904278355'' to ''/u01/app/oracle/oradata/logfile_5''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_3.286.903909317'' to ''/u01/app/oracle/oradata/logfile_6''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_3.257.903909317'' to ''/u01/app/oracle/oradata/logfile_7''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_4.300.903909317'' to ''/u01/app/oracle/oradata/logfile_8''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_4.256.903909317'' to ''/u01/app/oracle/oradata/logfile_9''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_5.265.904278355'' to ''/u01/app/oracle/oradata/logfile_10''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_6.1264.904278355'' to ''/u01/app/oracle/oradata/logfile_11''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_6.264.904278355'' to ''/u01/app/oracle/oradata/logfile_12''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_7.1263.904278357'' to ''/u01/app/oracle/oradata/logfile_13''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_7.263.904278357'' to ''/u01/app/oracle/oradata/logfile_14''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_8.1262.904278357'' to ''/u01/app/oracle/oradata/logfile_15''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_8.262.904278357'' to ''/u01/app/oracle/oradata/logfile_16''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_9.1261.904278357'' to ''/u01/app/oracle/oradata/logfile_17''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_9.261.904278357'' to ''/u01/app/oracle/oradata/logfile_18''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_10.1260.904278393'' to ''/u01/app/oracle/oradata/logfile_19''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_10.267.904278393'' to ''/u01/app/oracle/oradata/logfile_20''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_11.1258.904278393'' to ''/u01/app/oracle/oradata/logfile_21''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_11.266.904278393'' to ''/u01/app/oracle/oradata/logfile_22''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_12.1259.904278393'' to ''/u01/app/oracle/oradata/logfile_23''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_12.268.904278393'' to ''/u01/app/oracle/oradata/logfile_24''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_13.1255.904278395'' to ''/u01/app/oracle/oradata/logfile_25''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_13.269.904278395'' to ''/u01/app/oracle/oradata/logfile_26''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_14.1257.904278395'' to ''/u01/app/oracle/oradata/logfile_27''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_14.270.904278395'' to ''/u01/app/oracle/oradata/logfile_28''

Starting restore at 20-APR-16
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00040 to /u01/app/oracle/oradata/PDB3/system01.dbf
channel ORA_DISK_1: restoring datafile 00041 to /u01/app/oracle/oradata/PDB3/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00042 to /u01/app/oracle/oradata/PDB3/PDB3_users01.dbf
channel ORA_DISK_1: restoring datafile 00043 to /u01/app/oracle/oradata/PDB3/tbs_archive05.dbf
channel ORA_DISK_1: restoring datafile 00044 to /u01/app/oracle/oradata/PDB3/tbs_archive04.dbf
channel ORA_DISK_1: restoring datafile 00045 to /u01/app/oracle/oradata/PDB3/tbs_archive03.dbf
channel ORA_DISK_1: restoring datafile 00046 to /u01/app/oracle/oradata/PDB3/tbs_archive02.dbf
channel ORA_DISK_1: restoring datafile 00047 to /u01/app/oracle/oradata/PDB3/tbs_archive01.dbf
channel ORA_DISK_1: restoring datafile 00048 to /u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf
channel ORA_DISK_1: restoring datafile 00049 to /u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf
channel ORA_DISK_1: restoring datafile 00050 to /u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1sr3h7gg_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1sr3h7gg_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00034 to /u01/app/oracle/oradata/PDB2/system01.dbf
channel ORA_DISK_1: restoring datafile 00035 to /u01/app/oracle/oradata/PDB2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00036 to /u01/app/oracle/oradata/PDB2/PDB2_users01.dbf
channel ORA_DISK_1: restoring datafile 00037 to /u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf
channel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/PDB2/tbs_PDB202.dbf
channel ORA_DISK_1: restoring datafile 00039 to /u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1tr3h7ma_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1tr3h7ma_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/PDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/PDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/PDB1/PDB1_users01.dbf
channel ORA_DISK_1: restoring datafile 00025 to /u01/app/oracle/oradata/PDB1/tbs_inz02.dbf
channel ORA_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/PDB1/tbs_inz03.dbf
channel ORA_DISK_1: restoring datafile 00027 to /u01/app/oracle/oradata/PDB1/tbs_inz01.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1vr3h7p4_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1vr3h7p4_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20-APR-16

datafile 1 switched to datafile copy
input datafile copy RECID=33 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/system.299.903908697
datafile 3 switched to datafile copy
input datafile copy RECID=34 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/sysaux.285.903908663
datafile 4 switched to datafile copy
input datafile copy RECID=35 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs1.281.903908743
datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_system01
datafile 6 switched to datafile copy
input datafile copy RECID=37 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/users.282.903908743
datafile 7 switched to datafile copy
input datafile copy RECID=38 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_sysaux01
datafile 8 switched to datafile copy
input datafile copy RECID=39 STAMP=909693180 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs2.293.903909187
datafile 22 switched to datafile copy
input datafile copy RECID=40 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/system01.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=41 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/sysaux01.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=42 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/PDB1_users01.dbf
datafile 25 switched to datafile copy
input datafile copy RECID=43 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/tbs_inz02.dbf
datafile 26 switched to datafile copy
input datafile copy RECID=44 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/tbs_inz03.dbf
datafile 27 switched to datafile copy
input datafile copy RECID=45 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB1/tbs_inz01.dbf
datafile 34 switched to datafile copy
input datafile copy RECID=46 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/system01.dbf
datafile 35 switched to datafile copy
input datafile copy RECID=47 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/sysaux01.dbf
datafile 36 switched to datafile copy
input datafile copy RECID=48 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/PDB2_users01.dbf
datafile 37 switched to datafile copy
input datafile copy RECID=49 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf
datafile 38 switched to datafile copy
input datafile copy RECID=50 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB202.dbf
datafile 39 switched to datafile copy
input datafile copy RECID=51 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf
datafile 40 switched to datafile copy
input datafile copy RECID=52 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/system01.dbf
datafile 41 switched to datafile copy
input datafile copy RECID=53 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/sysaux01.dbf
datafile 42 switched to datafile copy
input datafile copy RECID=54 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/PDB3_users01.dbf
datafile 43 switched to datafile copy
input datafile copy RECID=55 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/tbs_archive05.dbf
datafile 44 switched to datafile copy
input datafile copy RECID=56 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/tbs_archive04.dbf
datafile 45 switched to datafile copy
input datafile copy RECID=57 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive03.dbf
datafile 46 switched to datafile copy
input datafile copy RECID=58 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive02.dbf
datafile 47 switched to datafile copy
input datafile copy RECID=59 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive01.dbf
datafile 48 switched to datafile copy
input datafile copy RECID=60 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf
datafile 49 switched to datafile copy
input datafile copy RECID=61 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf
datafile 50 switched to datafile copy
input datafile copy RECID=62 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf

renamed tempfile 3 to /u01/app/oracle/oradata/PDB3/temp012016-02-06_03-13-05-pm.dbf in control file
renamed tempfile 4 to /u01/app/oracle/oradata/PDB1/temp012016-02-06_03-13-05-pm.dbf in control file
renamed tempfile 6 to /u01/app/oracle/oradata/PDB2/temp022016-02-20_03-13-05-pm.dbf in control file
renamed tempfile 7 to /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/cdb_temp01 in control file
renamed tempfile 8 to /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_temp01 in control file

Recover database

RMAN> recover database;

Starting recover at 20-APR-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10275 is already on disk as file /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10272
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11124
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11125
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10273
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11126
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11127
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10274
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_arch_22r3h7r4_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_arch_22r3h7r4_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10272_ckhd9vrm_.arc thread=1 sequence=10272
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11124_ckhd9twc_.arc thread=2 sequence=11124
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11124_ckhd9twc_.arc RECID=59793 STAMP=909693691
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11125_ckhd9ttm_.arc thread=2 sequence=11125
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10272_ckhd9vrm_.arc RECID=59794 STAMP=909693692
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10273_ckhd9vwm_.arc thread=1 sequence=10273
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11125_ckhd9ttm_.arc RECID=59792 STAMP=909693691
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11126_ckhd9w8h_.arc thread=2 sequence=11126
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11126_ckhd9w8h_.arc RECID=59798 STAMP=909693693
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11127_ckhd9w9m_.arc thread=2 sequence=11127
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10273_ckhd9vwm_.arc RECID=59797 STAMP=909693692
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10274_ckhd9wbq_.arc thread=1 sequence=10274
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10274_ckhd9wbq_.arc RECID=59796 STAMP=909693692
archived log file name=/home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931 thread=1 sequence=10275
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11127_ckhd9w9m_.arc RECID=59795 STAMP=909693692
unable to find archived log
archived log thread=2 sequence=11128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2016 20:42:18
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11128 and starting SCN of 842116000

Copy archivelogs that recovery needs and recover database

[oracle@testdb1 2016_04_20]$ scp thread_2_seq_11128.989.909680709  192.168.1.109:/home/oracle/backup/rman/bkp2/
oracle@192.168.1.109's password:
thread_2_seq_11128.989.909680709

RMAN> recover database
2> ;

Starting recover at 20-APR-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10275 is already on disk as file /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
unable to find archived log
archived log thread=2 sequence=11128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2016 20:45:52
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11128 and starting SCN of 842116000

Open database with resetlogs

RMAN> alter database open resetlogs;

Statement processed

RMAN>

SQL> show pdbs;

CON_ID 	CON_NAME OPEN MODE	RESTRICTED
------ --------- --------- ---------- 	
2 		PDB$SEED  READ ONLY	 NO
3 		PDB1      MOUNTED
4 		PDB2 	  MOUNTED
5 		PDB3      MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

You can also use set new name only for database if you dont want place all files to different directories like in source:

RMAN> run
{
set newname for database to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/%U';
set newname for tempfile 1 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/%U';
restore database;
switch datafile all;
switch tempfile all;
}

Useful Oracle RAC Commands


Shutdown and Start sequence of Oracle RAC 

 

STOP ORACLE RAC (11g)
1. emctl stop dbconsole
2. srvctl stop listener -n racnode1
3. srvctl stop database -d RACDB
4. srvctl stop asm -n racnode1 -f
5. srvctl stop asm -n racnode2 -f
6. srvctl stop nodeapps -n racnode1 -f
7. crsctl stop crs

START ORACLE RAC (11g)
1. crsctl start crs
2. crsctl start res ora.crsd -init
3. srvctl start nodeapps -n racnode1
4. srvctl start nodeapps -n racnode2
5. srvctl start asm -n racnode1
6. srvctl start asm -n racnode2
7. srvctl start database -d RACDB
8. srvctl start listener -n racnode1
9. emctl start dbconsole

 

To start and stop oracle clusterware (run as the superuser) :

[root@node1 ~]# crsctl stop crs

[root@node1 ~]# crsctl start crs

 

To start and stop oracle cluster resources running on all nodes :

[root@node1 ~]#  crsctl stop cluster -all

[root@node1 ~]#  crsctl start cluster -all

 

To check the current status of a cluster :

[oracle@node1~]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

 

To check the current status of CRS :

[oracle@node1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

 

To display the status cluster resources :

[oracle@node1 ~]$ crsctl stat res -t

 

To check version of  Oracle Clusterware :

[oracle@node1 ~]$ crsctl query crs softwareversion
Oracle Clusterware version on node [node1] is [11.2.0.4.0]
[oracle@node1 ~]$ 
[oracle@node1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]
[oracle@node1 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]

 

To check current status of OHASD (Oracle High Availability Services) daemon :

[oracle@node1 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is onli

 

Forcefully deleting resource :

[oracle@node1 ~]$ crsctl delete resource testresource -f

 

Enabling and disabling CRS daemons (run as the superuser) :

[root@node1 ~]# crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@node1 ~]# 
[root@node1 ~]# crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.

 

To check the status of Oracle CRS :

[oracle@node1 ~]$ olsnodes
node1
node2

 

To print node name with node number :

[oracle@node1 ~]$ olsnodes -n
node1	1
node2	2

 

To print private interconnect address for the local node :

[oracle@node1 ~]$ olsnodes -l -p
node1	192.168.1.101

 

To print virtual IP address with node name :

[oracle@node1 ~]$ olsnodes -i
node1	node1-vip
node2	node2-vip
[oracle@node1 ~]$ olsnodes -i node1
node1	node1-vip

 

To print information for the local node :

[oracle@node1 ~]$ olsnodes -l
node1
pl

 

To print node status (active or inactive) :

[oracle@node1 ~]$ olsnodes -s
node1	Active
node2	Active
[oracle@node1 ~]$ olsnodes -l -s
node1	Active

 

To print node type (pinned or unpinned) :

[oracle@node1 ~]$ olsnodes -t
node1	Unpinned
node2	Unpinned
[oracle@node1 ~]$ olsnodes -l -t
node1	Unpinned

 

To print clusterware name :

[oracle@node1 ~]$ olsnodes -c
rac-scan

 

To display global public and global cluster_interconnect :

[oracle@node1 ~]$ oifcfg getif
eth0  192.168.100.0  global  public
eth1  192.168.1.0  global  cluster_interconnect

 

To display the database registered in the repository :

[oracle@gpp4 ~]$ srvctl config database
TESTRACDB

 

To display the configuration details of the database :

[oracle@TEST4 ~]$ srvctl config database -d TESTRACDB
Database unique name: TESTRACDB
Database name: TESTRACDB
Oracle home: /home/oracle/product/11.2.0/db_home1
Oracle user: oracle
Spfile: +DATA/TESTRACDB/spfileTESTRACDB.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTRACDB
Database instances: TESTRACDB1,TESTRACDB2
Disk Groups: DATA,ARCH
Mount point paths: 
Services: SRV_TESTRACDB
Type: RAC
Database is administrator managed

 

To change  policy of database from automatic to manual :

[oracle@TEST4 ~]$ srvctl modify database -d TESTRACDB -y MANUAL

 

To change  the startup option of database from open to mount :

[oracle@TEST4 ~]$ srvctl modify database -d TESTDB -s mount

 

To start RAC listener :

[oracle@TEST4 ~]$ srvctl start listener

 

To display the status of the database :

[oracle@TEST4 ~]$ srvctl status database -d TESTRACDB
Instance TESTRACDB1 is running on node TEST4
Instance TESTRACDB2 is running on node TEST5

 

To display the status services running in the database :

[oracle@TEST4 ~]$ srvctl status service -d TESTRACDB
Service SRV_TESTRACDB is running on instance(s) TESTRACDB1,TESTRACDB2

 

To check nodeapps running on a node :

[oracle@TEST4 ~]$ srvctl status nodeapps
VIP TEST4-vip is enabled
VIP TEST4-vip is running on node: TEST4
VIP TEST5-vip is enabled
VIP TEST5-vip is running on node: TEST5
Network is enabled
Network is running on node: TEST4
Network is running on node: TEST5
GSD is enabled
GSD is not running on node: TEST4
GSD is not running on node: TEST5
ONS is enabled
ONS daemon is running on node: TEST4
ONS daemon is running on node: TEST5
 

 
[oracle@TEST4 ~]$  srvctl status nodeapps -n TEST4
VIP TEST4-vip is enabled
VIP TEST4-vip is running on node: TEST4
Network is enabled
Network is running on node: TEST4
GSD is enabled
GSD is not running on node: TEST4
ONS is enabled
ONS daemon is running on node: TEST4

 

To start or stop all instances associated with a database. This command also starts services and listeners on each node :

[oracle@TEST4 ~]$ srvctl start database -d TESTRACDB

 

To shut down instances and services (listeners not stopped):

[oracle@TEST4 ~]$ srvctl stop database -d TESTRACDB

 

You can use -o option to specify startup/shutdown options.
To shutdown immediate database – srvctl stop database -d TESTRACDB -o immediate
To startup force all instances – srvctl start database -d TESTRACDB -o force
To perform normal shutdown – srvctl stop database -d TESTRACDB -i instance racnode1

To start or stop the ASM instance on racnode01 cluster node :

[oracle@TEST4 ~]$ srvctl start asm -n racnode1
[oracle@TEST4 ~]$ srvctl stop asm -n racnode1

 

To display current configuration of the SCAN VIP’s :

[oracle@test4 ~]$ srvctl config scan
SCAN name: vmtestdb.exo.local, Network: 1/192.168.5.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /vmtestdb.exo.local/192.168.5.100
SCAN VIP name: scan2, IP: /vmtestdb.exo.local/192.168.5.101
SCAN VIP name: scan3, IP: /vmtestdb.exo.local/192.168.5.102

 

Refreshing  SCAN VIP’s with new IP addresses from DNS :

[oracle@test4 ~]$ srvctl modify scan -n your-scan-name.example.com

 

To stop or start SCAN listener and the  SCAN VIP resources :

[oracle@test4 ~]$ srvctl stop scan_listener 
[oracle@test4 ~]$ srvctl start scan_listener 
[oracle@test4 ~]$ srvctl stop scan
[oracle@test4 ~]$ srvctl start scan

 

To display the status of SCAN VIP’s and SCAN listeners :

[oracle@test4 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node test4
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node test5
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node test5
 
 
[oracle@test4 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node test4
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node test5
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node test5

 

To add/remove/modify SCAN :

[oracle@test4 ~]$ srvctl add scan -n your-scan
[oracle@test4 ~]$ srvctl remove scan
[oracle@test4 ~]$ srvctl modify scan -n new-scan

 

To add/remove SCAN listener :

[oracle@test4 ~]$ srvctl add scan_listener
[oracle@test4 ~]$ srvctl remove scan_listener

 

To modify SCAN listener port :

srvctl modify scan_listener -p <port_number>
srvctl modify scan_listener -p <port_number>  (reflect changes to the current SCAN listener only)

To start the ASM instnace in mount state :

ASMCMD> startup --mount

 

To shut down ASM instance immediately(database instance must be shut down before the ASM instance is shut down) :

ASMCMD> shutdown --immediate

 

Use lsop command on ASMCMD to list ASM operations :

ASMCMD > lsop

 

To perform quick health check of OCR :

[oracle@test4 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       3304
	 Available space (kbytes) :     258816
	 ID                       : 1555543155
	 Device/File Name         :      +DATA
                                    Device/File integrity check succeeded
	 Device/File Name         :       +OCR
                                    Device/File integrity check succeeded

                                    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

 

To dump content of OCR file into an xml :

[oracle@test4 ~]$ ocrdump testdump.xml -xml

 

To add or relocate the OCR mirror file to the specified location :

[oracle@test4 ~]$ ocrconfig -replace ocrmirror ‘+TESTDG’
[oracle@test4 ~]$ ocrconfig -replace +CURRENTOCRDG -replacement +NEWOCRDG

 

To relocate existing OCR file :

[oracle@test4 ~]$ ocrconfig  -replce ocr ‘+TESTDG’

 

To add mirrod disk group for OCR :

[oracle@test4 ~]$ ocrconfig -add +TESTDG

 

To remove OCR mirror :

ocrconfig -delete +TESTDG

 

To remove the OCR or the OCR mirror :

[oracle@test4 ~]$ ocrconfig -replace ocr

[oracle@test4 ~]$ ocrconfig replace ocrmirror

 

To list ocrbackup list :

[oracle@test4 ~]$ ocrconfig -showbackup

test5     2016/04/16 17:30:29     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/backup00.ocr

test5     2016/04/16 13:30:29     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/backup01.ocr

test5     2016/04/16 09:30:28     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/backup02.ocr

test5     2016/04/15 13:30:26     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/day.ocr

test5     2016/04/08 09:30:03     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/week.ocr

 

Performs OCR backup manually :

[root@testdb1 ~]# ocrconfig -manualbackup

testdb1     2016/04/16 17:31:42     /votedisk/backup_20160416_173142.ocr     0  

 

Changes OCR autobackup directory

[root@testdb1 ~]# ocrconfig -backuploc /backups/ocr

 

To verify the integrity of all the cluster nodes:

[oracle@node1]$ cluvfy comp ocr -n all -verbose
Verifying OCR integrity 
Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations

Checking daemon liveness...

Check: Liveness for "CRS daemon"
  Node Name                             Running?                
  ------------------------------------  ------------------------
  node2                                yes                     
  node1                                yes                     
Result: Liveness check passed for "CRS daemon"

Checking OCR config file "/etc/oracle/ocr.loc"...
OCR config file "/etc/oracle/ocr.loc" check successful

Disk group for ocr location "+DATA/testdb-scan/OCRFILE/registry.255.903592771" is available on all the nodes
Disk group for ocr location "+CRS/testdb-scan/OCRFILE/registry.255.903735431" is available on all the nodes
Disk group for ocr location "+MULTIPLEX/testdb-scan/OCRFILE/registry.255.903735561" is available on all the nodes

Checking OCR backup location "/bkpdisk"
OCR backup location "/bkpdisk" check passed
Checking OCR dump functionality
OCR dump check passed

NOTE: 
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.
OCR integrity check passed
Verification of OCR integrity was successful. 

.