Restore Database from RMAN backups

Restore the database from production backup

Start the database nomount stage

sqlplus / as sysdba

SQL> startup nomount pfile=’<location>/initPROD.ora’;


rman target /

RMAN>restore controlfile from '/u02/prod_backup/prod_Control_Copy.ctl';

RMAN>alter database mount;

RMAN>catalog start with '/u02/prod_backup/';

RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK clear; - This will clear the disk type from production RMAN configurations

RMAN>restore database preview;



Restoration Script:

run {

set newname for database to '/u01/oracle/test/db/data/%U';

set until scn 8854465393;

restore database;

SWITCH DATAFILE ALL;

recover database;

}

Rename the redo log files:

alter database rename file '/u01/oracle/prod/db/data/log05.dbf' to '/u01/oracle/test/db/data/log01.log';

alter database rename file '/u01/oracle/prod/db/data/log06.dbf' to '/u01/oracle/test/db/data/log02.log';

alter database rename file '/u01/oracle/prod/db/data/log07.dbf' to '/u01/oracle/test/db/data/log03.log';

alter database rename file '/u01/oracle/prod/db/data/log08.dbf' to '/u01/oracle/test/db/data/log04.log';


alter database clear unarchived logfile group 8;

Rename the tempfile:

alter database rename file '/u01/oracle/prod/db/data/temp01.dbf' to '/u01/oracle/test/db/data/temp01.dbf';

Create pfile

Change the database name to TEST

$nid target=sys/<sys_password> dbname=TEST

Rename the pfile to initTEST.ora

Change the database name to TEST

Start the database in nomount and create spfile.

Bounce the database

ADOP Phases

Daily Used Queries

To delete old / unused files:

find /u01/inst/apps/QAT/logs/appl/conc/out -type f -mtime +30 -exec rm -f {} \;    --To delete files older than 30 days

find /path/to/ -type f -mtime +7 -name '*.gz' -execdir rm -- '{}' \;                        --To delete gzip files older than 7days

find . -mtime +10 -print -exec gzip {} \;   --for gzip files                                       --To gzip files older than 10days

find . -mtime +10 | xargs  tar -czvPf  trace_log_$(date +%F).tar.gz                       --To tar files older than 10days

find . -type f -mtime +30 -exec rm -f {} \;                                                           --To remove files older than 30days

delete noprompt archivelog until time 'SYSDATE-15';                                         -- To remove archives from rman older than 15days

To check Inactive / Blocking sessions

select sid,serial#,username,status,schemaname,logon_time from v$session where status='INACTIVE' and username is not null;

select distinct vlock.id1,decode(vlock.request,0,'Holder','Waiter') Type,vses.client_identifier,vses.sid,vses.serial#,vses.inst_id,vses.status,round(vses.last_call_et/60) mins,vses.event,vses.module,vses.sql_id,vses.machine,vses.row_wait_obj#

from gv$session vses, gv$lock vlock

where vlock.sid=vses.sid and vlock.inst_id=vses.inst_id

and (vlock.id1,vlock.id2,vlock.type) in (select id1, id2, type from gv$lock where request>0)

and vlock.ctime/60 > 5

order by id1 ;

To Drop a User who is connected:

DECLARE

  v_user_exists NUMBER;

  user_name CONSTANT varchar2(20) := 'XXEIS';

BEGIN

  LOOP

    -- Kill any currently active sessions

    FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE upper(s.username) = user_name)

    LOOP

      EXECUTE IMMEDIATE

        'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE';

    END LOOP;

    -- Try to drop the user.

    BEGIN

      EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade';

      EXCEPTION WHEN OTHERS THEN

      IF (SQLCODE = -1940) THEN

        -- Ignore ORA-01940 'Cannot drop user that is currently connected' exception

        NULL;

      ELSE

        RAISE;

      END IF;

    END;

    -- Continue looping, killing sessions and retrying to drop until successful

    BEGIN

      SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name;

      EXIT WHEN v_user_exists = 0;

    END;

  END LOOP;

END;

/

To Get error from the files:

egrep -i "PLS-|ORA-|unab-|Unab-|Error-" *.log

grep -iE "ORA-|Error-|Unab-"  filename

Data Pump:

CREATE DIRECTORY XXSBIWFA AS '/u02/XXSBIWFA';

select *from dba_directories;

GRANT  read,  write  ON  DIRECTORY  XXSBIWFA  TO  XXSBIWFA;

GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  hr;

GRANT  read,  write  ON  DIRECTORY  SBI_DIR  TO  XXEBS;

expdp \"/ as sysdba\" schemas=XXOCA_M directory=XXOCA_M_EXP dumpfile=XXOCA_M.dmp logfile=XXOCA_M.log

expdp XXSBIWFA/XXSBIWFA schemas=XXSBIWFA directory=XXSBIWFA dumpfile=XXSBIWFA3rdSep.dmp logfile=expdp_XXSBIWFA.log

impdp \'/ AS SYSDBA\' remap_schema=XXSBIWFA:XXSBIWFA2 dumpfile=XXSBIWFA3rdSep.dmp directory=XXSBIWFA  logfile=XXSBIWFAimp.log

select *from tab;

To Compile Invalid Objects:

select owner,object_name,object_type,status from dba_objects where STATUS='INVALID' and OWNER='XXSBIWFA';

EXEC DBMS_UTILITY.compile_schema(schema => 'XXSBIWFA2');

 @/u01/DEMO2_DB/db/tech_st/11.2.0/rdbms/admin/utlrp.sql

SELECT COUNT (*) FROM ALL_OBJECT WHERE STATUS='INVALID' ;

exec DBMS_UTILITY.compile_schema(schema => 'XXEIS');

select object_name, object_type, owner from dba_objects where status='INVALID' and owner='XXSPLASH_STOWERSLST' ;

EXEC DBMS_UTILITY.compile_schema(schema => 'XXSPLASH_STOWERSLST') ;

To Check the Quota of Tablespace Size:

set linesize 300

set pagesize 300

select a.tablespace_name, b.total,

       c.available, d.large_extent,

       (b.total - c.available) used,

       round((((b.total - c.available)*100)/b.total),2) pctused

  from (select tablespace_name, (sum(bytes)/1024)/1024 total

          from dba_data_files group by tablespace_name)  b,

       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

          from dba_free_space group by tablespace_name)  c,

       (select tablespace_name, (max(bytes)/1024)/1024 large_extent

          from dba_free_space group by tablespace_name)  d,

       dba_tablespaces  a

where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = c.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

/

 select default_tablespace from dba_users where username='XXWFAOCA';

select file_name,file_id,bytes/1024/1024/1024 from dba_data_files where tablespace_name='TEMP';

To get version of File name at OS Level

strings -a | grep '$Header' file_name

adident Header file_name

Convert Trace files with TKPROF

tkprof <trace_File>.trc <tkprof>.txt sys=no explain=apps/<apps pwd>

ADOP options in 12.2

ADOP Options in 12.2

ADOP stands for Application DBA Online Patching and this was introduced in EBS R12.2
This is new feature that we can able to patch running system without taking any down time for a significant period of time
ADOP uses dual file system to support online patching

If you want to know all the available options in ADOP, try below command
$adop -examples

To check the status of ADOP session
$adop -status

To get the list of log files along with the snippet of the error message corresponding to each log file try this command
$adopscanlog -latest=yes

Onlilne Patching Cycle: Consists of 5phases which needs to be execute in order.
Source the environment variable
adop phase=prepare
adop phase=apply patches=000000
adop phase=finalize
adop phase=cutover
source the environment variable
adop phase=cleanup



Also multiple phases can be executed in a single adop command
Example: adop phase=finalize,cutover,cleanup

Prior to cutover phase, it is possible to execute additional 'apply' and 'finalize' phases as needed.
Exaple: adop phase=prepare
        adop phase=apply patches=000000
adop phase=finalize
adop phase=apply patches=000000
adop phase=cutover
adop phase=cleanup

If you apply any patches after the finalize, you need to run the finalize again before cutover

Parameters applicable to all phases:

workers=<number> Number of parallel workers used to execute tasks. Default value is computed according to CPU cores.
Example: adop phase=prepare workers=8

prompt=(yes/no) [default:yes]
    This parameter specifies whether adop should prompt for user input on warnings.
By default adop will ask user whether to continue or exit on some warning messages. If this parameter is set to 'no' adop will remain fully non-iterative, and will continue past any warning messages without user confirmation.
Example: adop phase=cutover prompt=no

Prepare Parameters:
skipsyncerror=(yes/no) [default:no]
    This specifies whether to ignore errors that may occur during incremental file system synchronization. This might happen if you applied a patch in the previous patching cycle
Example: adop phase=prepare skipsyncerror=yes

Apply Parameters:

apply=(yes/no) [default:yes]
    This parameter controls adop acually applies the patch. In test environment we can use this parameter to complete the patch cycle without applying the patch
Example: adop phase=apply apply=no patches=000000

Apply two patches in series:
adop phase=apply patches=123456,987654

Apply patch to a user-specific directory
patchtop=<directory-name> [default:$PATCH_TOP]
Example: adop phase=apply patches=000000 patchtop=/u01/fs_ne/EBSapps/patches

apply_mode=(online/downtime/hotpatch) [default:online]
 Use online mode to apply a patch to the patch edition during an online patching cycle.
 Downtime mode to apply a patch to the run edition when services are down.
 Hotpatch mode to apply a patch to the run edition when services are up and running. [Use only when Oracle recommends]

Example: adop phase=apply patches=000000 apply_mode=downtime


adop phase=fs_clone
It will check for any pending cleanup actions
It will clone patch file system from run file system
It will check if there are any blocking managed server ports

Weblogic Server Upgrade from 12.2.1.2 to 12.2.1.3

Oracle WebLogic Server Upgrade from 12.2.1.2 to 12.2.1.3

Applies To: Oracle Fusion Middleware - Version 12.2.1.2.0

Information in this document applies to any platform.

Goal: Goal of this document is to upgrade WebLogic Server from 12.2.1.2 to 12.2.1.3

What's New in Oracle WebLogic Server 12c (12.2.1.3.0):

Feature

Description

Service Restart In Place

Service Restart In Place adds support for the ability to use any custom store with any migration policy.

Message limit in a JMS message subscription

WebLogic JMS adds a message limit option to help prevent individual overloaded subscriptions from using up all available resources

Security

New features include:

The WebLogic Security Service adds the secured production mode feature, which helps ensure a highly secure environment for applications and resources. See Support for Secured Production Mode.

A new security provider, Oracle Identity Cloud Integrator, that is an authentication and identity assertion provider that can access users, groups, and Oracle Identity Cloud Service scopes and application roles stored in the Oracle Identity Cloud Service.

Use of the SHA2 signature algorithm as the default for signing requests and responses. For backward compatibility, set the com.bea.common.security.saml2.useSHA1SigAlgorithm to true. 

By default, certificates that are expired or not yet valid are no longer used in SAML signing. To allow use of these certificates, set the com.bea.common.security.saml2.allowExpiredCerts to true.

Zero Downtime Patching

ZDT Patching adds support for modifying workflows using custom hooks.

Applied Patches List

Oracle WebLogic Server adds the ability to obtain a list of patches that have been applied to a server instance.

JDBC data sources

New features include:

Shared pooling for WebLogic data sources. Shared pooling provides the ability for multiple data source definitions to share an underlying connection pool.

Additional integration support for several new Oracle Database 12.2 features.

Initial Capacity Enhancement in the Connection Pool. WebLogic Server 12.2.1.3 enhances creation of initial capacity connections in a data source by providing control over connection retry, early failure, and critical resources.

JTA

WebLogic JTA adds transaction guard, which provides at-most-once execution during planned and unplanned outages and prevents duplicate submissions. See Transaction Guard.

Temporary Configuration Overriding

Temporary configuration overriding lets administrators place configuration information, contained in an XML file, in a known location where running servers identify and load it, overriding aspects of the existing configuration.

Apache Ant

This patch set release of WebLogic Server now includes Apache Ant 1.9.8, which may have an impact on the use of the clientgen Ant task. See Upgraded Version of Apache Ant in Upgrading Oracle WebLogic Server.

JEP 290

WebLogic Server uses the JDK JEP 290 mechanism to filter incoming serialization data and prevent denial of service (DOS) attacks. See JEP 290 Utilization.

Deprecated components

The following components are deprecated in this patch set and will be removed in a future release of WebLogic Server:

The following WebLogic JMS features and components:

·         WebLogic Replicated Store, which is intended solely for use in Oracle Exalogic Elastic Cloud environments

·         JMS resource adapter

·         WebLogic JMS Automatic Reconnect

·         WebLogic JMS Application Modules for Deployment

WebLogic SAF Agent support for JAX-RPC Reliable Messaging

EJBGen utility

The AnonymousAdminLookupEnabled attribute on the SecurityConfigurationMBean

The RESTful management resources listed in Deprecated RESTful Management Features.

Solution: Upgrade can be performed by running the Reconfiguration Wizard only.   It is not necessary to run the Upgrade Assistant (UA) for schemas or configuration upgrades.

1.      Install WebLogic Server 12.2.1.3.0 in a new Oracle home.

2.      Run the Reconfiguration Wizard to upgrade your domain. Document below has detailed information about Reconfiguration Wizard:

Fusion Middleware Upgrading Oracle WebLogic Server

https://docs.oracle.com/middleware/12213/wls/WLUPG/upgrade_dom.htm#WLUPG384

Before that we have to upgrade java jdk version

[oraweb@orawlda1 ~]$ java -version

java version "1.8.0_112"

Java(TM) SE Runtime Environment (build 1.8.0_112-b15)

Java HotSpot(TM) 64-Bit Server VM (build 25.112-b15, mixed mode)

Take the Domain Backup

[oraweb@orawls u01]$ cp -r oraweb/ oraweb/oraweb_backup/

Reference Docs:

How to Install WebLogic 12.2.1.3 and Create the Domain (Doc ID 2329789.1)

How To Replicate WebLogic 12c (12.2.1.3.0) Standalone Installation onto Remote Linux Server Without XServer? (Doc ID 2535666.1)

How To Upgrade WebLogic Server From 12.2.1.1 To 12.2.1.3？ (Doc ID 2391980.1)

https://docs.oracle.com/en/middleware/fusion-middleware/12.2.1.3/wlsig/installing-oracle-weblogic-server-and-coherence-software.html#GUID-5C7D4437-46A2-45A2-85F3-738B0DFE9AE2

Existing System Properties:

/u01/oraweb/products/fmw_12.2.1

/u01/oraweb/config/domains

/u01/oraweb/config/applications

[oraweb@orawls applications]$ echo $ORACLE_HOME

/u01/oraweb/products/fmw_12.2.1

[oraweb@orawls applications]$ echo $DOMAIN_HOME

/u01/oraweb/config/domains/fra_domain

[oraweb@orawls applications]$ echo $JAVA_HOME

/u01/oraweb/jdk/current_ver

[oraweb@orawls jdk]$ ls

current_ver  jdk1.8.0_112  jdk-8u112-linux-x64.tar.gz

[oraweb@orawls jdk]$ pwd

/u01/oraweb/jdk

[oraweb@orawls ~]$ vi .bash_profile

export JAVA_OPTIONS="-Djava.security.egd=file:/dev/./urandom ${JAVA_OPTIONS}"

# Oracle sourcing for Forms / Reports Install

export TNS_ADMIN=/u01/$USER/config/domains/fra_domain/config/fmwconfig

export ORACLE_HOME=/u01/$USER/products/fmw_12.2.1

export FORMS_PATH=/u01/$USER/products/fmw_12.2.1/forms

export PATH=$ORACLE_HOME/OPatch:$PATH

export PATH=$JAVA_HOME/bin:$ORACLE_HOME/bin:$FORMS_PATH/bin:$PATH

export PATH=$ORACLE_HOME/oracle_common/common/bin/:$PATH

# end of Oracle sourcing for Forms / Reports Install.

# Set $ORACLE_SID for custom written scripts.

export ORACLE_SID=WEB

# Oracle sourcing for Weblogic rda tool.

export DOMAIN_HOME=/u01/$USER/config/domains/fra_domain

# End of Oracle sourcing for Weblogic rda tool.

PATH=$PATH:$HOME/bin

export PATH

[oraweb@orawls applications]$ echo $PATH

/u01/oraweb/products/fmw_12.2.1/oracle_common/common/bin/:/u01/oraweb/jdk/current_ver/bin:/u01/oraweb/products/fmw_12.2.1/bin:/u01/oraweb/products/fmw_12.2.1/forms/bin:/u01/oraweb/products/fmw_12.2.1/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oraweb/bin

Create a complete backup copy of your existing deployment.

Complete all of the required pre-upgrade tasks for your environment.

Pre-Upgrade Checklist in component upgrade guides

Install the product distributions in a new Oracle home.

Oracle Universal Installer (OUI)

Run a pre-upgrade readiness check to determine if your deployment is ready to be upgraded.

Upgrade Assistant (UA)

Create new schemas for your Fusion Middleware components.

Use the Repository Creation Utility (RCU) for schemas that require custom settings such as additional tablespace size, for example.

Or:

Use the Upgrade Assistant to create missing or required schemas using the default schema settings. Schemas are created during the upgrade process.

Upgrade the 11g or previous 12c database schemas for the components you will be upgrading.

Upgrade Assistant (UA)

Reconfigure the domain.

Reconfiguration Wizard

Upgrade component configurations.

Upgrade Assistant (UA)

Perform all required post-upgrade tasks.

Verify that the upgrade is successful. At a minimum, verify that the servers and node manager start and your applications functions as expected.

Moved the files fmw_12.2.1.3.0_infrastructure_Disk1_1of1 and jdk-8u131-linux-x64.tar to the server and unzip at the desired location.

Before you upgrade, you must backup your domain folders and shut down all servers in the application environment.

(Reference: https://docs.oracle.com/cd/E23943_01/web.1111/e13754/roadmap.htm#WLUPG123)

Jdk Upgrade:

we will upgrade from JDK version 1.8.0_112 to 1.8.0_131 and we have a Linux 64 bit environment.

Make sure weblogic server and admin server are all stopped before following the below steps (nohup $DOMAIN_HOME/bin/stopWebLogic.sh &)

·         Download JDK, copy it to the server and unzip it.

[oraweb@orawls jdk]$ ls -ltrh

total 352M

-rwxr--r-- 1 oraweb oinstall 175M Apr 17  2017 jdk-8u112-linux-x64.tar.gz

lrwxrwxrwx 1 oraweb oinstall   30 Apr 17  2017 current_ver -> /u01/oraweb/jdk/jdk1.8.0_112

drwxr-xr-x 8 oraweb oinstall 4.0K Sep  7  2018 jdk1.8.0_112

-rwxr--r-- 1 oraweb oinstall 177M Oct 31 14:02 jdk-8u131-linux-x64.tar.gz

[oraweb@orawls jdk]$ tar -xvnzf jdk-8u131-linux-x64.tar.gz

Symbolic link changed to point to new version

ln -nfs jdk1.8.0_131/ current_ver

Update the Java Home path in following files

ORACLE_HOME/oui/.globalEnv.properties

DOMAIN_HOME/bin/setNMJavaHome.sh  

DOMAIN_HOME/nodemanager/nodemanager.properties

DOMAIN_HOME/bin/setDomainEnv.sh 

Now start the admin server

[oraweb@orawls products]$ export DISPLAY=”172.22.23.27:10.0”

Ran the below command through VNC server to launch GUI

$ $JAVA_HOME/bin/java -jar fmw_12.2.1.2.0_infrastructure.jar

ADF managed server details

Installing and Configuring Oracle Forms and Reports

Download the software

unzip fmw_12.2.1.3.0_fr_linux64_Disk1_1of2.zip

./fmw_12.2.1.3.0_fr_linux64.bin

Give the JAVA_HOME as jdk_home

Now run the upgrade assistant ua from the new home

Password: adminpw

Start NodeManager from backend. Got error as it is already running. Shutdown the weblogic server admin. Kill all the processes running on the node. Start weblogic admin.

Check NodeManager Properties file. Commented SecureListener parameter or make it false and start the NodeManager now. Go to the weblogic console and start the managed servers one by one.

How to reset Weblogic Server Password

$DOMAIN_HOME/bin/stopWebLogic.sh

->Rename the data folder.

$ mv $DOMAIN_HOME/servers/AdminServer/data $DOMAIN_HOME/servers/AdminServer/data-backup

Set the environment variables.
$  $DOMAIN_HOME/bin/setDomainEnv.sh

$ cd $DOMAIN_HOME/security
$ java weblogic.security.utils.AdminAccount weblogic Yellow292scotch .

java weblogic.security.utils.AdminAccount <weblogic> <Yellow292scotch>

Update the “$DOMAIN_HOME/servers/AdminServer/security/boot.properties” file with the new username and password. The file format is shown below.
username=weblogic
password=Yellow292scotch

Start the WebLogic domain.
$ $DOMAIN_HOME/bin/startWebLogic.sh

nohup $DOMAIN_HOME/bin/startWebLogic.sh > $DOMAIN_HOME/servers/AdminServer/logs/AdminServer.out &

Weblogic Server Failed to Start: Authentication denied: Boot identity not valid

Weblogic Server Startup failed: Caused By: 
weblogic.security.SecurityInitializationException: Authentication denied: Boot identity not valid

Server Fails to Start with Error, BEA-090402 - Authentication Denied: Boot Identity Not Valid, after Changing Password

Edit the boot.properties file at <ORACLE_WLS_HOME>/wlserver_10.3/samples/domains/wl_server/servers/examplesServer/security/boot.properties, override the encrypted password and replace it with the new one in clear text, for example, in our case, "manager":

# Generated by Configuration Wizard on Thu Sep 25 12:49:49 EDT 2008

username=<Encrypted_Value>password=manager

If it is not resolved!..  You can start the server without boot.properties file.

Try starting with the below single command

java -Dweblogic.management.username=weblogic -Dweblogic.management.password=weblogic123 -Dweblogic.system.StoreBootIdentity=true -Dweblogic.Name=AdminServer weblogic.Server