Oracle Database Commands and Practical Examples

Wednesday 26 June 2019

opatchauto apply -analyze 12c Grid

Grid Patch Number : 28980120

[root@srv1 28813884]# opatchauto apply -analyze

OPatchauto session is initiated at Wed Jun 26 16:29:27 2019

System initialization log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-06-26_04-29-36PM.log.

Session log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/opatchauto2019-06-26_04-30-51PM.log
The id for this session is 1Q2T

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0/dbhome_2

Patch applicability verified successfully on home /u01/app/12.1.0/grid

Patch applicability verified successfully on home /u01/app/oracle/product/12.1.0/dbhome_2

Verifying SQL patch applicability on home /u01/app/oracle/product/12.1.0/dbhome_2

SQL patch applicability verified successfully on home /u01/app/oracle/product/12.1.0/dbhome_2

OPatchAuto successful.

--------------------------------Summary--------------------------------

Analysis for applying patches has completed successfully:

Host:srv1
CRS Home:/u01/app/12.1.0/grid
Version:12.1.0.2.0

==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /u01/28980120/28813884/26983807
Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-06-26_16-31-54PM_1.log

Patch: /u01/28980120/28813884/28729169
Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-06-26_16-31-54PM_1.log

Patch: /u01/28980120/28813884/28729213
Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-06-26_16-31-54PM_1.log

Patch: /u01/28980120/28813884/28729220
Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-06-26_16-31-54PM_1.log

Host:srv1
RAC Home:/u01/app/oracle/product/12.1.0/dbhome_2
Version:12.1.0.2.0

==Following patches were SKIPPED:

Patch: /u01/28980120/28813884/26983807
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /u01/28980120/28813884/28729220
Reason: This patch is not applicable to this specified target type - "rac_database"

==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /u01/28980120/28813884/28729169
Log: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatchauto/core/opatch/opatch2019-06-26_16-31-57PM_1.log

Patch: /u01/28980120/28813884/28729213
Log: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatchauto/core/opatch/opatch2019-06-26_16-31-57PM_1.log

OPatchauto session completed at Wed Jun 26 16:39:55 2019
Time taken to complete the session 10 minutes, 29 seconds
[root@srv1 28813884]#

Change listener , mgmtlsnr on RAC 12c R1 1521 to 1522

[root@srv1 grid]# crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 STABLE
ora.DATA.dg
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 STABLE
ora.FRA.dg
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 STABLE
ora.asm
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 Started,STABLE
ora.net1.network
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 STABLE
ora.ons
ONLINE ONLINE srv1 STABLE
ONLINE ONLINE srv2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE srv2 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE srv1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE srv1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE srv1 169.254.163.239 192.
168.10.1,STABLE
ora.cvu
1 ONLINE ONLINE srv1 STABLE
ora.mgmtdb
1 ONLINE ONLINE srv1 Open,STABLE
ora.oc4j
1 ONLINE ONLINE srv1 STABLE
ora.rac.db
1 ONLINE ONLINE srv1 Open,STABLE
2 ONLINE ONLINE srv2 Open,STABLE
ora.scan1.vip
1 ONLINE ONLINE srv2 STABLE
ora.scan2.vip
1 ONLINE ONLINE srv1 STABLE
ora.scan3.vip
1 ONLINE ONLINE srv1 STABLE
ora.srv1.vip
1 ONLINE ONLINE srv1 STABLE
ora.srv2.vip
1 ONLINE ONLINE srv2 STABLE
--------------------------------------------------------------------------------

[root@srv1 grid]# srvctl status database -d rac
Instance rac1 is running on node srv1
Instance rac2 is running on node srv2

[root@srv1 grid]# srvctl config database -d rac
Database unique name: rac
Database name: rac
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/RAC/PARAMETERFILE/spfile.268.1011961131
Password file: +DATA/RAC/PASSWORD/pwdrac.256.1011959883
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: rac1,rac2
Configured nodes: srv1,srv2
Database is administrator managed
[root@srv1 grid]# pwd

[root@srv1 grid]# srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

[root@srv1 grid]# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

[root@srv1 grid]# srvctl config mgmtlsnr
Name: MGMTLSNR
Type: Management Listener
Owner: grid
Home: <CRS home>
End points: TCP:1521
Management listener is enabled.
Management listener is individually enabled on nodes:
Management listener is individually disabled on nodes:
[root@srv1 grid]# status listener -l LISTENER
status: invalid option: -l
Try `status --help' for more information.

[root@srv1 grid]# srvctl status listener -l LISTENER
Listener LISTENER is enabled
Listener LISTENER is running on node(s): srv1,srv2
[root@srv1 grid]# srvctl modify listener -l LISTENER -p 1522
[root@srv1 grid]# srvctl stop listener
[root@srv1 grid]# srvctl start listener
[root@srv1 grid]# srvctl status listener -l MGMTLSNR
Listener MGMTLSNR is enabled
Listener MGMTLSNR is running on node(s): srv1

[root@srv1 grid]# srvctl modify listener -l MGMTLSNR -p 1522
[root@srv1 grid]# srvctl stop MGMTLSNR
PRCR-1065 : Failed to stop resource ora.MGMTLSNR
CRS-2529: Unable to act on 'ora.MGMTLSNR' because that would require stopping or relocating 'ora.mgmtdb', but the force option was not specified

[root@srv1 grid]# srvctl stop MGMTDB
[root@srv1 grid]# srvctl stop MGMTLSNR
[root@srv1 grid]# srvctl start MGMTLSNR
[root@srv1 grid]# srvctl start MGMTDB

[root@srv1 grid]# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

[root@srv1 grid]# srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1522
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

[root@srv1 grid]# srvctl config mgmtlsnr
Name: MGMTLSNR
Type: Management Listener
Owner: grid
Home: <CRS home>
End points: TCP:1522
Management listener is enabled.
Management listener is individually enabled on nodes:
Management listener is individually disabled on nodes:
[root@srv1 grid]#

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

RAC READ WRITE PRIMARY

SQL> sho parameter listener

NAME TYPE VALUE

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

listener_networks string

local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=

192.168.56.82)(PORT=1521))

remote_listener string srv-scan:1522

SQL> sho parameter listener

NAME TYPE VALUE

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

listener_networks string

local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=

192.168.56.82)(PORT=1522))

remote_listener string srv-scan:1522

SQL>

Monday 24 June 2019

Online Redo log file drop and recreate

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRPATCH7/redo03.log
/u01/app/oracle/oradata/SMRPATCH7/redo02.log
/u01/app/oracle/oradata/SMRPATCH7/redo01.log

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> alter database drop logfile group 1;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SMRPATCH7 (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/SMRPATCH7/redo03.log'

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo01.log')size 50m;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 INACTIVE
3 1 CURRENT

SQL> alter database drop logfile group 2;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
3 1 CURRENT

SQL> alter database add logfile group 2('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

1 1 CURRENT

2 1 INACTIVE

3 1 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m;

alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m

ERROR at line 1:

ORA-01184: logfile group 1 already exists

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m;

alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m

ERROR at line 1:

ORA-01184: logfile group 1 already exists

SQL> alter database add logfile group 3 ('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m;

Database altered.

SQL> select member from v$logfile;

MEMBER

/u04/MasterDB/oradata/SMRPATCH7/redo03.log

/u04/MasterDB/oradata/SMRPATCH7/redo02.log

/u04/MasterDB/oradata/SMRPATCH7/redo01.log

SQL> exit

#################### 2 node RAC Example ##############################

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> select member from v$logfile;

MEMBER

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

+DATA/SMRPROD/ONLINELOG/group_1.277.1051537329

+FRA1/SMRPROD/ONLINELOG/group_1.296.1051537329

+DATA/SMRPROD/ONLINELOG/group_2.278.1051537329

+FRA1/SMRPROD/ONLINELOG/group_2.297.1051537331

+DATA/SMRPROD/ONLINELOG/group_3.287.1051537877

+FRA1/SMRPROD/ONLINELOG/group_3.298.1051537877

+DATA/SMRPROD/ONLINELOG/group_4.288.1051537877

+FRA1/SMRPROD/ONLINELOG/group_4.299.1051537877

8 rows selected.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

1 1 INACTIVE

2 1 CURRENT

3 2 INACTIVE

4 2 CURRENT

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> alter database add logfile thread 1 group 5 ('+DATA','+FRA1') size 250M,group 6 ('+DATA','+FRA1') size 250M,group 7 ('+DATA','+FRA1') size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 8 ('+DATA','+FRA1') size 250M, group 9 ('+DATA','+FRA1') size 250M,group 10 ('+DATA','+FRA1') size 250M;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

1 1 INACTIVE

2 1 CURRENT

3 2 INACTIVE

4 2 CURRENT

5 1 UNUSED

6 1 UNUSED

7 1 UNUSED

8 2 UNUSED

9 2 UNUSED

10 2 UNUSED

10 rows selected.

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

2 1 CURRENT

4 2 CURRENT

5 1 UNUSED

6 1 UNUSED

7 1 UNUSED

8 2 UNUSED

9 2 UNUSED

10 2 UNUSED

8 rows selected.

SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

5 1 ACTIVE

6 1 ACTIVE

7 1 CURRENT

8 2 CURRENT

9 2 INACTIVE

10 2 INACTIVE

6 rows selected.

SQL> select member from v$logfile;

MEMBER

+DATA/SMRPROD/ONLINELOG/group_5.290.1051801493

+FRA1/SMRPROD/ONLINELOG/group_5.327.1051801493

+DATA/SMRPROD/ONLINELOG/group_6.291.1051801493

+FRA1/SMRPROD/ONLINELOG/group_6.328.1051801493

+DATA/SMRPROD/ONLINELOG/group_7.292.1051801495

+FRA1/SMRPROD/ONLINELOG/group_7.329.1051801495

+DATA/SMRPROD/ONLINELOG/group_8.293.1051801503

+FRA1/SMRPROD/ONLINELOG/group_8.330.1051801505

+DATA/SMRPROD/ONLINELOG/group_9.294.1051801505

+FRA1/SMRPROD/ONLINELOG/group_9.331.1051801505

+DATA/SMRPROD/ONLINELOG/group_10.295.1051801505

+FRA1/SMRPROD/ONLINELOG/group_10.332.1051801507

12 rows selected.

SQL>

SQL> select BYTES/1024/1024 from v$log;

BYTES/1024/1024

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

250

6 rows selected.

SQL>

HOME

Wednesday 26 June 2019

opatchauto apply -analyze 12c Grid

Change listener , mgmtlsnr on RAC 12c R1 1521 to 1522

Monday 24 June 2019

Online Redo log file drop and recreate