Error While Restore Oracle Database - ORA-28365: wallet is not open

ORA-39083: Object type TABLE:"DBNAME" failed to create with error:
ORA-28365: wallet is not open

SQL Error: ORA-28367: wallet does not exist
28367. 0000 -  "wallet does not exist"
*Cause:    The Oracle wallet has not been created or the wallet location
           parameters in sqlnet.ora specifies an invalid wallet path.
*Action:   Verify that the WALLET_LOCATION or the ENCRYPTION_WALLET_LOCATION
           parameter is correct and that a valid wallet exists in the path
           specified

Problem : I was trying to restore the database on Oracle machine. Then it is giving above mention error. Since i resolved the problem months ago and not remember accurately, the sequence of error i faced.

Solution :

I performed below steps while fixing above problem

1. Please add below in sqlnet.ora and restarted the services

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

SQLNET.AUTHENTICATION_SERVICES= (NONE) 

--which was NTS earlier

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = C:\app\oracle\admin\<sid>\wallet)

     )

   )

SQLNET.WALLET_OVERRIDE = False

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

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

2.Confirm the follwing content should be present in tnsname.ora 

NEAORACLE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = **hostname**)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = <sid>)

    )

  )

  

3. Create folder wallet in this path (C:\app\oracle\admin\<sid>\)and give full access to ewallet file by right clicking on it properties>security

4. Execute following query in sys db

select * from v$encryption_wallet; --- here wallet_type is unknown and status  is not open

administer key management set keystore close; -- to make it wallet type as  password

administer key management set keystore open identified by admin_123;--it will create ewallet file

administer key management set key identified by admin_123 with backup;--change password then in import utility  use ENCRYPTION_PASSWORD=admin_123--use password which u set for key

5. open cmd ,set sid and execute following command to import table into existing db

   

   impdp sys/password@<sid> DIRECTORY=Source_dmp ENCRYPTION_PASSWORD=<pwd> DUMPFILE=<dumpname> TABLES=<tname> PARALLEL=8 EXCLUDE=INDEX remap_schema=<originalSchema>:<remapSchema> remap_tablespace=<originalTablespace>:<remapTablespace> exclude=statistics 

Minus 2 dates and calculate the difference in years/Months/Days separately

 If you are working in Excel and trying to write a formula to subtract 2 dates and if you need output in number of Years/Months/Days then use below formula. 

=INT((TODAY()-D3)/365.25) & " years , " & INT(MOD((TODAY()-D3)/365.25|1)*12) & " months and " & INT(MOD((TODAY()-D3)/30.4375|1)*30.4375) & " days"

It will give output like below format :

4 Years, 5 months and 12 days

You can change alias or comma as per your choice

Minus/Subtract 2 dates in Excel, Result is not showing in Number of Days

I was working in excel and trying to subtract 2 dates. Minus old date from today date to find the ages of something in number of days.

But when i apply formula "=Today()-A2" in excel it is showing output not as expected.

Problem : Column formatting is not set

Solution : Right click the column and > Format > Change formatting to "Text" or "General"

SQL Server : Find Out the List of Backups taken of Database

Like Restore Audit, we can also find the list of all backup taken, Since SQL Server store the backup history in system tables, using them we can find the information regarding same.

SELECT  bs.database_name
    , bs.backup_start_date
    , bs.compressed_backup_size
    , bs.expiration_date
    , bs.name Backup_Name
    , bs.recovery_model
    , bs.server_name
    , CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Log' 
        ELSE '[unknown]' END
    , bmf.logical_device_name
    , bmf.physical_device_name
FROM 
msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE 
     bs.type = 'D' and
bs.database_name = '**FillDatabaseName**'
ORDER BY 
bs.backup_start_date DESC;

You can copy and paste above query to Query Window and replace "**FillDatabaseName**" string with database name whose backup history need to populate.

Interview Question 2 - Count of all Occurrence of status/color until it changes to another

 Reproduce table script

select * from (
select '1' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-03-01' reporting_month ,'GREEN' STATUS union all
select '1' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-01-01' reporting_month ,'GREEN' STATUS union all
select '3' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '4' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-06-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-02-01' reporting_month ,'GREEN' STATUS union all
select '6' id ,'2020-01-01' reporting_month ,'RED' STATUS  ) COMMULATIVE_SUM

Need in output , count of Status for first all occurrence of RED color (Occurrence will be end by GREEN color)

--Interview Question (SQL Query)
"Solution 1 "

select 
id
, min(NewRank)-1  
from
(
select 
id
, reporting_month
, STATUS
, case when STATUS = 'GREEN' then TRank else 99 end NewRank
, TRank
from
(
select 
DENSE_RANK() over (partition by id order by id, reporting_month desc) as TRank
, id
, reporting_month
, STATUS
from
COMMULATIVE_SUM
) d
) d2 
where 
NewRank <>99
group 
by id

"Solution 2 "
select 
id
,count(*)
from
(
select 
id,
sum(case when status = 'Red' then 0 else 1 end)
over(partition by id order by reporting_month desc) as r
from 
COMMULATIVE_SUM
)as t
where 
r=0
group by id

Interview Question 1 - Print only those ParentID which are having 1 Female Child and 1 Male Chile both

Create table in sqlserver database using below query

 

select * into test from (
select '1' p,'M' as g union all
 select '1','M' as g union all
 select '1','F' as g union all
 select '2','F' as g union all
 select '2','F' as g union all
 select '2','M' as g union all
 select '3','F' as g union all
 select '3','M' as g union all
 select '4','M' as g union all
 select '5','F' as g union all
 select '6','F' as g union all
 select '6','M' as g union all
 select '7','F' as g union all
 select '7','F') temp

Table Looks like as below:

Col1 Col2

1 M

1 M

1 F

2 F

2 F

2 M

3 F

3 M

4 M

5 F

6 F

6 M

7 F

7 F

Query 

select 

t1.p 

from (

select 

count(*) F_Count

, p 

from 

test 

where 

g = 'F' group by p

)t1 

join

(

select 

count(*) M_Count

, p 

from 

test 

where 

g = 'M' group by p

) t2 on t1.p = t2.p

where 

F_Count = 1 and M_Count =1 

Query 2

select distinct

    p

from

    (

    select

        p,

        g,

        count(*) over (partition by p) count,

        count(case when g='M' then 1 end)

            over (partition by p) male,

        count(case when g='F' then 1 end)

            over (partition by p) female

    from

        test

    )as t

where

    t.male=1

    and

    t.female=1