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

INSTALL AND CONFIGURE HTTPD

INSTALL AND CONFIGURE HTTPD, ENABLE PUT METHOD:

 

Here we will see how to install HTTPD(apache server) and enable PUT Method for curl to upload file.

IN this doc we are installing  linux server my server is Linux OEL 7

[root@server ~]# cat /etc/os-release

NOW, we are connecting to root user to install HTTPD

Su - root

yum install httpd ( it will download and install httpd)

systemctl enable httpd (it will enable httpd)

systemctl start httpd (it will start httpd)

check the status of server

 

 

Now check with your  URL http://IP or Hostname

e.g. http://192.168.50.90

 

NOTE : to access the http server from different server we need to enable a port 80

List out the port which are open in my server

firewall-cmd --list-all

First iam adding a port in my server then need to restart firewall for reflect of my port

 

firewall-cmd --add-port=80/tcp –permanent

firewall-cmd –reload

firewall-cmd --list-all  

 

Creating a initial file basic index.html page

When we install httpd it will create html output directory in /var/www/html , iam creating my index.html file under /var/www/html because when apache trying to open page it look under html directory

Just copy paste below index.html file body  and save it.

·       Create index.html file

Cd /var/www/html

Vi index.html

Add entries

<html>

<head>

<title>Welcome to my test site!</title>

</head>

<body>

This site is for testing purposes only

</body>

</html>

 

Now check with your URL it should give a index page which we created

Here we completed the installation of HTTPD ( apache) server,

Now, I am starting to enable put method for curl to upload files in my server

 

Here First iam creating a password file for user apache

·       Create password file

htpasswd -c /home/html/.htpasswd apache

when we run a htpasswd it will ask promt two times for password, please make sure to keep this password save as it require when we upload a file with curl

I am creating my password file I location  /home/html you can create any location but make sure this password file location will b update in httpd configuration file

Which will see in below steps

Here we are  doing changes in httpd configuration file please make according to your environment in below lines

·       Make changes in httpd configuration file –

/etc/httpd/conf

Vi httpd.conf

 

1)       

#

# Deny access to the entirety of your server's filesystem. You must

# explicitly permit access to web content directories in other

# <Directory> blocks below.

#

<Directory />

    AllowOverride none

    Require all denied

</Directory>

 

2)

 

#

# Relax access to content within /var/www.

#

<Directory "/var/www">

    AllowOverride None

    # Allow open access:

    Require all granted

</Directory>

 

3)

Enable DAV

 

    #

    # Controls who can get stuff from this server.

    #

   #Require all granted

        Dav On

        AuthType Basic

        AuthUserFile "/home/html /.htpasswd"

        AuthName "Staging Area"

        Require valid-user

 

</Directory>

 

4)

# The following lines prevent .htaccess and .htpasswd files from being

# viewed by Web clients.

#

<Files ".ht*">

    Require all denied

</Files>

 

5)

 

Add –

    #

    MIMEMagicFile conf/magic

</IfModule>

 

<IfModule mod_dav_fs.c>

    # Location of the WebDAV lock database.

    DAVLockDB /var/lib/dav/lockdb

</IfModule>

 

 

Give apache user permission

 

·       Give permission :

Apache permission to /var/www/html and /var/lib/dav

Chown apache:apache /var/www/html

Chown apache:apache /var/lib/dav

 

Here I am setting  SELINUX to permissive after setting this it will required boot server for reflect this is important if not your see a permission error

·       Make  SELINUX = permissive in /etc/selinux config file

SELINUX=permissive

 

Now restart the httpd and run the curl command

·       Bounce httpd

Systemctl restart httpd

·       Now run curl command

curl --user apache:XXXX  --upload-file filename   http://HOSTIP

note : make sure credential must b correct which create is htpasswd file