How to enable ARCHIVELOG MODE in Oracle
Unknown
shutdown database
SQL> shutdown immediate;
startup database in mount mode
SQL> startup mount;
Change DB log mode
SQL> alter database archivelog;
Open DB for normal use
SQL> alter database open;
check archivelog status
SQL> archive log list
Default Archive log destination.
1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.
2. If your not using FRA then all files created in $ORACLE_HOME/database
We can check database archivelog mode through below query
SQL> select log_mode from v$database;
or
SQL> archive log list
SQL> shutdown immediate;
startup database in mount mode
SQL> startup mount;
Change DB log mode
SQL> alter database archivelog;
Open DB for normal use
SQL> alter database open;
check archivelog status
SQL> archive log list
Default Archive log destination.
1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.
2. If your not using FRA then all files created in $ORACLE_HOME/database
We can check database archivelog mode through below query
SQL> select log_mode from v$database;
or
SQL> archive log list
8:55 AM
ORACLE
Standby database start tips for Oracle database
Unknown
# sqlplus '/as sysdba'
Cheking ps -ef|grep ora -- that oracle processes has been started
On source db run :
SQL>alter system switch logfile;
Cheking Alert log file for successfully starting Oeracle.
Starting standby database with activation
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup mount
SQL> shutdown immediate
SQL> startup
| SQL> startup nomount |
| SQL> alter database mount standby database; |
Cheking ps -ef|grep ora -- that oracle processes has been started
On source db run :
SQL>alter system switch logfile;
Starting standby database with activation
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup mount
SQL> shutdown immediate
SQL> startup
8:53 AM
ORACLE
How to add a tempfile in Oracle
Unknown
To add a tempfile into the mounted opened Oracle db:
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata1/temp_1/temp.data1' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata2/temp_2/temp.data2' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata3/temp_3/temp.data3' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata4/temp_4/temp.data4' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata1/temp_1/temp.data1' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata2/temp_2/temp.data2' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata3/temp_3/temp.data3' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata4/temp_4/temp.data4' size 32000M reuse;
8:51 AM
ORACLE
How to list all schemas from oracle database
Unknown
In Oracle the entity "user" and entity "schema" are same if provided user has created at least one object in it.
Login in sqlplus as sys
To list all schemas:
SQL> SELECT username FROM all_users ORDER BY username;
SQL> Select username from dba_users;
Will give you list of all users but all users may not have created objects in it. So we can not call them as schema.
SQL> Select distinct owner from dba_objects;
Will give you the list of schemas available.
Using some conditions
SQL> SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER' ;
Login in sqlplus as sys
To list all schemas:
SQL> SELECT username FROM all_users ORDER BY username;
SQL> Select username from dba_users;
Will give you list of all users but all users may not have created objects in it. So we can not call them as schema.
SQL> Select distinct owner from dba_objects;
Will give you the list of schemas available.
Using some conditions
SQL> SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER' ;
or
select distinct(owner) from dba_objects where owner in ( SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER');
8:49 AM
ORACLE
Oracle select - how to check all tables from schema
Unknown
Select how to check all tables from schema with table sizes.
SELECT /*+ PARALLEL(dba_segments,16) */ owner,
segment_name,
segment_type,
tablespace_name,
round(SUM (bytes) / POWER (2, 20), 1) size_gb
FROM dba_segments
WHERE owner IN ('<SCHEMA OWNER NAME>')GROUP BY owner, segment_name, segment_type, tablespace_name
order by segment_name;segment_name,
segment_type,
tablespace_name,
round(SUM (bytes) / POWER (2, 20), 1) size_gb
FROM dba_segments
WHERE owner IN ('<SCHEMA OWNER NAME>')GROUP BY owner, segment_name, segment_type, tablespace_name
8:48 AM
ORACLE
Applying redolog files in Oracle
Unknown
procedure for applying redolog files in Oracle db.
sqlplus '/as sysdba'
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
Choose files for applying after last file enter CANCEL
ALTER DATABASE OPEN RESETLOGS;
Checking that resetlogs were applyed correctly
SELECT * FROM "any_small_table";
SHUTDOWN IMMEDIATE;
STARTUP;
5:50 PM
ORACLE
,
SAP Beginners
How to set date time output in sqlplus
Unknown
Sometimes it is useful to check report runtime via sqlplus when you have a date timestamp . It can be set by set time on command.thru sqlplus command prompt .
SQL> set time on
11:33:30 SQL> set sqlprompt >
SQL> set time on
11:30:01 SQL>
11:33:30 SQL>
also, you may omit SQL prompt perfix entry
11:33:30 SQL> set sqlprompt >
11:33:37 >
11:33:38 >
5:45 PM
ORACLE
Deleting a scheduled Background job in SAP
Unknown
To delete a job:
Go to Transaction SM37. Select a job (or jobs) from the Select Background Jobs screen. In the Job Overview, mark the job or jobs you want to delete by checking the box to the left of the job name. Choose Job --> Delete.
Deleting Jobs That Have Dependent Jobs:
If you delete a job that must be processed before another job can be started, the dependent job can no longer be started. The system will inform you of any such existing dependent, or successor, jobs.
Go to Transaction SM37. Select a job (or jobs) from the Select Background Jobs screen. In the Job Overview, mark the job or jobs you want to delete by checking the box to the left of the job name. Choose Job --> Delete.
Deleting Jobs That Have Dependent Jobs:
If you delete a job that must be processed before another job can be started, the dependent job can no longer be started. The system will inform you of any such existing dependent, or successor, jobs.
5:29 PM
BASIS
,
BASIS BASIC
,
SAP Beginners
Subscribe to:
Posts
(
Atom
)
