ORACLE

UNINSTALL/REMOVING ORACLE FROM WINDOWS

NOTE: Do not perform this activity if you have more than one Oracle DB in your machine. This activity will remove all the databases. If you want to delete any particular DB, rather use DBCA utility of Oracle.

https://www.amazon.com/clouddrive/share/J2cVUWLWLhuidcJOnfQddTblo93GYTrjaSoJ4Sj7LK8

PDF file : Steps to delete Oracle
Pictures.rar : Screenshots present in pdf file(if some files are not properly visible in pdf)

Configuring Master Repository for ODI

1. make sure your database is up and running
2. make sure listener is started
 SQL>lsnrctl status
3. go to your $ODI_HOME/ODI/oracledi/client/
4. start ODI
5. follow screenshots

https://www.amazon.com/clouddrive/share/VeLj05Mps0pOFe7MIjNG865bakeWIBx6b6jv7AMerGi

How to access ODI Studio on Linux

http://onlineappsdba.com/index.php/2012/02/29/how-to-access-odi-studio-on-linux/

[oracle@sauravhost client]$ pwd
/u01/Middleware/ODI/oracledi/client
[oracle@sauravhost client]$ ll
total 76
drwxr-x---. 5 oracle oinstall 4096 Jun 8 14:30 ant
drwxr-x---. 8 oracle oinstall 4096 Jun 8 14:30 ide
drwxr-x---. 3 oracle oinstall 4096 Jun 8 14:30 integration
drwxr-x---. 6 oracle oinstall 4096 Jun 8 14:30 jdev
drwxr-x---. 2 oracle oinstall 4096 Jun 8 14:30 jlib
drwxr-x---. 2 oracle oinstall 4096 Jun 8 14:30 jviews
drwxr-x---. 2 oracle oinstall 4096 Jun 12 06:54 log
drwxr-x---. 12 oracle oinstall 4096 Jun 8 14:30 modules
drwxr-x---. 4 oracle oinstall 4096 Jun 8 14:30 odi
-rwxr-x---. 1 oracle oinstall 10752 Nov 29 2012 odi64.exe
-rwxr-x---. 1 oracle oinstall 10240 Nov 27 2012 odi.exe
-rwxr-x---. 1 oracle oinstall 655 Nov 29 2012 odi.sh
drwxr-x---. 3 oracle oinstall 4096 Jun 8 14:30 rdbms
drwxr-x---. 3 oracle oinstall 4096 Jun 8 14:30 sleepycat
drwxr-x---. 2 oracle oinstall 4096 Jun 8 14:30 timingframework

[oracle@sauravhost client]$ ./odi.sh

installing Oracle Data Integrator – ODI

1. Download and unzip the 2 files
 in Disk1 navigate to runInstaller
2. ./runInstaller
3. Follow the screenshots
https://www.amazon.com/clouddrive/share/I0BIya564fu8qN59ZtC7HdL6Sn5Q7AMtIJYDOuDe2aM

Installing Fusion Middleware – FMW in Oracle linux

1. Download the FMW from Oracle website
2. unzip the file : this will give you a jar file
 unzip path/filename.zip
 You will get a jar file
3. navigate to your installed java location 
 cd /usr/lib/jdk1.7.0.81/jre/bin
4. Unpack the jar file
 java -jar D:\fusion Middleware\fmw_12.2.1.3.0_wls_Disk1_1of1\fmw_12.2.1.3.0_wls.jar
You may get the following ERROR, if your JDK is not supportive:
Launcher log file is /u04/tmp/OraInstall2017-07-13_08-41-06AM/launcher2017-07-13_08-41-06AM.log.
Extracting the installer . . . . . . . . . . . . Done
The OpenJDK JVM is not supported on this platform.
The log is located here: /u04/tmp/OraInstall2017-07-13_08-41-06AM/launcher2017-07-13_08-41-06AM.log.
 solution : Download the latest(any supported version) of JDK from oracle. Better if you download the finename.rpm file as rmp files can be executed directly by double click.
 Once installed locate the jdk_1.8 location and now repeat step 4
5. If all is good, you will get installation window, follow screen-shots and proceed

https://www.amazon.com/clouddrive/share/tmNxMXSqr1BKIQNDhuNFQw6tZRYcP8Vc0mU47rqliB2

#Priveleges,Roles and Grants for a User in Oracle

select * from USER_ROLE_PRIVS where USERNAME='SCHEMANAME';
select * from USER_TAB_PRIVS where Grantee = 'SCHEMANAME';
select * from USER_SYS_PRIVS where USERNAME = 'SCHEMANAME';

for formatting/beautifying the output, use code below

#Beautifying SQLPLUS output in cmdline

set linesize 150
column GRANTEE for a40
column TABLE_NAME for a40
column OWNER for a40

NOTE: GRANTEE, TABLE_NAME, and OWNER are column names. Instead of 40 you can put any numerical number. But except that all remains the same.

Task10 – impdp / expdp

impdp licadmin/licadmin1234@orclsaurav schemas=licapp directory=test_backup dumpfile=LICAPP.dmp logfile=logLICAPP.log

imp licdev/licdev1234@amsprod file=C:\Users\Administrator\Desktop\ORAINST\dumps\new_dum_24-feb-17\licdev_dump.dmp log=C:\Users\Administrator\Desktop\ORAINST\dumps\new_dum_24-feb-17\licdev_24-02-2017_imp_log.log full = Y;

expdp licdev/licdev1234 schemas=LICDEV directory=BACKUP dumpfile=LICDEV.dmp logfile=expdp_LICDEV.log;

Task9 – Creating Tablespace, User and Grant priviledges

CREATE TABLESPACE "AMS_TAB1" DATAFILE 'C:\oracle\product\10.2.0\oradata\amsprod\amstab101.dbf' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER amsadmin IDENTIFIED BY gtfsamsadmin;
GRANT AUTHENTICATEDUSER,resource,connect,DBA,EXP_FULL_DATABASE,IMP_FULL_DATABASE,JAVA_DEPLOY TO amsadmin;

CREATE USER myuser IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users QUOTA 100M ON my_data;

Task 8 – ERR-ins32025, ORACLE_HOME ALREADY EXIST

C:\Program Files\Oracle\Inventory\ContentsXML
remove HOME NAME from inventory XML files

<HOME NAME="OraDB12Home1" LOC="C:\app\baazarkolkata\product\12.1.0\dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="OraDB12Home2" LOC="C:\app\baazarkolkata\product\12.1.0\dbhome_2" TYPE="O" IDX="2"/>
<HOME NAME="OraDB12Home3" LOC="D:\oracle_database\app\SouravG\product\12.1.0\dbhome_1" TYPE="O" IDX="3"/>
<HOME NAME="OraDB12Home4" LOC="D:\oracle_database\app\product\12.1.0\dbhome_1" TYPE="O" IDX="4"/>
<HOME NAME="OraDB12Home5" LOC="D:\oracle_database\app\SouravG\product\12.1.0\dbhome_2" TYPE="O" IDX="5"/>
<HOME NAME="OraDB12Home6" LOC="C:\oracle_DBapp\product\12.1.0\dbhome_1" TYPE="O" IDX="6"/>
<HOME NAME="OraDB12Home7" LOC="C:\oracle_DB\app\product\12.1.0\dbhome_1" TYPE="O" IDX="7"/>

Task 7 : If pfile or spfile does not exist

if startup nomount from pfile = 'Path/filename.ora'; does not work
-> startup pfile='Path/filename.ora';
-> create spfile from pfile='Path/filename.ora'

if spfile does not exist, work with initOrcl.ora file to create it

TASK 6 : ORA-01438 error and locating related column

The error reported 'ORA-01438: value larger than specified precision allowed for this column'.

You will receive this error while trying to insert/update numeric value greater than required precision value.

Action: Enter a value that complies with the numeric column's precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.

Solution for expanding the precision
All related files are present here:
https://www.amazon.com/clouddrive/share/EGaUX8aVMeWibSUGxdHK7zS1J52gkdqmuT0krRhXkch
STEP 1:
As the error does not list the name of the column causing precision overflow, we need to enable audit trail in your database to get a more detailed view.

SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;

You can follow the attached 'db_extend.txt' for all steps required(in details).

NOTE: This activity requires you to restart the database.

STEP 2:

As your concerned error can occur only on INSERTION and UPDATION operation. You need to perform the following step

SQL> audit insert,update on USERNAME.TABLENAME;

Once enabled, you have to execute a sample INSERT/UPDATE command to get 'ORA-01438' error. As the auditing is enabled, we can now trace the error code and SQL command causing this error.

SQL> select sql_text,returncode from dba_audit_trail where owner='SCHEMA_NAME' and obj_name='TABLENAME';

You can follow attached 'testing.txt' file for details.

STEP 3:

Once you have identified the column causing the said error. You can modify the column by either of the two methods defined in attached file - 'solution.txt'.

STEP 4:

turn off the audit trail as it was before.

SQL> NOAUDIT insert,update ON USERNAME.TABLENAME;

SQL> alter system set audit_trail='DB' scope=spfile;

You can follow 'final.txt' file for details

NOTE: This activity requires you to restart the database.

TASK 5 : TABLE PINNING IN BUFFER POOL

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile ='D:\pfileNewSPG.ora';
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 3200376 bytes
Variable Size 7516195464 bytes
Database Buffers 1.3824E+10 bytes
Redo Buffers 35135488 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='D:\pfileNewSPG.ora';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startupcreate
SP2-0734: unknown command beginning "startupcre..." - rest of line ignored.
SQL> startup;
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 3200376 bytes
Variable Size 7516195464 bytes
Database Buffers 1.3824E+10 bytes
Redo Buffers 35135488 bytes
Database mounted.
Database opened.
SQL> Select component, current_size from v$memory_dynamic_components where
 2
SQL> current_size!=0;;
SP2-0734: unknown command beginning "current_si..." - rest of line ignored.
SQL> Select component, current_size from v$memory_dynamic_components where current_size!=0;

COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 1476395008
large pool 268435456
java pool 201326592
streams pool 134217728
SGA Target 1.6106E+10
DEFAULT buffer cache 1.2415E+10
KEEP buffer cache 805306368
RECYCLE buffer cache 134217728
Shared IO Pool 536870912
PGA Target 3422552064

10 rows selected.

SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 20G
sga_target big integer 15G
unified_audit_sga_queue_size integer 1048576
SQL> alter table DLSDBA.T_INSP_RUN_COIL_STA_RCL1 storage (BUFFER_POOL
 2
SQL> KEEP);
SP2-0042: unknown command "KEEP)" - rest of line ignored.
SQL> alter table DLSDBA.T_INSP_RUN_COIL_STA_RCL1 storage (BUFFER_POOL KEEP);

Table altered.

SQL> alter table DLSDBA.T_INSP_RUN_COIL_STA_RCL2 storage (BUFFER_POOL KEEP);

Table altered.

SQL> alter table DLSDBA.T_INSP_RUN_COIL_STA_RCL3 storage (BUFFER_POOL KEEP);

Table altered.

SQL> alter table DLSDBA.T_DEFECT_LOG_TEMP storage (BUFFER_POOL KEEP);

Table altered.

SQL> alter table DLSDBA.T_CLOSE_INSP_TEMP storage (BUFFER_POOL KEEP);

Table altered.

SQL> alter table DLSDBA.T_LIB_ALARM_HIST storage (BUFFER_POOL KEEP);

Table altered.

SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_INSP_RUN_COIL_STA_RCL1';

BUFFER_
-------
KEEP

SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_INSP_RUN_COIL_STA_RCL2';

BUFFER_
-------
KEEP

SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_INSP_RUN_COIL_STA_RCL3';

BUFFER_
-------
KEEP

SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_DEFECT_LOG_TEMP';

BUFFER_
-------
KEEP

SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_ClOSE_INSP_TEMP';

no rows selected

SQL>
SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_CLOSE_INSP_TEMP';

BUFFER_
-------
KEEP

SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_LIB_ALARM_HIST';

BUFFER_
-------
KEEP

set timing on;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> starup;
SP2-0042: unknown command "starup" - rest of line ignored.
SQL> startup;
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 3200376 bytes
Variable Size 7784630920 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 35135488 bytes
Database mounted.
Database opened.
SQL> select buffer_pool from dba_tables where owner = 'DLSDBA' and table_name = 'T_LIB_ALARM_HIST';

BUFFER_
-------
KEEP

SQL>

TASK 4 : Oracle Linux installation on VM

https://www.amazon.com/clouddrive/share/1yy5YNbWz9WVudys9yXIuxoNS4BPgcLbMMb44MB1WJ6

https://www.youtube.com/watch?v=H8sITSBiH5s&t=17s

TASK 3 : Things to check after Oracle startup

[oracle@os1 orcl1]$set ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@os1 orcl1]$set ORACLE_SID=orcl1
[oracle@os1 bin]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@os1 bin]$ echo $ORACLE_SID
orcl1
———————————–
[oracle@os1 orcl1]$ cd /u01/app/oracle/product/11.2.0/db_1/bin/
[oracle@os1 bin]$ ls -lrt sqlplus*
-rwxr-x–x. 1 oracle oinstall 9039 May 19 17:16 sqlplus
[oracle@os1 bin]$ ./sqlplus
USERNAME – sys as sysdba
PASSWORD – redhat (remains hidden)
———————————–
Note : on your initial/ first run you may not be able to initiate sqlplus directly via cmd line (from anywhere). For this you have to locate $ORACLE_HOME/bin location and start sqlplus explicitly using command above.
Make sure PATH is set in .bash_profile for your ‘oracle user’ (OS user named oracle)
———————————–
sqlplus sys/syspassword@dbConnection as sysdba
or
sqlplus / as sysdba

SQL>SELECT INSTANCE_NAME, DATABASE_STATUS, INSTANCE_ROLE from v$instance;
SQL>select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

oraC

———————–TASK 4 COMPLETE——————

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

TASK 3 : Database Health Report

Download the SQL script from here :

https://www.amazon.com/clouddrive/share/ZZqoTsZp8S5IeKBbOWPFAS6Q66hsg2TChneeV3ggXRv?_encoding=UTF8&%2AVersion%2A=1&%2Aentries%2A=0&mgh=1

See Screenshot :

https://www.amazon.com/clouddrive/folder/7M8gjqJZR6qD5GHy-J_nyg/A2DblPKsTwCVUKC40Y5rKA

———————–TASK 3 COMPLETE——————

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

TASK 2 : Take staspack report

SQL>sqlplus / as sysdba
SQL>show user
–SYS
SQL>select name from v$datafile;
–PATH\filesnames
SQL>create tablespace TS_perfstat datafile ‘PATH\perfstat01.dbf’ size 5G;

—-checking—
SQL>select table_name from dba_tables where table_name like ‘STATS$%’;
SQL>select table_name from dba_tables where tablespace_name = ‘TS_perfstat’;
———–
**********************
Creating PERFSTAT user and other requirements
SQL>@D:\Oracle\Database\ora12cDB\rdbms\admin\spcreate.sql;
–password : pwd4perfstat
–default tablespace : TS_perfstat (choose from list available)
–temporary tablespace : temp (choose from list available)
**********************
—–testing if everything is created properly———-
select username from dba_users where username = ‘PERFSTAT’;
select table_name from dba_tables where table_name like ‘STATS$%’;
select table_name from dba_tables where tablespace_name = ‘TS_perfstat’;
select table_name from dba_tables where owner = ‘PERFSTAT’;
select object_name, object_type from dba_objects where object_name = ‘STATSPACK’
desc STATSPACKS;

—-testing done——
**********************
–Now connect to perfstat/pwd4perfstat user
SQL>connect perfstat/pwd4perfstat
–Execute the snap
SQL>execute statspack.snap;

–after 10 min executed another snap.
SQL>execute statspack.snap;

SQL>selecty snap_id,snap_time from stats$snapshot;
**********************
generate report from the snaps taken.
SQL>@D:\Oracle\Database\Ora12CDB\rdbms\admin\spreport.sql
enter value of begenning snap : 1
enter value of begenning snap : 2
enter value for report_name : test_snap_report

———————–TASK 2 COMPLETE——————

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

Task 1 : ORACLE STANDBY DB CREATION(PASSIVE MODE) WITH BACKUP.

Install Auxiliary Server's DB instance in same ORACLE_BASE loaction as the primary Db instance

creating password file for the auxiliary server

configuring network files. tnslistener in primary and listener.ora is auxiliary

creating pfile from primary for the auxiliary

creating necessary directories for standby database files

taking backup of the primary and copying it to auxiliary

```
startup the auxiliary in nomount steps
```

running duplicate database command with standby clause.

Actual steps

DB INSTANCE

OS VERSION

ORACLE EDITION/VERSION

IP ADDRESS

PORT NUMBER

SID

DB_UNIQUE_NAME

PRIMARY

WINDOWS SERVER 2012

Standard Edition/ 12.1.0.2.0

130.162.69.40

GINESYS

GINESYS_PRMY

AUXILIARY

Same

same

130.162.68.170

same

GINESYS_STNDBY

STEP 1

IN AUXILIARY SERVER

Create a DB instance in auxiliary server within a definite ORACLE_BASE location, the location should be same as we used in our primary DB server.

NOTE: For us, the ORACLE_BASE location for both my primary and auxiliary server is : C:\oracle_DB\app\.

The ORACLE_SID(global database name) should be same in both primary and auxiliary server(GINESYS in our case). We can have different DB_UNIQUE_NAME for the auxiliary and primary server. Like in Our case the names of primary server's DB_UNIQUE_NAME = GINESYS_PRMY and for auxiliary server it is GINESYS_STNDBY. DB_UNIQUE_NAME is modifiable. But not necessary in our case.

To view SID

SQL>select name from v$database;

To view DB_UNIQUE_NAME

SQL>show parameter db_unique_name;

To modify DB_UNIQUE_NAME

SQL>alter system set DB_UNIQUE_NAME='newName' scope=spfile SID='*';

While creating DB instance please note, do not use 9g,10g or 11g with 12c as a primary and standby pair. The binary of grid and cloud versions of Oracle are different and this will create issues. I have used oracle 12.1.0.2.0 in both primary and secondary.

NOTE: While DB instance creation make sure flash_recovery_area/fast_recovery_area location is properly set, else all archive log files will be saved in 'ORACLE_HOME/RDBMS/'.

 

STEP 2

IN PRIMARY SERVER

Creating a directory in primary and auxiliary servers. to keep all your backup_files, password files and pfiles we will require to operate. I have used 'C:\tmp\backup_files'.

Note try to keep the location same in auxiliary server as well. Though this is not mandatory, but it is helpful as you do not have to change the default backup locations for auxiliary server while operating on it.

c:\>mkdir tmp\backup_files

create an Oracle password file from primary DB server for the auxiliary server.

c:\> orapwd file=C:\tmp\backup_files\ORAPWGINESYS password=GINESYS entries=2.

STEP 3

Now as we have Primary and Auxiliary servers installed(not connected nor in standby mode). Let us create connections between the two.

first: modify the 'tnsnames.ora' file of the primary server. Note the tnsnames.ora file is located in the following location

C:\oracle_DB\app\product\12.1.0\dbhome_1\NETWORK\ADMIN\

i.e: ORACLE_HOME\NETWORK\ADMIN.

In case if it is not visible, please run 'netca' to make it available.

Now, Add values in tnsnames.ora file of the primary server as the following section marked in red.

Note : The DB instance in my primary server is running in port 1522 whereas in Auxiliary server it is port 1521(as I already have another DB instance in my primary server). This is not an issue as long as you mention required port numbers properly.

Now, In Listener.ora file of the Auxiliary server add the section marked red in the below screenshot. The location of Listener.ora file is 'ORACLE_HOME/NETWORK/ADMIN/'. In case it is not visible, please run 'netca'.

Now, If the tnsnames.ora file of the Primary server and Listener.ora file of the Auxiliary server is properly modified(values added) as shown in the screenshots above. You must be able to 'tnsping' and 'sqlplus' into the auxiliary server from primary server easily.

In the screenshot above, I can easily tnsping and sqlplus into auxiliary server from my primary server.

STEP 4:

In primary server, create pfile from your spfile of your primary server.

SQL> CREATE PFILE='C:\TMP\BACKUP_FILES\pfile_prmyServ.ora' from spfile;

We will require this pfile to create a spfile for the Auxiliary server, So that all parameters of Auxiliary server will remain same as our primary server. We will see this in details in later section of this document.

In case if you are using same 'SID' name, same 'Oracle_Base' and 'Oracle_Home', same Oracle versions and edition, you do not need to edit the file as all parameters are the same. But in case if some any of the mentioned points are different, we do have to make proper corrections in pfile before using it.

STEP 5:

IN AUXILIARY SERVER

Create necessary directories in the Auxiliary server.

C:\>cd C:\oracle_DB\app\product\12.1.0\dbhome_1\admin

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin>mkdir GINESYS

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin>cd GINESYS

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin\GINESYS>mkdir adump bdump cdump udump

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin\GINESYS>cd ..

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin>dir

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin>mkdir flash_recovery_area

C:\oracle_DB\app\product\12.1.0\dbhome_1\admin>cd ..

C:\oracle_DB\app\product\12.1.0\dbhome_1>cd oradata

C:\oracle_DB\app\product\12.1.0\dbhome_1\oradata>dir

C:\oracle_DB\app\product\12.1.0\dbhome_1\oradata>mkdir GINESYS

C:\oracle_DB\app\product\12.1.0\dbhome_1\oradata>

In all above steps in place of 'GINESYS', please use the name of the SID you have used.

STEP 6:

IN PRIMARY SERVER

Now, it's time to take backup of your primary server's DB. But before we start taking RMAN backups, we must take care of the following things.

The database must have force_logging enabled.

SQL>  select FORCE_LOGGING from v$database;

IF, FORCE_LOGGING is NO, then

SQL> ALTER DATABASE FORCE LOGGING;

```
Create SRL ( Standby Redo Logs):
```

You need to create SRL with the same size or greater size as of the online redo logs and also the number of SRLs should be one greater than the online redologs.

As I had 3 Redo log files of size 501MB each in my Primary DB Instance, I have created 4 STANDBY REDO LOG FILES each of size 502MB.

alter database add standby logfile 'C:\oracle_DB\app\oradata\GINESYS\stby_redo01.log' size 502M;

alter database add standby logfile 'C:\oracle_DB\app\oradata\GINESYS\stby_redo02.log' size 502M;

alter database add standby logfile 'C:\oracle_DB\app\oradata\GINESYS\stby_redo03.log' size 502M;

alter database add standby logfile 'C:\oracle_DB\app\oradata\GINESYS\stby_redo04.log' size 502M;

alter system switch logfile;




STEP 7:

IN PRIMARY SERVER

Now, it's time to take backup of your primary server's DB. Before we take backup let us define the path where the RMAN_backup files will be saved, else it will be save to its default location.

To take RMAN backup, make sure the database is in MOUNTED/OPEN mode.

c:/> rman target /

RMAN> configure channel device type disk format 'C:\TMP\BACKUP_FILES\Ginesys_backupfile_%U';

RMAN>show all; (check and confirm)

RMAN>backup database include current controlfile for standby plus archivelog;

see Annexure:' RMAN_prmy_backup_activityScript' for details of the script.




STEP 8:

IN PRIMARY SERVER

Now you have the following in 'C:\TMP\BACKUP_FILES\' of your primary server

Oracle password file for the Auxiliary server

```
pfile of primary server's DB.
```
```
RMAN backup files.
```

Now it's time to Copy-Paste these files to Auxiliary server.




STEP 9:

IN AUXILIARY SERVER

You can Keep files in any location, but for the sake of simplicity we will use 'C:\TMP\BACKUP_FILES\' in Auxiliary server.

C:\> cd tmp\backup_files

-> dir

The list of all files must come here.




STEP 10:

IN AUXILIARY SERVER

Copy paste the Oracle Password file in 'ORACLE_HOME\dbs'

C:\>copy C:\tmp\backup_files\ORAPWGINESYS C:\oracle_DB\app\product\12.1.0\dbhome_1\dbs

Create spfile of Auxiliary server from the pfile of the primary server and then start the Auxiliary DB server in nomount state. Make sure you have done the proper modification in                the pfile(if required). In our case no modification was required because all parameters were               same, except *.local_listener='LISTENER_GINESYS' which should be commented off.

You may take backup of your present spfile from Auxiliary server(just for safety sake).

C:> sqlplus / as sysdba

SQL> create pfile='C:\tmp\backup_files\pfile_auxServ.ora' from spfile;

SQL> Create spfile from pfile=' C:\tmp\backup_files\pfile_prmyServ.ora';

SQL> shutdown immediate

SQL>startup nomount;

SQL>exit

Now, it's time to run the RMAN command to duplicate database for standby

c:\> rman auxiliary /

Before you run the backup standby script below, make sure all the proper READ-WRITE permissions are available to all the backup files in Auxiliary server.

files->properties->Security Tab->Edit Option-> allow proper grants for your user.

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY BACKUP LOCATION 'C:\tmp\backup_files' NOFILENAMECHECK;

(See Annexure:' RMAN_aux_standbyDuplicate_activityScript' for details of the script )

You may receive the following Warning. We will eliminate this soon.

RMAN-05535: WARNING: All redo log files were not defined properly.

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must

be renamed

RMAN>exit

C:\>RMAN TARGET /

(Now in auxiliary server you will enter the RMAN as a target)

RMAN> recover database;

(see Annexure: 'RMAN_aux_DBrecovery_activityScript' for details of the script)

RMAN>exit

C:\>sqlplus / as sysdba;

SQL>alter database open;

SQL>alter system set log_file_name_convert='C:\oracle_DB\app\oradata\GINESYS\','C:\oracle_DB\app\oradata\GINESYS\','C:\oracle_DB\app\oradata\GINESYS\','C:\oracle_DB\app\oradata\GINESYS\' SCOPE=SPFILE;

SQL>recover standby database; => Choose The option 'AUTO'

This will also show you which next sequence of archive file is required by the Standby database.




STEP 11:

Now as the Standby restore is finished. let us run some scripts to test everything is working fine.

(See Annexure 4 for details)




-----------------------TASK 1 COMPLETE------------------

UNINSTALL/REMOVING ORACLE FROM WINDOWS

Configuring Master Repository for ODI

How to access ODI Studio on Linux

installing Oracle Data Integrator – ODI

Installing Fusion Middleware – FMW in Oracle linux

#Priveleges,Roles and Grants for a User in Oracle

#Beautifying SQLPLUS output in cmdline

Task10 – impdp / expdp

Task9 – Creating Tablespace, User and Grant priviledges

Task 8 – ERR-ins32025, ORACLE_HOME ALREADY EXIST

Task 7 : If pfile or spfile does not exist

TASK 6 : ORA-01438 error and locating related column

TASK 5 : TABLE PINNING IN BUFFER POOL

TASK 4 : Oracle Linux installation on VM

TASK 3 : Things to check after Oracle startup

TASK 3 : Database Health Report

TASK 2 : Take staspack report

Task 1 : ORACLE STANDBY DB CREATION(PASSIVE MODE) WITH BACKUP.

Share this: