Changing dataguard mode

 Changing Dataguard mode from Maximum performance mode to maximum availability

alter system set log_archive_dest_2='SERVICE=oradb_s2 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 REOPEN=50 DB_UNIQUE_NAME=oradb_s2';

SERVICE=oradb_s2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_s2

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 50 seconds.

Note : Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.

Now, shutdown database :

STARTUP MOUNT

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

alter database open;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

alter system dump logfile '/u01/app/oracle/fra/ORADB_S2/onlinelog/o1_mf_5_ho2pm6m3_.log' validate;

PR00 (PID:16647): Media Recovery Log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc

Errors with log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc

PR00 (PID:16647): MRP0: Background Media Recovery terminated with error 328

2022-01-27T16:58:26.816034+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_16647.trc:

ORA-00328: archived log ends at change 3830620, need later change 4142985

ORA-00334: archived log: '/u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc'

PR00 (PID:16647): Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

col name for a80

col thread# for 99

col sequence# for 9999

col archived for a5

col status for a10

select name, thread#, sequence#, archived, applied, status from v$archived_log where 4142985 between FIRST_CHANGE# and NEXT_CHANGE#;

select name, thread#, sequence#, archived, applied, status from v$archived_log where 3830620 between FIRST_CHANGE# and NEXT_CHANGE#;

The standby database is not-in-sync after converting maximum performance mode to maximum availability with the above error

Lets do roll forward for this , as we have no solution and will see the result..

what happens at DR side if space is exhausted during tablespace creation at primary side

oradb--> primary db

oradb_s2 -->Standby db

SQL> CREATE BIGFILE TABLESPACE TESTTBS2 DATAFILE '/backup/testtbs02.dbf' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Tablespace created.

SQL> show parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      oradb.localdomain     

The below is the alert log from DR side, when space is exhausted at DR side while tablespace creation at primary side. 

WARNING: File being created with same name as in Primary

Existing file may be overwritten

2022-01-07T22:09:20.122725+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-27072: File I/O error

Additional information: 4

Additional information: 12288

Additional information: 237568

2022-01-07T22:09:20.179041+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-19502: write error on file "/backup/testtbs02.dbf", block number 12288 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 12288

Additional information: 237568

File #14 added to control file as 'UNNAMED00014'.

Originally created as:

'/backup/testtbs02.dbf'

Recovery was unable to create the file as:

'/backup/testtbs02.dbf'

PR00 (PID:4229): MRP0: Background Media Recovery terminated with error 1274

2022-01-07T22:09:20.343651+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'

PR00 (PID:4229): Managed Standby Recovery not using Real Time Apply

2022-01-07T22:09:22.638703+04:00

Recovery interrupted!

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

Recovered data files to a consistent state at change 3262529

stopping change tracking

2022-01-07T22:09:23.132280+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'

2022-01-07T22:09:23.184841+04:00

Background Media Recovery process shutdown (oradb_s2)

Again if we forcefully starting the MRP, it is terminating , below is the log from alert log.

alter database recover managed standby database disconnect

2022-01-07T22:17:03.094994+04:00

Attempt to start background Managed Standby Recovery process (oradb_s2)

Starting background process MRP0

2022-01-07T22:17:03.116141+04:00

MRP0 started with pid=60, OS id=5031

2022-01-07T22:17:03.118382+04:00

Background Managed Standby Recovery process started (oradb_s2)

2022-01-07T22:17:08.146408+04:00

 Started logmerger process

2022-01-07T22:17:08.164904+04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

PR00 (PID:5037): Managed Standby Recovery starting Real Time Apply

2022-01-07T22:17:08.216985+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:

ORA-01186: file 14 failed verification tests

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

2022-01-07T22:17:08.217231+04:00

File 14 not verified due to error ORA-01157

2022-01-07T22:17:08.218311+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:

ORA-01157: cannot identify/lock data file 203 - see DBWR trace file

ORA-01110: data file 203: '/u01/app/oracle/oradata/ORADB/pdb1/temp01.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-01-07T22:17:08.431502+04:00

max_pdb is 3

PR00 (PID:5037): MRP0: Background Media Recovery terminated with error 1111

2022-01-07T22:17:08.462884+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_5037.trc:

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

PR00 (PID:5037): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2022-01-07T22:17:08.626152+04:00

Recovery Slave PR00 previously exited with exception 1111

2022-01-07T22:17:08.674098+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_mrp0_5031.trc:

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

2022-01-07T22:17:08.674347+04:00

Background Media Recovery process shutdown (oradb_s2)

2022-01-07T22:17:09.182195+04:00

Completed: alter database recover managed standby database disconnect

DATAGUARD

 DATAGUARD

select name,db_unique_name,open_mode,log_mode,database_role from v$database;

alter database recover managed standby database disconnect;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where STATUS='IDLE';

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where PROCESS='MRP0';

set lines 555

col DEST_NAME for a30

 col DESTINATION for a30

col ERROR for a30

 select INST_ID,DEST_ID,DEST_NAME,DESTINATION,ERROR,STATUS from gv$archive_dest where DESTINATION IS NOT NULL;

RMAN

 RMAN:

=========

RMAN Retention Policy

The RMAN retention policy determines how long backups are kept for and how many copies are retained.

The retention policy can be defined in terms of:

• REDUNDANCY - number of copies to retain
• RECOVERY WINDOW - number of days to retain backups

The retention policy is configured by the RETENTION POLICY parameter. The default value is

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; 

To specify a recovery window of 1 day use:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS; 

Backup files can be marked as expired or obsolete.

• Expired - RMAN has performed a crosscheck and the file cannot be found
• Obsolete - based on the retention policy the file is not needed for recovery

The backup retention policy only applies to full or level 0 datafile and control file backups.

The retention policy does not directly affect archived redo logs and incremental level 1 backups. These files become obsolete when no full backups exist that need them.

Datafile backup sets cannot be deleted until all datafile backups within the backup set are obsolete.

Obsolete backups can be identified using the REPORT OBSOLETE command and deleted using the DELETE OBSOLETE command.

The retention policy can also be set to NONE

RMAN> CONFIGURE RETENTION POLICY TO NONE;

To revert to the default value use:

RMAN> CONFIGURE RETENTION POLICY CLEAR;

RMAN> crosscheck backup;

using channel ORA_DISK_1

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fra/ORADB/autobackup/2022_01_07/o1_mf_s_1093368491_jxjhzcyy_.bkp RECID=10 STAMP=1093368491

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/media/sf_DR_TEST/ORADB_20220107_11_1_FULL RECID=11 STAMP=1093370071

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/media/sf_DR_TEST/ORADB_20220107_12_1_FULL RECID=12 STAMP=1093370127

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/media/sf_DR_TEST/ORADB_20220107_13_1_FULL RECID=13 STAMP=1093370154

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fra/ORADB/autobackup/2022_01_07/o1_mf_s_1093370178_jxjkn50v_.bkp RECID=14 STAMP=1093370181

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/media/sf_DR_TEST/ORADB_20220107_15_1_ARCHIVE RECID=15 STAMP=1093370187

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/media/sf_DR_TEST/ORADB_20220107_16_1_CONTROL RECID=16 STAMP=1093370199

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fra/ORADB/autobackup/2022_01_07/o1_mf_s_1093370201_jxjknsmo_.bkp RECID=17 STAMP=1093370201

Crosschecked 8 objects

RMAN Compression

How does RMAN compression works ?

Goal of this document is to give you a Complete Understanding of

1. Null compression

2. Unused block compression

3. Binary compression

ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

 ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

You can encounter this error if your Oracle Database machine was suddenly stopped without proper shutdown command due to some unavoidable problem like Power Failure, Power cable issue, etc.

-bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 2 22:49:25 2022

Version 19.5.0.0.0

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  281016480 bytes

Fixed Size                  8882336 bytes

Variable Size             218103808 bytes

Database Buffers           50331648 bytes

Redo Buffers                3698688 bytes

Database mounted.

ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/testdb/redo02.log'

SQL> select status from v$instance;

STATUS

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

MOUNTED

2.Check the status of log file 2 in v$log & v$logfile and you can notice log group 2 is the current logfile.

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID

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

         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log                    NO           0

         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log                    NO           0

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID

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

         2          1        104   52428800        512          1 NO  CURRENT                 479386 02-JAN-22   9.2954E+18                    0

         1          1        103   52428800        512          1 NO  INACTIVE                479343 02-JAN-22       479386 02-JAN-22          0

3.Try to clear the current log file most likely it will fail as group log 2 is the current logfile.

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance testdb (thread 1)

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/testdb/redo02.log'

4.So we will try to perform manual recovery on the database, Please note since Group log 2 is current it will be not archived, and the requested archive log sequence 149 is the current redo logfile, and hence I have provided manually redo log 2 file location. But even after “Media recovery complete” While trying to open DB, I encounter an ORA-00600 error.

SQL> recover database until cancel;

ORA-00279: change 479386 generated at 01/02/2022 00:06:42 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TESTDB/archivelog/2022_01_02/o1_mf_1_104_%u_.arc

ORA-00280: change 479386 for thread 1 is in sequence #104

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/testdb/redo02.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

-bash-4.4$ ps -ef | grep pmon

  oracle  1309     1   0 22:49:47 ?           0:00 ora_pmon_testdb

-bash-4.4$

Manual DB creation

 Manual Oracle database creation 19c on Oracle-Solaris_11.4

SunOS kolsol.localdomain 5.11 11.4.0.15.0 i86pc i386 i86pc

If we don't have an option to use GUI mode dbca, then we can create database manually.  we can use dbca in silent mode also, but this is for our knowledge , that how the oracle is using internal processes (.bsq files) to create a database when we issue the command " CREATE DATABASE"

BSQ file is read by the instance and is used to create the database system tablespace and all of the tables, clusters, indexes, sequences, and initial data required by the kernel code to store and maintain the metadata for user-created objects.

Below are the prerequisites to create a database :

1.Create the required directories.

mkdir -p /u01/app/oracle/admin/testdb/adump

mkdir -p /u01/app/oracle/oradata/testdb/

mkdir -p /u01/app/oracle/fast_recovery_area 

chmod 655 /u01/app/oracle/admin/testdb/adump

chmod 655 /u01/app/oracle/oradata/testdb/

chmod 655 /u01/app/oracle/fast_recovery_area

2. create the pfile in ORACLE_HOME/dbs location with name inittestdb.ora

*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'

*.audit_sys_operations=TRUE

*.audit_trail='db'

*.compatible='19.5.0.0.0'

*.control_files='/u01/app/oracle/oradata/testdb/control01.ctl','/u01/app/oracle/control02.ctl'

*.db_block_size=8192

*.db_domain='localdomain'

*.db_name='testdb'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4560m

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_format='%t_%s_%r.arc'

*.processes=300

*.recyclebin='OFF'

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

-bash-4.4$ export ORACLE_SID=testdb

-bash-4.4$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

-bash-4.4$ sqlplus / as sysdba

-bash: sqlplus: command not found

-bash-4.4$ export PATH=$PATH:$ORACLE_HOME/bin

-bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 17:01:48 2022

Version 19.5.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  281016480 bytes

Fixed Size                  8882336 bytes

Variable Size             218103808 bytes

Database Buffers           50331648 bytes

Redo Buffers                3698688 bytes

SQL>

check the alert log is created or not, 

/u01/app/oracle/diag/rdbms/testdb/testdb/trace

-bash-4.4$ ls -ltr alert_testdb.log

-rw-r-----   1 oracle   oinstall    6669 Jan  1 17:03 alert_testdb.log

it is created

from alert log , we can find the below details

ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1

System name:    SunOS

Node name:      kolsol.localdomain

Release:        5.11

Version:        11.4.0.15.0

Machine:        i86pc

Using parameter settings in server-side pfile /u01/app/oracle/product/19.0.0/dbhome_1/dbs/inittestdb.ora

After starting the database in nomount mode, then create the database now

SQL> CREATE DATABASE testdb

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 50

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

UNDO TABLESPACE undotbs1

DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'

  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  SIZE 200M;

CREATE DATABASE testdb

*

ERROR at line 1:

ORA-01518: CREATE DATABASE must specify more than one log file

here we can see that , we require atleast two log files to create a database, means , one is for writing and another is for archiving . 

lets add one more log file and create the database

SQL> CREATE DATABASE testdb

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,

GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 50

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

UNDO TABLESPACE undotbs1

DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'

SIZE 200M;

  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  CREATE DATABASE testdb

*

ERROR at line 1:

ORA-01501: CREATE DATABASE failed

ORA-00200: control file could not be created

ORA-00202: control file: '/u01/app/oracle/oradata/testdb/control01.ctl'

ORA-27038: created file already exists

Additional information: 1

here in the first try , the control file and redolog files are already created, lets remove those files, and then try

SQL> startup nomount

ORACLE instance started.

Total System Global Area  281016480 bytes

Fixed Size                  8882336 bytes

Variable Size             218103808 bytes

Database Buffers           50331648 bytes

Redo Buffers                3698688 bytes

SQL> CREATE DATABASE testdb

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,

GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 50

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

UNDO TABLESPACE undotbs1

DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'

SIZE 200M;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18

Database created.

from alert log we can see db creation with some .bsq files

processing ?/rdbms/admin/dcore.bsq

create tablespace SYSTEM datafile  '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

  default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online

2022-01-01T17:18:00.134237+05:30

Endian type of dictionary set to little

Completed: create tablespace SYSTEM datafile  '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

  default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online

create rollback segment SYSTEM tablespace SYSTEM

  storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

  storage (initial 50K next 50K)

alter tablespace system force logging

Completed: alter tablespace system force logging

2022-01-01T17:18:03.687898+05:30

processing ?/rdbms/admin/dsqlddl.bsq

processing ?/rdbms/admin/dmanage.bsq

CREATE TABLESPACE sysaux DATAFILE  '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING

2022-01-01T17:18:07.513338+05:30

Completed: CREATE TABLESPACE sysaux DATAFILE  '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING

alter tablespace system default compress for all operations

Completed: alter tablespace system default compress for all operations

alter tablespace sysaux default compress for all operations

Completed: alter tablespace sysaux default compress for all operations

2022-01-01T17:18:07.676503+05:30

processing ?/rdbms/admin/dplsql.bsq

processing ?/rdbms/admin/dtxnspc.bsq

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/testdb/undotbs01.dbf'

SIZE 200M

2022-01-01T17:18:15.295970+05:30

[1543] Successfully onlined Undo Tablespace 2.

Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/testdb/undotbs01.dbf'

SIZE 200M

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE  '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

Completed: CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE  '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"

CREATE  TABLESPACE "USERS" DATAFILE  '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on

 SEGMENT SPACE MANAGEMENT AUTO

2022-01-01T17:18:18.232890+05:30

2022-01-01T17:18:18.232890+05:30

Completed: CREATE  TABLESPACE "USERS" DATAFILE  '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on

 SEGMENT SPACE MANAGEMENT AUTO

ALTER DATABASE DEFAULT TABLESPACE "USERS"

Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"

2022-01-01T17:18:18.434077+05:30

processing ?/rdbms/admin/dfmap.bsq

processing ?/rdbms/admin/denv.bsq

processing ?/rdbms/admin/drac.bsq

processing ?/rdbms/admin/dsec.bsq

2022-01-01T17:18:21.857237+05:30

processing ?/rdbms/admin/doptim.bsq

processing ?/rdbms/admin/dobj.bsq

2022-01-01T17:18:23.372012+05:30

processing ?/rdbms/admin/djava.bsq

processing ?/rdbms/admin/dpart.bsq

processing ?/rdbms/admin/drep.bsq

2022-01-01T17:18:26.086262+05:30

processing ?/rdbms/admin/daw.bsq

processing ?/rdbms/admin/dsummgt.bsq

2022-01-01T17:18:27.439197+05:30

processing ?/rdbms/admin/dtools.bsq

processing ?/rdbms/admin/dexttab.bsq

processing ?/rdbms/admin/ddm.bsq

2022-01-01T17:18:28.683148+05:30

processing ?/rdbms/admin/dlmnr.bsq

processing ?/rdbms/admin/ddst.bsq

processing ?/rdbms/admin/dfba.bsq

2022-01-01T17:18:29.786777+05:30

processing ?/rdbms/admin/dpstdy.bsq

processing ?/rdbms/admin/drupg.bsq

processing ?/rdbms/admin/dtlog.bsq

processing ?/rdbms/admin/dmisc.bsq

processing ?/rdbms/admin/dhcs.bsq

Successfully created internal service SYS$BACKGROUND at open

Successfully created internal service SYS$USERS at open

Now, check in the db

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

TESTDB    READ WRITE

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

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

testdb           OPEN

POST STEPS:

Below post scripts create database data dictionary views which is require to fully funtional database creation.

catalog.sql

catproc.sql

pupbld.sql

it creates default dictionary views, we have the dictionaries like dba_users, but accessing views are not available.

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

the below pupbld.sql run as system user.

@?/sqlplus/admin/pupbld.sql

generate password file

bash-4.4$ orapwd file=orapwdtestdb password=oracle321! force=y entries=10

OPW-00029: Password complexity failed for SYS user : Password must not be too simple.

bash-4.4$ orapwd file=orapwdtestdb password=oracle$321! force=y entries=10

OPW-00029: Password complexity failed for SYS user : Password must not be too simple.

by using format=12 , password is created with simple string

orapwd file=orapwdtestdb password=oracle force=y format=12 entries=10 

-rw-r-----   1 oracle   oinstall    2048 Jan  1 18:58 orapwtestdb

Now create spfile

bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 19:00:06 2022

Version 19.5.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

SQL> show parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      testdb.localdomain

SQL>

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string

SQL> create spfile from pfile;

File created.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  281016480 bytes

Fixed Size                  8882336 bytes

Variable Size             218103808 bytes

Database Buffers           50331648 bytes

Redo Buffers                3698688 bytes

Database mounted.

Database opened.

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/19.0.0

                                                 /dbhome_1/dbs/spfiletestdb.ora

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

allow_group_access_to_sga            boolean     FALSE

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     TRUE

sga_max_size                         big integer 268M

sga_min_size                         big integer 0

sga_target                           big integer 0

unified_audit_sga_queue_size         integer     1048576

SQL>

SQL> show parameter pga

NAME                                 TYPE        VALUE

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

pga_aggregate_limit                  big integer 2G

pga_aggregate_target                 big integer 10M

I didnot mentioned the SGA and PGA target in the pfile, the above are the values it is created with

Now the database is ready, this is for testing purpose only.