Change timezone on Linux using /etc/localtime file

Assume we have timezone UTC:

[root@oradbfw-hq01-mgmt ~]# date
Mon Sep 11 09:29:27 UTC 2017

On many distributions , like OEL, CentOS.. . the timezone is conntrolled by localtime file located in /etc directory.

# cd /etc

Localtime is a symbolic link , so we can remove it

# rm localtime

All timezones are located under /usr/share/zoneinfo.
I live and my servers are located in Tbilisi, Georgia. So searching my timezone file:

[root@oradbfw-hq01-mgmt etc]# ll /usr/share/zoneinfo/Asia/Tbilisi
-rw-r–r–. 1 root root 1024 Mar 24 09:21 /usr/share/zoneinfo/Asia/Tbilisi

My country is not in Asia, but in Europe ūüôā this condition changed recently so Linux has not updated and still thinks we in Asia. (Don’t tell this to my goverment ūüôā )

Let’s create correct symbolic link:

# cd /etc
# ln -s /usr/share/zoneinfo/Asia/Tbilisi localtime

Let’s check that it was changed:

[root@oradbfw-hq01-mgmt etc]# date
Mon Sep 11 13:47:04 +04 2017

Advertisements

RAC: root.sh | CRS-2672: Attempting to start ‘ora.storage’ | ORA-01017: invalid username/password

I was configuring clusterware on node1 and got the following error:

CRS-2672: Attempting to start ‘ora.storage’ on ‘node1’
ORA-01017: invalid username/password; logon denied
CRS-5017: The resource action “ora.storage start” encountered the following error:
Storage agent start action aborted. For details refer to “(:CLSN00107:)” in “/u01/app/oracle/diag/crs/node1/crs/trace/ohasd_orarootagent_root.trc”.
CRS-2883: Resource ‘ora.storage’ failed during Clusterware stack start.
CRS-4406: Oracle High Availability Services synchronous start failed.
CRS-4000: Command Start failed, or completed with errors.
2016/09/27 05:41:01 CLSRSC-117: Failed to start Oracle Clusterware stack

Died at /u01/app/12.1.0.2/grid/crs/install/crsinstall.pm line 930.
The command ‘/u01/app/12.1.0.2/grid/perl/bin/perl -I/u01/app/12.1.0.2/grid/perl/lib -I/u01/app/12.1.0.2/grid/crs/install /u01/app/12.1.0.2/grid/crs/install/rootcrs.pl ‘ exe ution failed

 

/u01/app/oracle/diag/crs/node1/crs/trace/ohasd_orarootagent_root.trc file says:

2016-09-27 05:40:56.787330*:kgfn.c@6018: kgfnConnect2Int: sysasm=0 envflags=0x10 srvrflags=0x3 unam=NULL password is NULL pstr=_ocr
2016-09-27 05:40:56.787330*:kgfn.c@6194: kgfnConnect2Int: cstr=(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/12.1.0.2/grid/bin/oracle)(ARGV0=oracle+ASM1_ocr)(ENVS=’ORACLE_HOME=/u01/app/12.1.0.2/grid,ORACLE_SID=+ASM1′)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))’)(PRIVS=(USER=root)(GROUP=root)))(enable=setuser))
2016-09-27 05:40:57.273302 : AGENT:2583111424: {0:9:3} {0:9:3} Created alert : (:CRSAGF00113:) : Aborting the command: start for resource: ora.storage 1 1

 

So why user root???

See, when I connect using root I got ORA-01017

[root@node1 ~]# . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@node1 ~]# sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 05:59:01 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

If I connect through Oracle it is OK:

su – oracle

[oracle@node1 ~]$ . oraenv
ORACLE_SID = [LBTCI1] ? +ASM1

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 05:59:45 2016
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 Real Application Clusters and Automatic Storage Management options

SQL>

 

Look the connection string again there is “PROGRAM=/u01/app/12.1.0.2/grid/bin/oracle”, so let’s check file permissions.

[oracle@node1 ~]$ ll /u01/app/12.1.0.2/grid/bin/oracle
-rwsr-s–x 1 root root 295054213 Sep 27 05:26 /u01/app/12.1.0.2/grid/bin/oracle

It must be oracle:oinstall  not root:root 

chown oracle:oinstall /u01/app/12.1.0.2/grid/bin/oracle
chmod 6751 /u01/app/12.1.0.2/grid/bin/oracle

 

deconfigure(rootcrs.pl -deconfig  -verbose) crs and reconfigure(run root.sh) it again.

 

error: package cvuqdisk is not installed

I have applied patch on RAC and after running postinstall script on the first node, it failed because of some file permission and the problem started…

I could not startup clusterware on first node.

I have deconfigured clusterware by:

[root@lbdm01-dr-adm ~]# $ORACLE_HOME/crs/install/rootcrs.pl -deconfig -force -verbose

And here I got error(cvuqdisk):

PRCR-1070 : Failed to check if resource ora.net1.network is registered
CRS-0184 : Cannot communicate with the CRS daemon.
PRCR-1070 : Failed to check if resource ora.helper is registered
CRS-0184 : Cannot communicate with the CRS daemon.
PRCR-1070 : Failed to check if resource ora.ons is registered
CRS-0184 : Cannot communicate with the CRS daemon.

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘lbdm01-dr-adm’
CRS-2679: Attempting to clean ‘ora.cssd’ on ‘lbdm01-dr-adm’
CRS-2680: Clean of ‘ora.cssd’ on ‘lbdm01-dr-adm’ failed
CRS-2799: Failed to shut down resource ‘ora.cssd’ on ‘lbdm01-dr-adm’
CRS-2795: Shutdown of Oracle High Availability Services-managed resources on ‘lbdm01-dr-adm’ has failed
CRS-4687: Shutdown command has completed with errors.
CRS-4000: Command Stop failed, or completed with errors.
2016/09/26 19:54:12 CLSRSC-463: The deconfiguration or downgrade script could not stop current Oracle Clusterware stack.

2016/09/26 19:54:12 CLSRSC-4006: Removing Oracle Trace File Analyzer (TFA) Collector.

2016/09/26 19:54:26 CLSRSC-4007: Successfully removed Oracle Trace File Analyzer (TFA) Collector.

error: package cvuqdisk is not installed
2016/09/26 19:54:26 CLSRSC-557: Oracle Clusterware stack on this node has been successfully deconfigured. There were some errors which can be ignored.

 

I have searched information about this package on documentation and I have found the following:

Link: https://docs.oracle.com/database/121/LADBI/pre_install.htm#LADBI7632

Installing the cvuqdisk RPM for Linux

If you do not use an Oracle Preinstallation RPM, then you must install the cvuqdisk RPM. Without cvuqdisk, the Cluster Verification Utility cannot find shared disks, and you receive a “Package cvuqdisk not installed” error when you run the Cluster Verification Utility. Use the cvuqdisk RPM for your hardware (for example, x86_64, or i386).

To install the cvuqdisk RPM, complete the following procedure:

  1. Locate the cvuqdisk RPM package, which is in the directory rpm on the Oracle Database installation media. If you installed Oracle Grid Infrastructure, then it is in the directory oracle_home1/cv/rpm.
  2. Log in as root.
  3. Use the following command to find if you have an existing version of the cvuqdisk package:
  4.  
    # rpm -qi cvuqdisk
    

    If you have an existing version, then enter the following command to deinstall the existing version:

    # rpm -e cvuqdisk
    
  5. Set the environment variable CVUQDISK_GRP to point to the group that owns cvuqdisk, typically oinstall, for example:
    # CVUQDISK_GRP=oinstall; export CVUQDISK_GRP
    
  6. In the directory where you have saved the cvuqdisk RPM, use the following command to install the cvuqdisk package:
    rpm -iv package
    

    For example:

    # rpm -iv cvuqdisk-1.0.9-1.rpm

So I have found the mentioned package in the following directory and installed it.

cd /u01/app/12.1.0.2/grid/cv/rpm

yum install cvuqdisk-1.0.9-1.rpm

Note: The problem is strange but, I am not writing why this happened in this post, because I don’t know it yet ūüôā
The aim of this post is that you should know where to find¬†cvuqdisk package and what is it for ūüôā

Good Luck!

How to find which version of Ralus is installed

Many problems are caused because of version mismatch between server and client backup exec software. Here is the simple steps how to check the version.

###############Linux
  1. From root user run:

    strings  /opt/VRTSralus/bin/beremote | grep -i version

You may already know these commands, but I prefer to explain:

Strings main uses are to determine the contents of and to extract text from binary files. The strings command returns each string of printable characters in files Printable characters are those which actually display on a monitor screen.

Grep prints lines that contain a match for a pattern.
-i option means ignore case. So tells grep to do case insensitive search of “version” keyword.

###############Windows
  1. go to:  ?\Program  Files\Symantec\Backup Exec\beremote.exe
  2. right click and select Properties
  3. view the Details tab

 

Creating a Snapshot-Based Backup of Oracle Linux Database Server(Exadata)

Story: We have Exadata x5-2 servers and they must be PCI compliant. Our PCI scanner found a lot of vulnerabilities on these machines, most of them were rpm upgrades.

So we are in a hell (devil) . Exadata is not a toy and upgrading it’s system is not like playing :):)

We must backup the system , and take the image before doing such things.

Because of Exadata has LVM-s installed we can use LVM based backups.

So let’s start:

Creating a Snapshot-Based Backup of Oracle Linux Database Server with Uncustomized Partitions

This means that you have not changed anything after the initial installation.

a) create mount point where you save backups. It is better if you use NFS. But if you don’t have NFS you may temporarily use local storage and then copy backup to another disk to be more safe.

Exadata local disk is 1.6TB in size and there is about 1.4TB free , so we can create another logical volume for backup. Let’s create logical volume 400GB ¬†in size.
Here, I can tell you the difference between DBA and Sysadmin ūüôā DBA needs more and more space and always takes more than required for being calm for next 5 years. And sysadmin is¬†giving to DBA less space than required and everytime you ask him/her storage his/her¬†face becomes like¬†thinking_80_anim_gif

# lvcreate -L 400GB -n backup VGExaDb
# mkdir /backup
# mkfs.ext4 /dev/VGExaDb/backup
# echo “/dev/VGExaDb/backup ¬† ¬† /backup ¬† ¬† ¬† ¬† ¬† ¬† ¬† ¬† ext4 ¬† ¬†defaults ¬† ¬† ¬† ¬†0 0” >>¬†/etc/fstab
# mount /backup

b) Take a snapshot-based backup of the / (root), /u01. Name them root_snap and u01_snap. Mount them to the newly created directories called exadata_os_backup and exadata_u01_backup.

# mkdir /backup/exadata_os_backup
# mkdir /backup/exadata_u01_backup
# lvcreate -L1G -s -c 32K -n root_snap /dev/VGExaDb/LVDbSys1
Logical volume “root_snap” created.
# e2label /dev/VGExaDb/root_snap DBSYS_SNAP
# mount /dev/VGExaDb/root_snap /backup/exadata_os_backup -t ext4
# lvcreate -L5G -s -c 32K -n u01_snap /dev/VGExaDb/LVDbOra1
Logical volume “u01_snap” created.
# e2label /dev/VGExaDb/u01_snap DBORA_SNAP
# mount /dev/VGExaDb/u01_snap  /backup/exadata_u01_backup -t ext4

c)  Create the backup file using the following command:

# cd  /backup
# tar -pjcvf /backup/os_backup.tar.bz2 * –exclude os_backup.tar.bz2 > /backup/os_backup.stdout 2> /backup/os_backup.stderr

Check the /tmp/backup_tar.stderr file for any significant errors. Errors about failing to tar open sockets, and other similar errors, can be ignored.

d) Unmount the snapshots and remove the snapshots for the root and /01 directories using the following commands:

umount /backup/exadata_u01_backup
umount /backup/exadata_os_backup
lvremove /dev/VGExaDb/u01_snap
lvremove /dev/VGExaDb/root_snap

More information:
https://docs.oracle.com/cd/E50790_01/doc/doc.121/e51951/db_server.htm#DBMMN21380
There is also discussed “Creating a Snapshot-Based Backup of Oracle Linux Database Server with Customized Partitions”.

 

Create a Local Yum Repository for Oracle Linux 6

0. Just to note¬†–repoid parameter has two dashes in front (- – repoid). Unfortunately wordpress makes it one dash. Please, do not blame me ūüôā

1. Create OEL.repo file in /etc/yum.repos.d and add the following entry

# vi /etc/yum.repos.d/OEL.repo

[oraclerpms]
name=OracleEnterpriseLinux
baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
enabled=1

2. Install yum-utils and createrepo packages

# yum install yum-utils createrepo

3. It is better to add another disk and create repository there, do not use / disk(disk where OS is installed) for repository. It is just a best practice.

Create necessary folders:

# mkdir -p /OEL_repo/OEL6/latest
# mkdir -p /OEL_repo/logs
# mkdir -p /OEL_repo/scripts

4. Download rpms.
If you are using proxy you may need to set proxy parameters like  export http_proxy=http://proxy_server_ip:proxy_port/

/usr/bin/reposync –newest-only –repoid=oraclerpms -p /OEL_repo/OEL6/latest

5. After download completes create repository on downloaded rpms:

/usr/bin/createrepo /OEL_repo/OEL6/latest/oraclerpms/getPackage/

####################
The above steps should be done every time you want to update your local repository, so let’s create script and place all them in one file.

vi /OEL_repo/scripts/repo_sync.sh

#!/bin/bash

LOG_FILE=/OEL_repo/logs/repo_sync_$(date +%Y.%m.%d).log

# Delete old logs
find /OEL_repo/logs/repo_sync* -mtime +5 -delete; >> $LOG_FILE 2>&1

#Clean cache
yum clean all

# Sync repositories
/usr/bin/reposync /usr/bin/reposync –newest-only –repoid=oraclerpms -p /OEL_repo/OEL6/latest >> $LOG_FILE 2>&1

/usr/bin/createrepo /OEL_repo/OEL6/latest/oraclerpms/getPackage/ >> $LOG_FILE 2>&1

2. Change permissions for the executable script:

# chmod u+x /OEL_repo/scripts/repo_sync.sh

3. Set up a CRON job to run the script on a daily basis. The following entry runs the script each day at 01:00.

0 1 * * * /OEL_repo/scripts/repo_sync.sh > /dev/null 2>&1

###############

1. Install the Apache HTTP servers, start it and make sure it restarts automatically on reboot.

# yum install httpd
# service httpd start
# chkconfig httpd on

2. Create directory and place link there

mkdir -p /var/www/html/OEL_repo/OEL6/latest
ln -s /OEL_repo/OEL6/latest/oraclerpms/getPackage/ /var/www/html/OEL_repo/OEL6/latest/x86_64

# cd /var/www/html
# wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

Note: /var/www/html is the default DocumentRoot directory. To check yours(sysadmin may have changed it) please check /etc/httpd/conf/httpd.conf file and find the keyword DocumentRoot

##############

1. Configure clients

vi /etc/yum.repos.d/local-ol6.repo

[oraclerpms]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://RepositoryServerHostname.domain.ge/OEL_repo/OEL6/latest/$basearch/
gpgkey=http://RepositoryServerHostname.domain.ge/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

Now you are able to install packages on clients by

# yum install ntp

Oracle: Audit DMLs by specific user

Ordinary auditing do not have option to indicate audit some activities done by specific user.

I mean, you cannot write the following:

audit insert on my_schema.my_table by my_user; ¬†<<—-not possible. The right statement is:
audit insert on my_schema.my_table by access;
or
audit insert on my_schema.my_table by session;

If I want to audit only activities done by my_user, one of the way is to create audit policy like the following;

begin
dbms_fga.add_policy(
object_schema=>’my_schema‘,
object_name=> ‘my_table‘,
policy_name=> ‘my_policy’,
audit_condition => ‘sys_context(”USERENV”,”CURRENT_USER”)=”MY_USER”’,
enable => TRUE,
statement_types => ‘INSERT, UPDATE, DELETE’,
audit_column_opts => dbms_fga.all_columns);
END;

So audit_condition gives the opportunity to check something and in this case we are checking user that is running statements indicated in statement_types option.

–Logs will be located here

SELECT * FROM dba_fga_audit_trail

–To see what policies we have

SELECT * FROM dba_audit_policies