sys username and password invalid

 SYS USERNAME and PASSWORD INVALID:

check select * from v$pwfile_users;

it should give value for sys user if not create password file in $ORACLE_HOME/dbs

cd  <ORACLE_HOME>/dbs

CREATE PASSWORD FILE

$ORACLE_HOME/bin/orapwd file=<OH>/dbs/orapw<sid> password=<password> entries=<max entries number>;

Using the sys user and the password which is set in the password file to logging into the dbconsole

Make sure the DB parameter REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME                                   TYPE     VALUE
------------------------------------ -----------  ------------------------------
remote_login_passwordfile       string       EXCLUSIVE

check    sqlnet.ora below enters: 

# sqlnet.ora Network Configuration File: /data/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.EXPIRE_TIME=10

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

now login with sys user it should login.

Setup glogin.sql

  

Setup glogin.sql in Oracle

Setup glogin.sql in Oracle

Let's take you want to execute some sql commands every time you login to SQL*PLUS. It would be so hard to run those commands manually whenever you start SQL*PLUS. With glogin.sql you can specify the commands you want to run every time you invoke SQL*PLUS.

Any query, script, parameter, PL/SQL code etc, you can simply put under glogin.sql file and SQL*PLUS will first execute glogin.sql before allowing you to query database.

I would like to display below message every time a user logs into the database

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 19 14:47:05 2023

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

Hello

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

Welcome, you are connected to MOMDEV database

setup glogin.sql in oracle - testdb database 

Open the glogin.sql file

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

Put below query at the end of the file

select 'Welcome, you are connected to '||name||' database' "Hello" from v$database;

Save and close. That's it, now whenever you login to SQL*PLUS, you will be greeted with a nice welcome message!

start weblogic managed server

 vi start-domain.sh

# -----------------------------------------------------------------------------------

# File Name    : start-domain.sh

# Author       : AMIR DBA

# Description  : Starts a WebLogic domain and managed server.

# Call Syntax  : ./start-domain.sh

# Notes        : Amend DOMAIN_HOME and managed server name appropriately.

# -----------------------------------------------------------------------------------

export MW_HOME=/u01/app/oracle/middleware

export DOMAIN_HOME=$MW_HOME/user_projects/domains/clusterDomain

echo "Starting AdminServer"

nohup $DOMAIN_HOME/startWebLogic.sh > /dev/null 2>&1 &

sleep 120

echo "Starting clusterServer_1"

nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh clusterServer_1 > /dev/null 2>&1 &

sleep 60

echo "Done!"

stop weblogic managed server

vi stop-domain.sh

 # -----------------------------------------------------------------------------------

# File Name    : stop-domain.sh

# Author       : AMIR DBA

# Description  : Stops a WebLogic domain and managed server.

# Call Syntax  : ./stop-domain.sh

# Notes        : Amend DOMAIN_HOME and managed server name appropriately.

# -----------------------------------------------------------------------------------

export MW_HOME=/u01/app/oracle/middleware

export DOMAIN_HOME=$MW_HOME/user_projects/domains/clusterDomain

echo "Stopping clusterServer_1"

$DOMAIN_HOME/bin/stopManagedWebLogic.sh clusterServer_1

echo "Stopping AdminServer"

$DOMAIN_HOME/bin/stopWebLogic.sh

echo "Tidy up temp files"

find $DOMAIN_HOME/servers -name "*.lok" -exec rm -f {} \;

find $DOMAIN_HOME/servers -name "*.DAT" -exec rm -f {} \;

echo "Done!"

 

What if I add data files or create tablespace in snapshot standby database Oracle 19c

Note : new tablespace or datafile will b droped if you change standby to physical database 

Check the database role. Execute below command in snapshot standby database.

SQL>  select name,open_mode,database_role,controlfile_type from v$database;

NAME OPEN_MODE    DATABASE_ROLE    CONTROL

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

PR   READ WRITE   SNAPSHOT STANDBY CURRENT

Capture the data file details with tablespace names before performing any testing.

SQL> col name for a60

SQL> select ts#,file#,name File_Name,bytes/1024/1024 File_Size_MB from v$datafile order by 1;

TS# FILE# FILE_NAME                                      FILE_SIZE_MB

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

  0     1 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF            1180

  1     3 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF             990

  2     4 D:\TESTDB\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF            440

  4     7 D:\TESTDB\SETUPS\STANDBY\DB\DR\USERS01.DBF                5

SQL> select ts#,name from v$tablespace order by 1;

TS# NAME

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

  0 SYSTEM

  1 SYSAUX

  2 UNDOTBS1

  3 TEMP

  4 USERS

Now create new tablespace TEST_FLB in snapshot standby database and add some data files in this tablespace or any existing tablespace. You can also resize existing data files or create any table.

SQL> create tablespace TEST_FLB datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g;

Tablespace created.

SQL> alter tablespace TEST_FLB add datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g;

Tablespace altered.

SQL> alter database datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' resize 3g;

Database altered.

SQL> create table customer (cust_id number(10), cust_name varchar2(14)) tablespace TEST_FLB;

Table created.

SQL> insert into customer values(1,'A');

1 row created.

SQL>  insert into customer values(2,'B');

1 row created.

SQL>  insert into customer values(3,'C');

1 row created.

SQL>  insert into customer values(4,'D');

1 row created.

SQL>  insert into customer values(5,'E');

1 row created.

SQL>  commit;

Commit complete.

SQL> select * from customer;

   CUST_ID CUST_NAME

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

         1 A

         2 B

         3 C

         4 D

         5 E

Snapshot Standby Alert Log File:

2023-09-02T20:53:49.598851+05:30

create tablespace TEST_FLB datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g

2023-09-02T20:53:51.052190+05:30

Completed: create tablespace TEST_FLB datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.dbf' size 1g

2023-09-02T20:53:53.860701+05:30

alter tablespace TEST_FLB add datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g

2023-09-02T20:53:55.967650+05:30

Completed: alter tablespace TEST_FLB add datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' size 1g

2023-09-02T20:53:57.391041+05:30

alter database datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' resize 3g

2023-09-02T20:53:59.654427+05:30

Resize operation completed for file# 2, fname D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF, old size 1048576K, new size 3145728K

Completed: alter database datafile 'D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.dbf' resize 3g

2023-09-02T20:54:26.785953+05:30

You can check the data file and tablespace details by querying below command:

SQL> col name for a60

SQL> select ts#,file#,name File_Name,bytes/1024/1024 File_Size_MB from v$datafile order by 1;

TS# FILE# FILE_NAME                                     FILE_SIZE_MB

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

  0     1 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF           1180

  1     3 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF            990

  2     4 D:\TESTDB\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF           440

  4     7 D:\TESTDB\SETUPS\STANDBY\DB\DR\USERS01.DBF               5

  6     5 D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF         1024

  6     2 D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF         3072

6 rows selected.

SQL> select ts#,name from v$tablespace order by 1;

TS# NAME

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

  0 SYSTEM

  1 SYSAUX

  2 UNDOTBS1

  3 TEMP

  4 USERS

  6 TEST_FLB

6 rows selected.

Now suppose, your testing is done and your plan is to convert back the snapshot standby database into physical standby database then perform below steps.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2147482432 bytes

Fixed Size                  9030464 bytes

Variable Size             452984832 bytes

Database Buffers         1677721600 bytes

Redo Buffers                7745536 bytes

Database mounted.

SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL

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

PR   DR             MOUNTED   SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT

SQL> alter database convert to physical standby;

Database altered.

Once you execute the above "convert to physical standby" command, it will delete the data files and drop the tablespaces created in snapshot standby database which means all the changes are discarded once you execute the "convert to physical standby" command.  

Snapshot Standby Database Alert Log File:

2023-09-02T20:58:39.261800+05:30

alter database convert to physical standby

2023-09-02T20:58:39.482013+05:30

Flashback Restore Start

Deleted file D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB02.DBF

Flashback: deleted datafile #2 in tablespace #6 of pdb #0 from control file.

Deleted file D:\TESTDB\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF

Flashback: deleted datafile #5 in tablespace #6 of pdb #0 from control file.

Flashback: dropped tablespace #6: 'TEST_FLB' of pdb #0 from the control file.

Flashback Restore Complete

Drop guaranteed restore point 

Stopping background process RVWR

2023-09-02T20:58:41.228496+05:30

Deleted Oracle managed file D:\TESTDB\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LH6NTGC1_.FLB

Deleted Oracle managed file D:\TESTDB\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LH6NTKCV_.FLB

Guaranteed restore point  dropped

2023-09-02T20:58:41.275867+05:30

.... (PID:18848): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8969]

Clearing standby activation ID 2841025717 (0xa9569cb5)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;

.... (PID:18848): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8974]

.... (PID:18848): RT: Role transition work is not done

.... (PID:18848): Redo network throttle feature is disabled at mount time

Physical Standby Database mounted.

2023-09-02T20:58:41.386798+05:30

In-memory operation on ADG is currently only supported on Engineered systems and PaaS.

inmemory_adg_enabled is turned off automatically.

Please contact our support team for EXADATA solutions

CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby

Completed: alter database convert to physical standby

You can also verify by executing below query to ensure newly created tablespace or added data files are not a part of the database now.

SQL> set lines 300 pages 3000

SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL

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

PR   DR             MOUNTED   PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY

SQL> col name for a60

SQL> select ts#,file#,name File_Name,bytes/1024/1024 File_Size_MB from v$datafile order by 1;

TS# FILE# FILE_NAME                                    FILE_SIZE_MB

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

  0     1 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSTEM01.DBF          1180

  1     3 D:\TESTDB\SETUPS\STANDBY\DB\DR\SYSAUX01.DBF           990

  2     4 D:\TESTDB\SETUPS\STANDBY\DB\DR\UNDOTBS01.DBF          440

  4     7 D:\TESTDB\SETUPS\STANDBY\DB\DR\USERS01.DBF              5

SQL> select ts#,name from v$tablespace order by 1;

TS# NAME

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

  0 SYSTEM

  1 SYSAUX

  2 UNDOTBS1

  3 TEMP

  4 USERS