Oracle Database Commands and Practical Examples

Thursday, 19 December 2019

MSSQL -Database Installtion steps developer addition

STEP 1:

STEP 2:

STEP 2a :

STEP 3

STEP 4

STEP 5

STEP 6

STEP 7

STEP 8

STEP 9

STEP 10

STEP 11

STEP 12

STEP 13

STEP 14

STEP 15

STEP 16

Wednesday, 18 December 2019

RAC convert physical standby to snapshot database

[oracle@srv3 rmanbkp]$ . oraenv
ORACLE_SID = [stdrac] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv3 rmanbkp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 13 17:20:58 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER SYSTEM SET db_flashback_retention_target = 2880 SCOPE = BOTH;

System altered.

SQL> sho parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4560M
SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC MOUNTED PHYSICAL STANDBY stdrac

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 610 1
ARCH CLOSING 607 1
ARCH CONNECTED 0 0
ARCH CLOSING 609 1
RFS IDLE 640 2
RFS IDLE 0 0
RFS IDLE 611 1
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 640 2

9 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 610 1
ARCH CLOSING 607 1
ARCH CONNECTED 0 0
ARCH CLOSING 609 1
RFS IDLE 640 2
RFS IDLE 0 0
RFS IDLE 611 1
RFS IDLE 0 0

8 rows selected.

SQL> shut immediate;
ORA-01109: database not open

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

Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 566234808 bytes
Database Buffers 180355072 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> alter database convert to snapshot standby;

Database altered.

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

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED SNAPSHOT STANDBY

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC MOUNTED SNAPSHOT STANDBY stdrac

SQL> alter database open;

Database altered.

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC READ WRITE SNAPSHOT STANDBY stdrac

[oracle@srv3 rmanbkp]$ sqlplus sys/system123@stdrac as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 13 17:31:28 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC READ WRITE SNAPSHOT STANDBY stdrac

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/oradata/stdrac/system.279.1020351597
SYSTEM

/u02/oradata/stdrac/sysaux.271.1020351511
SYSAUX

/u02/oradata/stdrac/undotbs2.278.1020352203
UNDOTBS2

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/oradata/stdrac/users.272.1020351691
USERS

/u02/oradata/stdrac/idata.284.1020709157
IDATA

/u02/oradata/stdrac/undotbs1.270.1020351693
UNDOTBS1

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/oradata/stdrac/ilog.285.1020709245
ILOG

/u02/oradata/stdrac/indx.286.1020709561
INDX

/u02/oradata/stdrac/indx.291.1026913655
INDX

9 rows selected.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 574623416 bytes
Database Buffers 171966464 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
stSQL
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 574623416 bytes
Database Buffers 171966464 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> select name,open_mode,database_Role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CLOSING 623 1
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 653 2
RFS IDLE 0 0
RFS IDLE 624 1
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 653 2

9 rows selected.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CLOSING 623 1
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 653 2
RFS IDLE 0 0
RFS IDLE 624 1
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 653 2

9 rows selected.

SQL>

Patch for Bug# 21255373 for platform 12.1.0.2.0 grid

Scenario Preview:

Patch for Bug# 21255373 for platform 12.1.0.2.0 grid

While applying patch we do pre-check analyze. We found getting error as stdrac database as instance do not exists , There was service stdrac was offline and it was do not exists on clusterware services, As we remove stdrac database service error got removed.

[root@srv1 grid]# . oraenv
ORACLE_SID = [root] ? +ASM1
The Oracle base has been set to /u01/app/grid
[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 Started,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 srv1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE srv2 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE srv2 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE srv2 169.254.106.5 192.16
8.10.2,STABLE
ora.cvu
1 ONLINE ONLINE srv2 STABLE
ora.mgmtdb
1 ONLINE ONLINE srv2 Open,STABLE
ora.oc4j
1 ONLINE ONLINE srv1 STABLE
ora.rac.acsrac.svc
1 ONLINE ONLINE srv1 STABLE
ora.rac.db
1 ONLINE ONLINE srv1 Open,STABLE
2 ONLINE ONLINE srv2 Open,STABLE
ora.rac.pretaf.svc
1 ONLINE ONLINE srv1 STABLE
ora.rac.pretaf_preconnect.svc
1 ONLINE ONLINE srv2 STABLE
ora.scan1.vip
1 ONLINE ONLINE srv1 STABLE
ora.scan2.vip
1 ONLINE ONLINE srv2 STABLE
ora.scan3.vip
1 ONLINE ONLINE srv2 STABLE
ora.srv1.vip
1 ONLINE ONLINE srv1 STABLE
ora.srv2.vip
1 ONLINE ONLINE srv2 STABLE
ora.stdrac.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE STABLE
--------------------------------------------------------------------------------

[root@srv1 OPatch]# ./opatchauto apply /u01/21255373/21255373 -analyze

opatchauto must run from one of the homes specified
opatchauto returns with error code = 2
[root@srv1 OPatch]# . oraenv
ORACLE_SID = [rac1] ? +ASM1
The Oracle base has been changed from /u01/app/oracle to /u01/app/grid
[root@srv1 OPatch]# ./opatchauto apply /u01/21255373/21255373 -analyze

OPatchauto session is initiated at Wed Dec 18 14:13:36 2019

System initialization log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-12-18_02-13-41PM.log.

Following home(s) will not be included as part of current opatchauto session as it's software only RAC home:
Home path:/u01/app/12.1.0/grid, Host:srv2

Session log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/opatchauto2019-12-18_02-14-32PM.log
The id for this session is TQQV
The following RAC Databases have no instances associated with it.
stdrac

The following RAC Databases have no instances associated with it.
stdrac

The following RAC Databases have no instances associated with it.
stdrac

OPATCHAUTO-72017: No RAC instance.
OPATCHAUTO-72017: RAC Databases have no instances associated with it.
OPATCHAUTO-72017: Check system configuration.
OPatchAuto failed.

OPatchauto session completed at Wed Dec 18 14:14:42 2019
Time taken to complete the session 1 minute, 6 seconds

opatchauto failed with error code 42
[root@srv1 OPatch]#
#################################################################################
[root@srv1 grid]# srvctl remove database -d stdrac
Remove the database stdrac? (y/[n]) y
[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 Started,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 srv1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE srv2 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE srv2 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE srv2 169.254.106.5 192.16
8.10.2,STABLE
ora.cvu
1 ONLINE ONLINE srv2 STABLE
ora.mgmtdb
1 ONLINE ONLINE srv2 Open,STABLE
ora.oc4j
1 ONLINE ONLINE srv1 STABLE
ora.rac.acsrac.svc
1 ONLINE ONLINE srv1 STABLE
ora.rac.db
1 ONLINE ONLINE srv1 Open,STABLE
2 ONLINE ONLINE srv2 Open,STABLE
ora.rac.pretaf.svc
1 ONLINE ONLINE srv1 STABLE
ora.rac.pretaf_preconnect.svc
1 ONLINE ONLINE srv2 STABLE
ora.scan1.vip
1 ONLINE ONLINE srv1 STABLE
ora.scan2.vip
1 ONLINE ONLINE srv2 STABLE
ora.scan3.vip
1 ONLINE ONLINE srv2 STABLE
ora.srv1.vip
1 ONLINE ONLINE srv1 STABLE
ora.srv2.vip
1 ONLINE ONLINE srv2 STABLE
--------------------------------------------------------------------------------
[root@srv1 grid]#

[root@srv1 OPatch]# ./opatchauto apply /u01/21255373/21255373 -analyze

OPatchauto session is initiated at Wed Dec 18 15:26:32 2019

System initialization log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-12-18_03-26-37PM.log.

Session log file is /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/opatchauto2019-12-18_03-27-25PM.log
The id for this session is RJVJ

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
No step execution required.........

OPatchAuto successful.

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

Analysis for applying patches has failed:

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

==Following patches were SKIPPED:

Patch: /u01/21255373/21255373
Log: /u01/app/12.1.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-18_15-27-57PM_1.log
Reason: /u01/21255373/21255373 is not required to be applied to oracle home /u01/app/12.1.0/grid

Host:srv1
RAC Home:/u01/app/oracle/product/12.1.0/dbhome_2
Version:12.1.0.2.0
Analysis for patches has failed.

==Following patches FAILED in analysis for apply:

Patch: /u01/21255373/21255373
Log: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatchauto/core/opatch/opatch2019-12-18_15-27-58PM_1.log
Reason: Failed during Analysis: CheckConflictAgainstOracleHome Failed, [ Prerequisite Status: FAILED, Prerequisite output:
Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
21255373, 29494060

Whole composite patch Conflicts/Supersets are:

Patch : 21255373

Conflict with Composite Patch 29494060

Detail Conflicts/Supersets for each patch are:

Patch : 21255373

Confl ...

OPATCHAUTO-72053: Analysis for the patches failed.
OPATCHAUTO-72053: Command execution failed.
OPATCHAUTO-72053: Please check the summary for more details.

OPatchauto session completed at Wed Dec 18 15:28:12 2019
Time taken to complete the session 1 minute, 41 seconds
[root@srv1 OPatch]#

NOTE: Already applied patch 29494060 so it is conflicting the same with patch number 21255373.

we can rollback 29494060 patch and apply further 21255373 patch.

HOME

Thursday, 19 December 2019

MSSQL -Database Installtion steps developer addition

Wednesday, 18 December 2019

RAC convert physical standby to snapshot database

Patch for Bug# 21255373 for platform 12.1.0.2.0 grid