Advertisements

Upgrade the Oracle RAC Grid Infrastructure from release 12.1.0.2 to release 12.2.0.1.

Upgrade Oracle Grid Infrastructure:

Note: Oracle Grid Infrastructure 12.2 has added new features that require plenty of disk space in the CRS disk group. Therefore, you must expand the size of the CRS disk group before you can upgrade Oracle Grid to the new release.

Expand the CRS disk group in ASM.
## Shut down srv1 and srv2.
## Add a new fixed-size sharable disk to srv1. Give it the name DISK4 and set its size to 40 GB.

up21.JPG
In Oracle VirtualBox, click on srv1 >> click on Settings >> click on Storage in the right pane >> click on Controller: SATA >> click on Add Hardisk >> click on Add a New Disk >> select VDI option >> make it fixed-size >> enter the full-path filename of the disk >> set its size to 40 GB

up22.JPG
## Once the disk is created, make it sharable.
File menu item >> Virtual Media Manager >> select the created disk DISK4 >> click on the Modify button >> choose the option to make this file shareable >> press Ok >> press Close
## Link the new disk to srv2.
click on srv2 >> click on Settings >> click on Storage in the right pane >> click on Controller: SATA >> click on Add Hardisk >> click on an Existing Disk button>> navigate to the new disk file and press OK

 ## Create the directory of the new Oracle Grid home. Make grid the owner of the home directory.

Start srv1 and wait for its OS to load.
## Start Putty and login to srv1 as root user. Format the added disk.
## display all the available disks:
ls -l /dev/sd*
# format the disk:
# answer "n", "p","1", default, default, "w" when prompted
fdisk /dev/sde

## Add the partitioned disk to the ASM recognized disk list.
oracleasm listdisks
oracleasm createdisk DISK4 /dev/sde1
oracleasm listdisks

Start srv2 and wait for its OS to load.
## Start Putty and login to srv2 as root user.
## Scan the ASM disks and make sure DISK4 is seen by srv2.

oracleasm scandisks
oracleasm listdisks

## Login to the VirtualBox window of srv1 as grid.

## Start asmca utility and add DISK4 to the CRS disk group.
Right-click on the CRS disk group >> select Add Disks >> select DISK4 then click on OK button
You should see the CRS disk group size increased to nearly 50GB, as shown in the following screenshot:

up20.JPG

## Make sure you have Putty sessions connected to srv1 and srv2 as root.
## Create the directory of the new Oracle Grid home. Make grid the owner of the home directory.

##srv1
mkdir -p /u01/app/12.2.0/grid
chown grid:oinstall /u01/app/12.2.0/grid

##Create the same directory in srv2.

ssh srv2
mkdir -p /u01/app/12.2.0/grid
chown grid:oinstall /u01/app/12.2.0/grid
exit

##In the VirtualBox window of srv1, login as grid user

##Unzip the linuxx64_12201_grid_home.zip file to the new Grid home directory.

unzip linuxx64_12201_grid_home.zip -d /u01/app/12.2.0/grid

##In the terminal windows, change the current directory to the new Oracle Grid directory.

cd /u01/app/12.2.0/grid
export ORACLE_HOME=/u01/app/12.2.0/grid
./gridSetup.sh
up1

up2.JPG

up3.JPG

up4

 

Click on SSH Connectivity button

Enter the OS grid user password.

Click on Test button. If it reports that the SSH connectivity is not configured, click on Setup button.

up5.JPG

up6.JPG

up7

 

Make sure asmadmin and asmdba are selected.

up8

Make sure /u01/app/grid is selected.

up9Mark the check box “Automatically run configuration scripts

Enter the root password

up10

Set the upgrade on srv2 to Batch 2.

If you keep it in Batch 1, the system will not be available while the upgrade is going on.

up11

up12

up13.JPG

If you receive error: “cvuqdisk-1.0.10-1” being unavailable on the system, click on “Fix and Check Again” button.

The following warning can be ignored (for a production system they must be addressed):

– Memory is less than 8 GB

– resolv.conf Integrity

– (Linux)resolv.conf Integrity

Select Ignore All check box then click on Next button

Note: You could have run the Cluster Verification Utility (CVU) before running the installer. Running it from within the installer gives the same results.

up14.JPG

up15.JPG

click on Install button

up16.JPG

up17.JPG

up18.JPG

up19.JPG

In the Putty window, verify the upgrade has been successfully concluded:

/u01/app/12.2.0/grid/bin/crsctl check cluster -all
/u01/app/12.2.0/grid/bin/crsctl query crs activeversion
Advertisements

How to Reconfigure Oracle Restart | ASM startup fail with ORA-29701.

How to Reconfigure Oracle Restart :
Cause: Today I faced one issue while cloning my VM for making a dataguard server,
I done the server name change while crs was up and running and end up in ASM startup failure.

Solution:

The solution is to reconfigure Oracle Restart.

[root@srv2 grid]# /u01/app/12.1.0/grid/crs/install/roothas.pl -deconfig
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2016/07/21 19:20:30 CLSRSC-39: Oracle Restart stack is not active on this node

2016/07/21 19:20:30 CLSRSC-312: Failed to verify HA resources

Died at /u01/app/12.1.0/grid/crs/install/crsdeconfig.pm line 1358.
***********************************************************************************
######OK LOOKS LIKE WE NEED TO ADD FORCE OPTION ###################
***********************************************************************************

[root@srv2 grid]# /u01/app/12.1.0/grid/crs/install/roothas.pl -deconfig -force
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
2016/07/21 19:22:03 CLSRSC-337: Successfully deconfigured Oracle Restart stack

[root@srv2 grid]# ./root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME=  /u01/app/12.1.0/grid
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user ‘grid’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node srv2 successfully pinned.
2016/07/21 19:23:17 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.conf’

srv2     2016/07/21 19:23:37     /u01/app/12.1.0/grid/cdata/srv2/backup_20160721_192337.olr     0
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘srv2’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘srv2’
CRS-2677: Stop of ‘ora.evmd’ on ‘srv2’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘srv2′ has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2016/07/21 19:25:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

2016/07/21 19:35:56 CLSRSC-352: CRS is already configured on this node for the CRS home location /u01/app/12.1.0/grid

[root@srv2 grid]# sudo su – grid
[grid@srv2 ~]$ srvctl add asm
[grid@srv2 ~]$ srvctl start asm
[grid@srv2 ~]$ ps -ef|grep pmon
grid      4902     1  0 19:37 ?        00:00:00 asm_pmon_+ASM
grid      4996  4778  0 19:37 pts/0    00:00:00 grep pmon
[grid@srv2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 19:38:11 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 Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+CRS’ from pfile=’/u01/test.ora’;
create spfile=’+CRS’ from pfile=’/u01/test.ora’
*
ERROR at line 1:
ORA-17635: failure in obtaining physical sector size for ‘+CRS’
ORA-15001: diskgroup “CRS” does not exist or is not mounted

SQL> alter diskgroup CRS mount;
alter diskgroup CRS mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “CRS” cannot be mounted
ORA-15040: diskgroup is incomplete

SQL>  alter diskgroup DATA mount;
alter diskgroup DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “DATA” cannot be mounted
ORA-15040: diskgroup is incomplete

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+CRS’ from pfile=’/u01/test.ora’;
create spfile=’+CRS’ from pfile=’/u01/test.ora’
*
ERROR at line 1:
ORA-17635: failure in obtaining physical sector size for ‘+CRS’
ORA-15001: diskgroup “CRS” does not exist or is not mounted

SQL>
Broadcast message from root@srv2.localdomain
(unknown) at 19:42 …

The system is going down for halt NOW!

Broadcast message from root@srv2.localdomain
(unknown) at 19:42 …

The system is going down for halt NOW!
login as: oracle
oracle@192.168.1.144’s password:
Last login: Thu Jul 21 19:01:31 2016 from 192.168.1.5
-bash: Page:: command not found
[oracle@srv2 ~]$ ps -ef|grep pmon
oracle    5157  5129  0 19:48 pts/0    00:00:00 grep pmon
[oracle@srv2 ~]$ sudo su – grid
[sudo] password for oracle:
[grid@srv2 ~]$ srvctl add asm
PRCA-1095 : Unable to create ASM resource because it already exists.
[grid@srv2 ~]$ srvctl start asm
PRCC-1014 : asm was already running
PRCR-1004 : Resource ora.asm is already running
PRCR-1079 : Failed to start resource ora.asm
CRS-5702: Resource ‘ora.asm’ is already running on ‘srv2′
[grid@srv2 ~]$ ps -ef|grep pmon
grid      5159     1  0 19:48 ?        00:00:00 asm_pmon_+ASM
grid      5437  5299  0 19:49 pts/0    00:00:00 grep pmon
[grid@srv2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 19:49:38 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 Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> alter diskgroup CRS mount;
alter diskgroup CRS mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “CRS” cannot be mounted
ORA-15040: diskgroup is incomplete

SQL> !
[grid@srv2 ~]$ asmcmd
ASMCMD> mount all
ORA-15110: no diskgroups mounted (DBD ERROR: OCIStmtExecute)
ASMCMD> exit
[grid@srv2 ~]$ ls -l /dev/mapper/HDD* |wc -l
ls: cannot access /dev/mapper/HDD*: No such file or directory
0
[grid@srv2 ~]$ oakcli show disk
bash: oakcli: command not found
[grid@srv2 ~]$ sudo su –
[sudo] password for grid:
[root@srv2 ~]# oakcli show disk
-bash: oakcli: command not found
[root@srv2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@srv2 ~]# /etc/init.d/oracleasm listdisks
CRSDISK1
DATADISK1
FRADISK1
[root@srv2 ~]# sudo su – grid
[grid@srv2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 20:06:13 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 Automatic Storage Management option

SQL> shutdown immediate
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted

SQL> shutdown
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup pfile=/u01/test.ora
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> create spfile=’+CRS’ from pfile=’/u01/test.ora’;
create spfile=’+CRS’ from pfile=’/u01/test.ora’
*
ERROR at line 1:
ORA-17635: failure in obtaining physical sector size for ‘+CRS’

SQL> create spfile from pfile=’/u01/test.ora’;

File created.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string
SQL> shutdown
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 1140850688 bytes
Fixed Size                  2933400 bytes
Variable Size            1112751464 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
—————————— ———- ———-
FRA                                 51199      47878
DATA                                51199      44783
CRS                                     0          0

SQL> alter diskgroup CRS mount;

Diskgroup altered.

SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB
—————————— ———- ———-
FRA                                 51199      47878
DATA                                51199      44783
CRS                                  2047       1985

SQL>  create spfile=’+CRS’ from pfile=’/u01/test.ora’;

File created.

SQL> !ps -ef|grep pmon
grid      9401     1  0 20:09 ?        00:00:00 asm_pmon_+ASM
grid     10068  8903  0 20:12 pts/0    00:00:00 /bin/bash -c ps -ef|grep pmon
grid     10070 10068  0 20:12 pts/0    00:00:00 grep pmon

SQL>

*(/etc/host) (/etc/hosts)

Steps for RMAN Restore backup of lower version database to a higher version

Source DB Name       :   TESTDB

Source DB Version     :   11.2.0.1.0

Source DB Host Name   :   HOSTNAME_1

Target DB Name       :   TESTDB

Target DB Version     :   11.2.0.4

Target DB Host Name   :   HOSTNAME_2

 

Step-1

=====

Installing the Oracle binary version 11.2.0.4 on HOSTNAME_2.

 

Step – 2

======

Taking a full backup of Database TESTDB from Source server (HOSTNAME_1),

Below command can be run on the source database RMAN prompt.

While finishing the backup it will be copied to the target location

@ HOSTNAME_1 ———> This should be executed in the Source database.

rman target / nocatalog

 

run {

allocate channel disk_1 type disk format ‘\\HOSTNAME_2\Source_backup\%U’;

backup keep until time ‘SYSDATE+3’ as BACKUPSET tag ‘%TAG’ format ‘\\HOSTNAME_2\Source_backup\hot_%d_%t’ database;

backup as BACKUPSET tag ‘%TAG’ format ‘\\HOSTNAME_2\Source_backup\%d_log_%d_%t’ archivelog all not backed up 1 times;

delete noprompt force obsolete device type disk;

delete noprompt expired backup;

delete noprompt expired archivelog all;

delete force obsolete;

release channel oem_backup_disk1;

backup current controlfile format ‘\\HOSTNAME_2\Source_backup\clone_TESTDB.ctl’;

}

 

Step -3

======

 

D:\programs\product\11204\dbhome_1\BIN>oradim.exe -NEW -SID TESTDB -pfile D:\programs\product\11204\dbhome_1\dbs\initTESTDB.ora

Instance created.

 

Step-4

=====

 

Copy the initTESTDB to D:\programs\product\11204\dbhome_1\database

 

SQL> CONN / AS SYSDBA

Connected to an idle instance

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 1081520128 bytes

Fixed Size                 2288080 bytes

Variable Size             574621232 bytes

Database Buffers         499122176 bytes

Redo Buffers                5488640 bytes

SQL> exit

 

Note: In windows init.ora file should be in $ORACLE_HOME/database folder instead of $ORACLE_HOME/dbs folder.

 

Step -5

======

Restore the control file form the backup.

 

D:\programs\product\11204\dbhome_1\BIN>rman target /

 

Recovery Manager: Release 11.2.0.4.0 – Production on Sun Nov 15 09:32:50 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

 

connected to target database: TESTDB (not mounted)

 

RMAN> restore controlfile from ‘D:\Source_backup\CLONE_TESTDB.CTL’;

 

Starting restore at 15-NOV-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=122 device type=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL01.CTL

output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL02.CTL

output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL03.CTL

Finished restore at 15-NOV-15

 

Step -6

======

 

Restore and recover the database.

 

RMAN> catalog start with ‘D:\Source_backup’;

 

RMAN> run

2> {

3> set newname for datafile 1 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\SYSTEM01.DBF’;

4> set newname for datafile 2 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\SYSAUX01.DBF’;

5> set newname for datafile 3 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\UNDOTBS01.DBF’;

6> set newname for datafile 4 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\USERS01.DBF’;

7> restore database;

8> switch datafile all;

9> recover database;

10> }

 

Step -7

=====

 

Rectifying log location mismatch error.

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00344: unable to re-create online log

‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1A.ORA’

ORA-27040: file create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 3) The system cannot find the path specified.

 

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG3A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG3A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG3B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG3B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG2A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG2A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG2B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG2B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG1A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG1B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG4A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG4A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG4B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG4B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG5A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG5A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG5B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG5B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG6A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG6A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG6B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG6B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG7A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG7A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG7B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG7B.ORA’;

 

Step – 8

======

 

Now open the database with “ALTER DATABASE OPEN RESETLOGS UPGRADE” command.

 

SQL> alter database open resetlogs upgrade;

Database altered.

 

If you would try opening with just “ALTER DATABASE OPEN RESETLOGS”, then it might fail with the below error.

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 6773

Session ID: 19 Serial number: 25

 

Step -9

======

Once opened, run the CATUPGRD.SQL script on the target database to upgrade the database

 

SQL> spool catupgrade.log

SQL> @D:\programs\product\11204\dbhome_1\RDBMS\ADMIN\catupgrd.sqlplus SQL> spool catupgrade.log

 

Note: If any errors are encountered, fix them and re-run the script before proceeding further.

 

Step -10

=======

Now start the target database normally and look out for any INVALID objects. Compile them by running the UTLRP.SQL script

 

sqlplus / as sysdba

SQL> startup

SQL> @D:\programs\product\11204\dbhome_1\RDBMS\ADMIN\utlrp.sql

 

And check all the registry components are valid as per the Source.

 

SQL> SET PAUSE ON

SQL> SET PAUSE ‘Press Return to Continue’

SQL> SET PAGESIZE 60

SQL> SET LINESIZE 300

SQL> SET VERIFY OFF

SQL> COL comp_name FOR a44 HEA ‘Component’

SQL> COL version FOR a17 HEA ‘Version’

SQL> COL status FOR a17 HEA ‘Status’

SQL>

SQL> SELECT comp_name, version, status FROM dba_registry;

 

COMP_NAME                               VERSION             STATUS

—————————————- ——————– ———–

Oracle Text                             11.2.0.4.0           VALID

Oracle Application Express               3.2.1.00.10         INVALID

Oracle Multimedia                       11.2.0.4.0           VALID

Oracle XML Database                     11.2.0.4.0           VALID

Oracle Expression Filter                 11.2.0.4.0           VALID

Oracle Rules Manager                    11.2.0.4.0           VALID

Oracle Workspace Manager                 11.2.0.4.0           VALID

Oracle Database Catalog Views           11.2.0.4.0           VALID

Oracle Database Packages and Types       11.2.0.4.0           VALID

JServer JAVA Virtual Machine             11.2.0.4.0           VALID

Oracle XDK                               11.2.0.4.0           VALID

Oracle Database Java Packages           11.2.0.4.0           VALID

 

12 rows selected.

 

ORA-12801,ORA-12853,ORA-04031

Error details:
————-
ORA-12801: error signaled in parallel query server P026, instance my_server:mydb (2)
ORA-12853: insufficient memory for PX buffers: current 323904K, max needed 2673360K
ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”)

The problem was that default parallel_max_servers was 470!
This number would be acceptable if database had enough free memory (specially pga)
to accommodate all of them, but database MEMORY_TARGET was the minimal.

SQL> show parameter parallel

NAME TYPE VALUE
———————————— ———– ——————————
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 470
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 512
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

So to resolve it, I had to change parallel_max_servers parameter to a lower value:

ALTER SYSTEM SET parallel_max_servers=40 SCOPE=BOTH;

Oracle datapump Import (IMPDP) fails due to Error ORA-31693,ORA-31640,ORA-19505,ORA-27037 Oracle 11gR2


Error Details:
==============

ORA-31693: Table data object “COMMON”.”LOC_ITEM_DSPTCH_SPEC” failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp” for read
ORA-19505: failed to identify file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solution Description:
=====================

Issue is due to the mount/file system was not being accessible from the second node in the RAC.
Then I made Cluster=N to force Data Pump to use only the instance where the job is started and ran again data pump job using below parfile

userid=”/ as sysdba”
CLUSTER=N
directory=MYDIR
JOB_NAME=JOB24_10thFeb2015
dumpfile=OMS_O_CUST_COMMON.dmp
logfile=imp_OMS_O_CUST_COMMON_10thFeb.log
TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=db_link
schemas=OMS_O,CUST,COMMON

Oracle datapump Import fails due to Error ORA-04030 Oracle 11gR2

===========================================================
ORA-39014,ORA-39029,ORA-31671,ORA-04030,ORA-06512,ORA-06512
===========================================================

Error:
—–
Starting “SYS”.”IMP_SCHEMA4″: /******** AS SYSDBA parfile=test.par
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”XYZ” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW00” prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04030: out of process memory when trying to allocate 16048 bytes (session heap,kuxLpxAlloc)
ORA-06512: at “SYS.KUPW$WORKER”, line 1887
ORA-06512: at line 2

Cause:
======
This seems to be an Oracle bug. But I haven’t seen any metalink note confirming this for the PASSWORD_HISTORY module.

Workaround/Solution:
====================
If you don’t have a requirement to import such a password history to the new schema, perform impdp with:

EXCLUDE=PASSWORD_HISTORY

Recovery of a Missing Datafile that has no backups (database is open).

Datafile Lost due to OS level delete:
=====================================
Recovery of a Missing Datafile that has no backups (database is open).
If a non system datafile that was not backed up since the last backup is missing,
recovery can be performed if all archived logs since the creation
of the missing datafile exist.
Pre requisites: All relevant archived logs.
1. alter datafile offline immediate;
2. alter database create datafile ‘fully_qualified_file_name’;
3. alter database datafile ‘fully_qualified_file_name’ online;

If the create datafile command needs to be executed to place the datafile on a
location different than the original use:
alter database create datafile ‘fully_qualified_file_name’ as ‘fully_qualified_file_name’;

CRS-2800 | Cannot start resource

set lines 190
set pages 150
select host_name,name,instance_name,database_role,status,open_mode,logins from v$instance,v$database;

HOST_NAME NAME INSTANCE_NAME DATABASE_ROLE STATUS OPEN_MODE LOGINS
—————————————————————- ——— —————- —————- ———— ——————– ———-
shanojrac1 ORCL ORCL1 PRIMARY OPEN READ WRITE ALLOWED

[grid@shanojrac1 root]$ srvctl status database -d orcl
Instance ORCL1 is not running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

[oracle@shanojrac1 ~]$ srvctl start instance -i ORCL1 -d ORCL
PRCR-1013 : Failed to start resource ora.orcl.db
PRCR-1064 : Failed to start resource ora.orcl.db on node shanojrac1
CRS-2800: Cannot start resource ‘ora.OCR_DATA.dg’ as it is already in the INTERMEDIATE state on server ‘shanojrac1’
[oracle@shanojrac1 ~]$ srvctl stop instance -i ORCL1 -d ORCL
PRCC-1017 : ORCL was already stopped on shanojrac1

[oracle@shanojrac1 ~]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE INTERMEDIATE shanojrac1 CHECK TIMED OUT
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE OFFLINE Instance Shutdown
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

=======================================
FIX:
=======================================

1) When you use the Oracle VM RAC templates with 11.2.0.2 and make use of role separation, you will not be able to build and RDBMS unless you
perform the following steps. During the creation of the database you will receive messages, Oracle not available. This is caused by the incorrect
setasmgid group used during the initiel configuration of you RAC 11.2.0.2 cluster.

Note:
—–
Please keep in mind that below work around is only applicable
when using role separation for the GRID infrastructure and uses the ORACLE RAC 11gR2 VM templates.

# cd /u01/app/11.2.0/grid/rdbms/lib
# cp -a ins_rdbms.mk ins_rdbms.mk.orig
# vi +1099 ins_rdbms.mk

Change line 1099 in ins_rdbms.mk, from this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) $(CONFIG)

to this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) config.$(OBJ_EXT)

2) Then tried to stop and start the ora.crsd to come out of INTERMEDIATE state.

[grid@shanojrac1 lib]$ crsctl stop res ora.crsd -init
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘shanojrac1’
CRS-2677: Stop of ‘ora.crsd’ on ‘shanojrac1’ succeeded
[grid@shanojrac1 lib]$ crsctl start res ora.crsd -init
CRS-2672: Attempting to start ‘ora.crsd’ on ‘shanojrac1’
CRS-2676: Start of ‘ora.crsd’ on ‘shanojrac1’ succeeded

[grid@shanojrac1 bin]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE ONLINE shanojrac1 Open
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

[grid@shanojrac1 bin]$ srvctl status database -d orcl
Instance ORCL1 is running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4. check parameter
SQL> show parameter audit_
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/db/product/10.2.0/rdbms/audit
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

Step 5. Log in as sysdba.

SQL> audit all by apps by access;

OR
Give the audit permission for delete and drop objects.

SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate -1 ORDER BY timestamp;

SQL> SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate -1 ORDER BY timestamp;

SQL> SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate – 1

SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;