Auto Kill INACTIVE Sessions for more Then 1 Hour

 -- A procedure to disconnect INACTIVE sessions

CREATE OR REPLACE PROCEDURE SYS.DISCONNECT_INACTIVE AS 

BEGIN

  FOR SESSION IN (

         SELECT SID,

                SERIAL#

         FROM

                V$SESSION

         WHERE

                STATUS='INACTIVE' AND 

                USERNAME IS NOT NULL AND LAST_CALL_ET > 3600

                -- [Optional]. 

                -- Only sessions that are in INACTIVE status

                -- longer than 1 hour (or whatever you want)

                -- AND LAST_CALL_ET > 3600

                  )

  LOOP

    EXECUTE IMMEDIATE Q'[ALTER SYSTEM KILL SESSION ']' || SESSION.SID || ',' || SESSION.SERIAL# || Q'[' IMMEDIATE]';

  END LOOP;

END;

/

-- A job to run the procedure on a regular basis

SET SERVEROUTPUT ON

VARIABLE JOB_NUMBER NUMBER;

BEGIN

  DBMS_JOB.SUBMIT(JOB  => :JOB_NUMBER,

                  WHAT => 'SYS.DISCONNECT_INACTIVE;',

                  NEXT_DATE => SYSDATE,           -- Run now

                  INTERVAL  => 'SYSDATE +1/24');  -- Execute every hour

  COMMIT;

END;

/

Manual Archive Shipping From Prod to Standby

Standby Archive Location:/arch_destination/Arch/

Scp from Prod to standby :go to the archive location and then give scp command.

PROD:

[Prod.DB]scp *65351* oracle@10.80.34.90:/arch_destination/Arch/

[Prod.DB]scp *65352* oracle@10.80.34.90:/arch_destination/Arch/

After SCP:Register the Archive logfile

Standby:

alter database register logfile '/arch_destination/Arch/1_65351_1016024259.dbf';

alter database register logfile '/arch_destination/Arch/1_65352_1016024259.dbf';

MRP:

Stop & Start.

 Start MRP Process:

==================

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Stop MRP Process:
==================

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

 Note:Archive log should be available in physical location in both prod & standby if not ,we need to move it manually by below steps

Standby is out of Sync or GAP is not getting resolved on standby

 

Standby is out of Sync or GAP is not getting resolved on standby

It can happen due to several reasons like
1. Archived log has been deleted
2. Moved log file has corrupted
3. Any network issues

If it’s because of either 1 or 2 incremental backup from SCN is the best option to recover it. Suppose if we have proper archive logs exist on the primary database then FAL_SERVER, FAL_CLIENT will take care of the gap issue.

SQL> select name,database_role,switchover_status,protection_mode,force_logging from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE FOR
——— —————- ——————– ——————– —
PRD6A PHYSICAL STANDBY NOT ALLOWED MAXIMUM PERFORMANCE YES

SQL>

Make sure all the logs upto the gap is applied using v$archived_log and applied=YES. APPLIED=NO means MRP process is not applying logs on the standby database even though all the logs are available in standby database except the gaps.

SQL> SELECT SEQUENCE#,applied, FIRST_TIME, NEXT_TIME,REGISTRAR,status,END_OF_REDO_TYPE,DELETED FROM V$ARCHIVED_LOG;

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
34 YES 14-JUL-12 14-JUL-12 RFS A NO
35 YES 14-JUL-12 14-JUL-12 RFS A NO
36 YES 14-JUL-12 14-JUL-12 RFS A NO
37 YES 14-JUL-12 14-JUL-12 RFS A NO
38 YES 14-JUL-12 14-JUL-12 RFS A NO
39 YES 14-JUL-12 15-JUL-12 RFS A NO
40 YES 15-JUL-12 15-JUL-12 RFS A NO
41 YES 15-JUL-12 15-JUL-12 RFS A NO
42 YES 15-JUL-12 15-JUL-12 RFS A NO
43 YES 15-JUL-12 15-JUL-12 RFS A NO
44 YES 15-JUL-12 16-JUL-12 RFS A NO

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
45 YES 16-JUL-12 16-JUL-12 RFS A NO
71 NO 19-JUL-12 19-JUL-12 RFS A NO
73 NO 19-JUL-12 19-JUL-12 RFS A NO
75 NO 19-JUL-12 19-JUL-12 RFS A NO

SQL>

After 45 you will be able to see 71, archive log gap is 46-70. In this situation we can take the incremental backup from SCN

Take an Incremental backup of primary database using standby current SCN.

Get the current SCN from standby database
SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)
—————————————-
7728610994157

Stop the MRP process (stop redo Apply)
SQL> alter database recover managed standby database cancel;

Database altered.

Make sure no MRP is working, from V$MANAGED_STANDBY
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY where PROCESS=’MRP0′;

no rows selected

Connect primary database and take the RMAN Incremental backup using below script.
$ rman target /

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Jul 29 05:15:21 2012

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

connected to target database: PRD6A (DBID=2752208625)

RMAN> BACKUP INCREMENTAL FROM SCN 7728610994157 DATABASE FORMAT ‘/db/flashback/rmanback/ForStandby_%U’ tag ‘STANDBY’;
RMAN>

Starting backup at 29-JUL-12

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 device type=DISK
backup will be obsolete on date 05-AUG-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/db/prd6a/dbf1/datafile/doc_001.dbf
.
.
.
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/rmanback/ForStandby_11nh9epm_1_1 tag=STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:35

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/rmanback/ForStandby_12nh9f29_1_1 tag=STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUL-12

RMAN>

Copy this backup piece from primary server to standby server and catalog the pieces with standby controlfile.
RMAN> catalog backuppiece ‘/db/backup/ForStandby_11nh9epm_1_1’;

after this start the recovery on the sane session.
RMAN> recover database noredo;

allocated channel: disk1
channel disk1: SID=250 device type=DISK

allocated channel: disk2
channel disk2: SID=368 device type=DISK

Starting recover at 29-JUL-12
allocated channel: ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
….
..
..
.
channel ORA_DISK_1: reading from backup piece /db/backup/ForStandby_11nh9epm_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/db/backup/ForStandby_11nh9epm_1_1 tag=STANDBY
channel ORA_DISK_1: restore complete, elapsed time: 05:03:08
Finished recover at 29-JUL-12

Better to delete the backup from the standby using the TAG given while taking backup.

RMAN>delete backup tag ‘STANDBY’;

Start the managed recovery on standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Now check the database MRP is applying proper logs, It must solve the issue
SQL> SELECT SEQUENCE#,applied, FIRST_TIME, NEXT_TIME,REGISTRAR,status,END_OF_REDO_TYPE,DELETED FROM V$ARCHIVED_LOG;

46 YES 16-JUL-12 16-JUL-12 RFS A NO
47 YES 16-JUL-12 16-JUL-12 RFS A NO

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
48 YES 16-JUL-12 16-JUL-12 RFS A NO
49 YES 16-JUL-12 16-JUL-12 RFS A NO

select process,client_process,status,thread#,sequence#,block#,blocks,DELAY_MINS from v$managed_standby;

In case if it still persist then we need to re-create the controlfile of standby database from primary database.

Why we are recreating the controlfile?
Why because state of the database might not be changed while applying incremental backup. So that the database_scn
will be intact and scn for datafile were updated so that the database will look for old file copy.

Steps to re-create the controlfile [Refer note 734862.1 for details]

take the backup of control file from primary database
$:>rman target /
RMAN> backup current controlfile for standby;
Starting backup at 29-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=370 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/PRD6A/backupset/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp tag=TAG20120729T062512 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-12

Restore and start database using new controlfile

Copy this controlfile /db/flashback/PRD6A/backupset/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp from primary to standby and
restore the control file on standby.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> Shut immediate;

SQL> startup nomount;

Restore the controlfile using RMAN

rman target /
restore standby comtrolfile from ‘/db/backup/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp’;

SQL> shut immediate;
SQL> startup mount;

Start the MRP.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Note: If we have different file location on primary and standby, then we need change the datafile
location in standby database.

use alter database command to change the file name in controlfile.

SQL> alter database rename file ‘<from  file-name >’ to ‘<to file-name >’;

Once everything is fine, Make sure standby logfile exist both physically and on the controlfile too, then start
managed recovery using standby logfile.

In case still you have the problem with again few log file and those are existing on the standby server
then manually register it using the below command

SQL> alter database register logfile ”;

Check the MRP status now, we will be able to see it has changed from ARCHIVE_LOG_GAP to APPLYING_LOGS.

How to set processes & Sessions in Database

 

Below command is to Check Process & sessions:

SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes','sessions');     

                             

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION

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

processes                                      275             300

sessions                                       160             305

Below Command is to set process & Session

sql> alter system set processes=500 scope=spfile;

sql> alter system set sessions=555 scope=spfile;

Note: Once the database has been bounce the changes will effect.

Oracle Data Guard Explained – Step By Step

Oracle Data Guard consists of many components. In this post I’ll explain about an environment that helped me understand how all the components work. This is a 12.1.0.2 (with APR 2018 PSU, so should be pretty stable)with a physical standby in a remote site. The primary has 4 redo groups of 10GB each (single member) and the network between the sites (primary to standby) is slow (only a few MB/s). Because the files are large and the network is slow, every step of the standby operation takes time. This had taught me quite a lot about how DG works step by step.

Data Guard Overview

Before we start diving, a quick data guard overview. The common scenario for physical standby is async real time apply and this is what I’ll explain here (“real time apply” = redo is applied as soon at copied to the standby, “async” = the primary doesn’t wait for a confirmation from the standby to perform a commit). With this configuration (and actually in any DG configuration for that matter), there are 2 separated steps:

Log Transport – move redo data from the primary to the standby

Redo Apply – apply the redo on the standby database

Log Transport

Under normal circumstances, a TT process (Redo Transport Slave) is sending the redo stream to the standby site. On the standby site an RFS process (Remote File Server) will get the data and write it to the standby logs.

When there is a lag for some reason, the standby will use FAL (Fetch Archive Log) information to connect to the primary to ask for the missing archives. The primary will allocate an ARC process to copy the archive to the standby (it will be received by an RFS process as well).

How can we see this?

standby: lsof -p 35260 |grep sea

primary: netstat -np|grep 28215 (based on the output of previous)

Query the standby database to find a specific RFS process

1

2

3

4

5

6

7

8

SQL> select process,pid,status from v$managed_standby where process='RFS';

 

PROCESS   PID                      STATUS

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

RFS       38028                    RECEIVING

RFS       50159                    RECEIVING

RFS       32011                    RECEIVING

RFS       33444                    RECEIVING

The PID column is the OS process ID, so we’ll continue with shell on the standby (using PID 50159):

1

2

[oracle@standby:/tmp] $ lsof -p 50159 |grep TCP

oracle_50 50159 oracle   16u  IPv6 2552326962        0t0        TCP standby:ncube-lm->primary:10698 (ESTABLISHED)

Now we see the port this RFS connects to in the primary, so we’ll do the last step using shell on the primary (using port 10698):

1

2

[oracle@primary:~] $ netstat -np|grep 10698

tcp        0 1504800 1.1.1.1:10698         2.2.2.2:1521          ESTABLISHED 56242/ora_arc4_DB1

And this is how we see that ARC4 process on the primary is copying data to this specific RFS.

Redo Apply

This part happens on the standby database and is basically recovering the standby database from redo information. The process responsible for this is called MRP0 (Managed Standby Recovery Process). This process reads archive logs and standby redo logs and applies them to the database files.

You can see its progress in v$managed_standby:

1

2

3

4

5

6

SQL> select process,status,sequence#,block#

  2  from v$managed_standby where process='MRP0';

 

PROCESS   STATUS        SEQUENCE#     BLOCK#

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

MRP0      WAIT_FOR_LOG      33883      23871

Log Transport Process

Now, that we know how things work, let’s see what really happens. In this environment the redo logs were 10GB each, which means that it takes time to archive them, even locally. What I saw is that when a redo is full, Oracle first archives the file locally and only then starts copying it to the standby database.

As I said before, the network was slow, so copying a 10GB file to the standby took a little bit more than 1 hour. In many cases, during this time the primary managed to create more archives. What I saw was that LGWR (or actually the TT process) took part of copying the files. It took the current redo log while other archived logs were copied by ARCH processes.

One last thing, as far as I understand, in theory, when ARCH is copying an archive, it copies it to the archive location on the standby side. Then, MRP can access the file and apply it. What I saw is that even when ARCH was copying a file, in some cases the data went into the standby redo logs (I followed this in v$standby_log where you see the sequence and used_bytes).

I discussed that with Pieter van Puymbroeck (PM of Oracle DG) and this is what he said:

“When RFS receives live redo, it attempts to write it to an SRL. When RFS receives gap redo, it will use an SRL ONLY if there is an SRL already containing that branch/thread/sequence. Otherwise, RFS will write to an archive log (AL) file.

When RFS writes to an SRL, that file is available to MRP because the file already exists. When RFS writes to an archive log file, that file is not available to the MRP until all of the redo has been received and RFS has closed and committed the file. If RFS dies in the middle of receiving redo into an archive log file, the file is deleted. But redo written to an SRL is not lost. It is in the file as long as the disk write completes.”

Redo Apply Process

Following the process in v$managed_standby, I saw that even though data that came from the ARCH process and RFS wrote the data to a standby log, the MRP0 couldn’t apply it in real time. Every file that was copied by ARCH had to be fully copied before MRP0 started applying it.

Pieter explained this:

“Once the MRP has access to the file (SRL or AL), it needs to know how much redo it can read. For an AL, it looks at the NAB field in the header block (block 1) of the AL. For an SRL, it uses the RTA boundary array or the NAB field in the header block of the SRL. The RTA boundary array will only contain the number of blocks that can be read if the SRL is for live redo. So if gap redo is being received into an SRL, the RTA boundary array will NOT contain the number of blocks that can be read and the NAB field in the header block will not be correct until the entire file has been received because it is only updated after all the redo has been received.”

One more thing that happened to me is that sometimes MRP0 just got stuck in the middle of a log. In this case it waited for the entire redo file to be copied and then it continued. When this happened I got a “RTA boundary” message in MRP0 trace file. I guess that this happens when the primary switches a log file and start writing to a new one, but I haven’t checked that. I also have an SR on this, I will update this post when I have more information about it.

Minimizing the Lag

If you read carefully and followed the logic, you probably realized that we had a constant lag issue. The fact that copying an entire redo log takes over 1 hour, while during this time we create more archives led to a really huge lag, few hours during peaks, and we wanted to reduce this lag.

The problem is that in peak time we created more redo than we could copy. This caused to a case when MRP0 can’t apply in real time, so it waited for an entire redo to be copied (which took more than an hour), and because it took so long to copy the file we had a large lag all the time.

Our solution was to reduce the size of the redo logs to 2GB. That way, even if we create a few GB in a short while, we can copy them in parallel, allowing shorter delay until MRP0 can start applying. We still have lag during this time, of course, but we minimize the lag because we allow MRP to start applying faster (after 2GB instead of 10GB) while we keep copying more archives in parallel.

Script to Calculate Transfer Rate

This is not a perfect script, but I use it to measure to transfer rate. It’s quite simple, so it has the following limitations:

Assumes that block size for redo is 512B

Works only if during the script execution the files being copied have not changed (so if Oracle starts copying a new archive or finished copying an old one, it will return an error message instead of the rate)

#!/bin/bash

t=30

if [ -n "$1" ]; then

        t=$1

fi

first=$(sqlplus -s / as sysdba << EOF

        set pages 0 lines 1000 echo off

        select sum(sequence#),sum(block#) from v\$managed_standby where process='RFS';

        exit

EOF

)

echo "sleeping for $t seconds"

sleep $t

second=$(sqlplus -s / as sysdba << EOF

        set pages 0 lines 1000 echo off

        select sum(sequence#),sum(block#) from v\$managed_standby where process='RFS';

        exit

EOF

)

first_seq=$(echo ${first} | awk '{print $1}')

first_sum=$(echo ${first} | awk '{print $2}')

second_seq=$(echo ${second} | awk '{print $1}')

second_sum=$(echo ${second} | awk '{print $2}')

if [ "${first_seq}" != "${second_seq}" ]; then

        echo "something has changed, try again"

else

        diff=$((second_sum – first_sum))

        echo "blocked copied in $t seconds is $diff"

        diff_mb=$(bc <<< "scale=2; ${diff}*0.5/${t}/1024")

        echo "${diff_mb} MB/s"

fi

How to change the DBNAME /DBID using DBNEWID(nid) utility for Oracle database

 Change Only the DBID using NID Utility

Here we will be only changing the DBID of the Oracle database.

(1) Backup Database

rman target /

backup database;

exit

(2) shutdown immediate

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

(3) startup mount

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4764747646 bytes

Fixed Size                  2253584 bytes

Variable Size             457873600 bytes

Database Buffers          343860800 bytes

Redo Buffers                7471104 bytes

Database mounted.

SQL> exit

(4) Open one session and run NID utility(DBNEWID utility) with sysdba privileges  to change the DB ID.

nid TARGET=SYS/password@exptest

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

 

Password:

Connected to database EXPTEST (DBID=587585)

 

Connected to server version 11.2.0

 

Control Files in database:

    +DATA/EXPTEST/control01.ctl

    +FLASH/EXPTEST/control02.ctl

   

 

Change database ID ? (Y/[N]) => Y

 

Proceeding with operation

Changing database ID from 587585 to 587585534

 

Database ID for database EXPTEST changed to 587585534.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Successfully changed database  ID.

DBNEWID - Completed successfully.

(5) Startup of the database with open resetlogs

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4764747646 bytes

Fixed Size                  2253584 bytes

Variable Size             457873600 bytes

Database Buffers          343860800 bytes

Redo Buffers                7471104 bytes

Database mounted.

SQL> alter database open resetlogs;

Change Only the DBNAME

Here we will be only changing the DBNAME of the Oracle database.

(1) Backup Database

rman target /

backup database;

exit

(2) shutdown immediate

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

(3) startup mount

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4764747646 bytes

Fixed Size                  2253584 bytes

Variable Size             457873600 bytes

Database Buffers          343860800 bytes

Redo Buffers                7471104 bytes

Database mounted.

SQL> exit

(4) Open one session and run NID with sysdba privileges

nid TARGET=SYS/password@test_db DBNAME=exptest_db2 SETNAME=Y

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

 

Password:

Connected to database EXPTEST (DBID=587585)

 

Connected to server version 11.2.0

 

Control Files in database:

    +DATA/EXPTEST/control01.ctl

    +FLASH/EXPTEST/control02.ctl

   

 

Change database name ? (Y/[N]) => Y

 

Proceeding with operation

Database name changed to EXPTEST_DB2.

 

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name.

DBNEWID - Completed succesfully.

the value of DBNAME is the new db-name of the database

SETNAME must be set to Y. The default is N and causes the DBID to be changed also.

(5) Set the DB_NAME initialization parameter in the initialization parameter

file to the new database name

(6) Create password file in oracle using orapwd

(7) Startup of the database(with resetlogs)

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4764747646 bytes

Fixed Size                  2253584 bytes

Variable Size             457873600 bytes

Database Buffers          343860800 bytes

Redo Buffers                7471104 bytes

Database mounted.

SQL> alter database open resetlogs;

Change Both DBID and DBNAME

Here we will be changing both the DBID and DBNAME of the Oracle database.

(1) Backup Database

rman target /

backup database;

exit

(2) shutdown immediate

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

(3) startup mount

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4764747646 bytes

Fixed Size                  2253584 bytes

Variable Size             457873600 bytes

Database Buffers          343860800 bytes

Redo Buffers                7471104 bytes

Database mounted.

SQL> exit

(4) Open one session and run NID with sysdba privileges

nid TARGET=SYS/password@test_db DBNAME=exptest_db2

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

Password:

Connected to database EXPTEST (DBID=587585)

Connected to server version 11.2.0

Control Files in database:

+DATA/EXPTEST/control01.ctl

+FLASH/EXPTEST/control02.ctl

Change database name and ID ? (Y/[N]) => Y

Proceeding with operation

Database name changed to EXPTEST_DB2.

Modify parameter file and generate a new password file before restarting.

Database ID for database EXPTEST_DB2 changed to 587585534.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Successfully changed database name and ID.

DBNEWID - Completed successfully.

the value of DBNAME is the new dbname of the database

(5) After DBNEWID successfully changes the DBID,shutdown immediate

(6) Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.

(7) Create a new password file with orapwd

(8) Startup of the database with open resetlogs

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4764747646 bytes

Fixed Size                  2253584 bytes

Variable Size             457873600 bytes

Database Buffers          343860800 bytes

Redo Buffers                7471104 bytes

Database mounted.

SQL> alter database open resetlogs;

I hope you like this content on NID Utility in oracle. I have provided the step by step implementation for each of the path.

Oracle ORAchk | Database Health Check with Orachk

I will explain how to perform Database Health Check with Oracle ORAchk in this article.

 

Oracle ORAchk

ORAchk is a tool similar to the RACchk and Exadata Exachk tools, which offer database and clusterware, patchware, patch, OS recommendations and best practices. The most up-to-date version of ORAchk is 19.2 

 

ORAchk ( Database Health Check )

You can use the ORAchk tool to perform a health check, as well as run the RAC Database Upgrade, and get Pre-Upgrade Problems and recommendations.

The ORAchk tool can be used in the following environments.

• Oracle Real Application Clusters (RAC) databases

• non-RAC databases

• MAA configuration

• Oracle Golden Gate configuration

• EBS checks

• Preinstall configuration

• Pre-upgrade configuration

• Solaris system configuration

 

This Tool is supported on the following systems.

• Linux x86-64* (Enterprise Linux, RedHat and SuSE 9, SuSE 10 & SuSE 11)

• Oracle Solaris SPARC (Solaris 10 and 11)

• Oracle Solaris x86-64 (Solaris 10 and 11)

• AIX **

• HPUX**

 

Supported database versions of the ORAchk tool are as follows.

• Oracle 10g R2

• Oracle 11g

• Oracle 12c

• Oracle 18c

• Oracle 19c

 

I will use for upgrade on Oracle RAC 11.2.0.3 on IBM-AIX and will learn the status of the database before upgrading and Oracle’s best practices. After downloading the ORAchk tool, upload it to server, use ORAchk tool with the following steps.

 

 

 

 

Unzip orachk.zip file like following.

[oracle@Mussaib ~]$ cd orachk/

[oracle@Mussaib ~]$ ls -all

total 11288

drwxr-xr-x 2 oracle oinstall 256 Jul 02 10:33 .

drwxr-xr-x 10 oracle oinstall 4096 Jul 02 10:33 ..

-rw-r--r-- 1 oracle oinstall 5770368 Jul 02 09:53 orachk.zip

[oracle@Mussaib ~]$ unzip orachk.zip

Archive: orachk.zip

inflating: UserGuide.txt

inflating: rules.dat

inflating: orachk

creating: .cgrep/

................

................

................

inflating: readme.txt

inflating: collections.dat

[oracle@Mussaib ~]$

 

 

Contents of ORAchk are like following.

 

[oracle@Mussaib ~]$ ls -all

 total 75752

 drwxr-xr-x 3 oracle oinstall 4096 Jul 02 10:34 .

 drwxr-xr-x 10 oracle oinstall 4096 Jul 02 10:33 ..

 drwxrwxr-x 3 oracle oinstall 4096 May 31 09:37 .cgrep

 -rw-r--r-- 1 oracle oinstall 3435193 May 31 09:37 CollectionManager_App.sql

 -rw-r--r-- 1 oracle oinstall 432 May 31 09:37 UserGuide.txt

 -rw-rw-r-- 1 oracle oinstall 22951324 May 31 09:37 collections.dat

 -rwxr-xr-x 1 oracle oinstall 1604239 May 31 09:37 orachk

 -rw-r--r-- 1 oracle oinstall 5770368 Jul 02 09:53 orachk.zip

 -rwxr-xr-x 1 oracle oinstall 1604239 May 31 09:37 raccheck

 -rw-r--r-- 1 oracle oinstall 3879 May 31 09:37 readme.txt

 -rw-rw-r-- 1 oracle oinstall 3384097 May 31 09:37 rules.dat

 

 

Database Health Check

You can run the ORAchk tool with the following parameters for different purposes.

 

• -a  When execute with the -a parameter, it performs all the checks on the database / clusterware and operating system and makes recommendations.

• -b  Only the best practices are given with the -b parameter.

• -p  With the -p parameter, patch recommendations are given for the database and the clusterware.

• -u  With the -u parameter, pre-upgrade checks are performed and recommendations are provided.

• -v  Gives ORAchk version

 

 

 

 

Lets make an example to learn ORAchk, I will use ORAchk for pre upgrade like following.

 

[oracle@Mussaib ~]$ ./orachk -u -o pre

Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0, 12.1.0.2.0):- 11.2.0.4.0

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /oracle/product/11.2.0.3/grid?[y/n][y]y

Checking ssh user equivalency settings on all nodes in cluster

Node testserver02 is configured for ssh user equivalency for oracle user

Searching for running databases . . . . .

. .

List of running databases registered in OCR

1. TESTDB

2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].1

. .

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

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

Oracle Stack Status

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

Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name

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

testserver01Yes N/A Yes Yes Yes Yes TESTDB1

testserver02Yes N/A Yes Yes Yes Yes TESTDB2

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

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

Installed components summary

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

GI_HOME ORACLE_HOME Database Names

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

/oracle/product/11.2.0.3/grid - 11.2.0.3.0 /oracle/app/product/11.2.0.3/db - 11.2.0.3.0 TESTDB

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

Copying plug-ins

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . .

Checking Grid Infrastructure and RDBMS upgrade readiness to version 112040

Collections and audit checks log file is

/oracle/orachk/orachk_testserver01_TESTDB_070214_104039/log/orachk.log

Checking for prompts in /home/users/oracle/.profile on testserver01 for oracle user...

Checking for prompts in /home/users/oracle/.profile on testserver02 for oracle user...

Starting to run orachk in background on testserver02

=============================================================

Node name - testserver01

=============================================================

Collecting - ASM Diskgroup Attributes

Collecting - ASM initialization parameters

Collecting - Database Parameters for TESTDB database

Collecting - Database Undocumented Parameters for TESTDB database

Collecting - Pre-upgrade File Locations for TESTDB database

Collecting - AIX Packages

Collecting - CPU Information

Collecting - Disk Information

Collecting - DiskFree Information

Collecting - DiskMount Information

Collecting - Memory Information

Collecting - Network Interface Configuration

Collecting - Network Performance

Collecting - Network Service Switch

Collecting - Network tunable parameters

Collecting - OS fixes or patches

Collecting - OS version

Collecting - Patches for Grid Infrastructure

Collecting - Patches for RDBMS Home

Collecting - System and Kernel Settings

Data collections completed. Checking best practices on testserver01.

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

INFO => Prior to Upgrade Verify NO Materialized Views Being Refreshed (Automatically or Manually) for TESTDB

FAIL => Opatch version is lower than recommended in RDBMS_HOME for /oracle/app/product/11.2.0.3/db

FAIL => Opatch version is lower than recommended in GRID_HOME

INFO => Special Considerations for SCAN and HAIP of 11.2.0.2 and above Grid Infrastructure

INFO => Minimizing Planned Downtime for Upgrades and Patching

INFO => Upgrade Related References

INFO => Lifetime Support Policy and Product Certification INFO

INFO => Capture performance baseline, backup important configuration files and batch, cron, DBMS_JOBS and DBMS_SCHEDULER jobs

INFO => Known Issues Integrating pre-11gR2 Databases with Grid Infrastructure

INFO => Useful Information for Operating Oracle RAC on AIX Power Architecture

INFO => Be Aware of New Instant Client for Simplified Deployment

INFO => Oracle E-Business Suite interoperability and migration resources

INFO => Oracle Software Download Sites

WARNING => Some Users Needing Network ACLs for Oracle Utility Packages Found for TESTDB

FAIL => Auditing tables (SYS.AUD$/sys.fga_log$) row count is large enough that there could potentially be a problem during the upgrade process for TESTDB

INFO => Steps to execute rootupgrade.sh

INFO => Save "Oracle Enterprise Manager Database Control" files and data with the emdwgrd Utility before upgrading database.

INFO => Information about ASM process parameter when its not set to default value

INFO => Information about Grid Infrastructure software installation directory

WARNING => Package gpfs.base-3.3.0.11-ppc_64 is recommended but NOT installed

Copying results from testserver02 and generating report. This might take a while. Be patient.

=============================================================

Node name - testserver02

=============================================================

Collecting - AIX Packages

Collecting - CPU Information

Collecting - Disk Information

Collecting - DiskFree Information

Collecting - DiskMount Information

Collecting - Memory Information

Collecting - Network Interface Configuration

Collecting - Network Performance

Collecting - Network Service Switch

Collecting - Network tunable parameters

Collecting - OS fixes or patches

Collecting - OS version

Collecting - Patches for Grid Infrastructure

Collecting - Patches for RDBMS Home

Collecting - System and Kernel Settings

Data collections completed. Checking best practices on testserver02.

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

FAIL => Opatch version is lower than recommended in RDBMS_HOME for /oracle/app/product/11.2.0.3/db

FAIL => Opatch version is lower than recommended in GRID_HOME

INFO => Information about ASM process parameter when its not set to default value

WARNING => Package gpfs.base-3.3.0.11-ppc_64 is recommended but NOT installed

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

CLUSTERWIDE CHECKS

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

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

Detailed report (html) - /oracle/orachk/orachk_testserver01_TESTDB_070214_104039/orachk_testserver01_TESTDB_070214_104039.html

UPLOAD(if required) - /oracle/orachk/orachk_testserver01_TESTDB_070214_104039.zip

[oracle@Mussaib ~]$

ORAchk generated a Compressed report, download it and open,  it will be like following.

ORAchk report will give your database a System health Score and some recommendations.

Creating a user with SYSDBA privilege

SQL>create user mussaib identified by mussaib;

user created

SQL> grant sysdba to mussaib;

Grant succeeded.

SQL> conn mussaib/mussaib as sysdba

Connected.

SQL> show user

USER is "SYS"                     ---------- (!!!!!!!!!!!!!!!!!)

SQL> select * from v$pwfile_users ;

USERNAME                       SYSDB SYSOP SYSAS

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

SYS                            TRUE  TRUE  FALSE

mussaib                          TRUE  FALSE FALSE

SQL> exit;

Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

$ sqlplus mussaib/mussaib as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 07:26:29 2010

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

Connected to:

Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> show user

USER is "SYS"              ---------- (!!!!!!!!!!!!!!!!!)

How to kill Oracle RMAN backup job

 How to kill Oracle RMAN backup job

First, get the SID and SERIAL# from below query:

SQL> select b.sid, b.serial#, a.spid, b.client_info

from v$process a, v$session b

where a.addr=b.paddr and client_info like 'rman%';

SID        SERIAL#    SPID         CLIENT_INFO

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

592        12         865          rman channel=full_chanel

OR

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

SID        SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE

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

592        12         1          9115569    19258880   47.33

Use the following command to kill RMAN backup job:

SQL> alter system kill session '592,12' immediate;

system altered.

Backup job killed successfully, simultaneously you will get below mentioned error log in RMAN backup logs:

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

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

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

RMAN-03002: failure of backup plus archivelog command at 09/05/2019 00:09:15

RMAN-10038: database session for channel full_chanel terminated unexpectedly

Method II:

Directly kill RMAN job from OS level with the help of “kill -9”

[oracle@PR ~]$ ps -ef | grep rman|grep -v grep

oracle 2348 3124 3 01:28 pts/1 00:00:00 rman target /

[oracle@PR ~]$ kill -9 2348

ORA-28017: The password file is in the legacy format.

ORA-28017: The password file is in the legacy format.

Today I was trying to change password for one of my 12c database and got below error :-

SQL> alter user dbsnmp identified by "*************"

*

ERROR at line 1:

ORA-28017: The password file is in the legacy format.

Cause :- 

Looks like update to password file failed as the file format is wrong. 

Administrative privileges like sysbackup, syskm, sysdg cannot be granted unless the password file is in 12c Format

Solution :-

This error generally comes when you migrate your database from 11g to 12c

Regeneration of password will resolve this error.

PFB steps :-

1. Check which users have access to password file before regenerating it.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

2. Recreate your password file :-

==>orapwd file=$ORACLE_HOME/dbs/orapwamit entries=5 force=y

Enter password for SYS:

[Target_server][oracle][amit]

3. Now by default only sys have access to it

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

4. Grant sysdba privilege to DBSNMP user to have access to password file

grant sysdba to dbsnmp;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

5. Now again go for the password change, this time it will be successful.

SQL> alter user dbsnmp identified by "*************";

User altered.

I hope this article helped you.

How to take user grants Backup

spool /production/10_july_20_export_dump/grants_scripts/MUSAIB_permissions_10july2020.sql
set heading off
SET NEWPAGE NONE
SET FEEDBACK OFF
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee ||  ';'
from dba_tab_privs
where owner in ('MUSAIB')
order by owner, table_name, grantee;
select 'quit;' from dual;
select '/' from dual;
spool off;

quit;

/

Resizeing UNDO Tablespace datafile

UNDO TABLESPACE Before Size :

Tablespace                                Size            Free            Used      Max Ext

 Name                File Count            (MB)            (MB)            (MB)         (MB)     % USED Graph

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

SYSTEM                        1             .80             .00             .79           32 99.2301829

SYSAUX                        1             .66             .03             .63           32 94.8988971

USERS                         1             .00             .00             .00           32         20 ********

UNDOTBS1                      1            1.00             .96             .03           32 3.06396484 *********





SQL> alter database datafile 'C:\APP\MMUSSAIB\ORADATA\TEST\UNDOTBS01.DBF' resize 2G;



SQL>
set feed off

column "Graph" heading "Graph" format a11

set pages 999

set lines 999

column "tablespace_name" heading "Tablespace | Name" format a20

column "FileCount" heading "File | Count" format 999999

column "Size(MB)" heading "Size | (MB)" format 999,999,999.99

column "Free(MB)" heading "Free | (MB)" format 999,999,999.99

column "Used(MB)" heading "Used | (MB)" format 999,999,999.99

column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999

column "%Free" heading "% | Free" format 999.99

column "%Free Ext" heading "% | Free Ext" format 999.99

column tablespace_name heading "Tablespace | Name" format a20
SELECT
 ts.tablespace_name, "File Count",

   TRUNC("SIZE(MB)", 2) "Size(MB)",

   TRUNC(fr."FREE(MB)", 2) "Free(MB)",

   TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",

   df."MAX_EXT" "Max Ext(MB)",

   100-((fr."FREE(MB)" / df."SIZE(MB)") * 100) "% USED",

   RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*')    "Graph"

FROM

   (SELECT tablespace_name,

   SUM (bytes) / (1024 * 1024*1024) "FREE(MB)"

   FROM dba_free_space

    GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024*1024) "SIZE(MB)", COUNT(*)

"File Count", SUM(maxbytes) / (1024 * 1024*1024) "MAX_EXT"

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+)

ORDER BY "% USED" desc

/

UNDO TABLESPACE After Increasing Size :

Tablespace                                Size            Free            Used      Max Ext

 Name                File Count            (MB)            (MB)            (MB)         (MB)     % USED Graph

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

SYSTEM                        1             .80             .00             .79           32 99.2301829

SYSAUX                        1             .66             .03             .63           32 94.8988971

USERS                         1             .00             .00             .00           32         20 ********

UNDOTBS1                      1            2.00            1.96             .03           32 1.53198242 *********

SQL>

Control File Restore from RMAN Backup

Note: Renamed Control file to create an issue

Location Of Control FIle:/u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl,
/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_ hjpbhtg8_.ctl


SQL> show parameter control

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time      integer 7
control_files      string /u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl, /u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_ hjpbhtg8_.ctl
control_management_pack_access      string DIAGNOSTIC+TUNING

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Control File 1:Renameing to create issue for practice. 

[oracle@musaib ~]$ cd /u01/app/oracle/oradata/TEST/controlfile/
[oracle@musaib controlfile]$ ls -ltr
total 9808
-rw-r-----. 1 oracle oracle 10043392 Jul 12 21:23 o1_mf_hjpbhtdb_.ctl

[oracle@musaib controlfile]$ mv o1_mf_hjpbhtdb_.ctl control_file_bkp.ctl
[oracle@musaib controlfile]$ ls
control_file_bkp.ctl

Control File 2:Renameing to create issue for practice.

[oracle@musaib controlfile]$ cd /u01/app/oracle/fast_recovery_area/TEST/controlfile/
[oracle@musaib controlfile]$ ls
o1_mf_hjpbhtg8_.ctl
[oracle@musaib controlfile]$ mv  o1_mf_hjpbhtg8_.ctl control_file_bkp.ctl
[oracle@musaib controlfile]$ ls
control_file_bkp.ctl

[oracle@musaib controlfile]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 21:24:19 2020

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> shut immediate;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size     2927528 bytes
Variable Size   671089752 bytes
Database Buffers 1828716544 bytes
Redo Buffers    13848576 bytes

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@musaib controlfile]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jul 12 21:24:54 2020

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/TEST/TEST_C_20200712_0cv558v3.ctl';

Starting restore at 12-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_hjpbhtg8_.ctl
Finished restore at 12-JUL-20

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1


RMAN> recover database;

Starting recover at 12-JUL-20
Starting implicit crosscheck backup at 12-JUL-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 12-JUL-20

Starting implicit crosscheck copy at 12-JUL-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-JUL-20

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_hjpbhw31_.log
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_hjpbhw31_.log thread=1 sequence=24
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-JUL-20

RMAN> alter database open resetlogs;

Statement processed


RMAN> select name,open_mode from v$database;

NAME      OPEN_MODE         
--------- --------------------
TEST      READ WRITE       

RMAN> exit


Recovery Manager complete.

[oracle@musaib controlfile]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 21:29:59 2020

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> show parameter control

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time      integer 7
control_files      string /u01/app/oracle/oradata/TEST/c
ontrolfile/o1_mf_hjpbhtdb_.ctl
, /u01/app/oracle/fast_recover
y_area/TEST/controlfile/o1_mf_
hjpbhtg8_.ctl
control_management_pack_access      string DIAGNOSTIC+TUNING
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@musaib controlfile]$ cd /u01/app/oracle/fast_recovery_area/TEST/
[oracle@musaib TEST]$ ls -ltr
total 12
drwxr-x---. 3 oracle oracle 4096 Jul 12 20:47 archivelog
drwxr-x---. 2 oracle oracle 4096 Jul 12 20:49 onlinelog
drwxr-x---. 2 oracle oracle 4096 Jul 12 21:26 controlfile
[oracle@musaib TEST]$ cd controlfile/
[oracle@musaib controlfile]$ ls -ltr
total 19904
-rw-r-----. 1 oracle oracle 10043392 Jul 12 21:24 control_file_bkp.ctl
-rw-rw----. 1 oracle oracle 10338304 Jul 12 21:30 o1_mf_hjpbhtg8_.ctl
[oracle@musaib controlfile]$

DB Rebuild from Level0 Backup

PROD :

RMAN >RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/u01/app/oracle/TEST/%d_D_%T_%u_s%s_p%p'
  DATABASE
  CURRENT CONTROLFILE
  FORMAT '/u01/app/oracle/TEST/%d_C_%T_%u.ctl'
  SPFILE
  FORMAT '/u01/app/oracle/TEST/%d_S_%T_%u'
  PLUS ARCHIVELOG
  FORMAT '/u01/app/oracle/TEST/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
}

Backup location: '/u01/app/oracle/TEST/

[oracle@musaib oracle]$ ls -ltr
total 44
drwxrwxr-x. 19 oracle oracle 4096 Apr 19 01:59 diag
drwxr-xr-x.  2 oracle oracle 4096 Apr 19 01:59 checkpoints
drwxr-x---.  2 oracle oracle 4096 Apr 19 02:06 musaib
drwxr-xr-x.  4 oracle oracle 4096 Apr 19 02:06 product
drwxr-x---.  4 oracle oracle 4096 Apr 19 02:06 cfgtoollogs
drwxr-x---.  3 oracle oracle 4096 Apr 19 02:06 MUSAIB
drwxr-x---.  3 oracle oracle 4096 Jul 12 20:46 admin
drwxr-x---.  4 oracle oracle 4096 Jul 12 20:47 fast_recovery_area
drwxr-x---.  4 oracle oracle 4096 Jul 12 20:47 oradata
drwxr-x---.  4 oracle oracle 4096 Jul 12 20:49 audit
drwxrwxr-x.  2 oracle oracle 4096 Jul 12 21:21 TEST
[oracle@musaib oracle]$ cd TEST/

SCP BACKUP PIECES TO TEST SERVER FROM PROD:

[oracle@musaib TEST]$ scp * oracle@192.168.189.133:/u01/clone/
The authenticity of host '192.168.189.133 (192.168.189.133)' can't be established.
RSA key fingerprint is fd:f7:4b:c8:8f:a8:40:f0:dd:6b:ac:94:18:81:03:43.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.189.133' (RSA) to the list of known hosts.
oracle@192.168.189.133's password:
TEST_A_20200712_09v558uo_s9_p1               100%   30MB  30.3MB/s   00:01 
TEST_A_20200712_0ev558v6_s14_p1              100% 4608     4.5KB/s   00:00 
TEST_C_20200712_0cv558v3.ctl                 100% 9856KB   9.6MB/s   00:01 
TEST_D_20200712_0av558up_s10_p1                                                                                            100% 1163MB  40.1MB/s   00:29 
TEST_D_20200712_0bv558v1_s11_p1                                                                                            100% 9888KB   9.7MB/s   00:00 
TEST_S_20200712_0dv558v5                                                                                                   100%   96KB  96.0KB/s   00:00 

**************************************************************************

SCP Pfile FROM PROD TO TEST SERVER

[oracle@musaib dbs]$ ls -ltr
total 19648
-rw-r--r--. 1 oracle oracle     2992 Feb  3  2012 init.ora
-rw-rw----. 1 oracle oracle 10043392 Jul 12 20:35 snapcf_musaib.f
-rw-r-----. 1 oracle oracle        0 Jul 12 20:46 lkinstmusaib
-rw-rw----. 1 oracle oracle     1544 Jul 12 20:46 hc_musaib.dat
-rw-r-----. 1 oracle oracle       24 Jul 12 20:47 lkTEST
-rw-r-----. 1 oracle oracle     7680 Jul 12 20:49 orapwtest
-rw-rw----. 1 oracle oracle 10043392 Jul 12 21:21 snapcf_test.f
-rw-rw----. 1 oracle oracle     1544 Jul 12 21:57 hc_test.dat
-rw-r-----. 1 oracle oracle     3584 Jul 12 21:57 spfiletest.ora
-rw-rw-r--. 1 oracle oracle     1068 Jul 12 22:03 inittest.ora
[oracle@musaib dbs]$ scp inittest.ora oracle@192.168.189.133:/u01/clone/
oracle@192.168.189.133's password:
inittest.ora
100% 1068     1.0KB/s   00:00 
[oracle@musaib dbs]$


************************************************************************************

ON TEST SERVER:

[oracle@musaib ~]$ export ORACLE_SID=test
[oracle@musaib ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 23:20:29 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/clone/inittest.ora';
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size     2927528 bytes
Variable Size   671089752 bytes
Database Buffers 1828716544 bytes
Redo Buffers    13848576 bytes
SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@musaib ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jul 12 23:22:54 2020

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '/u01/clone/TEST_C_20200712_0cv558v3.ctl';

Starting restore at 12-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_hjpbhtg8_.ctl
Finished restore at 12-JUL-20


RMAN> catalog start with '/u01/clone';

Starting implicit crosscheck backup at 12-JUL-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 12-JUL-20

Starting implicit crosscheck copy at 12-JUL-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-JUL-20

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpktbm3_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpgosk4_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpds6ry_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_24_hjpds6s7_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_22_hjpds6rf_.arc

searching for all files that match the pattern /u01/clone

List of Files Unknown to the Database
=====================================
File Name: /u01/clone/TEST_S_20200712_0dv558v5
File Name: /u01/clone/TEST_C_20200712_0cv558v3.ctl
File Name: /u01/clone/TEST_D_20200712_0bv558v1_s11_p1
File Name: /u01/clone/TEST_A_20200712_09v558uo_s9_p1
File Name: /u01/clone/TEST_A_20200712_0ev558v6_s14_p1
File Name: /u01/clone/TEST_D_20200712_0av558up_s10_p1
File Name: /u01/clone/inittest.ora

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/clone/TEST_S_20200712_0dv558v5
File Name: /u01/clone/TEST_C_20200712_0cv558v3.ctl
File Name: /u01/clone/TEST_D_20200712_0bv558v1_s11_p1
File Name: /u01/clone/TEST_A_20200712_09v558uo_s9_p1
File Name: /u01/clone/TEST_A_20200712_0ev558v6_s14_p1
File Name: /u01/clone/TEST_D_20200712_0av558up_s10_p1

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/clone/inittest.ora
  RMAN-07517: Reason: The file header is corrupted


RMAN> restore database;

Starting restore at 12-JUL-20
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_hjpbfb7y_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_hjpbcm3v_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_hjpbh2gq_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_hjpbh1dl_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TEST/TEST_D_20200712_0av558up_s10_p1
channel ORA_DISK_1: piece handle=/u01/app/oracle/TEST/TEST_D_20200712_0av558up_s10_p1 tag=TAG20200712T212129
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 12-JUL-20

RMAN> recover database;

Starting recover at 12-JUL-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_24_hjpds6s7_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpgosk4_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_24_hjpds6s7_.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpgosk4_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpktbm3_.arc thread=1 sequence=1
unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/12/2020 23:29:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 1910939

RMAN> alter database open resetlogs;

Statement processed

RMAN> select name,open_mode from v$database;

NAME      OPEN_MODE         
--------- --------------------
TEST      READ WRITE          

Dropping undo & Creating New UNDO

Old ---undo
New----UNDOTBS_NEW


SQL> select  TABLESPACE_NAME,FILE_NAME from dba_data_files where tablespace_name ='undo';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS10.DBF

UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS11.DBF

UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS12.DBF


3 rows selected.

SQL> create undo tablespace UNDOTBS_NEW datafile 'E:\MUSAIB\DATA\UNDOTBS151.DBF' size 30G reuse autoextend off;

Tablespace created.

SQL> alter tablespace UNDOTBS_NEW add datafile 'E:\MUSAIB\DATA\UNDOTBS152.DBF' size 30G reuse autoextend off;

Tablespace altered.

SQL>
SQL> alter system set undo_tablespace=UNDOTBS_NEW;

System altered.

SQL> SHOW parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS_NEW


SQL> drop tablespace undo including contents and datafiles;

Tablespace dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

k:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 21 09:23:04 2019

Copyright (c) 1982, 2017, 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 from v$database;

NAME      OPEN_MODE
--------- --------------------
MUSAIB     READ WRITE

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%UNDO%';

SUM(BYTES/1024/1024/1024)
-------------------------
                       60

SQL>

How to change VNC password on Linux

~/.vnc/passwdis the default location where the VNC password is stored. The password is stored at this location when the vncserver starts for a first time. To update or change your VNC password you should use vncpasswd command. vncpasswd will promt you two times to enter your new password:

$ vncpasswd
Password:
Verify:
The vncpasswd command also accepts a password input from STDIN which also allows to store password file into different location. The following example will change the VNC password to MYVNCPASSWORD and store it to ~/.secret/vncpass given that the .secret directory exists:

$ echo MYVNCPASSWORD | vncpasswd -f > ~/.secret/passvnc
$