Changing SYS or DB User Password in RAC Database Environment

Below are the procedures for changing SYS or DB USER password in RAC Environment.

Step-1: At first view which users password will expiry soon.

Q-1: Following query will find user expiry date.

SQL> select profile,username, to_char(expiry_date, 'DD-MM-YYYY') EXP_DATE from dba_users where username in ('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');

PROFILE                        USERNAME                       EXP_DATE

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

DEFAULT                        MGMT_VIEW                     01-08-2016

MONITORING_PROFILE    DBSNMP                           01-08-2016

DEFAULT                        SYSMAN                           01-08-2016

DEFAULT                        SYS                                  01-08-2016

DEFAULT                        SYSTEM                            01-08-2016

Q-2: Following query will profile default limit.

SQL> select profile,limit from dba_profiles where RESOURCE_NAME LIKE 'PASSWORD_LIFE_TIME';

PROFILE                        LIMIT

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

DEFAULT                        180

MONITORING_PROFILE             DEFAULT

Q-3: Following query will find password life time.

SQL> select * from user_password_limits;

RESOURCE_NAME                    LIMIT

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

FAILED_LOGIN_ATTEMPTS            10

PASSWORD_LIFE_TIME                  180

PASSWORD_REUSE_TIME               UNLIMITED

PASSWORD_REUSE_MAX                UNLIMITED

PASSWORD_VERIFY_FUNCTION     NULL

PASSWORD_LOCK_TIME                1

PASSWORD_GRACE_TIME              7

Step-2: Changing USERS password.

On each node, replace the word password with your desired password.

Task-1: On node1, do the following...

oracle@node1 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 12:43:02 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Data Mining and Real Application Testing options

SQL>

SQL> alter user SYS identified by test123 account unlock;

User altered.

SQL> alter user SYSTEM identified by test123 account unlock;

User altered.

SQL> alter user SYSMAN identified by test123 account unlock;

User altered.

SQL> alter user MGMT_VIEW identified by test123 account unlock;

User altered.

SQL> alter user DBSNMP identified by test123 account unlock;

User altered.

Task-2: On node2, do the following...

oracle@node2 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 12:43:02 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Data Mining and Real Application Testing options

SQL>

SQL> alter user SYS identified by test123 account unlock;

User altered.

SQL> alter user SYSTEM identified by test123 account unlock;

User altered.

SQL> alter user SYSMAN identified by test123 account unlock;

User altered.

SQL> alter user MGMT_VIEW identified by test123 account unlock;

User altered.

SQL> alter user DBSNMP identified by test123 account unlock;

User altered.

Resizing OR Extending an ASM Datafile in RAC

The following example shows how to Resize OR Extend a datafile to the NBLT24DATA tablespace in the +DATA ASM group.

1. Identify the datafiles for the tablespace you want to increase.

SQL> select file_name,bytes/1024/1024/1024 GB from dba_data_files where tablespace_name = 'NBLT24DATA' order by file_name;

2. Resizing OR extending the datafile on ASM.

SQL> alter database datafile '+DATA/dcpdb/datafile/nblt24data.278.914343685' resize 32g;

Tablespace altered.

Adding datafile to tablespace on ASM in RAC Environment

The following example shows how to add a datafile to the NBLT24DATA tablespace in the +DATA ASM group.

1. Find out where the current datafiles are defined. In this example the ASM group is +DATA.

SQL> select file_name,bytes/1024/1024/1024 GB from dba_data_files where tablespace_name = 'NBLT24DATA' order by file_name;

2. Add the datafile on ASM.

SQL> alter tablespace NBLT24DATA add datafile '+DATA' size 30G;

Tablespace altered.

ORA-00245: control file backup failed; target is likely on a local file system, RMAN-03009: failure of Control File and SPFILE Autobackup command, RMAN backup fail

At the time of RMAN backup, below error raise and backup fail. 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel at 07/13/2016 11:04:09

ORA-00245: control file backup failed; target is likely on a local file system

CAUSE:

The reason for the error is clear ORA-00245 - in a RAC environment the location of the Snapshot Controlfile must be on a shared location

CURRENT:

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DCPDB are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_dcpdb1.f'; # default

RMAN>

SOLUTION:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/DCPDB/snapcf_dcpdb1.f';

CURRENT:

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DCPDB are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/DCPDB/snapcf_dcpdb1.f';

RMAN>

VIEW on ASM

V$ASM_DISKGROUP displays one row for every ASM disk group discovered by the ASM instance on the node.

select * from v$asm_diskgroup;

SQL> desc v$asm_disk;

 Name                                      Null?    Type

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

 GROUP_NUMBER                                       NUMBER

 DISK_NUMBER                                        NUMBER

 COMPOUND_INDEX                                     NUMBER

 INCARNATION                                        NUMBER

 MOUNT_STATUS                                       VARCHAR2(7)

 HEADER_STATUS                                      VARCHAR2(12)

 MODE_STATUS                                        VARCHAR2(7)

 STATE                                              VARCHAR2(8)

 REDUNDANCY                                         VARCHAR2(7)

 LIBRARY                                            VARCHAR2(64)

 OS_MB                                              NUMBER

 TOTAL_MB                                           NUMBER

 FREE_MB                                            NUMBER

 HOT_USED_MB                                        NUMBER

 COLD_USED_MB                                       NUMBER

 NAME                                               VARCHAR2(30)

 FAILGROUP                                          VARCHAR2(30)

 LABEL                                              VARCHAR2(31)

 PATH                                               VARCHAR2(256)

 UDID                                               VARCHAR2(64)

 PRODUCT                                            VARCHAR2(32)

 CREATE_DATE                                        DATE

 MOUNT_DATE                                         DATE

 REPAIR_TIMER                                       NUMBER

 READS                                              NUMBER

 WRITES                                             NUMBER

 READ_ERRS                                          NUMBER

 WRITE_ERRS                                         NUMBER

 READ_TIME                                          NUMBER

 WRITE_TIME                                         NUMBER

 BYTES_READ                                         NUMBER

 BYTES_WRITTEN                                      NUMBER

 PREFERRED_READ                                     VARCHAR2(1)

 HASH_VALUE                                         NUMBER

 HOT_READS                                          NUMBER

 HOT_WRITES                                         NUMBER

 HOT_BYTES_READ                                     NUMBER

 HOT_BYTES_WRITTEN                                  NUMBER

 COLD_READS                                         NUMBER

 COLD_WRITES                                        NUMBER

 COLD_BYTES_READ                                    NUMBER

 COLD_BYTES_WRITTEN                                 NUMBER

 VOTING_FILE                                        VARCHAR2(1)

 SECTOR_SIZE                                        NUMBER

 FAILGROUP_TYPE                                     VARCHAR2(7)

SQL>

select * from v$asm_disk;

select * from v$asm_operation;

select * from v$asm_diskgroup_stat;

select * from v$asm_client;

select * from v$asm_template;

select * from v$asm_file; 

select * from v$asm_alias;

select * from v$asm_attribute;

LAG and LEAD analytical function

LAG is an analytical function that can be used to get the value of a column in a previous row. It provides access to a row at a given offset prior to the current position.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

value_expression - Can be a column or a built-in function, except for other analytic functions.

offset - The number of rows preceding/following the current row, from which the data is to be retrieved. The default value is 1.

default - The value returned if the offset is outside the scope of the window. The default value is NULL.

query_partition_clause - Optional. It is used to partition the results into groups based on one or more expressions.

order_by_clause - Optional. It is used to order the data within each partition.