Jeyaseelan Blog

Dear Friends, The content in this blog are purely based on my own opinion ,it is not reflecting any of my current or previous employers materials or official documents. All my posts here are not warranted to be free of errors. Please use at your own risk and after thorough testing in your environment. If you feel that i am violating any of the company's policies or documents, kindly mail me at jeyaseelan.hi@gmail.com,I am happy to take out those content from this blog.

Showing posts with label 12c. Show all posts

Friday, 15 March 2019

OPatch lsinventory command doesnot show RAC details

Please use the below command to get the remote nodes patching details

$ORACLE_HOME/OPatch/opatchauto report -format xml -type patches -remote

===============================================================
This is applicable to all releases of OPatch.
The 12.2.0.1.13 release (and later) of OPatch, which is used for all releases 12.1.0.x and later
The 11.2.0.3.18 release (and later) of OPatch, which is used for all releases 11.2.0.1 - 11.2.0.4
Beginning with these releases of OPatch, OPatch will only support patching/listing inventory for local node of a RAC cluster. There will be no propagation to other nodes in cluster.

1. In Opatch 12.2.0.1.13 and Opatch 11.2.0.3.18, OPatch command option “-all_nodes” will be no-op and existed in -help.
If opatch command is being called with option “-all_nodes”, Opatch will print out the warning on console msg as:
""OPatch was called with -all_nodes option. The -all_nodes option is being deprecated. Please remove it while calling OPatch."
2. In future release Opatch 12.2.0.1.14 (and later) and Opatch 11.2.0.3.20 (and later) , Option “-all_nodes” will be removed from -help, user will get syntax error if specify the option.
Alternative Feature of -all_node is available for Multi-Node GI/RAC :

$ORACLE_HOME/OPatch/opatchauto report -format xml -type patches -remote
(The remote command will get a list of patches from all nodes)
===========================================================

Refer:
GI/RAC/Single Instance Announcing Deprecation of OPatch Command Option "-all_nodes" (Doc ID 2331762.1)

Thursday, 15 November 2018

sqlplus - new features ( 12c to 18c)

The following are important new sqlplus features in 12c & 18c

1)SET FEEDBACK ON SQL_ID

It will show sql_id for the sql statements we execute from sqlplus prompt

SQL> create table test_invisible_cols (emp_id number, emp_info char(20),
                                  emp_acc_no number invisible); 2
Table created.

SQL_ID: cw7wvs1x99d72

2) SET ROWLIMIT 10

it limits the number of rows returned from a sql

3) set linesize window

It will make the columns to fit into the window size

4) SET COLINVISIBLE ON

This will allow to show the invisible columns using desc

SQL> create table test_invisible_cols (emp_id number, emp_info char(20),
                                  emp_acc_no number invisible); 2
Table created.

SQL> desc test_invisible_cols
Name                                                                                                                                    Null?    Type
--------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
EMP_ID                                                                                                                                           NUMBER
EMP_INFO                                                                                                                                         CHAR(20)

SQL> SET COLINVISIBLE ON

SQL> desc test_invisible_cols
Name                                                                                                                                    Null?    Type
--------------------------------------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------------------
EMP_ID                                                                                                                                           NUMBER
EMP_INFO                                                                                                                                         CHAR(20)
EMP_ACC_NO (INVISIBLE)                                                                                                                           NUMBER
SQL>

5) sqlplus -nologintime

from 12.2 sqlplus connection will show the last_login ( dba_users has additional column last_login).

if we want to disable the last login time show, we can use -nologintime

6) set history on - Another important feature is sqlplus history
     - we can list the history
     - we can run the command out of the history list
    - we can edit a command from history
- we can delete all/a command from history
( hist clear/ hist 1 delete )

SQL> set history on

SQL> set history on
SQL> select count(*) from dba_objects;
COUNT(*)
----------
     60537
SQL> show sga
Total System Global Area 3.9278E+10 bytes
Fixed Size                 30153104 bytes
Variable Size            7381975040 bytes
Database Buffers         3.1675E+10 bytes
Redo Buffers              190418944 bytes

SQL> show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FLASH01
db_recovery_file_dest_size           big integer 17793M
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

SQL> history
1 select count(*) from dba_objects;
2 show sga
3 show parameter recovery

SQL> history 2 run
Total System Global Area 3.9278E+10 bytes
Fixed Size                 30153104 bytes
Variable Size            7381975040 bytes
Database Buffers         3.1675E+10 bytes
Redo Buffers              190418944 bytes
SQL>

SQL> help history
HISTORY
-------
Stores, lists, executes, edits of the commands
entered during the current SQL*Plus session.
HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]
N is the entry number listed in the history list.
Use this number to recall, edit or delete the command.
Example:
HIST 3 RUN - will run the 3rd entry from the list.
HIST[ORY] without any option will list all entries in the list.

SQL>

Friday, 25 May 2018

Hot Cloning of PDB - 12c2 / 18c

12C has brought the container & Pluggable database feature. To clone a PDB from another clone, we need to keep the source PDB in read only mode.

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'
2 ;
no rows selected

SQL> create pluggable database DEVPDB4 from DEVPDB3;
create pluggable database DEVPDB4 from DEVPDB3
*
ERROR at line 1:
ORA-65035: unable to create pluggable database from DEVPDB3

From 12cr2 onwards we can perform this operation while source PDB is in read write mode
In-order to achieve this, we should have the following conditions met.

    1. Enabled local Undo
    2. cloneddb=true ( init.ora parameter)

Enable Local Undo
   This need to be done in upgrade mode

# stop DB
srvctl stop database -d db1

# set cluster_database = false ( it you are doing on RAC)
sqlplus /nolog
conn /as sysdba
alter system set cluster_database=false scope=spfile;
shutdown immediate;

# enable local undo
sqlplus /nolog
conn / as sysdba
startup upgrade
ALTER DATABASE LOCAL UNDO ON;
shutdown immediate;
exit

# enable cluster_database=true ( if you are peforming this activity in RAC)
sqlplus /nolog
conn /as sysdba
alter system set cluster_database=true scope=spfile;
shutdown immediate;

# start db
srvctl start database -d db1

set clonedb=true

alter system set cloned=true scope=spfile sid='*' ;

# bounce the db to effect the above parameter

# Verify if local undo is enabled

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE

Hot Clone PDB

# Source PDB in read write mode

1* select name,open_mode from v$pdbs where name='DEVPDB3'
SQL> /
NAME                           OPEN_MODE
------------------------------ ----------------------------------------
DEVPDB3                        READ WRITE

SQL> create pluggable database DEVPDB4 from DEVPDB3;
Pluggable database created.
SQL>

MIRA - Multi Instance Redo Apply - 12cR2 onwards/18C

With Oracle 12c Release 2, We can enable Multi Instance Redo apply ( MIRA) option in the DataGuard which will apply redo logs to multiple instance at the same time to improve recovery time objective.

Once we enabled, we could see there will be pr* process trees in both instances and pr** trace files on both nodes (ps -ef|grep pr*)

Please note that there will be 1 MRP0 for the entire MIRA recovery which is normal. MRP0 is the coordinator process for all other pr* recovery processes on all instances.

This can be enabled either using sqlplus / dgmgrl

1. How to enable using sql plus

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL -> to start from all the instances

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2 -> to start from any two instances

2. How to enable using DGMGRL

Change value for ApplyInstances = number of instances (set value to 2 in this case)

DGMGRL> edit database "18CDB" set property ApplyInstances=2;

Property "applyinstances" updated

DGMGRL> show configuration

Alert.log

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2

2018-05-25T07:22:10.126643+01:00

Attempt to start background Managed Standby Recovery process (18CDB1)

Starting background process MRP0

2018-05-25T07:22:10.144066+01:00

MRP0 started with pid=47, OS id=343863

2018-05-25T07:22:10.145157+01:00

Background Managed Standby Recovery process started (18CDB1)

2018-05-25T07:22:15.302656+01:00

Started logmerger process on instance id 1

Started logmerger process on instance id 2

Starting Multi Instance Redo Apply (MIRA) on 2 instances

2018-05-25T07:22:15.354896+01:00

Starting Multi Instance Redo Apply (MIRA)

2018-05-25T07:22:15.459146+01:00

.... (PID:399563): Managed Standby Recovery starting Real Time Apply

2018-05-25T07:22:16.289661+01:00

Started 24 apply slaves on instance id 1 =========> apply slaves started on instance 1

2018-05-25T07:22:16.818633+01:00

Started 24 apply slaves on instance id 2 =========> apply slaves started on instance 2

2018-05-25T07:22:17.515862+01:00

ALTER SYSTEM SET remote_listener=' ahbdre1-sc.des.sg.com:1722' SCOPE=MEMORY SID='18CDB1';

2018-05-25T07:22:17.516825+01:00

ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='18CDB1';

3. Background processes => once we enabled we could see the following background process

SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.2.0.0.0

[18CDB1] $ ps -ef | grep GW | grep pr

oracle 323507 1 1 07:11 ? 00:00:01 ora_pr00_18CDB1

oracle 323512 1 0 07:11 ? 00:00:00 ora_pr02_18CDB1

oracle 323514 1 0 07:11 ? 00:00:00 ora_pr03_18CDB1

oracle 323516 1 0 07:11 ? 00:00:00 ora_pr04_18CDB1

oracle 323518 1 0 07:11 ? 00:00:00 ora_pr05_18CDB1

oracle 323520 1 0 07:11 ? 00:00:00 ora_pr06_18CDB1

oracle 323522 1 0 07:11 ? 00:00:00 ora_pr07_18CDB1

oracle 323534 1 0 07:11 ? 00:00:00 ora_pr08_18CDB1

oracle 323544 1 0 07:11 ? 00:00:00 ora_pr09_18CDB1

oracle 323546 1 0 07:11 ? 00:00:00 ora_pr0a_18CDB1

oracle 323548 1 0 07:11 ? 00:00:00 ora_pr0b_18CDB1

oracle 323550 1 0 07:11 ? 00:00:00 ora_pr0c_18CDB1

oracle 323552 1 0 07:11 ? 00:00:00 ora_pr0d_18CDB1

oracle 323554 1 0 07:11 ? 00:00:00 ora_pr0e_18CDB1

oracle 323556 1 0 07:11 ? 00:00:00 ora_pr0f_18CDB1

oracle 323558 1 0 07:11 ? 00:00:00 ora_pr0g_18CDB1

oracle 323560 1 0 07:11 ? 00:00:00 ora_pr0h_18CDB1

oracle 323562 1 0 07:11 ? 00:00:00 ora_pr0i_18CDB1

oracle 323564 1 0 07:11 ? 00:00:00 ora_pr0j_18CDB1

oracle 323566 1 0 07:11 ? 00:00:00 ora_pr0k_18CDB1

oracle 323568 1 0 07:11 ? 00:00:00 ora_pr0l_18CDB1

oracle 323570 1 0 07:11 ? 00:00:00 ora_pr0m_18CDB1

oracle 323572 1 0 07:11 ? 00:00:00 ora_pr0n_18CDB1

oracle 323574 1 0 07:11 ? 00:00:00 ora_pr0o_18CDB1

oracle 323576 1 0 07:11 ? 00:00:00 ora_pr0p_18CDB1

oracle 323582 1 0 07:11 ? 00:00:00 ora_pr1e_18CDB1

oracle 323584 1 0 07:11 ? 00:00:00 ora_pr1f_18CDB1

oracle 323586 1 0 07:11 ? 00:00:00 ora_pr1g_18CDB1

oracle 323588 1 0 07:11 ? 00:00:00 ora_pr1i_18CDB1

oracle 323590 1 0 07:11 ? 00:00:00 ora_pr1l_18CDB1

oracle 323592 1 0 07:11 ? 00:00:00 ora_pr1n_18CDB1

4. Managed standby processes => there will be only one MRP process

1* select inst_id,PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS from gv$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#

SQL> /

INST_ID PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

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

1 ARCH CONNECTED ARCH 399744 0 0 0 0 0

1 ARCH CONNECTED ARCH 399776 0 0 0 0 0

1 ARCH CONNECTED ARCH 399774 0 0 0 0 0

1 ARCH CONNECTED ARCH 399770 0 0 0 0 0

1 ARCH CONNECTED ARCH 399768 0 0 0 0 0

1 ARCH CONNECTED ARCH 399766 0 0 0 0 0

1 ARCH CONNECTED ARCH 399764 0 0 0 0 0

1 ARCH CONNECTED ARCH 399750 0 0 0 0 0

1 ARCH CONNECTED ARCH 399754 0 0 0 0 0

1 ARCH CONNECTED ARCH 399756 0 0 0 0 0

1 ARCH CONNECTED ARCH 399758 0 0 0 0 0

INST_ID PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

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

1 ARCH CONNECTED ARCH 399760 0 0 0 0 0

2 ARCH CLOSING ARCH 117678 1 2242 10240 0 0

2 ARCH CLOSING ARCH 117670 1 2243 1 0 0

2 ARCH CLOSING ARCH 117690 1 2244 1 0 0

2 ARCH CLOSING ARCH 117684 1 2245 1 0 0

2 ARCH CLOSING ARCH 117680 1 2246 10240 0 0

2 ARCH CLOSING ARCH 117674 1 2247 1 0 0

2 ARCH CLOSING ARCH 117686 2 2215 45056 0 0

2 ARCH CLOSING ARCH 117664 2 2218 4096 0 0

2 ARCH CLOSING ARCH 117668 2 2237 1 0 0

2 ARCH CLOSING ARCH 117688 2 2238 1 0 0

INST_ID PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

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

2 ARCH CLOSING ARCH 117682 2 2239 10240 0 0

2 ARCH CLOSING ARCH 117676 2 2240 1 0 0

2 RFS IDLE Archival 222920 1 0 0 0 0

2 RFS IDLE Archival 67323 2 0 0 0 0

2 RFS IDLE LGWR 222400 1 2248 136 0 0

2 RFS IDLE LGWR 67057 2 2241 180 0 0

1 DGRD ALLOCATED N/A N/A 0 0 0 0 0

2 DGRD ALLOCATED N/A N/A 0 0 0 0 0

1 DGRD ALLOCATED N/A N/A 0 0 0 0 0

1 MRP0 APPLYING_LOG N/A N/A 1 2248 135 62 62

33 rows selected.

Tuesday, 1 May 2018

Point in time recovery of one PDB alone in a oracle 12c container database ( CDB)

1) A CDB Contains two PDBs.

SQL> select name,OPEN_MODE from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PLUGDB1 READ WRITE

PLUGDB2 READ WRITE

2) Backup the CDB

CDBJEY@oracle@exadata0103 [UAT ACTIVE]:/app/oracle

$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 12 02:29:52 2016

connected to target database: CDBJEY (DBID=642267487)

RMAN> backup database plus archivelog;

Starting backup at 12-01-2016 02:29:57

.........

Finished backup at 12-01-2016 02:30:12

RMAN>

====

3) Create a tablespace, user , capture the SCN & popuate some data in PDB1

SQL> create tablespace test_recover datafile '+FRA' size 1G;

Tablespace created.

SQL> create user jeya identified by jeya123;

User created.

SQL> grant create session, create table,unlimited tablespace to jeya;

Grant succeeded.

SQL> create table jeya.recover_table(name varchar2(10)) tablespace test_recover;

Table created.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

1993433

SQL> begin

for i in 1.. 10000 loop

insert into jeya.recover_table values ('hari');

end loop;

commit;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

1993523

4.Create a tablespace, user , capture the SCN & popuate some data in PDB2

SQL> alter session set container=PLUGDB2;

Session altered.

SQL> create tablespace retain datafile '+FRA' size 1g;

Tablespace created.

SQL> create user jeya_pdb2 identified by jeya123;

User created.

SQL> grant create session, create table,unlimited tablespace to jeya_pdb2;

Grant succeeded.

SQL> create table jeya_pdb2.retain_tab( name varchar2(20)) tablespace retain;

Table created.

SQL> insert into jeya_pdb2.retain_tab values('hello');

1 row created.

SQL> commit;

Commit complete.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

1995814

SQL> begin

for i in 1.. 10000 loop

insert into jeya_pdb2.retain_tab values ('test');

end loop;

commit;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

1995867

SQL>

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

1995814

SQL> begin

for i in 1.. 10000 loop

insert into jeya_pdb2.retain_tab values ('test');

end loop;

commit;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

1995867

5. Close the PDB2 to make it ready for PITR recover

SQL> alter pluggable database PLUGDB2 close;

Pluggable database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

6. Recover the PDB2 to SCN 1993433 ( captured in step 3)

CDBJEY@oracle@exadata0103 [UAT ACTIVE]:/app/oracle

$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 12 03:19:38 2016

connected to target database: CDBJEY (DBID=642267487)

RMAN> run {

set until SCN = 1993433 ;

restore pluggable database PLUGDB2;

recover pluggable database PLUGDB2 auxiliary destination='+DATA01';

alter pluggable database PLUGDB2 open resetlogs;

}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 12-01-2016 03:21:46

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

Finished recover at 12-01-2016 03:22:23

Statement processed

RMAN>

7. Verify if the PDB2 alone rollback to SCN 1993433 ( captured in step 3)

CDBJEY@oracle@exadata0103 [UAT ACTIVE]:/app/oracle

$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 12 03:23:38 2016

SQL> conn / as sysdba

Connected.

SQL> alter session set container=PLUGDB2;

Session altered.

SQL> select name from v$tablespace;

NAME

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

UNDOTBS1

SYSTEM

SYSAUX

TEMP

USERS

SQL> alter session set container=PLUGDB1

2 ;

Session altered.

SQL> select name from v$tablespace;

NAME

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

UNDOTBS1

SYSTEM

SYSAUX

TEMP

USERS

TEST_RECOVER

6 rows selected.

SQL> select count(*) from jeya.recover_table;

COUNT(*)

----------

10000

SQL>

Jeyaseelan Blog

Friday, 15 March 2019

OPatch lsinventory command doesnot show RAC details

Thursday, 15 November 2018

sqlplus - new features ( 12c to 18c)

Friday, 25 May 2018

Hot Cloning of PDB - 12c2 / 18c

MIRA - Multi Instance Redo Apply - 12cR2 onwards/18C

Tuesday, 1 May 2018

Point in time recovery of one PDB alone in a oracle 12c container database ( CDB)

ZFS

Public Cloud tools comparison

About Me